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.

Answer 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.

Answer 2

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?

Answer 3

Hi!

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.