• Post Reply Bookmark Topic Watch Topic
  • New Topic

call stored procedure  RSS feed

 
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 1084
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Shailesh Chandra
Ranch Hand
Posts: 1084
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 182
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!