Re: Function with RETURN TYPE RECORD Called From JAVA - Mailing list pgsql-general

From Kris Jurka
Subject Re: Function with RETURN TYPE RECORD Called From JAVA
Date
Msg-id Pine.BSO.4.56.0405162251020.24910@leary.csoft.net
Whole thread Raw
List pgsql-general

On Sun, 16 May 2004, Sumita Biswas wrote:

> Hi All,
>
> I have a Function that returns a variable of Type RECORD.

No you don't.  You have a function that returns setof
Proc_ConferenceSummary which is different than returning record or setof
record.  There is a difference in calling conventions in that you must
specify the output type of a function that returns record when calling it.
This difference is not essential to the problem at hand though.


> When I execute this Function from JAVA and try to get the Return
> Variable in a ResultSet object I get the following Error:
>
> Exception in thread "main" java.lang.ClassCastException
>         at com.cisco.ccm.car.general.Test.testStoredProc(Test.java:119)
>
>     objCallStmt = objCARConn.prepareCall("{ ? = call
> Proc_ConferenceSummary(?,?,?,?,?) }");
>     objCallStmt.registerOutParameter(1, 12);
>
>     objCallStmt.setString(2,"3/5/2004");
>     objCallStmt.setString(3,"5/5/2004");
>     objCallStmt.setInt(4,1);
>     objCallStmt.setInt(5,1);
>     objCallStmt.setInt(6,5001);
>     objCallStmt.execute();
>     ResultSet rs = (ResultSet)objCallStmt.getObject(1);//THIS IS

This only works for functions that return refcursor.  Instead of using a
CallableStatement you should just use a regular PreparedStatement and a
SELECT query:

Connection conn = DriverManager.getConnection(...);
PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM
myfunction(?,?)");
pstmt.setInt(1,12);
pstmt.setString(2,"abc");
ResultSet rs = pstmt.executeQuery();

Kris Jurka

pgsql-general by date:

Previous
From: Stephan Szabo
Date:
Subject: Re: one to many
Next
From: David Teran
Date:
Subject: Re: RTRIM always used with JDBC?