Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Trouble executing stored procedure and getting a value back

 
Keith Blackwell
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2608
14
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to JavaRanch, Keith.
 
Keith Blackwell
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jan Cumps wrote:Welcome to JavaRanch, Keith.


Thanks!
 
Keith Blackwell
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2608
14
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you for sharing the solution. Other Ranchers can learn from it.
 
Keith Blackwell
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2608
14
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic