I get same values from SQLquery althou database changed

[LennartH]

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]

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

Code: Pascal  [Select][+][-]

1.  procedure dbquery ( s : string );
2.   Begin
3.      form2.SQLquery1.close;
4.      form2.SQLquery1.SQL.Text:= s;
5.      form2.sqlquery1.Open;
6.   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][+][-]

1.  
2.   sqlstr := 'select Regn from Rain where id=1';
3.   dbquery ( sqlstr);
4.   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

Please, 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][+][-]

1. procedure dbquery (s: string);
2. begin
3.   // Connect MySQL56Connection to a SQLTransaction
4.   // Connect SQLquery to the same SQLTransaction or to a new one.
5.   // Commit transaction will naturally close SQLquery
6.   form2.SQLTransaction1.Commit;
7.   // form2.SQLquery1.Close;
8.   form2.SQLquery1.SQL.Text :=  s;
9.   form2.sqlquery1.Open;
10. end;

[Thaddy]

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]

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