File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes call stored procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
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: 1081

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: 1081

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

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: call stored procedure
 
Similar Threads
calling postgres 7.3 functon from java
CallableStatement PostgreSQL Error
Problem : accessing record from a cursor returned from postgresql
manual transaction for all JDBC calls
Problem : accessing record from a cursor returned from postgresql