* * *

Author Topic: How to refresh a query? SQLQuery.Refresh does not work as expected  (Read 479 times)

laskov

  • Newbie
  • Posts: 3
How to refresh a query? SQLQuery.Refresh does not work as expected
« 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 ???).
« Last Edit: September 12, 2017, 02:27:51 pm by laskov »

sash

  • New member
  • *
  • Posts: 10
Re: How to refresh a query? SQLQuery.Refresh does not work as expected
« Reply #1 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).
XUbuntu 16.04. Lazarus 1.6.4  FPC 3.0.2 x86_64-linux-gtk 2

laskov

  • Newbie
  • Posts: 3
Re: How to refresh a query? SQLQuery.Refresh does not work as expected
« Reply #2 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.

tudi_x

  • Sr. Member
  • ****
  • Posts: 282
Re: How to refresh a query? SQLQuery.Refresh does not work as expected
« Reply #3 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.

sash

  • New member
  • *
  • Posts: 10
Re: How to refresh a query? SQLQuery.Refresh does not work as expected
« Reply #4 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)?
XUbuntu 16.04. Lazarus 1.6.4  FPC 3.0.2 x86_64-linux-gtk 2

mangakissa

  • Hero Member
  • *****
  • Posts: 708
Re: How to refresh a query? SQLQuery.Refresh does not work as expected
« Reply #5 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.
Lazarus 1.64 (32b) / FPC 3.0
Windows 10

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus