Zeoslib Portal

This is the official forum of ZeosLib providing support to all ZeosLib users.
https://zeoslib.sourceforge.io/

PostgreSQL - cannot insert multiple commands into a prepared statement

https://zeoslib.sourceforge.io/viewtopic.php?t=70160

Page 1 of 1

PostgreSQL - cannot insert multiple commands into a prepared statement

Posted: 02.11.2017, 16:51
by ertank
Hello,

In my application, I added ability to create PostgreSQL database if it does not exists. I created tables and insert my initial records together with creating database.

Everything works until I insert my initial records which is the final step in creating my database. For ease of use, I have all my insert statements in a single file. When executed, TZSQLProcessor is complaining about subject error message. I would have understood it with TZQuery as it can be prepared, but, TZSQLProcessor should be able to handle multiple SQL statements at once as I believe its main purpose is. I might be completely wrong, so I appreciate some details on subject.

However, this is me thinking PostgreSQL at mind. I do not know if there are situations that even TZSQLProcessor needs to have a prepared statement. In this case, I would suggest a property to be added in TZSQLProcessor (as it is in Devart products UniDAC for example) and users are able to select desired way of working.

Before posting, I searched forum messages and only mention was in below link, again PostgreSQL and no particular suggestion other than executing each insert statement one by one which will be another time consuming operation on a tiny Raspberry Pi device that my application is working on.

http://zeoslib.sourceforge.net/viewtopi ... =38&t=3817

I appreciate any comments on if it is possible to use TZSQLProcessor without using prepared statements.

Thanks.

Re: PostgreSQL - cannot insert multiple commands into a prepared statement

Posted: 03.11.2017, 08:28
by marsupilami
Hello Ertan,

what is the exact error message you get? Do you use parameters in your SQL script? Currently the TZSQLProcessor uses prepared statements and it is not that easy to disable the use of prepared statements for it.

Also I seem to remember that I tested the problem of fhaut in the thread that you linked. I think I didn't get an error there...

With best regards,

Jan

Re: PostgreSQL - cannot insert multiple commands into a prepared statement

Posted: 03.11.2017, 15:34
by ertank
Hello Jan,

Below is my complete file. There are no parameters in my SQL command. Error message is exactly as it is in the subject "cannot insert multiple commands into a prepared statement"

Prepared statements are not accepted for multiple command executions for PostgreSQL. I am not sure about other database platforms.

File text:

Code: Select all

