ERROR: cannot insert multiple commands into a prepared statement

[mobius1qwe]

Hi,
I'm updating my system from Zeos 7.2.14 to latest 8.0.0 release and my queries are throwing this error now with PostgreSQL 15.
The queries requires a schema so before any SQL query I concatenate

SET search_path = %s;

so, for an example, I concatenate queries like:

  Query.SQL.Add('SET search_path = %s;' schema);
  Query.SQL.Add('SELECT * FROM %s;' tablename);
  Query.Open;

It works nicely with Zeos 7, but now on Zeos 8 Its throwing the error of the title:

[Debugger Exception Notification]
Project --- raised exception class 'EZSQLException' with message:
SQL Error: ERROR:  cannot insert multiple commands into a prepared statement
ERROR 42601
cannot insert multiple commands into a prepared statement 
Code: 7 SQL: SET search_path = 'teste';
DELETE FROM autorizacao
 WHERE to_timestamp(EXTRACT(EPOCH FROM aut_data) + aut_duracao)
 < CURRENT_TIMESTAMP

 In file 'ZDbcPostgreSql.pas' at line 670:
raise Error;
[Ignore this exception type][Break][Continue]

What should I do to fix this?

[marsupilami]

Hello

this is a known issue. We changed to an API function that seems to prepare staements inernally although it isn't documented. Please add the emulate_prepares parameter to get your query working again:

Code: Select all

  Query.SQL.Add('SET search_path = %s;' schema);
  Query.SQL.Add('SELECT * FROM %s;' tablename);
  Query.Properties.Add('emulate_prepares=true'); // <--- This is new
  Query.Open;

Best regards,

Jan

[mobius1qwe]

sorry for late response, been trying to apply what you suggested but still doesn't work.

Tried applying directly as a param of ZConnection, tried applying it as a properties attribute of ZQuery, tried doing this in runtime but still didn't work. Same error happens

what am I doing wrong?

I'm trying to update my system and also update my public lib that abstracts database connection: https://github.com/OpenSourceCommunityB ... s.zeos.pas

this is what I'm using on my system to connect with postgres using zeos.

[marsupilami]

please try this:

Code: Select all

    FQuery := TQuery.Create(nil);
    FQuery.Connection := FConnection;
    FQuery.Properties.Add('emulate_prepares=true'); // <-- this line is new

[mobius1qwe]

I tried adding it but the error is still thrown. The code that I'm doing that is throwing the error is in the image below:
https://prnt.sc/agusyUNZB9B_

Code: Select all

procedure TLicenca.LimparAuth;
var
  teste: string;
begin
  with FQuery do
  begin
    SQL.Clear;
    if not Schema.IsEmpty then
      SQL.Add('SET search_path = ' + QuotedStr(Schema) + ';');
    SQL.Add('DELETE FROM autorizacao');
    SQL.Add(' WHERE to_timestamp(EXTRACT(EPOCH FROM aut_data) + aut_duracao)');
    SQL.Add(' < CURRENT_TIMESTAMP;');
    teste := FQuery.Properties.Text;
    try
      ExecSQL;
      Close;
    except
      Close;
    end;
  end;
end; 

[marsupilami]

Hello,

maybe you should split your statements then:

Code: Select all

procedure TLicenca.LimparAuth;
var
  teste: string;
begin
  with FQuery do
  begin
    SQL.Clear;
    if not Schema.IsEmpty then
      FQuerry.Connection.Executedirect('SET search_path = ' + QuotedStr(Schema)); // <--- This is changed
    SQL.Add('DELETE FROM autorizacao');
    SQL.Add(' WHERE to_timestamp(EXTRACT(EPOCH FROM aut_data) + aut_duracao)');
    SQL.Add(' < CURRENT_TIMESTAMP;');
    teste := FQuery.Properties.Text;
    try
      ExecSQL;
      Close;
    except
      Close;
    end;
  end;
end; 

[mobius1qwe]

will that work in a multi-layered system?
Since the executedirect will be ran directly on the database before the query gets assembled and executed, some other query might be ran in the meantime, won't that cause issues?

[marsupilami]

will that work in a multi-layered system?
Since the executedirect will be ran directly on the database before the query gets assembled and executed, some other query might be ran in the meantime, won't that cause issues?

If you keep to the Zeos rules for multi threading, then yes - it should work. At least in your example. Other queries should be run in other sessions and are isolated from the queries in question. If your concern is about data committed in other transactions, then you should think about changing the isolation level for your transaction.

[mobius1qwe]

Sorry for the delayed response, it seems to be working, I replaced all the search_path lines with the direct query from the connection and been testing to see if there's any issue. I still haven't found any. Thanks for the attention!

Altho I prefer the other way, concatenating commands in the query, which works with FireDAC and SQLDB, but this will work for now, I'm sure you guys will find a way to make it work in the near future.