Re: [GENERAL] plpgsql function with RETURNS SETOF refcursor in JAVA - Mailing list pgsql-jdbc

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
afunction stored in a PostgreSQL database, but, living and working in Argentina, made an impossible work last week (
abnormaltasks 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
usein Oracle Stored Procedures ) in the function, then, in the java program, after made the connection to the database,
Ihave 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
isprovided by the calllablestatement java class ) with each value returned ( in this case the cursors ) numbered from 1
forthe 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
eachRECORDSET variable : 
 

I use the integer variable called num_cursor for the first returned and num_cursor_02 for the second ( je .. I have
brokenmy 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
thispoint, never use the name you gave to each cursor in the function stored, simply provide a number for which cursor
wantto put in each recordset variable..... after, using a java loop as "while recordset.next()" method, you can extract
eachvalue 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@gmail.com] 
Enviado el: viernes, 11 de diciembre de 2015 06:54 p.m.
Para: Corradini, Carlos
CC: Adrian Klaver; pgsql-jdbc@postgresql.org; pgsql-general@postgresql.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@correoargentino.com.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
providea 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