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.Cre…

Accepted Answer

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.

Using npgsql to call a function that takes character as parameter

Solution

Problem