This week's book giveaway is in the JDBC and Relational Databases forum.
We're giving away four copies of Murach's MySQL and have Joel Murach on-line!
See this thread for details.
Win a copy of Murach's MySQL this week in the JDBC and Relational Databases forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Getting result set using callablestatement??

 
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
could you show us your stored procedure ?

Shailesh
 
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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...
 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks , it was really helpful
 
reply
    Bookmark Topic Watch Topic
  • New Topic