This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
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 Android Security Essentials Live Lessons this week in the Android 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: 2491
    
    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: 2491
    
    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: 2491
    
    8

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Trouble executing stored procedure and getting a value back
 
Similar Threads
calling SQL Server stored procedures
Error in getting data from database
Oracle wrong number or types of arguments...
Calling Oracle stored procedures in JRun container
Strange oracle problem--plz HELP