Page 1 of 1
ERROR: cannot insert multiple commands into a prepared statement
Posted: 18.04.2024, 22:55
by 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?
Re: ERROR: cannot insert multiple commands into a prepared statement
Posted: 19.04.2024, 10:20
by 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
Re: ERROR: cannot insert multiple commands into a prepared statement
Posted: 07.05.2024, 02:48
by 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.
Re: ERROR: cannot insert multiple commands into a prepared statement
Posted: 08.05.2024, 10:31
by marsupilami
please try this:
Code: Select all
FQuery := TQuery.Create(nil);
FQuery.Connection := FConnection;
FQuery.Properties.Add('emulate_prepares=true'); // <-- this line is new
Re: ERROR: cannot insert multiple commands into a prepared statement
Posted: 08.05.2024, 22:47
by 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;
Re: ERROR: cannot insert multiple commands into a prepared statement
Posted: 13.05.2024, 09:10
by 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;
Re: ERROR: cannot insert multiple commands into a prepared statement
Posted: 15.05.2024, 20:26
by 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?
Re: ERROR: cannot insert multiple commands into a prepared statement
Posted: 16.05.2024, 15:16
by marsupilami
mobius1qwe wrote: ↑15.05.2024, 20:26
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.
Re: ERROR: cannot insert multiple commands into a prepared statement
Posted: 01.06.2024, 10:16
by 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.