*
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
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: 566
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?