FireDac PostgreSQL Parameters

[egnew 5]

A TFDQuery with PostgreSQL fails to execute when I have parameters in the query.  The error message is:  [FireDAC][Phys][PG][libpq] ERROR: syntax error at or near ":"

ParamByName executes but PostgreSQL sees the ":" and the exception occurs in ExecSQL.  This seems to indicate there is an issue with FireDAC setting the parameter.

Although I could manually work around the issue by substituting the parameters in the SQL at runtime, I would prefer this to work as it does in other databases.

Code

  try
    v_Step := 'SetParameter';
    FDQuery3.ParamByName('increment').AsInteger := 100;
    v_Step := 'ExecSQL';
    FDQuery3.ExecSQL;
  except
    on E: Exception do
    begin
      Memo1.Lines.Add(v_Step+': '+E.Message);
      Memo1.Lines.Add(FDQuery3.SQL.Text);
    end;
  end;

Memo1.Text

ExecSQL: [FireDAC][Phys][PG][libpq] ERROR: syntax error at or near ":"
do $$
begin
  update mytable set testno = testno+:increment where id = 1;
  commit;
end
$$

[Dmitry Arefiev 106]

https://www.postgresql.org/docs/current/sql-do.html

Quote

The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed and executed a single time.

 

[Roger Cigol 107]

I use parameters in my TFDQuery with PostgreSQL 13 database all the time. No problems. You need to post more information in order for the community here to be able to help.

 

[egnew 5]

1)  This appears to be a FireDAC bug related to PostgreSQL.  Unless someone convinces me otherwise, I will post a bug report at https://qp.embarcadero.com/.

 

At runtime the error is:   [FireDAC][Phys][PG][libpq] ERROR: syntax error at or near ":"

At design time the error is:   List index out of bounds (0).  TList<System.Classes.TCollectionItem> is empty. 

 

The design time error prevents the INCREMENT param value from being defined properly in the FIREDAC Query Editor.

I was able to set the desired values by switching to text mode, adding the correct settings, and then switching back to form mode.  It did not make a difference. 

 

2)  Environment

 

RAD Studio 12 Version 29.0.51961.7529 

Delphi 12 and VC++Builder 12 Update 1

RAD Studio 12.1 Patch 1

Windows 11 (Version 23H2, OS Build 22631.4460, 64-bit Edition)

 

I have installed PostgreSQL drivers for both 32 and 64 bit executables.

I have modified the User System Override Path in Rad Studio to allow PostgreSQL queries in the FireDAC Query Editor.

c:\postgresql\odbc32\bin;c:\postgresql\odbc64\bin;$(PUBLIC)\Documents\Embarcadero\InterBase\redist\InterBase2020\IDE_spoof;$(PATH)

 

3)  All Application Code is in this event:

procedure TForm1.FormShow(Sender: TObject);
var
  v_Step,v_SQL: String;
  v_pointer: Pointer;
begin
  if SizeOf(v_Pointer) = 4 then
    FDPhysPGDriverLink1.VendorLib := 'C:\PostgreSQL\odbc32\bin\libpq.dll'
  else
    FDPhysPGDriverLink1.VendorLib := 'C:\PostgreSQL\odbc64\bin\libpq.dll';
  Memo1.Text := '';
  FDConnection1.Connected := True;
  try
    FDQuery1.ExecSQL;
    Memo1.Lines.Add('Query1: Success');
  except
    on E: Exception do
      Memo1.Lines.Add('Query1: '+E.Message);
  end;
  FDQuery1.Close;
  try
    FDQuery2.Open;
    Memo1.Lines.Add('Query2: Success');
  except
    on E: Exception do
      Memo1.Lines.Add('Query2: '+E.Message);
  end;
  FDQuery2.CLose;
  try
    v_Step := 'SetParameter';
    FDQuery3.ParamByName('increment').AsInteger := 100;
    v_Step := 'ExecSQL';
    FDQuery3.ExecSQL;
    Memo1.Lines.Add('Query3: Success');
  except
    on E: Exception do
    begin
      Memo1.Lines.Add('Query3: Step '+v_Step+' '+E.Message);
      Memo1.Lines.Add(FDQuery3.SQL.Text);
      Memo1.Lines.Add('End Query3');
    end;
  end;
  FDQuery3.CLose;
  try
    v_SQL := FDQuery3.SQL.Text;
    FDQuery3.SQL.Text := StringReplace(v_SQL,':increment','100',[rfReplaceAll,rfIgnoreCase]);
    FDQuery3.ExecSQL;
    FDQuery3.SQL.Text := v_SQL;
    Memo1.Lines.Add('Query3.WorkAround: Success');
  except
    on E: Exception do
      Memo1.Lines.Add('Query3: '+E.Message);
  end;
  FDQuery3.Close;
  FDConnection1.Connected := False;
