Geek Answers Handbook

Using npgsql to call a function that takes a character as a parameter

I am trying to use Npgsql to call a function (stored procedure) that takes a CHARACTER parameter as a parameter, but it does not work. If I declare the same function with no parameters or with an INTEGER parameter, I get the desired result factors. When I declare a CHARACTER parameter, it stops working. What's 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 use:

 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 am not getting an error message, but instead of getting the expected result set, I get an empty result set with one column that has the same name as the function I'm trying to call.

+4
source share
4 answers

You pass a Unicode argument to an ASCII parameter.

Change this line:

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

To:

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

Typically, Postgres varchar columns are in Unicode, provided that the Unicode option in the database is enabled ( see here ). I assume this is not the case, and your parameter cannot convert itself to the correct type, which will be passed through the function.

+5
source

What version of Npgsql are you using?

Also can you specify the parameter type using NpgsqlDbType? Sometimes the mapping is not entirely accurate, and Npgsql cannot find the function you are trying to use, and cannot make it work.

Npgsql tries to find the exact match between the function name and parameter types. DbString matches types of text parameters. Could you try to try and change your parameter type to text?

Hope this helps.

+2
source

Not sure if this is related to your problem, but yesterday I stumbled upon the fact that PostgreSQL has a "single-byte internal type" char, which is different from the type char (1). Maybe there is some kind of confusion in this?

+1
source

I tried with the approach below (which is similar to yours):

 using (NpgsqlConnection connection = new NpgsqlConnection(< connectionString >)) { DataSet ds = new DataSet(); DataTable dt = new DataTable(); connection.Open(); NpgsqlCommand cmd = new NpgsqlCommand(< name of your SP >, connection); cmd.CommandType = CommandType.StoredProcedure; var param = cmd.CreateParameter(); param.ParameterName = < exact parameterName you used in your SP>; param.DbType = System.Data.DbType.AnsiString; param.Value = < parameter value >; cmd.Parameters.Add(param); NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(cmd); adapter.Fill(ds); dt = ds.Tables[0]; }

This works fine for me and I get the correct DataTable.

0
source

More articles:


All Articles