[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, 2023 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, 2023 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, 2023 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!

[Bil 6]

Hello world,

 

Thank you so much for the great work drjdpowell !! Wonderful library !

 

I have a question concerning the possibility to get all the tables of a database : is it possible to list all the tables of a database (here an array containing [table_1, table_80, Table_9012]) with the library ?

It seems the ".tables" request in SQLite is not understood:

 

I tried a lot of other request, ("show tables", "SELECT * FROM information_schema.tables;", etc), same result.

I'm working with LabVIEW 23 Q3. Does somebody know if there's a solution for my problem by using this library ?

 

BR !
Bilsix

[drjdpowell]

You're looking for the sqlite-schema table: https://www.sqlite.org/schematab.html

[Bil 6]

Thanks for your answer Dr. James !

In fact, the database is already created, I only would like to read the tables already created in the database, I don't need to create a new table.

 

Do you confirm sqlite_schema is the request I need to do so ?

 

Thx & KR !

 

Bilsix

 

 

[drjdpowell]

Why don't you just try it?  Open your SQLite viewer app if choice and execute "SELECT * FROM sqlite_schema"

[Bil 6]

It works...
Thank you so much, I didn't understand this table contains all the info I wanted

 

Thank you again !

KR,

 

Bilsix