jQuery in Action, 2nd edition*
The moose likes JDBC and the fly likes Output Parameters - anything different for SQL Server 7? 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 "Output Parameters - anything different for SQL Server 7?" Watch "Output Parameters - anything different for SQL Server 7?" New topic
Author

Output Parameters - anything different for SQL Server 7?

Tina Coleman
Ranch Hand

Joined: Dec 12, 2001
Posts: 150
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

Joined: Dec 12, 2001
Posts: 150
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

Joined: Oct 09, 2001
Posts: 565
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.


I have seen things you people would not believe, attack ships on fire off the shoulder of Orion, c-beams sparkling in the dark near the Tennhauser Gate. All these moments will be lost in time, like tears in the rain.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Output Parameters - anything different for SQL Server 7?
 
Similar Threads
Executing a stored procedure from a servlet
Insertion Values not get stored in sql server using stored procdure in struts2
Problem calling IN parameters Stored Procedure from Java
how to get data
Date format and SQL Server stored procedure