Jump to content

[CR] SQLite Library


Recommended Posts

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...

image.png.7e68f615c510290085c9d9b1d040b904.png

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?

Link to comment
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.

599333866_.png.4a786c484fe9f4ca7efb601cc24e8378.png

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 by IvanLis
  • Like 1
Link to comment

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 by mwebster
Link to comment
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.

 

  • Thanks 1
Link to comment
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).

 

  • Thanks 1
Link to comment

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 by drjdpowell
  • Like 2
Link to comment
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!

Link to comment
  • 1 year later...

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 ?

image.png.9b14fa6aee8728b249d763268746a2ea.png

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

image.png.acdb8688d895c894b5d9db483ce76e16.png

 

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

Link to comment

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

 

 

Link to comment

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.