Re: [GENERAL] plpgsql function with RETURNS SETOF refcursor in JAVA

Yes, I was thinking to post my solution in this list for any who need to know how to receive more than one cursors from a function stored in a PostgreSQL database, but, living and working in Argentina, made an impossible work last week ( abnormal tasks to do are normal here !!!! ) , but now I have 5 minutes, then, I decided to post the java code.

The solution is very, very simple.... having a stored function with, for example, 2 input parameters and 2 cursors returned, I do not use RETURNS SETOF REFCURSORS in the function, but I have use the key OUT as out parameter ( just I use in Oracle Stored Procedures ) in the function, then, in the java program, after made the connection to the database, I have used the CallableStatement setting the value of all the inputs parameters with the values and type needed in the function, and as for the data returned ( in this case the cursors ), I have used the REGISTEROUTPARAMETER ( this method is provided by the calllablestatement java class ) with each value returned ( in this case the cursors ) numbered from 1 for the first out parameter and n for the last, informing the type TYPES.OTHER too. After made the java call ( callablestatement.execute() ), I set a public ( in this example ) variable as a RECORDSET and assigning each cursor to each RECORDSET variable :

I use the integer variable called num_cursor for the first returned and num_cursor_02 for the second ( je .. I have broken my mind and brain thinking for the perfect names ... ).....

rs = (ResultSet) cs.getObject(num_cursor);
rs1 = (ResultSet) cs.getObject(num_cursor_02);

the data obtained by the GETOBJECT method, being a cursor, must be parsed to a RECORDSET, and not, is very important this point, never use the name you gave to each cursor in the function stored, simply provide a number for which cursor want to put in each recordset variable..... after, using a java loop as "while recordset.next()" method, you can extract each value with getxxx(namedvariable) returned into each cursor

and it's all .......

I hope I have been the most clear as my poor level of English could be....

Many thanks for all and specially to the postgresql community list !!!!!!

-----Mensaje original-----
De: Kevin Grittner [mailto:kgrittn(at)gmail(dot)com]
Enviado el: viernes, 11 de diciembre de 2015 06:54 p.m.
Para: Corradini, Carlos
CC: Adrian Klaver; pgsql-jdbc(at)postgresql(dot)org; pgsql-general(at)postgresql(dot)org; Kris Jurka
Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA

On Fri, Dec 11, 2015 at 2:11 PM, Corradini, Carlos <CCorradini(at)correoargentino(dot)com(dot)ar> wrote:

> with your and Mr. Kevin explanations, the Java program have worked
> fine and have printed the data obtained from a two cursors inside a
> PostgreSQL Database Stored Function.
>
> Then, I can confirm that this version of DB ( 9.4 ) use the OUT
> parameter with refcursors and works fine. The JDBC interface provided
> by the Server Postgresql can read the data inserted into these two
> cursors via a callablestatement.registeroutparameter.

For the benefit of others who may later have a similar problem and find this thread, it would be great if you could provide a little self-contained example of a Java program which uses the technique that you settled on.

Thanks!

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company