cannot insert multiple commands into a prepared statement

[lalbin]

I am trying to execute several commands at once and have basically followed the sample on http://www.devart.com/dotconnect/postgr ... cript.html

When it gets to the Execute() it complains about "cannot insert multiple commands into a prepared statement"

Code: Select all

...
Dim script As String = _
   "BEGIN;" & _
   "DELETE FROM popsci_secure.mpmybt_fte_data WHERE version_id IN (SELECT version_id FROM popsci.mpmybt_data_versions WHERE save_id = '" & Me.Comments.SelectedItem.col5.ToString & "');" & _
   "DELETE FROM popsci.mpmybt_data_versions WHERE save_id = '" & Me.Comments.SelectedItem.col5.ToString & "';" & _
   "DELETE FROM popsci.mpmybt_save WHERE save_id = '" & Me.Comments.SelectedItem.col5.ToString & "';" & _
   "END;"
Dim pgScript = New Devart.Data.PostgreSql.PgSqlScript(script, db.conn)
db.Open()
pgScript.Execute()
db.Close()
...

Devart.Data.PostgreSql.PgSqlException was unhandled by user code
CallStack=""
DetailMessage=""
ErrorCode="42601"
FileName="postgres.c"
Hint=""
LineNumber=1166
Message="cannot insert multiple commands into a prepared statement"
Position=0
ProcedureName="exec_parse_message"
Source="Devart.Data.PostgreSql"
StackTrace:
at Devart.Data.PostgreSql.g.a(Boolean A_0, Boolean A_1)
at Devart.Data.PostgreSql.g.d(Boolean A_0)
at Devart.Data.PostgreSql.g.af()
at Devart.Data.PostgreSql.g.b(String A_0)
at Devart.Data.PostgreSql.g.l()
at Devart.Data.PostgreSql.PgSqlCommand.a(Boolean A_0, Int32 A_1, Int32 A_2)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at Devart.Common.DbScript.ExecuteSqlStatement(SqlStatement sqlStatement)
at Devart.Common.DbScript.b(SqlStatement A_0)
at Devart.Common.SqlStatement.Execute()
at Devart.Common.DbScript.ExecuteNext(IDataReader& reader)
at Devart.Common.DbScript.Execute()
at mpmybt.DeleteVersion.OK_Button_Click(Object sender, EventArgs e) in c:\Visual Studio 2008\Projects\mpmybt\mpmybt\DeleteVersion.vb:line 24
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
InnerException:

I was able to get around it using this method, it is just that I think that the above method is cleaner looking in the code.

Code: Select all

...
Dim delete_cmd As Devart.Data.PostgreSql.PgSqlCommand = db.conn.CreateCommand()
db.Open()
db.conn.BeginTransaction()
delete_cmd.CommandText = "DELETE FROM popsci_secure.mpmybt_fte_data WHERE version_id IN (SELECT version_id FROM popsci.mpmybt_data_versions WHERE save_id = '" & Me.Comments.SelectedItem.col5.ToString & "');"
delete_cmd.ExecuteNonQuery()
delete_cmd.CommandText = "DELETE FROM popsci.mpmybt_data_versions WHERE save_id = '" & Me.Comments.SelectedItem.col5.ToString & "';"
delete_cmd.ExecuteNonQuery()
delete_cmd.CommandText = "DELETE FROM popsci.mpmybt_save WHERE save_id = '" & Me.Comments.SelectedItem.col5.ToString & "';"
delete_cmd.ExecuteNonQuery()
db.conn.Commit()
db.Close()
...

[Shalex]

Unprepared execution with protocol 3 is implemented (the UnpreparedExecute property of PgSqlCommand) and will be available in the upcoming build of dotConnect for PostgreSQL.
Please refer to http://www.devart.com/forums/viewtopic.php?p=48034 where this issue was discussed.