[SOLVED] MYSQL check 'Empty set' properly

[OH1KH]

HI
What is the proper way to check if sql query has 'Empty set'?
I think I have missed something from FPC's changelog as I have started to receive "Field 'xxxx' not found" errors from program that used to work before.
 

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

1. dmData.CQ.SQL.Text :=
2.      'select count(callsign) as rate from cqrlog_main where timestampdiff(minute,concat(qsodate," ",time_off),utc_timestamp())<60';
3.    
4.      dmData.CQ.Open();
5. -    if (dmData.CQ.FieldCount > 0) then
6. +    if (dmData.CQ.Fields.FindField('rate')<> nil) then
7.                 lblRate60.Caption:=dmData.CQ.FieldByName('rate').AsString+'/60';]
8.      dmData.CQ.Close;
9.  
10.  

Originally I did not have check at all before used "FieldByName(xxxxx).AsYyyyy",it worked.

After problems appeared I added "check .FieldCount > 0" (here seen as diff's "-" line) but it seems to fail randomly.
Then I changed to  "Fields.FindField" (here seen as diff's "+" line) but I am not sure if this works either.

What bothers me is that similar queries are in many places and it seems that some of them randomly fail "field not found" error even it is now always checked before use.


Fedora release 40 (Forty)
Free Pascal Compiler version 3.2.2 [2024/07/28] for x86_64
mysql  Ver 15.1 Distrib 10.11.10-MariaDB, for Linux (x86_64) using  EditLine wrapper

[dsiders]

What is the proper way to check if sql query has 'Empty set'?
I think I have missed something from FPC's changelog as I have started to receive "Field 'xxxx' not found" errors from program that used to work before.
 

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

1. dmData.CQ.SQL.Text :=
2.      'select count(callsign) as rate from cqrlog_main where timestampdiff(minute,concat(qsodate," ",time_off),utc_timestamp())<60';
3.    
4.      dmData.CQ.Open();
5. -    if (dmData.CQ.FieldCount > 0) then
6. +    if (dmData.CQ.Fields.FindField('rate')<> nil) then
7.                 lblRate60.Caption:=dmData.CQ.FieldByName('rate').AsString+'/60';]
8.      dmData.CQ.Close;
9.  
10.  

Originally I did not have check at all before used "FieldByName(xxxxx).AsYyyyy",it worked.

After problems appeared I added "check .FieldCount > 0" (here seen as diff's "-" line) but it seems to fail randomly.
Then I changed to  "Fields.FindField" (here seen as diff's "+" line) but I am not sure if this works either.

What bothers me is that similar queries are in many places and it seems that some of them randomly fail "field not found" error even it is now always checked before use.

Seems like you're assuming there is always a row in the result set. Why not check the record count before trying to access anything in the dataset?

[GAN]

Hi, there is an other option to check if empty: TDataSet.EOF https://lazarus-ccr.sourceforge.io/docs/fcl/db/tdataset.eof.html

TDataSet.Open: If the dataset is empty, EOF is set to true  https://lazarus-ccr.sourceforge.io/docs/fcl/db/tdataset.open.html

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

1. dmData.CQ.Open;
2. if not (dmData.CQ.EOF) then ...

[egsuh]

There are IsEmpty method to check whether a dataset is empty. So, for your case,


   if not dmData.CQ.IsEmpty then begin
        ....
   end;

should work.

But even the dataset is empty, fields should be returned. There would be other problem --- like the SQL itself does not work, etc.

[OH1KH]

That is the main problem that fields do not return with null value when there is nothing to show.
They do not return at all. ("empty set")

Code: Text  [Select][+][-]

1. MariaDB [cqrlog009]> select callsign from cqrlog_main main where callsign="never_found";
2. Empty set (0,001 sec)
3.  
4. MariaDB [cqrlog009]> select callsign as foo from cqrlog_main main where callsign="never_found";
5. Empty set (0,001 sec)
6.  
7.  

Not with column name that really is in table, or with given "AS" name.

But I will test with "EOF" and "IsEmpty" given in replies.

Thanks!

There are IsEmpty method to check whether a dataset is empty. So, for your case,


   if not dmData.CQ.IsEmpty then begin
        ....
   end;

should work.

But even the dataset is empty, fields should be returned. There would be other problem --- like the SQL itself does not work, etc.

[Zvoni]

In your SQL-Query: I recognized everything (except your Column- and Tablename) except --> What is "time_off"?
I couldn't find anything either in MySQL or MariaDB-Documentation

Sounds like an "Offset", but of what? and you are concating it with a date "qsodate". Is it really JUST a Date or is it even a DateTime?

[OH1KH]

Do not care about the query.
That is not the subject. The problem is that when you get empty set from query you can not refer to any table column without checking that mysql has returned something (the requested column).

As for info to your question:
The original author has defined date and time_off as varchar and because of this the query looks weird because date and time (strings) must be concat to datetime before compare.

In your SQL-Query: I recognized everything (except your Column- and Tablename) except --> What is "time_off"?
I couldn't find anything either in MySQL or MariaDB-Documentation

Sounds like an "Offset", but of what? and you are concating it with a date "qsodate". Is it really JUST a Date or is it even a DateTime?

[Zvoni]

I think I have missed something from FPC's changelog as I have started to receive "Field 'xxxx' not found" errors from program that used to work before.

That's the reason for my Question.
Reading your description, your SQL-Query always was successful (..."used to work"), but now sometimes it fails.
and it can only fail, if you don't have any records according to your WHERE-Clause, and the only "variable" part of the Query is "qsoDate" and "time_off"

Do the math.....

[silvercoder70]

Looking at the original query... it's using a select count and unless something changed sql wise you will not get an empty result set. If no rows are matching the count is 0.

You would get an empty result set for something like select * from...

[Zvoni]

Looking at the original query... it's using a select count and unless something changed sql wise you will not get an empty result set. If no rows are matching the count is 0.

You would get an empty result set for something like select * from...

hmm....true....

[OH1KH]

OK!
I got what I was looking for.

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

1.  //different ways to check that sql query is not "Empty set"
2.          Writeln('-------------');
3.          Writeln('Is EOF:',dmData.CQ.EOF);
4.          Writeln('Is empty:',dmData.CQ.IsEmpty);
5.          Writeln('FindField:', dmData.CQ.Fields.FindField('QSOCount')<> nil);
6.          Writeln('Fieds.Count:',dmData.CQ.Fields.Count);                        
7.  

 Thanks for all replies!

When setting these to all places where a query exist in two units I got  next problem.

I cant remember the text of error splash but it was mysql error "packets arrive in wrong order". (of course it appear randomly)
That is probably because there are several threads and perhaps same query/transaction is used more than one place at same time.

But that is another subject.