[SOLVED] MYSQL check 'Empty set' properly lazarus / 63-Database ---------------------------------------------------------------------- OH1KH · 2025-02-03 18:46 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i dmData.CQ.SQL.Text :=     'select count(callsign) as rate from cqrlog_main where timestampdiff(minute,concat(qsodate," ",time_off),utc_timestamp())<60';         dmData.CQ.Open();-    if (dmData.CQ.FieldCount > 0) then+    if (dmData.CQ.Fields.FindField('rate')<> nil) then                lblRate60.Caption:=dmData.CQ.FieldByName('rate').AsString+'/60';]     dmData.CQ.Close;   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 · 2025-02-03 18:58 Quote from: OH1KH on February 03, 2025, 07:46:59 pmWhat 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i dmData.CQ.SQL.Text :=     'select count(callsign) as rate from cqrlog_main where timestampdiff(minute,concat(qsodate," ",time_off),utc_timestamp())<60';         dmData.CQ.Open();-    if (dmData.CQ.FieldCount > 0) then+    if (dmData.CQ.Fields.FindField('rate')<> nil) then                lblRate60.Caption:=dmData.CQ.FieldByName('rate').AsString+'/60';]     dmData.CQ.Close;   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 · 2025-02-04 02:43 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i dmData.CQ.Open;if not (dmData.CQ.EOF) then ... ---------------------------------------------------------------------- egsuh · 2025-02-04 05:39 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 · 2025-02-04 06:59 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i MariaDB [cqrlog009]> select callsign from cqrlog_main main where callsign="never_found";Empty set (0,001 sec) MariaDB [cqrlog009]> select callsign as foo from cqrlog_main main where callsign="never_found";Empty set (0,001 sec)   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! Quote from: egsuh on February 04, 2025, 06:39:12 amThere 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 · 2025-02-04 07:28 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 · 2025-02-04 09:18 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. Quote from: Zvoni on February 04, 2025, 08:28:15 amIn 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 · 2025-02-04 10:11 QuoteI 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 · 2025-02-04 10:48 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 · 2025-02-04 11:37 Quote from: silvercoder70 on February 04, 2025, 11:48:55 amLooking 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 · 2025-02-04 16:22 OK! I got what I was looking for. 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  //different ways to check that sql query is not "Empty set"         Writeln('-------------');         Writeln('Is EOF:',dmData.CQ.EOF);       Writeln('Is empty:',dmData.CQ.IsEmpty);         Writeln('FindField:', dmData.CQ.Fields.FindField('QSOCount')<> nil);         Writeln('Fieds.Count:',dmData.CQ.Fields.Count);                      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.