CODEWITHSUNDEEP
c#postgresqlnpgsql
Greg Finzer
Greg Finzer

Reputation: 7074

How to do an out parameter with Npgsql and PostgreSQL?

I am learning Npgsql and PostgreSQL. I am unable to define the output parameter correctly. What am I doing wrong?

Here is the function:

CREATE OR REPLACE FUNCTION Insert_Customer_WithOutputParameter(
    IN _FirstName character varying DEFAULT NULL::character varying,
    IN _LastName character varying DEFAULT NULL::character varying,
    OUT _CustomerID integer)
  RETURNS integer as
$BODY$
BEGIN
INSERT INTO Customers (FirstName, LastName) VALUES (_FirstName, _LastName);

SELECT _CustomerID = lastval();

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Here is the code: 

[Test]
public void ExecuteNonQuerySproc()
{
    NpgsqlConnection conn = new NpgsqlConnection("Host=localhost; Database=postgres; User ID=postgres; Password=password");
    conn.Open();
    IDbCommand command = conn.CreateCommand();
    command.CommandText = "Insert_Customer_WithOutputParameter";
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(new NpgsqlParameter("@FirstName", "John"));
    command.Parameters.Add(new NpgsqlParameter("@LastName", "Smith"));
    NpgsqlParameter outParm = new NpgsqlParameter("@CustomerID", NpgsqlDbType.Integer)
    {
        Direction = ParameterDirection.Output
    };
    command.Parameters.Add(outParm);

    command.ExecuteNonQuery();            
    conn.Close();
    Console.WriteLine(outParm.Value);
}

Here is the error message I am getting: Npgsql.NpgsqlException : ERROR: 42601: query has no destination for result data

Upvotes: 3

Views: 5975

Answers (1)

Shay Rojansky
Shay Rojansky

Reputation: 16722

The following doesn't work:

SELECT _CustomerID = lastval();

Replace it with a simple: 

_CustomerID = lastval();

Note that Npgsql currently binds parameters by position only, and not by name. This means that the names you give in the NpgsqlParameter instances mean nothing - their order of addition must correspond to the function's declaration. Npgsql 3.1 will support named binding of function arguments (see this issue).

Upvotes: 1

Related Questions