Lazarus

Programming => Databases => Topic started by: laskov on September 12, 2017, 11:24:23 am

Title: How to refresh a query? SQLQuery.Refresh does not work as expected
Post by: laskov on September 12, 2017, 11:24:23 am
MariaDB 10.0.32 on Linux, client: libmysql 5.5.43.0, Lazarus 1.6.2, FPC 3.0.0, Windows XP

There are another applications adding records and others changing data and all work well.

In my application I want to see summarized data. This app does not change any data. I have

Quote
Query.SQL.Text := 'SELECT COALESCE(COUNT(ID),0) as count from main where used_where=0 and sold_where IN (3,6);';
Query.Active:=True;
if Query.BOF and Query.EOF
    then Memo.Lines.Append('No records.')
    else Memo.Lines.Append('Count : '+ Query.FieldByName('count').AsString);

I have a Timer at every 10 seconds runs
Quote
Memo.Lines.Clear;
Query.Refresh;

and expected to see actual data, but not. It shows actual data just after application starts and Refresh does not work. I added curtime() to test like this:
Quote
Query.SQL.Text := 'SELECT COALESCE(COUNT(ID),0) as count, curtime() as ct from main where used_where=0 and sold_where IN (3,6);';
Query.Active:=True;
if Query.BOF and Query.EOF
    then Memo.Lines.Append('No records.')
    else Memo.Lines.Append('Count : '+ Query.FieldByName('count').AsString+
        ' , ' + Query.FieldByName('ct').AsString);
Now at every 10 seconds clock shows actual time but count does not changes.

I changed Query.Refresh to Query.Active:=False and then to True. And more: I need another sums and I change SQL.Text of the same Query to another one 'SELECT COUNT ...' and third, and then again the first SELECT mentioned above and all sums shows actual data after application starts and does not after that.

Why Refresh does not refreshes sums and how to resolve this?

Thank you in advance!

Added: One of queries is
Quote
SELECT COALESCE(COUNT(ID),0) as count FROM main
        WHERE now() < addtime(used_datetime,''00:40:00'');
and this works as expected (Edit: Not in all cases. ??? Not refreshes when grows and refreshes when decreases ???).
Title: Re: How to refresh a query? SQLQuery.Refresh does not work as expected
Post by: sash on September 14, 2017, 01:07:39 am
I think (just a guess) this is because of transaction isolation level.
Try reopen transaction : close then open, or commit retaining (not sure if last one works for mysql).
Title: Re: How to refresh a query? SQLQuery.Refresh does not work as expected
Post by: laskov on September 14, 2017, 08:22:55 am
Thank you sash and everyone read my topic,

I did some tests and now switching to ZEOSDBO.
Title: Re: How to refresh a query? SQLQuery.Refresh does not work as expected
Post by: tudi_x on September 14, 2017, 09:52:44 am
if you have budget you could consider Devart Unidac.
it spared a lot of troubles for me.
i am not affiliated.
Title: Re: How to refresh a query? SQLQuery.Refresh does not work as expected
Post by: sash on September 14, 2017, 01:35:04 pm
I did some tests and now switching to ZEOSDBO.

And what about Transaction? Does restart allow read changes from other transactions (because it should)?
Title: Re: How to refresh a query? SQLQuery.Refresh does not work as expected
Post by: mangakissa on September 15, 2017, 08:53:23 am
With SQLdb it's all about transactions. Your right sash.
All databases working with SQLdb depends on transaction. If it's not the database transaction, it will be the 'cache' transaction level on SQLdb.
This should do the trick:
Code: Pascal  [Select][+][-]
  1. procedure MyRefresh(aSQLQuery : TSQLQuery);
  2. begin
  3.   aSQLQuery.Transaction.committretaining;
  4.   aSQLQuery.refresh;
  5. end;
  6.  
Another way is to use a separate TTransaction for your Query component and put the level on Comittretaining.
TinyPortal © 2005-2018