Using npgsql to call a function that takes character as parameter

Question

I am trying to use Npgsql to invoke a function (stored procedure) that takes a CHARACTER as parameter, but it doesn't work. If I declare the same function without parameters, or with an INTEGER parameter, I get the result sets that I want. When I declare the parameter as CHARACTER, it stops working. What is wrong?

Here is the code of my function:

CREATE OR REPLACE FUNCTION testrefcursor1(in xxx character varying(10)) RETURNS SETOF refcursor AS
$$
DECLARE 
  ref1 refcursor;
  ref2 refcursor;
BEGIN

OPEN ref1 FOR 
 SELECT * FROM accounts;
RETURN NEXT ref1;

OPEN ref2 FOR 
 SELECT * FROM accounts;
RETURN NEXT ref2;

RETURN;
END;
$$
LANGUAGE plpgsql;

And here is the C# code that I am using:

var connection = new Npgsql.NpgsqlConnection(connectionString.ConnectionString);

connection.Open();
var trans = connection.BeginTransaction();

var command = new Npgsql.NpgsqlCommand("testrefcursor1", connection);
command.CommandType = System.Data.CommandType.StoredProcedure;

var parameter = command.CreateParameter();
parameter.ParameterName = "xxx";
parameter.DbType = System.Data.DbType.String;
parameter.Value = "10";
command.Parameters.Add(parameter);

var da = new Npgsql.NpgsqlDataAdapter(command);
var ds = new System.Data.DataSet();
da.Fill(ds);

trans.Commit();
connection.Close();

I already tried declaring the parameter as CHARACTER, CHARACTER(10), CHARACTER VARYING and CHARACTER VARYING(10)...

EDIT: I don't get any error message, but instead of getting the expected result set, I get an empty result set with a single column that has the same name as the function I am trying to call.

Solution 1

You're passing a Unicode argument to an ASCII parameter.

Change this line:

parameter.DbType = System.Data.DbType.String;

To:

parameter.DbType = System.Data.DbType.AnsiString;

Generally, Postgres's varchar columns are in Unicode, provided that the Unicode option on the database is enabled (see here). My guess is that it's not, and your parameter is unable to convert itself into the correct type to be passed through the function.

Solution 2

Which Npgsql version are you using?

Also, can you specify the parameter type using NpgsqlDbType? Sometimes the mapping isn't exactly and Npgsql can't find the function you are trying to use and can't make it work.

Npgsql tries to find an exact match of function name and parameter types. DbString matches text parameter types. Would you mind to give it a try and change your parameter type to text?

I hope it helps.

Solution 3

Not sure, if this has to do with your problem, but yesterday I stumbled across the fact that PostgreSQL has a "single-byte internal type" char that is different from the type char(1). Maybe there is some confusion about these?

Comments

[Eric]

...what's wrong with Postgres?

[Antoine Aubry]

I tried with DbType.String, DbType.AnsiString, DbType.StringFixedLength and DbType.AnsiStringFixedLength, both with and without specifying a parameter length and none worked :(

[Antoine Aubry]

I don't get any error message, but instead of getting the expected result set, I get an empty result set with a single column that has the same name as the function I am trying to call.

[Antoine Aubry]

I don't think that I am using this data type. Thanks anyways.

[Antoine Aubry]

According to the documentation, I should specify only the name of the function: "If you have parameters in your function, assign only the function name to the CommandText property and add parameters to the NpgsqlCommand.Parameters collection as usual. Npgsql will take care of binding your parameters correctly." npgsql.projects.postgresql.org/docs/manual/UserManual.html

[Whoever]

If you look at the text after the h3-heading "Using parameters in a query" and the definition of the first NpgsqlCommand in following example, I'd say it's worth a try to use :xxx

[Antoine Aubry]

Thanks for your answer. I am no longer working on that project, but I will try to find time to try it. I will post my results here.