I'm attempting to use Npgsql to invoke a function (saved procedure) that can take a personality as parameter, however it does not work. Basically declare exactly the same function without parameters, or by having an INTEGER parameter, I recieve the end result sets which i want. After I declare the parameter as CHARACTER, it reduces. What's wrong?
This is actually 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;
Here is the C# code that i'm 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 attempted proclaiming the parameter as CHARACTER, CHARACTER(10), CHARACTER Different and CHARACTER Different(10)...
EDIT: I do not have any error message, but rather than obtaining the expected result set, I recieve a clear result set having a single column which has exactly the same title because the function I'm attempting to call.
You are passing a Unicode argument for an ASCII parameter.
Change this line:
parameter.DbType = System.Data.DbType.String;
parameter.DbType = System.Data.DbType.AnsiString;
varchar posts have been in Unicode, so long as the Unicode option around the database is enabled (see here). My prediction is the fact that it isn't, as well as your parameter is not able to transform itself in to the correct type to become passed with the function.
Unsure, if the has related to your condition, but yesterday I happened over the fact that PostgreSQL includes a "single-byte internal type" char that's not the same as the kind char(1). Maybe there's some confusion about these?
Which Npgsql version are you currently using?
Also, are you able to specify the parameter type using NpgsqlDbType? Sometimes the mapping is not exactly and Npgsql aren't able to find the function you are attempting to make use of and should not have great results.
Npgsql attempts to locate an exact match of function title and parameter types. DbString matches text parameter types. Can you mind to try it out and alter your parameter type to text?
I really hope it will help.