end;

4)  Table mytable in your PostgreSQL database with a single record with testno set to zero.

id serial;

testno numeric;

 

5) Components Needed

FDConnection1 - TFDConnection to your PostgreSQL database

FDPhysPGDriverLink1 - TFDPhysPGDriverLink

FDQuery1, FDQuery2, FDQuery3 - TFDQuery as described below.

QUERY CONTENT

 

FDQuery1.SQL

do $$
begin
  update mytable set testno = testno+1 where id = 1;
  commit;
end
$$

 

FDQuery2.SQL

select t.* from mytable t

 

FDQuery3.SQL - Same as FDQuery1 except "+:increment" replaces "+1".

do $$
begin
  update mytable set testno = testno+:increment where id = 1;
  commit;
end
$$

 

RESULTS

 

Results for FDQuery1 and FDQuery2 in the IDE and at runtime

FDQuery1 Executes successfully

FDQuery2 Executes successfully

 

Result for FDQuery3 at Runtime

Runtime exception [FireDAC][Phys][PG][libpq] ERROR: syntax error at or near ":"

 

Result for FDQuery3 in FireDAC Query Editor

An attempt to modify the INCREMENT parameter DataType or Value or to execute the query results in:

List index out of bounds (0).  TList<System.Classes.TCollectionItem> is empty.

 

Result for FDQuery3 Workaround - runtime only

FDQuery3 Executes successfully with ReplaceString instead of ParamAsName.

 

[Dmitry Arefiev 106]

Please read my comment above.

[egnew 5]

12 hours ago, Dmitry Arefiev said:

https://www.postgresql.org/docs/current/sql-do.html

 

I did read your comment but it does not appear to have anything to do with my issue.  Delphi is not having a problem executing queries with the "DO" in either the IDE or at runtime.

 

The problem is that Delphi is not handling TFDQuery.Params correctly when the database is PostgreSQL.  Please see my previous detailed post.

At runtime the error is:   [FireDAC][Phys][PG][libpq] ERROR: syntax error at or near ":"

At design time the error is:   List index out of bounds (0).  TList<System.Classes.TCollectionItem> is empty. 

 

Clearly, FireDAC is not setting the parameter value as instructed at runtime as I get the error at or near ":".  But, it also will not allow it to be set when executing from the IDE.

 

Thanks

 

[Roger Cigol 107]

Can you paste the contents of the dfm definition of the form here for the FDQuery where the parameter :increment is defined and used.

One way of doing this is to go to the form designer, select the appropriate TFDQuery component on the form, right click and select "copy"

and then paste it into your Dephi-Praxis post....

Edited December 2 by Roger Cigol

[egnew 5]

Thanks, Roger. Here you go.

 

I have been using Borland, Inprise, etc. since Turbo Pascal in 1983 and was a beta tester for Rad Studio.  You can pretty much just tell me what you want and I will know how to do it.

 

