aspose file tools*
The moose likes JDBC and the fly likes call store procedure from Java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "call store procedure from Java" Watch "call store procedure from Java" New topic
Author

call store procedure from Java

Nil gawade
Greenhorn

Joined: Jun 14, 2012
Posts: 7
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

Joined: Jul 23, 2008
Posts: 296

Hi Nil,

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

Regards
Jatan
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1106

I think it differs depending on the database you are using, so can you tell us which one you are using?
Nil gawade
Greenhorn

Joined: Jun 14, 2012
Posts: 7
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

Joined: Jun 14, 2012
Posts: 7
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

Joined: Oct 21, 2008
Posts: 1106

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

Joined: Jun 14, 2012
Posts: 7
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

Joined: Oct 21, 2008
Posts: 1106

could you provide the connection string, and the driver you are using with your code please.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: call store procedure from Java
 
Similar Threads
Set Chained OFF error while executing from my Bean
Stored Procedure Problem
cobol stored procedure call
Calling Oracle Stored Procedure
myresultset.first