Thread: Getting Reference cursors

Getting Reference cursors

From
Andrés Luna
Date:
Hi guys
 
Im using the JDBC driver to connect to a Postgres Database. I have a function that returns a reference cursor and it works ok returning result itself. However, whenever I try to use the result set meta data of the ref cursor it doesn't work. Let me explain better:
// procedure call
CallableStatement proc = con.prepareCall("{ ? = call doquery ( ? ) }");
proc.registerOutParameter(1, Types.Other);
proc.setInt(2, -1);
proc.execute();
ResultSet results = (ResultSet) proc.getObject(1); //this the ref. cursor
ResultSetMetaData rm = results.getMetaData(); //result set meta data of the ref. cursor
System.out.println(rm); //prints the reference of the object so it is not null
int columnCount = rm.getColumnCount() //NullPointerException :(
results.close();
proc.close();
 
The rm object in this code is not null. However, the internall data of it, such as column counts and column names is null. Am I doing something wrong ? Is it possible to obtain the result set meta data from a Reference cursor returned from an function. If not, how do you get the column names ??
 
The driver version im using is : pg74.216.jdbc3.jar
 
Tks in advance for any help !!
 
 
 
ANDRES LUNA TAMAYO
Software Developer
IFX NETWORKS
(571) 3693000
 
***** AVISO DE CONFIDENCIALIDAD ***** 
ESTE MENSAJE, (INCLUYENDO CUALQUIER ANEXO) ESTÁ DIRIGIDO ÚNICAMENTE A LOS DESTINATARIOS ARRIBA SEÑALADOS. PUEDE CONTENER INFORMACIÓN CONFIDENCIAL O PRIVILEGIADA Y NO DEBE SER LEÍDO, COPIADO O DE OTRA FORMA UTILIZADO POR CUALQUIER OTRA PERSONA. SI USTED RECIBE ESTA COMUNICACIÓN POR ERROR, FAVOR AVISAR AL REMITENTE Y ELIMINAR EL MENSAJE DE SU SISTEMA.
 
THIS E-MAIL (INCLUDING ANY ATTACHMENTS) IS INTENDED ONLY FOR THE RECIPIENT(S) NAMED ABOVE. IT MAY CONTAIN CONFIDENTIAL OR PRIVILEGED INFORMATION AND SHOULD NOT BE READ, COPIED OR OTHERWISE USED BY ANY OTHER PERSON. IF YOU ARE NOT A NAMED RECIPIENT, PLEASE CONTACT THE SENDER AND DELETE THE E-MAIL FROM YOUR SYSTEM. 
 
 

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.6.6 - Release Date: 08/06/2005

Re: Getting Reference cursors

From
Kris Jurka
Date:

On Fri, 10 Jun 2005, [iso-8859-1] Andr�s Luna wrote:

> Im using the JDBC driver to connect to a Postgres Database. I have a
> function that returns a reference cursor and it works ok returning
> result itself. However, whenever I try to use the result set meta data
> of the ref cursor it doesn't work. Let me explain better:

This is fixed in the 8.0 driver.

Kris Jurka


Re: Getting Reference cursors

From
Andrés Luna
Date:
Kris

Thanks for your quick answer. However, now I change to the 8.0 driver
and still doesn´t work. The same code now fails during stored procedure
execution. Here is the stack trace.

java.sql.SQLException: ERROR: cursor "<unnamed portal 1>" does not exist
    at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecu
torImpl.java:1471)
    at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImp
l.java:1256)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:
175)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stateme
nt.java:389)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdb
c2Statement.java:314)
    at
org.postgresql.jdbc2.AbstractJdbc2Connection.execSQLQuery(AbstractJdbc2C
onnection.java:192)
    at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.internalGetObject(AbstractJd
bc2ResultSet.java:165)
    at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getObject(AbstractJdbc2Resul
tSet.java:2328)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdb
c2Statement.java:342)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stateme
nt.java:321)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.jav
a:39)
    at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor
Impl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:324)
    at
org.postgresql.ds.common.PooledConnectionImpl$StatementHandler.invoke(Po
oledConnectionImpl.java:467)
    at $Proxy1.execute(Unknown Source)
    ...

Tks in advance!

-----Mensaje original-----
De: Kris Jurka [mailto:books@ejurka.com]
Enviado el: domingo, 12 de junio de 2005 1:07
Para: Andrés Luna
CC: pgsql-jdbc@postgresql.org
Asunto: Re: [JDBC] Getting Reference cursors




On Fri, 10 Jun 2005, [iso-8859-1] Andrés Luna wrote:

> Im using the JDBC driver to connect to a Postgres Database. I have a
> function that returns a reference cursor and it works ok returning
> result itself. However, whenever I try to use the result set meta data

> of the ref cursor it doesn't work. Let me explain better:

This is fixed in the 8.0 driver.

Kris Jurka

--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.6.9 - Release Date: 11/06/2005


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.6.9 - Release Date: 11/06/2005



Re: Getting Reference cursors

From
Kris Jurka
Date:

On Mon, 13 Jun 2005, [iso-8859-1] Andr�s Luna wrote:

> Thanks for your quick answer. However, now I change to the 8.0 driver
> and still doesn�t work. The same code now fails during stored procedure
> execution. Here is the stack trace.
>
> java.sql.SQLException: ERROR: cursor "<unnamed portal 1>" does not exist

I'm not sure why this is happening, perhaps you have autocommit on?
Although that should have caused a failure with the 7.4 driver as well.
I've attached the test case I used to verify this works.  Could you show
us the code that is failing here?

Kris Jurka
Attachment