Cannot insert multiple commands into a prepared statement

Cannot insert multiple commands into a prepared statement

Postby ai3000 » Thu 23 Jun 2011 10:55

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?
ai3000
 
Posts: 2
Joined: Thu 23 Jun 2011 10:40

Postby AlexP » Thu 23 Jun 2011 11:26

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.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Postby ai3000 » Thu 23 Jun 2011 11:42

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?
ai3000
 
Posts: 2
Joined: Thu 23 Jun 2011 10:40

Postby AlexP » Thu 23 Jun 2011 12:05

Hello,

PostgreSQL supports anonymous blocks starting with the 9 version. That's why you cannot work with them using the 8.4 version.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35


Return to dbExpress driver for PostgreSQL