insert into params(paramname, valuetxt) values('KeyboardKey_01', '1');
insert into params(paramname, valuetxt) values('KeyboardKey_02', '2');
insert into params(paramname, valuetxt) values('KeyboardKey_03', '3');
insert into params(paramname, valuetxt) values('KeyboardKey_04', '4');
insert into params(paramname, valuetxt) values('KeyboardKey_05', '5');
insert into params(paramname, valuetxt) values('KeyboardKey_06', '6');
insert into params(paramname, valuetxt) values('KeyboardKey_07', '7');
insert into params(paramname, valuetxt) values('KeyboardKey_08', '8');
insert into params(paramname, valuetxt) values('KeyboardKey_09', '9');
insert into params(paramname, valuetxt) values('KeyboardKey_10', '0');
insert into params(paramname, valuetxt) values('KeyboardKey_11', '*');
insert into params(paramname, valuetxt) values('KeyboardKey_12', '-');
insert into params(paramname, valuetxt) values('KeyboardKey_13', 'Q');
insert into params(paramname, valuetxt) values('KeyboardKey_14', 'W');
insert into params(paramname, valuetxt) values('KeyboardKey_15', 'E');
insert into params(paramname, valuetxt) values('KeyboardKey_16', 'R');
insert into params(paramname, valuetxt) values('KeyboardKey_17', 'T');
insert into params(paramname, valuetxt) values('KeyboardKey_18', 'Y');
insert into params(paramname, valuetxt) values('KeyboardKey_19', 'U');
insert into params(paramname, valuetxt) values('KeyboardKey_20', 'I');
insert into params(paramname, valuetxt) values('KeyboardKey_21', 'O');
insert into params(paramname, valuetxt) values('KeyboardKey_22', 'P');
insert into params(paramname, valuetxt) values('KeyboardKey_23', 'Ğ');
insert into params(paramname, valuetxt) values('KeyboardKey_24', 'Ü');
insert into params(paramname, valuetxt) values('KeyboardKey_25', 'A');
insert into params(paramname, valuetxt) values('KeyboardKey_26', 'S');
insert into params(paramname, valuetxt) values('KeyboardKey_27', 'D');
insert into params(paramname, valuetxt) values('KeyboardKey_28', 'F');
insert into params(paramname, valuetxt) values('KeyboardKey_29', 'G');
insert into params(paramname, valuetxt) values('KeyboardKey_30', 'H');
insert into params(paramname, valuetxt) values('KeyboardKey_31', 'J');
insert into params(paramname, valuetxt) values('KeyboardKey_32', 'K');
insert into params(paramname, valuetxt) values('KeyboardKey_33', 'L');
insert into params(paramname, valuetxt) values('KeyboardKey_34', 'Ş');
insert into params(paramname, valuetxt) values('KeyboardKey_35', 'İ');
insert into params(paramname, valuetxt) values('KeyboardKey_36', ',');
insert into params(paramname, valuetxt) values('KeyboardKey_37', '<');
insert into params(paramname, valuetxt) values('KeyboardKey_38', 'Z');
insert into params(paramname, valuetxt) values('KeyboardKey_39', 'X');
insert into params(paramname, valuetxt) values('KeyboardKey_40', 'C');
insert into params(paramname, valuetxt) values('KeyboardKey_41', 'V');
insert into params(paramname, valuetxt) values('KeyboardKey_42', 'B');
insert into params(paramname, valuetxt) values('KeyboardKey_43', 'N');
insert into params(paramname, valuetxt) values('KeyboardKey_44', 'M');
insert into params(paramname, valuetxt) values('KeyboardKey_45', 'Ö');
insert into params(paramname, valuetxt) values('KeyboardKey_46', 'Ç');
insert into params(paramname, valuetxt) values('KeyboardKey_47', '.');
insert into params(paramname, valuetxt) values('KeyboardKey_48', '>');
insert into params(paramname, valuetxt) values('KeyboardKey_49', '_');
insert into params(paramname, valuetxt) values('KeyboardKey_50', '/');
insert into params(paramname, valuetxt) values('KeyboardKey_51', '\');
insert into params(paramname, valuetxt) values('KeyboardKey_52', '[');
insert into params(paramname, valuetxt) values('KeyboardKey_53', ']');
insert into params(paramname, valuetxt) values('KeyboardKey_54', '(');
insert into params(paramname, valuetxt) values('KeyboardKey_55', ')');
insert into params(paramname, valuetxt) values('KeyboardKey_56', '%');
insert into params(paramname, valuetxt) values('KeyboardKey_57', '@');
insert into params(paramname, valuetxt) values('KeyboardKey_58', '#');
insert into params(paramname, valuetxt) values('KeyboardKey_59', '?');
insert into params(paramname, valuetxt) values('KeyboardKey_60', ' ');
insert into params(paramname, valueint) values('sensormain', 23);
insert into params(paramname, valueint) values('sensortrashbin', 5);
insert into params(paramname, valuebool) values('shutdownsystemonexit', true);
insert into params(paramname, valuebool) values('combinedcounting', true);
insert into params(paramname, valuebool) values('countdown', false);
insert into params(paramname, valuebool) values('debugonscreen', false);
insert into params(paramname, valueint) values('delayafterobjectdetection', 500);
insert into params(paramname, valuebool) values('doubleclicktocount', false);
Relevant code block:

Code: Select all

function TDM.ExecuteSQLScript(const FileName: string): Boolean;
var
  Script: TZSQLProcessor;
begin
  if not FileExists(FileName) then 
  begin
    Log('TDM.ExecuteSQLScript()-FileExists()', 'Error: File cannot be found: ' + QuotedStr(FileName));
    Exit(False);
  end;
  
  Script := TZSQLProcessor.Create(nil);
  try
    Script.Connection := DB; // DB is a TZConnection class
    Script.Script.LoadFromFile(FileName);
    try
      Script.Execute();
    except
        on E: Exception do
        begin
          Log('TDM.ExecuteSQLScript()-Script.Execute()', E.Message);
          raise;
       end;
    end;
  finally
    Script.Free();
  end;
  Result := True;
end;
About the thread, please read OP answers. At some point he's saying that hes getting this error message, but he will do his work command by command executions. Basically, single commands are not a problem only time consuming.

Thanks.

Re: PostgreSQL - cannot insert multiple commands into a prepared statement

Posted: 03.11.2017, 17:17
by marsupilami
Hello Ertan,

I can confirm this bug. It is a problem in the parser. If you remove the line that tries to insert '\', the script works.

With best regards, Jan

Re: PostgreSQL - cannot insert multiple commands into a prepared statement

Posted: 03.11.2017, 17:38
by ertank
Hello Jan,

It is nice to know its a parser bug. Do you think it will be possible to handle it?

Thanks.
Ertan

Re: PostgreSQL - cannot insert multiple commands into a prepared statement

Posted: 05.11.2017, 13:27
by miab3
@ertank,

You can temporarily use:

Code: Select all

insert into params(paramname, valuetxt) values('KeyboardKey_51',U&'\005C');
Michal

Re: PostgreSQL - cannot insert multiple commands into a prepared statement

Posted: 08.11.2017, 21:56
by marsupilami
ertank wrote:Hello Jan,

It is nice to know its a parser bug. Do you think it will be possible to handle it?

Thanks.
Ertan
Hello Ertan,

I am quite sure, it can be handeled. But I cannot even guess how much time it will take :(

With best regards,

Jan

Re: PostgreSQL - cannot insert multiple commands into a prepared statement

Posted: 09.11.2017, 08:43
by Fr0sT
I did no tests but maybe the source of the issue is that '\' is considered an escape char in TZPostgreSQLQuoteState.GetQuotedString? Maybe doubling the '\' could fix the problem?

Re: PostgreSQL - cannot insert multiple commands into a prepared statement

Posted: 09.11.2017, 16:24
by marsupilami
Hello Fr0st,

having \ as an escape character can be right and can be wrong. It depends on the setting of "standard_conforming_strings" in the server. And wether the string constant is preceeded by an E. For more details on the PostgreSQL string madness ... erm... syntax see here, section4.1.2.1. String Constants in the PostgreSQL manual. I assume that the parser in Zeos was never updated, when PostgreSQL decided to have standard conforming strings... But then - this only is an assumption because I had no chance to look at the code (yet).

With best regards,

Jan

Re: PostgreSQL - cannot insert multiple commands into a prepared statement

Posted: 10.11.2017, 08:32
by Fr0sT
Hello Jan,
anyway the source sits there, right? Locating the issue is 50% of solving it :)

Re: PostgreSQL - cannot insert multiple commands into a prepared statement

Posted: 09.02.2018, 21:08
by EgonHugeist
To be honest. Using complex queries like posted and do not use the escape rules of a provider should fail as reported.

Note the TZPostgre..Tokenizer is/WAS able to handle both cases of Standart_Coforming_Strings until someone did commit break the test of our suits.
BUT its behavior is fix after open the connection. Changing this session varialble while connection is open would break all approaches to handle the postgres rules of splitting the queries into tokenizing.

Might it be related to http://zeoslib.sourceforge.net/viewtopi ... 40&t=49966 ?
All times are UTC+01:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited