PgSqlDataAdapter - Store Procedure - unnamed portal

[ak]

This is my Store

CREATE OR REPLACE FUNCTION sp_agente_seleccionar(IN "pId" integer, INOUT pcursordef refcursor) RETURNS refcursor AS
$BODY$
BEGIN
OPEN pcursordef FOR
SELECT * FROM "Agentes"
WHERE "Agentes".id = "pId";
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION sp_agente_seleccionar(integer, refcursor) OWNER TO postgres;

This is my Call

var cnn = (PgSqlConnection)this.CreateConnection();
cnn.Open();
var dbCommand = cnn.CreateCommand();
dbCommand.CommandText = com;
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.Parameters.Add(new PgSqlParameter("pId",1));
dbCommand.Parameters.Add(new PgSqlParameter("pcursordef",DBNull.Value));
dbCommand.ParameterCheck = true;
var ds = new DataSet();
var da = new PgSqlDataAdapter(dbCommand);
da.Fill(ds);
cnn.Close();
return ds;

And the result is ds.Tables[0].Rows[0][0] =

I've been prooving diferent ways and all result in the same behavior.

[Shalex]

We will investigate the issue and notify you about the results as soon as possible.

[Shalex]

The DataSet object can not be filled by the function posted above because refcursor is returned as a parameter, so its content can not be accessed.

But you can obtain the necessary result by executing the Fill() method withing transaction using the following function:

Code: Select all

CREATE OR REPLACE FUNCTION sp_agente_seleccionar(IN "pId" integer) RETURNS refcursor AS
$BODY$
DECLARE pcursordef refcursor;
BEGIN
OPEN pcursordef FOR SELECT * FROM "Agentes" WHERE "Agentes".id = "pId"; 
RETURN pcursordef;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE