Recent

Author Topic: problem with zeosdb8 and sqlite  (Read 1100 times)

calebs

  • Full Member
  • ***
  • Posts: 200
problem with zeosdb8 and sqlite
« on: January 25, 2025, 10:07:07 pm »
hello al. It seems i've had this problem before with sqlite. Don't recall if i was using zeos that time but the problem is the same.
on a tzquery store an insert statement as "insert into algo (v1,v2) values (1,2) and then tzquery.execsql
the problem is, that if in the same form i do a
"select * from algo" the last inserted row doesnt appear.
but if i close the forrm and reopen again and try the select the inserted row is correctly inserted.
So, i've opened a sqlite browser in paralell and try again.
do the insert with my app
then switch to the sqlite browser and the row is inserted
but back to my lazarus app the select doesn't list it.
but again, if i close the form and reopen there is.
I am missing something to post data to sqlite?
If i switch protocol to mysql the same code work as expected inserting and reading without opening and closing.
Thanks!

TRon

  • Hero Member
  • *****
  • Posts: 3995
Re: problem with zeosdb8 and sqlite
« Reply #1 on: January 25, 2025, 10:18:33 pm »
I don't know anything about zeosdb but as a hunch when I see SQL then I immediately think transaction and commit.
I do not have to remember anything anymore thanks to total-recall.

calebs

  • Full Member
  • ***
  • Posts: 200
Re: problem with zeosdb8 and sqlite
« Reply #2 on: January 26, 2025, 12:31:50 am »
I don't know anything about zeosdb but as a hunch when I see SQL then I immediately think transaction and commit.

thanks tron, yes i would think that too but if i use the same code but switch to mysql instead of sqlite it works without using transactions.
as a matter of fact, in zeos component is active autocommit that's what puzzles me

TRon

  • Hero Member
  • *****
  • Posts: 3995
Re: problem with zeosdb8 and sqlite
« Reply #3 on: January 26, 2025, 01:02:03 am »
Have you checked all properties so that there isn't a sneaky autocommit turned off or retained ?

As said, it is just a hunch as I am unaware of the details of zeos. Perhaps someone more experienced with zeos is able to help out.
I do not have to remember anything anymore thanks to total-recall.

Soner

  • Sr. Member
  • ****
  • Posts: 318
Re: problem with zeosdb8 and sqlite
« Reply #4 on: January 26, 2025, 01:28:34 am »
...
"select * from algo" the last inserted row doesnt appear.
but if i close the forrm and reopen again and try the select the inserted row is correctly inserted.
...
Make this:
ZConnections1.TransactIsolationLevel:=tiReadCommitted;

GAN

  • Sr. Member
  • ****
  • Posts: 378
Re: problem with zeosdb8 and sqlite
« Reply #5 on: January 26, 2025, 03:23:57 am »
Do not use INSERT INTO on a TZQuery, insted use ExecuteDirect wich is a method of TZConnection.

But if you want to use TZQuery to insert a record, check here: https://forum.lazarus.freepascal.org/index.php?topic=51917.msg382171#msg382171
Lazarus 2.0.8 FPC 3.0.4 Linux Mint Mate 19.3
Zeos 7̶.̶2̶.̶6̶ 7.1.3a-stable - Sqlite 3.32.3 - LazReport

CharlyTango

  • Jr. Member
  • **
  • Posts: 94
Re: problem with zeosdb8 and sqlite
« Reply #6 on: January 27, 2025, 10:01:05 am »
Do not use INSERT INTO on a TZQuery, insted use ExecuteDirect wich is a method of TZConnection.

But if you want to use TZQuery to insert a record, check here: https://forum.lazarus.freepascal.org/index.php?topic=51917.msg382171#msg382171

In my opinion that answer is not quite right.

calebs obvoisely wants to insert data using an INSERT statement. GAN showed an example with insert and post commands from an TZQuery object which is a complete different strategy.

https://www.freepascal.org/docs-html/fcl/sqldb/tsqlconnection.executedirect.html
I'd use ExecuteDirect (which is a command from the connection object) in some special cases only. Because the ExecSQL Method offers Parameters to form a statement. In my opinion ExecSQL ist absolutely correct.

By the way, the problem was not that the data was not written to the database, but that it was not automatically updated in the application after writing.

And Soner answered this:
Make this:
ZConnections1.TransactIsolationLevel:=tiReadCommitted;

Lazarus stable, Win32/64

jcmontherock

  • Sr. Member
  • ****
  • Posts: 278
Re: problem with zeosdb8 and sqlite
« Reply #7 on: January 27, 2025, 11:18:49 pm »
TRon is right: transaction is not mandatory in MySQL and in SQLite3 it is.
Windows 11 UTF8-64 - Lazarus 4.0RC2-64 - FPC 3.2.2

calebs

  • Full Member
  • ***
  • Posts: 200
Re: problem with zeosdb8 and sqlite
« Reply #8 on: January 28, 2025, 04:31:04 am »
I've reviewed your answers and also asked to chatgpt and it tells me that i could use commit method from zconnection. Also i've hard coded the autocommit but nothing seemed to work.
Also it tells me that sqlite handles differently transactions than mysql.
That could be the explanation for my previous problems that i've tell you before.
On the original code that i've asked here initialli, there it was a logic error. I've previously opened a query with same  connection with a select but didn't close it before do the insert (forgot completely to close the query also) and thats why when i close the form the query frees and then is commited the insert.
A newbie mistake i must accept.
Now is working with the autocommit hard-coded and the commit method from query.
Thank you all!

 

TinyPortal © 2005-2018