aspose file tools*
The moose likes JDBC and the fly likes Trouble executing stored procedure and getting a value back Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Trouble executing stored procedure and getting a value back" Watch "Trouble executing stored procedure and getting a value back" New topic
Author

Trouble executing stored procedure and getting a value back

Keith Blackwell
Greenhorn

Joined: May 05, 2010
Posts: 4
Hi
I have a stored procedure in SQL Server 2005. I've set up the user for this, etc., and it should work.
The stored procedure takes 4 parameters. Actually 5. The one on the end is the OUTPUT parameter.
So, in SQL Server a call to the stored procedure would be like this:

EXEC SP_GET_NUMBER '555-55-5555','19451215','DUGGAR','BILLYBOB', @NUMBER OUTPUT

The result is a VARCHAR. This works great from the query window, etc.

But here's what I'm trying in java:

after creating the connection etc.,
......................

CallableStatement cs=conn.prepareCall("{ ? = call SP_GET_NUMBER(?,?,?,?,?) }");
cs.registerOutParameter(6,java.sqlTypes.VARCHAR); // register 1 as outparameter, same results
cs.setString(2,SSN);
cs.setString(3,DOB);
cs.setString(4,LASTNAME);
cs.setString(5,FIRSTNAME);
cs.setString(6,""); // this, it doesn't matter if I set it or not, same results

cs.execute();

System.out.println(cs.getString(6)); // this prints out a 0 (zero), NOT the number I'm expecting to retrieve !!

....................

I've tried seting the OutParameter to (1), with the same result.
What am I doing wrong here?
I'm building against jdk1.3.1_20 - because that's the jdk on this system I'm building it for, and no, I can't change it.
SQL Server 2005
Using the JDBC-ODBC bridge thing
Developing this in Netbeans.
When I run the test, I'm able to see that the ODBC connection is working.

???

Thanks in advance for any help!


Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2497
    
    8

Welcome to JavaRanch, Keith.


OCUP UML fundamental and ITIL foundation
youtube channel
Keith Blackwell
Greenhorn

Joined: May 05, 2010
Posts: 4
Jan Cumps wrote:Welcome to JavaRanch, Keith.


Thanks!
Keith Blackwell
Greenhorn

Joined: May 05, 2010
Posts: 4
ACTUALLY............... I got this to work.
I could SWEAR I had tried this earlier, with no luck.

I changed it to be like so....

CallableStatement cs=conn.prepareCall("{ call SP_GET_NUMBER(?,?,?,?,?) }");
cs.registerOutParameter(5,java.sql.Types.VARCHAR); // 5... must've miscounted earlier
cs.setString(1,SSN);
cs.setString(2,DOB);
cs.setString(3,LASTNAME);
cs.setString(4,FIRSTNAME);
cs.execute();
strResult=cs.getString(5);


System.out.println("result="+strResult); /// Yay, it works finally.



Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2497
    
    8

Thank you for sharing the solution. Other Ranchers can learn from it.
Keith Blackwell
Greenhorn

Joined: May 05, 2010
Posts: 4
Jan Cumps wrote:Thank you for sharing the solution. Other Ranchers can learn from it.


Yep, I hate it when I find similar threads but the person just says "fixed it, thanks, bye".
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2497
    
    8

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Trouble executing stored procedure and getting a value back