Hi, I have a function in postgresql v. 8.2.6 like this

CREATE OR REPLACE FUNCTION testcursore(refcursor) RETURNS refcursor AS $BODY$ begin OPEN $1 FOR EXECUTE 'SELECT * FROM mytable'; return $1; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION testcursore(refcursor) OWNER TO postgres;

that returns a dinamic cursor.

my table is something like

CREATE TABLE mytable ( id serial NOT NULL, dex character varying(30) NOT NULL )

The generated code (see below) has errors in class RetrievalProcedures.cs at lines

10 (misses comma between parameters) 19 (misses comma between parameters) 22 (misses parameter creation)

Adding the missing commas and something like parameters[0]=new NpgsqlParameter("param4", NpgsqlDbType.Refcursor, 0, "", ParameterDirection.Input, true, 0, 0, DataRowVersion.Current, param4) on line 22 everything works fine.

Third function at line 32 is ok.

LlblGenPro v.2.6 October 6th, 2008, framework 2.0 as target platform.

Can You help? Thanks in advance

1 /// <summary> 2 /// Calls stored procedure 'testcursore'.<br/><br/> 3 /// 4 /// </summary> 5 /// <returns>Filled DataSet with resultset(s) of stored procedure</returns> 6 public static DataSet Testcursore(System.Object param4) 7 { 8 using(DataAccessAdapter adapter = new DataAccessAdapter()) 9 { 10 return Testcursore(param4 adapter); 11 } 12 }

13 /// <summary> 14 /// Calls stored procedure 'testcursore'.<br/><br/> 15 /// 16 /// </summary> 17 /// <param name="adapter">The DataAccessAdapter object to use for the call</param> 18 /// <returns>Filled DataSet with resultset(s) of stored procedure</returns> 19 public static DataSet Testcursore(System.Object param4DataAccessAdapter adapter) 20 { 21 NpgsqlParameter[] parameters = new NpgsqlParameter[1]; 22 23 DataSet toReturn = new DataSet("Testcursore"); 24 bool hasSucceeded = adapter.CallRetrievalStoredProcedure("public.testcursore", parameters, toReturn); 25 return toReturn; 26 }

27 /// <summary>Creates an IRetrievalQuery object for a call to the procedure 'testcursore'. 28 /// 29 /// </summary> 30 /// <param name="param4">Input parameter of stored procedure</param> 31 /// <returns>IRetrievalQuery object which is ready to use for datafetching</returns> 32 public static IRetrievalQuery GetTestcursoreCallAsQuery(System.Object param4) 33 { 34 RetrievalQuery toReturn = new RetrievalQuery( new NpgsqlCommand("public.testcursore" ) ); 35 toReturn.Parameters.Add(new NpgsqlParameter("param4", NpgsqlDbType.Refcursor, 0, "", ParameterDirection.Input, true, 0, 0, DataRowVersion.Current, param4)); 36 37 toReturn.Command.CommandType = CommandType.StoredProcedure; 38 return toReturn; 39 }

Mmm. I don't know if this is supported. We will look into it.

Your function appears to have a refcursor IN parameter without a name (see: http://www.postgresql.org/docs/8.3/interactive/sql-createfunction.html )

This is allowed by postgresql, though it's not supported: you can't have refcursor IN parameters on procs. As it seems your in-parameter doesn't have any relevance in the function itself, could you try removing it from the function declaration and try again (after a refresh) ?

Removing the refcursor, as you suggested, leads to the following error:

{Npgsql.NpgsqlException: cursor "<unnamed portal 1>" does not exist Severity: ERROR Code: 34000 in Npgsql.NpgsqlConnector.CheckErrors() in Npgsql.NpgsqlConnector.CheckErrorsAndNotifications() in Npgsql.NpgsqlCommand.ExecuteCommand() in Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb) in Npgsql.NpgsqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior cb) in System.Data.Common.DbDataAdapter.FillInternal [...]

As you noticed, we don't actually use the refcursor in the function, we only pass in a string in order to give a name to the cursor itseft... from the postgres docs:

"To specify a portal name, simply assign a string to the refcursor variable before opening it. The string value of the refcursor variable will be used by OPEN as the name of the underlying portal"

see http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html at 38.7.3.5. Returning Cursors

Anyway, making the corrections we suggested in the first post, everything works perfectly, so, waiting for a better solution, we planned to move the self-hand-corrected generated code into the user-def code section.

Hoping for good news, thanks for now.

To pass in a string, you should define the parameter type as varchar for example (e.g. varchar(50)), as llblgen pro will now think it's a cursor and cursors are always assumed to be output, never input. As it's a cursor, the template code will make mistakes (as an input cursor isn't anticipated/expected)

The type you specify in the function declaration is the type of the parameter, so I think it should be a string type, e.g. varchar. Then, don't pass in a refcursor but declare it locally and assign the passed in name to it.

Could you try that for me please?

Resolved !!! Postgres wants a transaction and a commit command even for a function with only select statements. So creating a function that returns a refcursor without any in parameters and using a transaction, works fine.

Thank you for your support and patience.

quid wrote:

Resolved !!! Postgres wants a transaction and a commit command even for a function with only select statements. So creating a function that returns a refcursor without any in parameters and using a transaction, works fine.

Thank you for your support and patience.

Glad it's solved!