PostgreSQL - cannot insert multiple commands into a prepared statement

The BETA tester's forum for ZeosLib 7.2 Report problems. Ask for help, post proposals for the new version and Zeoslib 7.2 features here. This is a forum that will be edited once the 7.2.x version goes into RC/stable!!

My personal intention for 7.2 is to speed up the internals as optimal a possible for all IDE's. Hope you can help?! Have fun with testing 7.2
ertank
Junior Boarder
Junior Boarder
Posts: 30
Joined: 02.06.2017, 12:00

PostgreSQL - cannot insert multiple commands into a prepared statement

Postby ertank » 02.11.2017, 16:51

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/viewtopic.php?f=38&t=3817

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

Thanks.

marsupilami
Gold Boarder
Gold Boarder
Posts: 591
Joined: 17.01.2011, 14:17

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

Postby marsupilami » 03.11.2017, 08:28

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

ertank
Junior Boarder
Junior Boarder
Posts: 30
Joined: 02.06.2017, 12:00

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

Postby ertank » 03.11.2017, 15:34

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.

marsupilami
Gold Boarder
Gold Boarder
Posts: 591
Joined: 17.01.2011, 14:17

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

Postby marsupilami » 03.11.2017, 17:17

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

ertank
Junior Boarder
Junior Boarder
Posts: 30
Joined: 02.06.2017, 12:00

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

Postby ertank » 03.11.2017, 17:38

Hello Jan,

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

Thanks.
Ertan

miab3
Zeos Test Team
Zeos Test Team
Posts: 916
Joined: 11.05.2012, 12:32
Location: Poland

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

Postby miab3 » 05.11.2017, 13:27

@ertank,

You can temporarily use:

Code: Select all

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

Michal

marsupilami
Gold Boarder
Gold Boarder
Posts: 591
Joined: 17.01.2011, 14:17

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

Postby marsupilami » 08.11.2017, 21:56

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

Fr0sT
Junior Boarder
Junior Boarder
Posts: 43
Joined: 08.05.2014, 12:08

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

Postby Fr0sT » 09.11.2017, 08:43

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?

marsupilami
Gold Boarder
Gold Boarder
Posts: 591
Joined: 17.01.2011, 14:17

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

Postby marsupilami » 09.11.2017, 16:24

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

Fr0sT
Junior Boarder
Junior Boarder
Posts: 43
Joined: 08.05.2014, 12:08

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

Postby Fr0sT » 10.11.2017, 08:32

Hello Jan,
anyway the source sits there, right? Locating the issue is 50% of solving it :)


Return to “ZeosLib 7.2 Betatest Forum”

Who is online

Users browsing this forum: No registered users and 1 guest