Access violation when re-opening SQL query with parameters

[Chris Osborne]

We have an application that ran perfectly well when compiled with an earlier version of Lazarus (don't know which version, but it used Free Pascal 2.6.4).

The machine that we compiled it on died, and we installed Lazarus 2.2.6 on a new computer. Since then we have a problem with the database queries (we are using Firebird 3.0.10 on Windows 10, the older executable still works fine with this).

This results in an access violation if we try to re-open a parameterised SQL query.
I have a short program that reproduces this error.
Note that the first 'open' works fine, but the second causes a crash.

I can make it work by clearing & re-adding the SQL & re-preparing the query before the second 'open', but that kind of defeats the point of having a parameterised query.

The problem remains if we use the latest Lazarus release.

Any help would be greatly appreciated.

N.B. just failed to post because I added the SQL file to create the dummy test table, this is apparently not allowed.
So here it is in line:

CREATE TABLE DBTEST (
        NAME          VARCHAR(40) NOT NULL,
        COLOUR      VARCHAR(40)
);
INSERT INTO DBTEST VALUES( 'APPLE', 'GREEN' );
INSERT INTO DBTEST VALUES( 'SAUSAGE', 'BROWN' );

[TRon]

Hi Chris and welcome.

It is good that you have been able to isolate things but the provided information is a bit too scarce 

Please provide a standalone small test example that is complete and able to compile out of the box.

Also note that having a interbase server is not usually something that is around for testing (at least I do not have one).

At first quick glance I never see a proper close down of things. That is probably because the posted example is merely a snippet of a complete (test/example) project. It is impossible to tell without having the whole picture to be bale to look at and that includes the configuration/modification of your visual components (lfm file).

See also wiki about publishing a project

[Chris Osborne]

Thanks!,
I was trying to keep down the posting size, but I have attached the entire demonstration project below.

The call stack shows that the failure occurs in IBCONNECTION but it might be good to know that the problem doesn't happen with other databases.

This is indeed a tiny fraction of a fairly complex program that uses parameterised queries in many places (It's a monitoring system for semiconductors undergoing environmental testing). Changing databases would be a huge problem! We need to be able to change the parameters to queries, and the official way to do this is to close the query, change parameters, and re-open as in the attached example. This worked fine with our previous (out of date) copy of Lazarus. Is there an archive of old versions somewhere? I couldn't find it on the site. Then we could go back to the old version (not the best solution) if we really have to.

I can't believe that I'm the first person to find this problem.

[TRon]

PS to everyone who reads it: is it me or is the forum site slow as molasses the last few days ? It takes an average of 5 to 25 seconds for the login window to popup, never mind a refresh (which sometimes is even worse).

I was trying to keep down the posting size, but I have attached the entire demonstration project below.

Thank you. I'll have a look at it later (*) and hopefully am able to reproduce (though I suspect the issue might be something specific to interbase).

(*) later:
I was unable to reproduce with using last Lazarus release and sqlite.

The call stack shows that the failure occurs in IBCONNECTION but it might be good to know that the problem doesn't happen with other databases.

See above. That seems indeed to indicate that something goes wrong with(in) IBConnection.

I have no idea if it is possible to further debug the connection other than trying a debug build so that you might be able to get a more decent/informative trace.

Is there an archive of old versions somewhere? I couldn't find it on the site. Then we could go back to the old version (not the best solution) if we really have to.

Yes, on sourceforge here and in case you are looking for a specific version of Free Pascal (the used compiler) then that can be found here.

It is also not too difficult to download the source-code of Lazarus,  check-out the correct version with git (or download the required tag directly from gitlab) and build Lazarus manually. See also wiki entry

I can't believe that I'm the first person to find this problem.

Well, if you were not able to locate something similar in these forums then most likely chances are that you probably am ? Do note that I hardly know anything about interbase. Also in case someone is around that has the experience.

[Chris Osborne]

Thanks for looking - it must just be a problem with the Interbase connection. At least we can always use an old version of Lazarus/fpc if we really have to. We could change the database but there are a lot of stored procedures (which tend to use different syntax between databases, so that might also cause trouble). I don't want to start debugging Lazarus (and the problem is probably at the fpc level anyway). So my options seem to be:
1) Go back to an old Lazarus version, probably 1.4.2 (ugh!).
2) Change database & probably have to re-write stored procedures - and other programs that extract data from the database (ugh!).
3) Add a fix-up bodge after every query close (if I copy out the SQL, clear the SQL, re-write it, and re-do the prepare, it fixes the bug) - this will slow everything down, but may be our best option.
4) Add this problem to the bug tracker & wait until someone fixes it (could take forever).
Any other ideas?

[TRon]

Any other ideas?

