Anonymous block: "Parameter name is missing"

Anonymous block: "Parameter name is missing"

Postby Matthias3 » Tue 22 May 2012 16:24

Hello,

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();


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:

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();


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.
Matthias3
 
Posts: 8
Joined: Tue 22 May 2012 15:10

Re: Anonymous block: "Parameter name is missing"

Postby Pinturiccio » Thu 24 May 2012 12:47

Matthias3 wrote:However, now I get another exception: "Parameter name is missing".

We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1983
Joined: Wed 02 Nov 2011 09:44

Re: Anonymous block: "Parameter name is missing"

Postby Matthias3 » Fri 25 May 2012 07:07

Pinturiccio wrote:We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.

Great. I really appreciate your efforts.
Matthias3
 
Posts: 8
Joined: Tue 22 May 2012 15:10

Re: Anonymous block: "Parameter name is missing"

Postby Pinturiccio » Tue 29 May 2012 11:41

Anonymous code blocks do not support parameters http://www.postgresql.org/docs/9.0/static/plpython-do.html

As a workaround, you can use stored procedures.
Pinturiccio
Devart Team
 
Posts: 1983
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL