how to fill the DataSet via DataAdapter for a refcursor returned by a plpgsql proc

how to fill the DataSet via DataAdapter for a refcursor returned by a plpgsql proc

Postby SeshuKumarRSR » Fri 03 Aug 2007 08:01

I'm trying to figure out how to fill the DataSet via DataAdapter for a refcursor returned by a plpgsql proc:

CREATE OR REPLACE FUNCTION test_reffunc()
RETURNS refcursor AS
$BODY$
DECLARE
v_refcursor refcursor;

BEGIN
OPEN v_refcursor FOR SELECT col1 FROM test;
RETURN v_refcursor;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION test_reffunc() OWNER TO postgres;

I am using the follwing code snippet in C#:

However, It is returning the value the name of the cursor: in a single row. Is there a way to have the fetch all accomplished by the corelab component on this cursor ? I am using the enteprise library components.
SeshuKumarRSR
 
Posts: 2
Joined: Fri 03 Aug 2007 07:24

Postby Alexey » Fri 03 Aug 2007 08:34

I don't see your C# code, but if I execute your function via pgAdmin, I will get "" as well. So the problem is in the function itself.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby SeshuKumarRSR » Fri 03 Aug 2007 08:45

Sorry I did not placed the code. Here is the code

private static DataTable GetDataTable(PgSqlConnection connection, CommandType commandType, string commandText, params PgSqlParameter[] commandParameters)
{
//create a command and prepare it for execution
PgSqlCommand cmd = new PgSqlCommand();
DataSet ds = new DataSet();
try
{
PgSqlTransaction trn;
trn = connection.BeginTransaction();
PrepareCommand(cmd, connection,(PgSqlTransaction)null, commandType, commandText, commandParameters);
//create the DataAdapter & DataSet
//cmd.Connection.BeginTransaction();
PgSqlDataAdapter da = new PgSqlDataAdapter(cmd);
//PgSqlDataReader dr;
//dr = cmd.ExecuteReader();
//fill the DataSet using default values for DataTable names, etc.
da.AcceptChangesDuringFill = true;
da.Fill(ds);
trn.Commit();
// detach the PgSqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();

}
catch (Exception ex)
{
throw ex;
}
//return the DataTable
return ds.Tables[0];
}
SeshuKumarRSR
 
Posts: 2
Joined: Fri 03 Aug 2007 07:24

Postby Alexey » Fri 03 Aug 2007 13:47

Any code doesn't make sense, actually.
As I mentioned the problem is in the function, because pgAdmin returns the same value as our provider.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for PostgreSQL