Win a copy of Java Mock Exams (software) this week in the Programmer Certification (OCPJP) forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Output Parameters - anything different for SQL Server 7?

 
Tina Coleman
Ranch Hand
Posts: 150
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Completely green here - I've seen other threads that address similar questions, but haven't yet seen an answer. . .
I'm executing a stored procedure in SQL Server 7 using the CallableStatement. The first parameter to my stored procedure is an OUTPUT parameter, so specc'ed in the CREATE PROCEDURE statement. I register the parameter as an output parameter for the CallableStatement, execute it, pull back my result sets, and then try to get back my output parameter. Result: null. Running the stored procedure through Query Analyzer does give me back an appropriate result.
I know in SQL Server 7, when I execute the stored procedure, I need to explicitly spec the parameter as an output parameter - otherwise, I get back a null. The { call storedProcedureName ?, ?, . . .? } format doesn't allow me to do that. Does the registerOutParameter handle all of that for me?? Or is there something specific to SQL Server 7 that I'm still not doing??
Pertinent technical info:
* I'm using the sun.jdbc.odbc.JdbcOdbcDriver Type 1 driver; we'll swap to the type 4 driver provided by Microsoft for SQL Server 2000, but for the moment, I'm working on an NT machine and thus can't install the type 4 driver
* I have registered the parameter as an output parameter
* I can execute the stored procedure through the Query Analyzer in SQL Server and have it give me the pertinent data (note that to get the value back, I have to explicitly spec the parameter as an OUTPUT parameter: [EXEC getNextISID @ISID OUTPUT] - I'm not seeing a way to do that via the { call } statement for CallableStatement
 
Tina Coleman
Ranch Hand
Posts: 150
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Think I found my answer, and figured I'd follow up here for the next person who's searching for the same thing: According to the documentation for CallableStatement in 1.3.1, parameters can be used for input, output, or both. I was attempting to do a both - pass in a value, and then get the value back out. I haven't figured out why this doesn't work, but it doesn't. Whether I registerOutParameter(1, java.sql.Types.VARCHAR) first or setString(1, stringValue) first, if I've done setString, I can't get a non-null value when I do a getString(1). My work-around was to pass in the value in another parameter than the one I'm trying to get it out of - in my case, to provide an in_ISID parameter, as well as an out_ISID parameter.
 
Adam Hardy
Ranch Hand
Posts: 567
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm afraid I haven't tried it in Java but I did in MS ADO and it was best to put the output return parameter first, then all the input parameters, and then the output parameters last.
 
What are you doing? You are supposed to be reading this tiny ad!
the new thread boost feature brings a LOT of attention to your favorite threads
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!