BUG #12681: Cannot Read Refcursor from JAVA

From: ousema2003(at)yahoo(dot)fr
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #12681: Cannot Read Refcursor from JAVA
Date: 2015-01-27 13:59:04
Message-ID: 20150127135904.19615.32001@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 12681
Logged by: Cannot Read Refcursor from JAVA
Email address: ousema2003(at)yahoo(dot)fr
PostgreSQL version: 9.2.0
Operating system: linux x86 64 / centos 5.9
Description:

Hi
i created fucntion in postgresql ... returning refcursor.

CREATE OR REPLACE FUNCTION popa()
RETURNs refcursor as
$$
declare
c refcursor;
BEGIN
OPEN c FOR SELECT * FROM contract ;
return c;
END;
$$
LANGUAGE 'plpgsql';

and later i developed java program to read this ref cursor :

import java.net.URI;
import java.net.URISyntaxException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Struct;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Date;

import com.impossibl.postgres.api.jdbc.PGConnection;
import com.impossibl.postgres.datetime.instants.Instant.Type;
import com.impossibl.postgres.jdbc.PGCallableStatement;
import com.impossibl.postgres.jdbc.PGDataSource;
import com.impossibl.postgres.types.ArrayType;

public class test2 {

public static PGDataSource datasource = null;
public static PGConnection connection = null;
public static ResultSet rs1 = null;
public static ResultSet rs2 = null;

public static void main(String args[]) {
datasource = createDataSource();
connection = getPGConnection(datasource);
displayRecsRefCursorOut();
}

public static void displayRecsRefCursorOut() {
try {
connection.setAutoCommit(false);
CallableStatement cs = null;
cs = connection.prepareCall("{ ? = call popa()}");
cs.registerOutParameter(1, Types.OTHER);
cs.execute();

ResultSet rs = (ResultSet) cs.getObject(1);

while (rs.next()) {
System.out.println(rs.getString(1));
}
} catch (SQLException e) {
e.printStackTrace();
}
}

public static PGDataSource createDataSource() {

PGDataSource pgd = new PGDataSource();
try {

pgd.setHost("xx.xx.xx.xx");
pgd.setPort(5432);
pgd.setDatabase("oussdb");
pgd.setUser("ouss");
pgd.setPassword("xxx");
} catch (Exception e) {
e.printStackTrace();
}
return pgd;
}

public static PGConnection getPGConnection(PGDataSource pgdatasource) {
PGConnection pgconnection = null;
try {
pgconnection = (PGConnection) pgdatasource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return pgconnection;
}
}

but i get this error : Exception in thread "main"
java.lang.ClassCastException: java.lang.String cannot be cast to
java.sql.ResultSet
at test2.displayRecsRefCursorOut(test2.java:41)
at test2.main(test2.java:30)

and i failed reading the refcursor..

could you please advice how can i deal with this err ...

Browse pgsql-bugs by date

  From Date Subject
Next Message Andrew Gierth 2015-01-28 01:05:20 Re: BUG #12670: Can't create postgis extension in single-user mode
Previous Message Heikki Linnakangas 2015-01-27 11:38:39 Re: BUG #12675: BIGINT Datatype performance