Anonymous block: "Parameter name is missing"

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
Matthias3
Posts: 8
Joined: Tue 22 May 2012 15:10

Anonymous block: "Parameter name is missing"

Post by 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.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Anonymous block: "Parameter name is missing"

Post by 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.

Matthias3
Posts: 8
Joined: Tue 22 May 2012 15:10

Re: Anonymous block: "Parameter name is missing"

Post by 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.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Anonymous block: "Parameter name is missing"

Post by Pinturiccio » Tue 29 May 2012 11:41

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

As a workaround, you can use stored procedures.

Post Reply