How to refresh a query? SQLQuery.Refresh does not work as expected
lazarus / 16-Databases

----------------------------------------------------------------------
laskov · 2017-09-12 09:24

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

QuoteQuery.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
QuoteMemo.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:
QuoteQuery.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 QuoteSELECT 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 ???).

----------------------------------------------------------------------
sash · 2017-09-13 23:07

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).

----------------------------------------------------------------------
laskov · 2017-09-14 06:22

Thank you sash and everyone read my topic,

I did some tests and now switching to ZEOSDBO.

----------------------------------------------------------------------
tudi_x · 2017-09-14 07:52

if you have budget you could consider Devart Unidac.
it spared a lot of troubles for me.
i am not affiliated.

----------------------------------------------------------------------
sash · 2017-09-14 11:35

Quote from: laskov on September 14, 2017, 08:22:55 amI did some tests
and now switching to ZEOSDBO.

And what about Transaction? Does restart allow read changes from other
transactions (because it should)?

----------------------------------------------------------------------
mangakissa · 2017-09-15 06:53

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
MyRefresh(aSQLQuery : TSQLQuery);begin 
aSQLQuery.Transaction.committretaining; 
aSQLQuery.refresh;end; Another way is to use a separate TTransaction
for your Query component and put the level on Comittretaining.