INPUT/OUTPUT parameters and stored procedure

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
jkucera
Posts: 10
Joined: Wed 27 Jun 2007 08:39

INPUT/OUTPUT parameters and stored procedure

Post by jkucera » Sat 03 Dec 2011 21:56

Hi,

if I create a PgSqlCommand with parameters and the parameters direction is INPUT/OUTPUT, then after executing command the parameter values are not changed though they were modified on the server. If the param direction is OUTPUT only, it works as expected. I found the same issue at these Forums (http://www.devart.com/forums/viewtopic. ... 7d48680e55) but regarding SQL server (not Postgres) - the reply was that you reproduced the behaviour and it is really an issue. But it was in 2008 - any progress made since then?

(Duplicating parameters, one for INPUT and one for OUTPUT is a workaround, but not a solution).

Thanks for the reply.

Shalex
Site Admin
Posts: 8239
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 05 Dec 2011 16:05

I have run this code with dotConnect for PostgreSQL v 5.50.250 against PostgreSQL server v 9.1. My output is 2. Try running it in your environment and notify us about the results.

Code: Select all

CREATE OR REPLACE FUNCTION proc_1(
  INOUT parameter integer )
AS $$
DECLARE
BEGIN
  parameter := parameter + 1;

  RETURN;
END;
$$ LANGUAGE plpgsql;

Code: Select all

    using (PgSqlConnection conn = new PgSqlConnection()) {
        conn.ConnectionString = "server=db;port=5439;uid=postgres;pwd=postgres;database=postgres;";
        conn.Open();
        PgSqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = "proc_1";
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.Add("parameter", PgSqlType.Int).Direction = System.Data.ParameterDirection.InputOutput;
        cmd.Parameters["parameter"].Value = 1;
        cmd.ExecuteNonQuery();
        Console.WriteLine(cmd.Parameters["parameter"].Value);
        Console.ReadKey();
    }

Post Reply