• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Rob Spoor
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Henry Wong
  • Liutauras Vilda
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Tim Holloway
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Mikalai Zaikin
  • Piet Souris

Trouble executing stored procedure and getting a value back

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!


 
Bartender
Posts: 2658
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Welcome to JavaRanch, Keith.
 
Keith Blackwell
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Jan Cumps wrote:Welcome to JavaRanch, Keith.



Thanks!
 
Keith Blackwell
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 2658
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 2658
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic