• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Getting result set using callablestatement??

 
Jignesh Gohel
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you can write code similar to this



or




Shailesh
 
Jignesh Gohel
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
could you show us your stored procedure ?

Shailesh
 
Pablo Ganga
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a similar problem mi stored procedure look like this in sqlserver 2000
I can't get any result...
 
Suresh Ks
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks , it was really helpful
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic