PgSqlScript throwing "cannot insert multiple commands into a prepared statement"

PgSqlScript throwing "cannot insert multiple commands into a prepared statement"

Postby hmuscroft » Wed 07 Mar 2007 14:37

Hi,

I am trying to execute the following script using the PgSqlScript component :-

Code: Select all
CREATE OR REPLACE FUNCTION grant_std_user (IN  text)    
RETURNS bool AS
$BODY$
   begin
   EXECUTE 'GRANT ALL ON ' || $1 || ' to std_user';
   return TRUE;
   end;
$BODY$
   LANGUAGE PLpgSQL
   CALLED ON NULL INPUT
   VOLATILE
   EXTERNAL SECURITY INVOKER;

CREATE OR REPLACE FUNCTION before_post_record ()    
RETURNS trigger AS
$BODY$
   DECLARE
   staff_id int4;
   BEGIN
   staff_id := current_staff_id();
   
   if (TG_OP = 'INSERT') THEN
   if (NEW.id IS NULL) THEN
   NEW.ID := generate_id();
   END IF;
   NEW.createdon := current_timestamp;
   NEW.createdby_staff_id := staff_id;
   END IF;
   
   NEW.updatedon := current_timestamp;
   NEW.updatedby_staff_id := staff_id;
   
   RETURN NEW;
   END;
$BODY$
   LANGUAGE PLpgSQL
   CALLED ON NULL INPUT
   IMMUTABLE
   EXTERNAL SECURITY INVOKER;


The error being thrown is :-
CoreLab.PostgreSql.PgSqlException was unhandled by user code
Message="cannot insert multiple commands into a prepared statement"
Source="CoreLab.PostgreSql"
ErrorCode=-2147467259
CallStack=""
DetailMessage=""
FileName="postgres.c"
Hint=""
LineNumber=1125
Position=0
ProcedureName="exec_parse_message"
StackTrace:
at CoreLab.Common.DbScript.ExecuteNext(IDataReader& reader)
at CoreLab.Common.DbScript.Execute()
at co2005_to_2007_pgsql.frmMain.btStart_Click(Object sender, EventArgs e) in E:\vs2005\source\co2005_to_2007_pgsql\co2005_to_2007_pgsql\frmMain.cs:line 417
at System.Windows.Forms.Control.OnClick(EventArgs e)
.....


Can you help please?

Kind Regards,

Hedley Muscroft
hmuscroft
 
Posts: 23
Joined: Tue 28 Nov 2006 10:07

Postby Alexey » Mon 12 Mar 2007 07:56

You should add "/" symbol before second CREATE statement.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby hmuscroft » Tue 13 Mar 2007 14:46

Hi Alexey - thanks for the reply - it works now. I am a little confused though. If I execute a series of simple statements such as :-

Code: Select all
update table1 set field='something' where id=2;
update table2 set field='something' where id=3;

That all works fine without the need of inserting '/'s inbetween statements. When do I use '/'s and when is it not necessary? Also - is this standard PostgreSQL notation?

Thanks.
hmuscroft
 
Posts: 23
Joined: Tue 28 Nov 2006 10:07

Postby Alexey » Wed 14 Mar 2007 08:08

You should use '/'s when you need to separate two blocks, for example two CREATE statements.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby hmuscroft » Thu 15 Mar 2007 11:56

Ok - thanks for your help Alexey.
hmuscroft
 
Posts: 23
Joined: Tue 28 Nov 2006 10:07

Postby Alexey » Thu 15 Mar 2007 12:12

Not at all.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for PostgreSQL