INPUT/OUTPUT parameters and stored procedure

INPUT/OUTPUT parameters and stored procedure

Postby 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.php?t=20390&sid=075dd4b484316ae850e1d97d48680e55) 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.
jkucera
 
Posts: 10
Joined: Wed 27 Jun 2007 08:39

Postby 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();
    }
Shalex
Devart Team
 
Posts: 7781
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL