SQLdb Cashing data on startup and doesnot show changed values
lazarus / 16-Databases

----------------------------------------------------------------------
mirce.vladimirov · 2016-05-18 17:14

I'm using Laz1.4.2 under windows 7, and I'm making an application
which uses MySQL over the LAN.
I use the SQLdb and  MySQLConnection50.

On startup the main form shows up and waits  for the user to take
action.
In the meantime From another program (on a different computer in the
LAN) a record in a table is changed. We use the command prompt on 3
cmputers to access the table and make sure that the record has been
changed , And we all see that the record was successefully changed.
But when I do the "select..." query in this new program, which was
waiting on the main form, I see the same record as it was before the
change, like it was never changed !?
In two cases it will load the correct values, changed ones :
1. If my app is restarted, it loads the correct values,  those that
have been changed by other users and checked by 3 of us.
2. If my app does a insert or delete and
SQLtransaction.commitretaining (to any table, no matter which one),
the correct values are loaded after that, those that have been changed
by other users and checked by 3 of us.

This looks like a newly started program is cashing values and stores
them I-dont-know-where and it is reloading values after a
transaction.commit ?!
Until few months a go I was using Lazarus 1.0.2 for years and never
had these issues, I never had to use SQLTransaction.commitretaining to
post my data, just sqlquery.execsql, and also never had this issue
which I am describing here, SQLdb was acting better in older version.
Do I have to set some things up to change this behaviour ?
What's your experience with switching to ZEOSdb, how many programers
are using it, is ZEOSdb used widely by the Lazarus community ?

----------------------------------------------------------------------
LacaK · 2016-05-19 11:34

Quote from: mirce.vladimirov on May 18, 2016, 07:14:40 pmUntil few
months a go I was using Lazarus 1.0.2 for years and never had these
issues, I never had to use SQLTransaction.commitretaining to post my
data, just sqlquery.execsql, and also never had this issue which I am
describing here, SQLdb was acting better in older version.
Your issue is related to transaction isolation
See http://wiki.freepascal.org/User_Changes_2.6.4#MySQL_support_for_tr
ansactions_added
There was added support for transactions (so MySQL no more works in
Auto commit mode)
I think you can swith to autocommit mode at least in FPC 3.0 there is
support for implicit transactions.