aspose file tools
The moose likes JDBC and Relational Databases and the fly likes call stored procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Storm Applied this week in the Other Open Source APIs forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "call stored procedure" Watch "call stored procedure" New topic
Author

call stored procedure

Manikandan Periyasamy
Greenhorn

Joined: Aug 13, 2004
Posts: 9
hello everybody,

I have created a function to display the rows in a table. The function is
returning the refcursor. I am calling the refcursor in Callable Statement, but
i am getting No class found for refcursor. I have seen these queries earlier in
this forum.

I am using postgresql 7.3.4 and pg74.215.jdbc3.jar.

As per the solutions given for earlier queries i tried to give getString(1), but
i am getting null value.

i am attaching the code along with this mail.

This is my pl/pgsql

CREATE or REPLACE FUNCTION displayall() RETURNS refcursor AS '
DECLARE
cursor1 cursor for select pageid,keyword from Maintable;

BEGIN
open cursor1;
return cursor1;
END;

'language 'plpgsql';

My jdbc code is

con.setAutoCommit(false);
CallableStatement cstmt2=null;
cstmt2=con.prepareCall("{? = call displayall()}");
cstmt2.registerOutParameter(1,Types.OTHER);
cstmt2.executeUpdate();
ResultSet rs2=(ResultSet)cstmt2.getObject(1);

i am getting No class found for refcursor and the error is pointing to
cstmt2.executeUpdate() line.

I also tried to use

con.setAutoCommit(false);
CallableStatement cstmt2=null;
cstmt2=con.prepareCall("{? = call displayall()}");
cstmt2.registerOutParameter(1,Types.OTHER);
String test=cstmt2.getString(1);

But test is pointing to null.

Can anyone please help me in solving this problem. I need to sort out this issue
at the earliest as i need to use Callable Statement in JDBC and also pl/pgsql.

Reply awaited.

Regards
P.mani
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1082

never worked with postgres, but if you can give a try then test it

ResultSet rs2= cstmt2.executeQuery();

I have see this on one website and exaple was for sql server

Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Nischal Tanna
Ranch Hand

Joined: Aug 19, 2003
Posts: 182
Try using OracleTypes.CURSOR instead of java.sql.types.Other. OracleTypes comes with ur classes12. Also while fetching the cursor as output in java, u need to do as below:

ResultSet output = (ResultSet)((OracleCallableStatement)Statement).getCursor(<index>) ;

[ April 28, 2005: Message edited by: Nischal Tanna ]
[ April 28, 2005: Message edited by: Nischal Tanna ]

Thnx
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1082

Originally posted by Nischal Tanna:


Try using OracleTypes.CURSOR instead of java.sql.types.Other.


OracleTypes.CURSOR ca be used with oracle only, but poster of this thread is using postgresql 7.3.4

Shailesh
Nischal Tanna
Ranch Hand

Joined: Aug 19, 2003
Posts: 182
Originally posted by Shailesh Chandra:


OracleTypes.CURSOR ca be used with oracle only, but poster of this thread is using postgresql 7.3.4

Shailesh



Oops, dint see the DB detail

 
It is sorta covered in the JavaRanch Style Guide.
 
subject: call stored procedure