• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

call store procedure from Java

 
Nil gawade
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
how to get result set from the sql store procedure?

Please help me Here is my code:
ResultSet rs = null;
CallableStatement cs =null;

try {
conn = this.getDBConnection();


cs = conn.prepareCall("{?=call usp_getReconciledGroupcast (?,?,?)}");


cs.setString(3, afterDate.toString()); //afterDate
cs.setString(4, toDate.toString()); //EndDate
cs.setInt(2, msPracticeId); //msPracticeid

cs.registerOutParameter(1, Types.VARCHAR);//output
cs.execute();

//Boolean str = cs.getMoreResults();
if (cs.getMoreResults()) {
System.out.println("Getting SOMETHING:::");
}else {
rs = cs.getResultSet();
while (rs.next())
{ }
}


Getting below error : Database error occurred, error code=102:Incorrect syntax near '@P1'.

Please can you give me example with store procedure which return result set?


Thanks.
 
jatan bhavsar
Ranch Hand
Posts: 299
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Nil,

Refer this article which will guide you to return resultset from store proc.

Regards
Jatan
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think it differs depending on the database you are using, so can you tell us which one you are using?
 
Nil gawade
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for reply.

I want to use SQL store procedure.

My actual question is if my proc is returning recordset , then is it possible to retrieve the data in java ?

As I have seen example of IN and OUT param.But i am not clear how i can get the multiple record from my proc ?

Thansk,
Nil
 
Nil gawade
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What I am trying to do is :

I have inline sql in the java code,so we want to move these sql to store proc for better performance.

Instead of inline sql we will call store proc but problem is I am not sure how I can retrieve data from the storeproc, right now it is executing the sql and returning the result set.

So please let me know if we convert below sql to store proc and call the storeproc , how we can retrieve the resultset? I am not sure if it is possible.

Please advice, urgent.



 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry we are here to help you when you have a problem. Thre are many threads already to help with this. Jatan posted you s link that looks helpful have you read that?

Earlier I asked you what database you are going to use as different database vendors have different ways of doing things, by this I mean oracle, MySQL etc.
jdbc result set from stored proc had several helpful answers.
 
Nil gawade
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Wendy.

I have tried several code but not working , i am trying to call sql store proc.

I am trying one more way now ,i will post my code incase of any issues or sucess

Thanks.
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
could you provide the connection string, and the driver you are using with your code please.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic