[CR] SQLite Library

[mwebster]

I've got a weird one that I can't seem to find the answer for.  When I try to update a row in an existing table, it seems to work OK, subsequent queries show the updated data is there.  However, after I close the connection and reopen it, the old data is still there.  Is there some in-memory copy that's not getting flushed out with UPDATE?  Insert's work fine...

This is just a two-field table, SN primary unique string key and PN string.  Creating them saves them almost instantly after finalize (checking with external viewer).  Update doesn't save them where external viewer can see it, but doing a query in LabVIEW does see it until I restart the connection, as mentioned before.

I've also tried it without binding using format into string where I just have the SQLite statement say UPDATE UUT SET PN='10029-201' WHERE SN=123 but the same thing happens.

Am I missing something simple here?

[LogMAN]

Sounds like an uncommitted transaction. Make sure you have committed all transactions before closing the file. Uncommitted transactions are lost.

[IvanLis]

48 minutes ago, mwebster said:

Am I missing something simple here?

Now there is no time for experiments.
When I update record, in all projects, I use template with a restore point. SQLite is quite paranoid about data integrity.

But I am using old version of pushit SQLite Library v1.10.0.85
Although I think on new versions, this template will work.

Edited March 8 by IvanLis

[mwebster]

I originally had it wrapped in a Begin/Commit as well but removed them to see if that was part of the problem.  Same issue there, the in-memory copy had the update, but not saved to file.  Thanks for the syntax check though, I didn't see an UPDATE in the example code, so wasn't completely sure about that.   

Okay, I found it.  I was attempting an Insert originally and, if that failed, I was trying an Update.  So, for whatever reason, the Insert transaction wasn't getting closed out properly on error.  For a named transaction, when there's an error, it is doing SAVEPOINT X / ROLLBACK TO X, but this isn't closing the transaction.  I didn't get an error on this until I tried an unnamed transaction for the update, which attempt to do BEGIN IMMEDIATE / (COMMIT or ROLLBACK depending on error state).  When the update function tried to do BEGIN IMMEDIATE, the API threw an error saying you couldn't begin a transaction without closing the previous one.  I then went back to the Insert and removed the names from that (so it does BEGIN IMMEDIATE / ROLLBACK when it hits an error) and then the subsequent UPDATE call works as intended.

Not sure why ROLLBACK TO X didn't close the transaction, but that was the root cause.

Thanks again for the help....

Mike

Edited March 8 by mwebster

[LogMAN]

9 hours ago, mwebster said:

Not sure why ROLLBACK TO X didn't close the transaction, but that was the root cause.

This is explained in the SQLITE help pages: https://www.sqlite.org/lang_savepoint.html#savepoints

Quote

The ROLLBACK TO command reverts the state of the database back to what it was just after the corresponding SAVEPOINT. Note that unlike that plain ROLLBACK command (without the TO keyword) the ROLLBACK TO command does not cancel the transaction. Instead of cancelling the transaction, the ROLLBACK TO command restarts the transaction again at the beginning. All intervening SAVEPOINTs are canceled, however.

 

[drjdpowell]

Hi @mwebster,

Can you upload an example showing this problem?  Something is wrong somewhere, as a Savepoint outside of an open transaction should behave the same as Begin-Rollback, to my understanding.

[mwebster]

LabVIEW 2020.  Example.vi

I removed the savepoint hookups in the Modify UUT Info.vi to make the error come out explicitly.  If you hook those up, it fails silent.  I "fixed" it by removing the savepoint hookups in the Save UUT Info.vi so it just does a BEGIN IMMEDIATE/ROLLBACK.

SQLite Transaction failure example.zip

[mwebster]

7 hours ago, LogMAN said:

This is explained in the SQLITE help pages: https://www.sqlite.org/lang_savepoint.html#savepoints

 

I think you're right.  I was expecting it behave like RELEASE.  ROLLBACK TO doesn't commit, you're still inside the SAVEPOINT transaction.

[drjdpowell]

2 hours ago, mwebster said:

LabVIEW 2020.  Example.vi

As an aside, you should look into the Upsert clause, which allows doing INSERT or UPDATE in a single SQL statement.

Also, note that you don't need savepoints about a single transaction (all single statements are their own transaction, and either succeed or rollback automatically).

 

[drjdpowell]

Re the main issue, this seems to be my misunderstanding about how Savepoints work (as distinct from BEGIN and ROLLBACK).  Here is a relevant discussion. 

From that discussion, I see I should, instead of "ROLLBACK TO <Savepoint>" I should do "ROLLBACK TO <Savepoint>; RELEASE <Savepoint>;"  Issue 22

Edited March 9 by drjdpowell

[mwebster]

18 minutes ago, drjdpowell said:

As an aside, you should look into the Upsert clause, which allows doing INSERT or UPDATE in a single SQL statement.

Also, note that you don't need savepoints about a single transaction (all single statements are their own transaction, and either succeed or rollback automatically).

 

Yep, I was thinking exactly that after I got it working, no need to wrap single statements....

Thanks for the links!