Please know that the values for Datatype and Value could not be set in the FireDAC Query Editor.  I edited the form and text to add the settings.  I confirmed that I could have just set them in the object inspector.  The issue with "List issue out of bounds" only occurs in the Query Editor.

 

  object FDQuery3_Copy_Params: TFDQuery
    Connection = FDConnection1
    SQL.Strings = (
      'do $$'
      'begin'
      '  update mytable set testno = testno+:increment where id = 1;'
      '  commit;'
      'end'
      '$$')
    Left = 296
    Top = 288
    ParamData = <
      item
        Name = 'INCREMENT'
        DataType = ftInteger
        ParamType = ptInput
        Value = 1
      end>
  end

 

[Brian Evans 109]

Parameters are not processed by Delphi inside string constants in SQL queries. You are using Dollar-Quoted String Constants (strings delimited by starting and ending with $$). 

 

do $$ string constant that is executed as an anonymous code block $$

 

Ref: PostgreSQL: Documentation: 17: 4.1. Lexical Structure

[egnew 5]

47 minutes ago, Brian Evans said:

Parameters are not processed by Delphi inside string constants in SQL queries. You are using Dollar-Quoted String Constants (strings delimited by starting and ending with $$). 

 

do $$ string constant that is executed as an anonymous code block $$

 

Removing the $$ from the query fixed the "Index out of bounds" issue in the FireDAC Query Editor.  The parameter properties can now be set and the execute button no longer produces that error.

 

However, that does not provide a solution.  I normally program for Oracle, SQL-Server, and other databases.  None of these have every had an issue with FDQuery and parameters.  I have never heard of $$ begin a thing in Delphi.  It is one thing for it to be a feature in PostgreSQL, but FIreDAC should not be bound by that in terms of doing the parameter substitution.  Just my opinion.

 

I am new to PostgreSQL but this is what happens with FDQuery3 when I Execute it in the Query Editor.

With the original SQL text and after entering the Query Editor, I get the "Index out of bounds" error.

Without the $$, I get "Syntax error at or near begin".

Without the DO $$, I get "Syntax error at or near update".

With just begin/end, I get "cannot insert multiple commands into a prepared statement". 

But, putting everything back to the original SQL test.

1) I no longer get the original "index out of bounds" to the error

2) Instead, I get the same message I get at runtime which is "Syntax error at or near ":".

3) Automatic recognition of the parameter no longer occurs and the parameter is no longer recognized.

 

If I then exit Query Editor and restart it with the original SQL

1)  I again get the "index out of bounds" error

2)  The parameter has been automatically recognized

 

Finally:

1)  I have a workaround where I just substitute the desired values into the SQL text.  That works without error.

2)  FireDAC gives different errors for the same SQL text depending on the sequence changes are made.  That is a bug.

3) I need to know if there is \another way to specify a query in PostgreSQL without the $$.

 

Is there another way to specify the query without the $$?

 

Thanks

 

[Brian Evans 109]

In Postgres BEGIN needs to be followed by a semicolon. It is equivalent to some other databases START TRANSACTION. It should be paired with a COMMIT and not END.

Ref: PostgreSQL: Documentation: 17: 3.4. Transactions

 

Single statements usually run as a transaction anyway - either the whole statement executes or none of it does. So, for a single statement your SQL would just be:

update mytable set testno = testno + :increment where id = 1;

 

[egnew 5]

Thanks for your explanation.

 

This works as expected:

update mytable set testno = testno+:increment where id = 1;

However, this does not:

begin;
   update mytable set testno = testno+:increment where id = 1;
commit;

This results in  "ERROR: cannot insert multiple commands into a prepared statement"

 

 

[Brian Evans 109]

Postgres limitation - no multiple statements in a prepared query (queries must be prepared if they use parameters).

 

Note a single statement runs in a transaction anyway so the begin;/end; are not needed in the example. 

 

Most use stored procedures in the database when there is a need to execute multiple statements based off passed in parameters. 

 

Some use DO and pass in the contents of a stored procedure body as a string to run as an anonymous code block but then you can't pass in any parameters. 

[egnew 5]

Thanks very much Brian.  You have been very helpful.  Everything I need is working.

This issue is resolved.  Thanks for the assistance.