Recent

Author Topic: I get same values from SQLquery althou database changed  (Read 755 times)

LennartH

  • New member
  • *
  • Posts: 5
I get same values from SQLquery althou database changed
« on: December 01, 2019, 10:38:23 pm »
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

  • Hero Member
  • *****
  • Posts: 993
Re: I get same values from SQLquery althou database changed
« Reply #1 on: December 02, 2019, 02:06:46 am »
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

  • Hero Member
  • *****
  • Posts: 10282
Re: I get same values from SQLquery althou database changed
« Reply #2 on: December 02, 2019, 08:40:46 am »
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.
« Last Edit: December 02, 2019, 08:42:53 am by Thaddy »
I am more like donkey than shrek

LennartH

  • New member
  • *
  • Posts: 5
Re: I get same values from SQLquery althou database changed
« Reply #3 on: December 16, 2019, 01:42:52 pm »
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

 

TinyPortal © 2005-2018