PgSqlDataAdapter - Store Procedure - unnamed portal

PgSqlDataAdapter - Store Procedure - unnamed portal

Postby ak » Fri 12 Jun 2009 12:06

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.
ak
 
Posts: 2
Joined: Thu 11 Jun 2009 21:16

Postby Shalex » Fri 12 Jun 2009 15:54

We will investigate the issue and notify you about the results as soon as possible.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Thu 18 Jun 2009 08:55

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
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL