[Solved] ZSQLProcessor Commit problem with temporary table

[naumov13]

Hello.

I found a problem with ZSQLProcessor (7.2.4-stable) and SQLite when I execute some sql-script with creating temporary table .
Without "create temporary table" all works fine. ZEOS 7.2.1-rc not reproduce this problem.

My environment:
Windows 8.1 x64
Lazarus 1.8.0 x32
FPC: 3.0.4
ZEOS version: 7.2.4-stable
Database driver: SQLite (sqlite3.dll v3.18.0 in attachment)

In the attachment is a project demonstrating that problem.
There is simple application with 1 buttons and 3 radiobuttons.
1 radiobutton - reproduce problem
2 and 3 - variants avoid problem

Database creates at button click, and deletes at application starts.
So you need restart application in order to try other radiobutton variants.

Some comments in code.

[marsupilami]

Hello,

I am sorry, I didn't respond more early. Could you ckeck if the problem still exists on Zeos 7.2.6?
Best regards,

Jan

[naumov13]

Hello,
Yes, I did checked it. The problem still exists on Zeos 7.2.6-stable

[marsupilami]

Ok - the problem is strange. It happens when starting the next transaction. Note: you should not use start transaction and commit in scripts. This can interfere with the Zeos transaction logic. Most probably strange things would happen if you encounter an error and cannot commit or rollback your transaction. Zeos still might think it is in AutoCommit mode, while there is a transaction giong on...

So - the strange thing about thi error is - when checking the extended error information, we get the messagt that this isn't an error:

[Window Title]
commitbug

[Content]
SQL Error: Error: SQL logic error or missing database
Message: not an error.

Press OK to ignore and risk data corruption.
Press Abort to kill the program.

[OK] [Abort]

I will open a ticket and see to it that I create a test in the test suites...

Edit: The ticket is: https://sourceforge.net/p/zeoslib/tickets/405/

[naumov13]

Hello.

This problem still persists.
Checked with current stable version zeos-7.2.10

When I looked under the debugger, I saw the following comment in the method TZAbstractConnection.Commit

Code: Select all

 { TODO -oEgonHugeist : Change this code sequence on 7.3! My automation idea simply is wrong! A commit vs. commitupdate(clear the cache) shouldn't be same! }
      //See: http://zeoslib.sourceforge.net/viewtopic.php?f=38&t=19800 

[marsupilami]

I reopened the ticket. Please keep an eye on that.

Best regards,

Jan

[naumov13]

I reread the entire thread more closely.
My apologies, it looks like I jumped to conclusions.

The error remains due to the fact that I am still using starting a transaction before calling TZSQLProcessor.Execute

Please explain in a little more detail why, when using TZSQLProcessor, you do not recommend opening a transaction?
Maybe TZSQLProcessor always does it itself?

[aehimself]

You can use transactions with SQLProcessor, the main point is:

in scripts

When you start a transcation, Zeos "translates" that to an SQL command and sends it to the server, e.g. "START TRANSACTION". Same goes when you do a commit, e.g. "COMMIT TRANSACTION". Now imagine it from the server side:

ZConnection.StartTransaction; // START TRANSACTION arrives to server

SQLProcessor.Execute; // Here, COMMIT TRANSACTION is included is in the script, so SQL server commits everything

At this stage, ZConnection still thinks that it is in a transaction as there was no ZConnection.Commit / .Rollback, but because the script commited the database server thinks otherwise. This just gets worse with nested transactions, as ZConnection has an internal counter on how "deep" we are in currently which can completely be messed up:

ZConnection.StartTransaction; // Zeos depth: 1

Script:
START TRANSACTION // SQL server depth: 2, Zeos depth: 1
START TRANSACTION // SQL server depth: 3, Zeos depth: 1
START TRANSACTION // SQL server depth: 4, Zeos depth: 1
START TRANSACTION // SQL server depth: 5, Zeos depth: 1

Either Remove all transactions from your scripts and use ZConnection, or set AutoCommit to True and handle all transactions from within the script. Mixing the two will disalign Zeos and the RDBMS.

[marsupilami]

I suggest handling all transactions from the Zeos side: If there is an error in the script we most probably stop processing it and don't know how many transcations were started. So if the script executes a "start transaction" and raises an error afterwards, Zeos will not know that there is an open transaction...

[naumov13]

Got it.
Thanks a lot for the detailed clarification!