How to determine the presence of a record in PostgreSQL
lazarus / 16-Databases

----------------------------------------------------------------------
shyub · 2024-03-20 16:34

I do this and get an error:
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 function
TDataModule2.ThisArrive(): Boolean;begin  Result:=false;  if not
DataModule2.PQConnection1.Connected then    exit; 
SQLQuery1.Active:=false;  SQLQuery1.SQL.Clear; 
SQLQuery1.SQL.Add('EXISTS (SELECT * FROM '+INI.Tab+' WHERE '+   
'(sNum1='+chr(39)+Num1s+chr(39)+' OR
sNum2='+chr(39)+Num1s+chr(39)+')'+    'AND nU=NULL)');  try   
SQLQuery1.Open;    if SQLQuery1.RecordCount = 0 then     
Result:=true;  finally    SQLQuery1.Close;  end;end;I do this and also
get an error:
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 function
TDataModule2.ThisArrive(): Boolean;begin  Result:=false;  if not
PQConnection1.Connected then    exit;  SQLQuery2.Active:=false; 
SQLQuery2.SQL.Clear;  SQLQuery2.SQL.Add('SELECT * FROM '+INI.Tab+'
WHERE '+    '(sNum1='+chr(39)+Num1s+chr(39)+' OR
sNum2='+chr(39)+Num1s+chr(39)+') '+    'AND nU=NULL RETURNING *'); 
try    SQLQuery2.Open;    if SQLQuery1.RecordCount = 0 then     
Result:=true;  finally    SQLQuery2.Close;  end;end;How to do this
correctly?

----------------------------------------------------------------------
Zvoni · 2024-03-20 16:38

*gazing into my crystal ball*

Nope, can’t see what the actual error message in plain text is

----------------------------------------------------------------------
rvk · 2024-03-20 16:47

Well, at least both are not valid SQL statements.

The first begins with EXISTS ?
Just do a SELECT COUNT(*) and read the value
or SELECT * and do the recordcount.
(Preferably the first one)

Second has RETURNING * ?
And wrong number of parenthesis.

nU=NULL is normally also incorrect in sql.
You need to do nU is NULL.

If you are not sure about the SQL just do a
showmessage(SQLQuery1.SQL.Text)
before the Open and copy it with CTRL+C.
Paste it in an editor and you see more clearly what sql was used.

----------------------------------------------------------------------
shyub · 2024-03-20 17:46

What's wrong with my parentheses?

Could the reason be that I perform this check before each record and
while the table is empty?

----------------------------------------------------------------------
rvk · 2024-03-20 17:58

Quote from: shyub on March 20, 2024, 06:46:10 pmWhat's wrong with my
parentheses?
Sorry, I may have miscounted those

Quote from: shyub on March 20, 2024, 06:46:10 pmCould the reason be
that I perform this check before each record and while the table is
empty?
Show the actual SQL like l showed you.
Is  where nU=NULL allowed in PostgreSQL?
Is sNum1 and sNum2 of type CHAR or CARCHAR? (Because you quote it)
Why do you use RETURNING while you don't check the result?
(TDataset.Recordcount is just automatically filled if the sql is
correct)

But most important... What is the actual error message?

----------------------------------------------------------------------
Zvoni · 2024-03-20 20:36

Quote from: rvk on March 20, 2024, 06:58:25 pmBut most important...
What is the actual error message?
„Syntax Error near……“

----------------------------------------------------------------------
shyub · 2024-03-21 05:24

The problem was solved in this way.
Probably the error was that I was trying to check a table that did not
yet have records.
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 function
TDataModule2.ThisArrive(): Boolean;begin  Result:=false;  if not
PQConnection1.Connected then    exit;  SQLQuery2.Active:=false; 
SQLQuery2.SQL.Clear;  SQLQuery2.SQL.Add('SELECT * FROM '+INI.Tab+'
WHERE '+    '(sNum1='+chr(39)+Num1s+chr(39)+' OR
sNum2='+chr(39)+Num1s+chr(39)+') '+    'AND nU=NULL');  try   
Form1.Label13.Caption:=SQLQuery2.SQL.Text;    SQLQuery2.Open;    if
SQLQuery1.FieldCount=0 then      Result:=true    else      if
SQLQuery1.Fields[0].AsInteger = 0 then        Result:=true;  finally 
 SQLQuery2.Close;  end;end;

----------------------------------------------------------------------
rvk · 2024-03-21 05:57

Quote from: shyub on March 21, 2024, 06:24:40 amThe problem was solved
in this way.
Probably the error was that I was trying to check a table that did not
yet have records.
No it wasn't. Recordcount just returns 0 for an empty table.
You removed the RETURNING

(I'm still unsure about the =NULL but apparently PostgreSQL can handle
that.)

BTW I'm also not sure what you are trying to return here in this
function.
You now retrieve the value of the first field in the first record.
If that's not an integer you'll get an error.
If it's an integer but not 0 you still get a false result.

If it was to just check if there are any results from the where then
your first attempt was correct, you just needed to remove the
RETURNING.

----------------------------------------------------------------------
Zvoni · 2024-03-21 06:09

Errrr…… he‘s assigning the SQL to Query2, but checks FieldCount of
Query1?
Huh?

----------------------------------------------------------------------
rvk · 2024-03-21 07:13

Quote from: Zvoni on March 21, 2024, 07:09:27 amErrrr…… he‘s assigning
the SQL to Query2, but checks FieldCount of Query1?
Huh?
I didn't even see that 

But looking at FieldCount is definitely the wrong way to check for
presence of a record (like the subject suggests).

BTW. I also had a look at the =NULL in PostgreSQL.
As I expected... That's also incorrect in PostgreSQL. You should use
is NULL
QuoteTo check if a value is NULL or not, you cannot use the equal to
(=) or not equal to (<>) operators. Instead, you use the IS NULL
operator.https://www.postgresqltutorial.com/postgresql-
tutorial/postgresql-is-null/

So, there is lots wrong with that code and won't even work and do what
TS wants.
(but I guess TS will find out  )

----------------------------------------------------------------------
Zvoni · 2024-03-21 08:34

Quote from: rvk on March 21, 2024, 08:13:28 amQuote from: Zvoni on
March 21, 2024, 07:09:27 amErrrr…… he‘s assigning the SQL to Query2,
but checks FieldCount of Query1?
Huh?
I didn't even see that 
I looked again at TS' first Post - second code-sample: Even there he
assigns SQL to Query2 and checks RecordCount of Query1.

Right.......
i have 2 Buckets.... i fill the first one with Water, and then i'm
wondering why the second bucket is still empty

That said: I'm out of here: I'm not going to help/teach people who are
too lazy to read up on basic stuff
Just that "... nU=NULL" is enough to turn me off.....

----------------------------------------------------------------------
shyub · 2024-03-21 10:38

You're right. This works for an empty table, but after the records
appear, problems begin.
I fixed SQLQuery1 everywhere with SQLQuery2 and again the problems.
As a result, I got the following result, which works both with an
empty table and after records appear.
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 function
TDataModule2.ThisArrive(): Boolean;begin  Result:=false; 
SQLQuery2.Active:=false;  SQLQuery2.SQL.Clear; 
SQLQuery2.SQL.Add('SELECT * FROM '+INI.Tab+' WHERE '+   
'(sNum1='+chr(39)+Num1s+chr(39)+' OR sNum2='+chr(39)+Num1s+chr(39)+'
'+    'OR sNum1='+chr(39)+Num2s+chr(39)+' OR
sNum2='+chr(39)+Num2s+chr(39)+') '+    'AND nU=0');  try   
SQLQuery2.Open;    if SQLQuery2.Fields[0].AsInteger = 0 then     
Result:=true;  finally    SQLQuery2.Close;  end;end;

----------------------------------------------------------------------
shyub · 2024-03-21 10:43

It would still be interesting to use
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
SQLQuery1.SQL.Add('EXISTS (SELECT * FROM...Maybe there is an example
for Lazarus?

----------------------------------------------------------------------
rvk · 2024-03-21 10:44

You are still using fields[0].AsInteger which is not correct.

Why not just go back to the Query2.RecordCount which should work now
that you removed that RETURNING.

Also... can nU be NULL or does it always contain an integer value?
If it can be NULL then this won't work.
You should need to do something like
coalesce(nU, 0)=0
so that if nU is NULL you can still check for =0

----------------------------------------------------------------------
rvk · 2024-03-21 10:46

Quote from: shyub on March 21, 2024, 11:43:02 amIt would still be
interesting to use
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
SQLQuery1.SQL.Add('EXISTS (SELECT * FROM...Maybe there is an example
for Lazarus?
You use EXISTS in a subquery (in where).
Not as start of an SQL.

https://www.geeksforgeeks.org/postgresql-exists-operator/

----------------------------------------------------------------------
Zvoni · 2024-03-21 11:22

Quote from: shyub on March 21, 2024, 11:43:02 amIt would still be
interesting to use
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
SQLQuery1.SQL.Add('EXISTS (SELECT * FROM...Maybe there is an example
for Lazarus?
No, there is not, because it has nothing to do with Lazarus

Start learning SQL