Not atm and not without compromising said requirements.

afaik it is a Free Pascal issue, not a Lazarus one so you should be looking at the last version of Free Pascal that did not produce the error for you and worked as expected. You could create the same test project using only Free Pascal and test that out on different versions of FPC.

Ofc. a (complete) stack trace of the crash would be helpful as well.

PS: have you already tried with (Free Pascal) trunk and/or fixes ?

[egsuh]

Hi,
Try to insert following section. Not sure whether this is the cause of the problem.

Code: Pascal  [Select][+][-]

1.     ....
2.     Close;
3.  
4.     (Transaction as TSQLTransaction).Commit;  // Or Rollback;
5.     Transaction.Active : True;
6.     Prepare;
7.  
8.     Params[0].AsString := 'SAUSAGE';
9.  

[Chris Osborne]

Thanks again for helpful suggestions.
egsuh : sadly committing or rolling back the transaction makes no difference.
TRon: Yes, I think it would be a good idea to make a version that only uses fpc & no visual components. Then I will check this against latest development fpc as well as using it to see what the last working version was. If it doesn't work with latest development version I will raise this as a bug. In the meantime I will leave the choice between going back to a working version, or fiddling the code to re-write & re-prepare the SQL every time, to the customer.

[Zvoni]

Just had a look at the source:
TSQLQuery.Close is actually a shortcut for setting the Dataset to Active:=False

For debugging-purposes: Execute a CheckActive on the Query after Closing but before assigning new parameter

Do you have any Try/Except in your source trying to catch EDatabaseError?

Have you tried leaving out the "Close"-Command?
And after setting the new Parameter a "Refresh" instead of a new "Open"?

[Chris Osborne]

Zvoni: thank you for your suggestions.

There are try blocks in the original code, but I was keeping it simple for a posted example.

Using 'Refresh' instead of 'Close' & 'Open' works! (Maybe some documentation needs updating somewhere, as this is what I was told to do when changing parameters.) This is probably my best option.

Even stranger, I can fix the program by just deleting the original 'Prepare' call. Never thought to do that before!

Obviously I'm not the only one to have found problems with the Interbase connection.
I was idly looking through 'c:\lazarus\fpc\source\packages\fcl-db\src\sqldb.pp' and found the following...

procedure TCustomSQLQuery.BeforeRefreshOpenCursor;
begin
  // This is only necessary because TIBConnection can not re-open a
  // prepared cursor. In fact this is wrong, but has never led to
  // problems because in SetActive(false) queries are always
  // unprepared. (which is also wrong, but has to be fixed later)
  if IsPrepared then with SQLConnection do
    UnPrepareStatement(Cursor);
end;

I guess this is why the 'Refresh' manages to work!
I suppose we will never get the advantages of a prepared query with new parameters until the above routine can be safely removed from the library.

[egsuh]

Well... yes.. I had some problems with prepare statement. Normally it does not do any harm.
It is interesting that "refresh" practically reopens dataset.

[Chris Osborne]

The existence of TCustomSQLQuery.BeforeRefreshOpenCursor seems to imply that this fault in TIBConnection is preventing parameterised queries from realising their potential efficiencies in EVERY database. It really needs fixing!

[TRon]

TRon: Yes, I think it would be a good idea to make a version that only uses fpc & no visual components. Then I will check this against latest development fpc as well as using it to see what the last working version was. If it doesn't work with latest development version I will raise this as a bug. In the meantime I will leave the choice between going back to a working version, or fiddling the code to re-write & re-prepare the SQL every time, to the customer.

For completeness I have attached a FPC version of your code (including a sqlite table for in case someone wants to compare against sqlite3).

When run with compiler/packages that are build with debug enabled it should be able to reproduce a complete stacktrace.

[cdbc]

Hi

Even stranger, I can fix the program by just deleting the original 'Prepare' call. Never thought to do that before!

Nowadays the "Query.Prepare" comes with its *Mandatory* "Query.UnPrepare" cousin, to be called after the use of 'Prepare'.
For example, forgetting to do this on SQLite3, results in a /Database is Busy/ Error!
I think that, given your code is a few FPC-versions old, there would be no 'UnPrepare' to be found... Maybe that's why removing the 'Prepare' solves the problem... 
Regards Benny

[Chris Osborne]

First, a big thank-you to all who helped!

Simply removing all 'prepare' calls from our source code seems to have fixed the problem (at a superficial level at least).

My priority has to be implementing the required changes to our code, rather than debugging the fpc library.
So I shall consider the case closed - it seems that the problem is known ( see comments in TCustomSQLQuery.BeforeRefreshOpenCursor ), and since this was known several versions ago I assume that fixing it was not trivial. I hope someone will tackle this in the near future!