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 Getting result set using callablestatement?? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Getting result set using callablestatement??" Watch "Getting result set using callablestatement??" New topic
Author

Getting result set using callablestatement??

Jignesh Gohel
Ranch Hand

Joined: Dec 28, 2004
Posts: 276
Hi,

In my procedure i am doing some SELECT queries.I want to obtain the result set which i would get when executing that procedure.So which method i should use on CallableSttement object to obtain that .
I tries to use executeQuery but it's not working??

Thanks,
Jignesh
[ April 17, 2006: Message edited by: Jignesh Gohel ]

Regards,
Jignesh

The Art Of Life Is To Know When To Be Useless And When To Be Useful - CHUANG TZU
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

you can write code similar to this



or




Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Jignesh Gohel
Ranch Hand

Joined: Dec 28, 2004
Posts: 276
Thanks Sailesh,


But i dont have any out parameters to be registered.In that case how i should write this code:

CallableStatement cstmt = connection.prepareCall("{call my_proc(?, ?)}");cstmt.registerOutParameter(1, java.sql.Types.YOUR_TYPE);cstmt.registerOutParameter(2, java.sql.Types.YOUR_TYPE); cstmt.execute(); ResultSet rs = (ResultSet)cstmt.getObject(index)

or the other one ???

Jignesh
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

could you show us your stored procedure ?

Shailesh
Pablo Ganga
Greenhorn

Joined: Oct 12, 2005
Posts: 13
I have a similar problem mi stored procedure look like this in sqlserver 2000
I can't get any result...


MCAD<br />SCJP JAVA 5<br />SCWCD 1.4
Suresh Ks
Greenhorn

Joined: Apr 18, 2006
Posts: 2
Hi All,

Here in the above code it is mentioned that java.sql.Types.YOUR_TYPE. I would like to know ,what I will write in the YOUR_TYPE?

Please can you elaborate on this....


Subu
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Pablo Ganga:
I have a similar problem mi stored procedure look like this in sqlserver 2000
I can't get any result...



You can use stored procedure in java IFF it works on your DataBase on the first place.

Not sure about SQL Server but if it were oracle the your procedure wouldn't have compiled. It would have given error like INTO clause is expected in this SELECT statement

your procedure shpuld be like



here VAR1,VAR2 are variable defined as COl1 and COl2 type. Also in order to retrun some output from procedure us should have some out arguement to hold output.

If you are planning to return multiple rows from procedure then your out type would be a 'REF CURSOR'


Shailesh
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Suresh Ks:
Hi All,

Here in the above code it is mentioned that java.sql.Types.YOUR_TYPE. I would like to know ,what I will write in the YOUR_TYPE?

Please can you elaborate on this....


Subu


It would be data type of your argument which you are setting

for example if your arguement is varchar in procedure then you will put

Types.VARCHAR at the place of YOUR_TYPE


Shailesh
Pablo Ganga
Greenhorn

Joined: Oct 12, 2005
Posts: 13
Originally posted by Shailesh Chandra:
<hr></blockquote>


You can use stored procedure in java IFF it works on your DataBase on the first place.

Not sure about SQL Server but if it were oracle the your procedure wouldn't have compiled. It would have given error like INTO clause is expected in this SELECT statement

your procedure shpuld be like



here VAR1,VAR2 are variable defined as COl1 and COl2 type. Also in order to retrun some output from procedure us should have some out arguement to hold output.

If you are planning to return multiple rows from procedure then your out type would be a 'REF CURSOR'


Shailesh[/QB]

that's right the procedure works fine in sql server 2000 but in oracle didn't work.

can you put an example with ref_cursor.
I'm a little green using jdbc.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Pablo Ganga:

can you put an example with ref_cursor.
I'm a little green using jdbc.


here is one example for you.

Shailesh
Pablo Ganga
Greenhorn

Joined: Oct 12, 2005
Posts: 13
Thanks , it was really helpful
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Getting result set using callablestatement??