Cannot insert multiple commands into a prepared statement

[ai3000]

Hello,

I've problem with use "begin ... end" blocks in TSQLQuery. For example, next code raises exception "Cannot insert multiple commands into a prepared statement"

Code: Select all

var
  DB: TSQLConnection;
  Q: TSQLQuery;
begin

  DB:=TSQLConnection.Create(Application);
  DB.DriverName    := 'DevartPostgreSQL';
  DB.LibraryName   := 'dbexppgsql.dll';
  DB.VendorLib     := 'dbexppgsql.dll';
  DB.GetDriverFunc := 'getSQLDriverPostgreSQL';
  DB.Params.Clear;
  DB.Params.Add('User_Name=postgres');
  DB.Params.Add('Password=postgres');
  DB.Params.Add('Database=db');
  DB.Params.Add('HostName=127.0.0.1');
  DB.Params.Add('ServerCharSet=WIN1251');

  Q:=TSQLQuery.Create(Application);
  Q.SQLConnection := DB;
  Q.SQL.Add('begin ;');
  Q.SQL.Add('  insert into test(name) values(''123'');');
  Q.SQL.Add('  insert into test(name) values(''234'');');
  Q.SQL.Add('end;');
  Q.ExecSQL;

end;

When I use dbExpress components for Oracle all is ok.
How can I use "begin; end;" blocks in PostgreSQL using dbExpress drivers? Is there another ways of using such kind of blocks?

[AlexP]

Hello,

You can use the following code to work with anonymous blocks in PostgreSQL:

Code: Select all

  Q.SQL.Add('do $$');
  Q.SQL.Add('begin'); 
  Q.SQL.Add('  insert into test(name) values(''123'');'); 
  Q.SQL.Add('  insert into test(name) values(''234'');'); 
  Q.SQL.Add('end;'); 
  Q.SQL.Add('$$');
  Q.ExecSQL; 

You can get more informaion in the PostgreSQL documentation.

[ai3000]

Thank you for quick answer!

But, this method work correctly only on PostgreSQL 9.0+. We use PostgreSQL 8.4.

Is there another ways of using anonymous blocks with PostgreSQL 8.4?

[AlexP]

Hello,

PostgreSQL supports anonymous blocks starting with the 9 version. That's why you cannot work with them using the 8.4 version.