I get same values from SQLquery althou database changed
lazarus / 16-Databases

----------------------------------------------------------------------
LennartH · 2019-12-01 21:38

Hi
I read the  same row from a database table over and over again.'The
field-values are changed from another program (Python) , but I read
same values over and over again i.e the query doesn't seem to be
updated with the new values.
The code is simply

 procedure dbquery ( s : string );

  Begin
     form2.SQLquery1.close;
     form2.SQLquery1.SQL.Text:= s;
     form2.sqlquery1.Open;
  end;

It works OK first time when the Lazarus program is started, but then I
get same r_value over and over again, when I use
 
  sqlstr := 'select Regn from Rain where id=1';
  dbquery ( sqlstr);
  r_value := Form2.sqlquery1.FieldByName('Regn').AsInteger;

is there anything more to do than sqlquery.close to cleanup?
I use MySQL56Connection for the connection to Mariadb.

Thanks in advance.
Rgds
Lennart Holm
Sweden

----------------------------------------------------------------------
valdir.marcos · 2019-12-02 01:06

Quote from: LennartH on December 01, 2019, 10:38:23 pmHi
I read the  same row from a database table over and over again.'The
field-values are changed from another program (Python) , but I read
same values over and over again i.e the query doesn't seem to be
updated with the new values.
The code is simply
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  procedure
dbquery ( s : string );  Begin     form2.SQLquery1.close;   
 form2.SQLquery1.SQL.Text:= s;     form2.sqlquery1.Open;  end;It works
OK first time when the Lazarus program is started, but then I get same
r_value over and over again, when I use
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i     sqlstr
:= 'select Regn from Rain where id=1';  dbquery ( sqlstr);  r_value :=
Form2.sqlquery1.FieldByName('Regn').AsInteger;is there anything more
to do than sqlquery.close to cleanup?
I use MySQL56Connection for the connection to Mariadb.

Thanks in advance.
Rgds
Lennart Holm
SwedenPlease, use [code] [/code] tags to better show your source code.


You case clearly seems to be a transaction isolation level problem.
First, review the concepts:
https://mydbops.wordpress.com/2018/06/22/back-to-basics-isolation-
levels-in-mysql/
https://blog.pythian.com/understanding-mysql-isolation-levels-
repeatable-read/
https://medium.com/@huynhquangthao/mysql-testing-isolation-
levels-650a0d0fae75
https://dev.mysql.com/doc/refman/5.6/en/set-transaction.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-
levels.html

Second, you must commit all mySQL data in Python.
Then, at last, commit Lazarus data and reopen your query to see the
previously committed data in Python:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
dbquery (s: string);begin  // Connect MySQL56Connection to a
SQLTransaction  // Connect SQLquery to the same SQLTransaction or to a
new one.  // Commit transaction will naturally close SQLquery 
form2.SQLTransaction1.Commit;  // form2.SQLquery1.Close; 
form2.SQLquery1.SQL.Text :=  s;  form2.sqlquery1.Open;end;

----------------------------------------------------------------------
Thaddy · 2019-12-02 07:40

Good answer.
To summarize: Your database did actually NOT change because you forgot
the commits on both sides.
One remark: for databases that are not under heavy load, you can use
auto commit. Otherwise use manual commits.

----------------------------------------------------------------------
LennartH · 2019-12-16 12:42

Thanks for the answers and the links.
A lot to read, A lot to learn , And thanks a lot.
now its working properly.

All the best
Lennart