Anonymous block: "Parameter name is missing"
Posted: Tue 22 May 2012 16:24
Hello,
I'm having trouble executing a batch statement. Here's my sample code:
I learned that this doesn't work with prepared statements (http://forums.devart.com/viewtopic.php?t=21348), so I started using an anonymous block:
However, now I get another exception: "Parameter name is missing". This seems to mean that the specified parameters do not occur in the statement.
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.
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.