Output Parameters - anything different for SQL Server 7?

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

posted Dec 12, 2001 13:40:00

0

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.

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.