I'm having trouble executing a batch statement. Here's my sample code:
Code: Select all
PgSqlConnection conn = new PgSqlConnection("...");
conn.Open();
PgSqlCommand cmd = conn.CreateCommand();
cmd.CommandText
= "delete from test where x = :P0; " +
"insert into test (x) values (:P1); ";
cmd.Parameters.AddWithValue("P0", 0);
cmd.Parameters.AddWithValue("P1", 0);
cmd.ExecuteNonQuery();
Code: Select all
PgSqlConnection conn = new PgSqlConnection("...");
conn.Open();
PgSqlCommand cmd = conn.CreateCommand();
cmd.CommandText
= "do $$ begin " +
"delete from test where x = :P0; " +
"insert into test (x) values (:P1); " +
"end; $$";
cmd.Parameters.AddWithValue("P0", 0);
cmd.Parameters.AddWithValue("P1", 0);
cmd.ExecuteNonQuery();
Finally I found that forcing unprepared execution solves the problem (cmd.UnpreparedExecute = true): Both types of statements work, simple command lists and wrapped into a block; so it is possible to pass parameters to anonymous blocks. However, I don't want to forgo statement preparation for performance reasons.
So my question is: Is there a way to pass parameters to a batch statement with prepared execution?
Thanks!
P.S.
Provider version is 5.80.350.0, Database server is 9.1.3.