Newbie needs help with "Object has been closed" exception

[Doug Slattery]

Aloha ranchers,

I'm starting to work with JDBC and MSSQL this week. This thread is related to this thread for those who are wondering...

With help from a co-worker, I have successfully setup a JNDI datasource in Tomcat. I've also been able to connect to the server and run a simple stored procedure. After turning up the heat a bit, I've ran into a problem and have searched for an answer, which has lead me here.

The Problem:
I'm trying to set up a simple stored procedure that will return a result set (empty is ok for now) of dummy values without accessing any tables. What I have seems to fly with SQL, but on the Java side, the result set is being closed somewhere.

Here's the SP:

CREATE PROCEDURE WEBLIB.WBR1100
@corp_no varchar(3) output,
@account_no varchar(9) output,
@bal_date varchar(8) output,
@bal_amt varchar(7) output,
@due_date varchar(8) output,
@due_amt varchar(7) output,
@pay_tot varchar(7) output,
@error_code as varchar(5) output
 
AS
 
SELECT  '001'       as corp_no,
        '123456789' as account_no,
        '02292008'  as bal_date,
        '1234567'   as bal_amt,
        '02292009'  as due_date,
        '7654321'   as due_amt,
        '9876543'   as pay_tot,
        '00000'     as error_code
 
SET @corp_no    = '001'
SET @account_no = '123456789'
SET @bal_date   = '20080229'
SET @bal_amt    = '1234567'
SET @due_date   = '20090228'
SET @due_amt    = '7654321'
SET @pay_tot    = '9876543'
SET @error_code = '00000'
GO

And here's the Java code:

...
         // This is really an MSSQL connection.  It's spoofed in the config
         connection = ConnectionUtils.getAS400Connection();
 
         String sql = "{Call WEBLIB.WBR1100  (?, ?, ?, ?, ?, ?, ?, ?)}";
 
         callableStatement = connection.prepareCall(sql);
         callableStatement.setString(1, applicationUser.getCorpNumber());
         callableStatement.setString(2, applicationUser.getAcctNumber());
         callableStatement.registerOutParameter(3, Types.CHAR);
         callableStatement.registerOutParameter(4, Types.CHAR);
         callableStatement.registerOutParameter(5, Types.CHAR);
         callableStatement.registerOutParameter(6, Types.CHAR);
         callableStatement.registerOutParameter(7, Types.CHAR);
         callableStatement.registerOutParameter(8, Types.CHAR);
 
         // Setting this has no effect.
         callableStatement.setQueryTimeout(10);
         resultSet = callableStatement.executeQuery();
 
         String returnCode = callableStatement.getString(8);
         logger.debug("returnCode [" + returnCode + "] Query Timeout: [" + callableStatement.getQueryTimeout() + "]");
...

The last Java line (logger.debug...) shows "returnCode [00000] Query Timeout: [10]" as expected. However, if I inspect resultSet at that line (using the Sysdeo debugger), it shows the _res->closed property as true. Thus, when I get to the set iterator, an "Object has been closed" exception is thrown. I have been quick enough on the draw at times to see the _res->closed property set to false, but it never makes it to the iterator. Even with no break points set. I suspect there is a setting somewhere, perhaps a poolable(?) one that I don't know where to look for. Anyone have any ideas?

Aloha,
Doug

-- Nothing is impossible if I'mPossible