#20 MSSQL Server 2000 Ex exception problem

closed-fixed
None
5
2005-02-24
2005-02-15
Anonymous
No

Hi There -

I'm running SQLUnit 4.4 - it's running very nicely except
for the following:

I throw an exception from a stored procedure (usintg
raiserror). The jdbc driver picks it up as a sql exception,
but SQLUnit seems to somehow lose the exception.

The following is a sample of the trace (I've attached the
full log):

[sqlunit] DEBUG [main] (ConnectionRegistry.java:144) -
>> invalidate(null)
[sqlunit] DEBUG [main] (DatabaseResult.java:160) - >>
resetAsException(50000,[Microsoft][SQLServer 2000
Driver for JDBC][SQLServer]sp_SetThreshold. One or
more of the parameters are null)
[sqlunit] DEBUG [main] (SymbolTable.java:150) - >>
removeSymbol
(${__OutParam:com.microsoft.jdbc.base.BaseCallableSta
tement@16614e7:0})
[sqlunit] DEBUG [main] (ConnectionRegistry.java:144) -
>> invalidate(null)
[sqlunit] DEBUG [main] (SQLUnitResult.java:47) - >>
addFailureCount()
[sqlunit] DEBUG [main] (SQLUnitResult.java:108) - >>
setLastException(e)
[sqlunit] DEBUG [main] (SymbolTable.java:106) - >>
getValue(${__ElapsedMillisStr__})
[sqlunit] DEBUG [main] (SymbolTable.java:79) - >>
getObject(${__ElapsedMillisStr__})
[sqlunit] DEBUG [main] (SymbolTable.java:267) - >>
isVariableName(${__ElapsedMillisStr__})
[sqlunit] DEBUG [main] (SymbolTable.java:267) - >>
isVariableName(${__ElapsedMillisStr__})
[sqlunit] Running test[2]: 2a : sp_AddTestThreshold
(954ms)
[sqlunit] DEBUG [main] (SQLUnitResult.java:99) - >>
getLastException()
[sqlunit] System error (java.sql.SQLException):
[Microsoft][SQLServer 2000 Driver for JDBC]The
requested data is not available.
[sqlunit] DEBUG [main] (SymbolTable.java:140) - >>
getSymbols()

I throw the exception 'sp_SetThreshold. One or more of
the parameters are null' from a procedure, with the
following tsql code:
raisError ('sp_SetThreshold. One or more of the
parameters are null',16,1);

My test is : run the procedure and expect an exception.

Instead of getting 'sp_SetThreshold. One or more of the
parameters are null' I get [Microsoft][SQLServer 2000
Driver for JDBC]The requested data is not available.

Please help!!! I'll probably be using a lot of exceptions to
control my logic.

Kind Regards
Kumar

PS - I'm not yet an sf user. Would appreciate copying
me on any responses :
krishnk1@telkom.co.za

Discussion

  • Nobody/Anonymous

    trace

     
  • Sujit Pal

    Sujit Pal - 2005-02-16

    Logged In: YES
    user_id=184803

    Hi Kumar,

    Looked through the logs you sent, it seems like the
    exception is being and coped out correctly by SQLUnit, but
    then it jumps back into the SQLException block immediately
    after, which is weird, unless its trying to pull the next
    exception in the chain for some reason...

    1580 [sqlunit] DEBUG [main] (DatabaseResult.java:160) -
    >> resetAsException (50000,[Microsoft][SQLServer 2000
    Driver for JDBC][SQLServer]sp_SetThres hold. One or
    more of the parameters are null)
    ...
    1590 [sqlunit] DEBUG [main] (SQLUnitResult.java:99) -
    >> getLastException() 1591 [sqlunit] System error
    (java.sql.SQLException): [Microsoft][SQLServer
    2000 Driver for JDBC]The requested data is not
    available.

    I have made an update to SqlHandler.java (iversion 1.16
    contains the fix, or what I think should be the fix), can
    you try doing a cvs update from CVS (you will get back
    CallHandler as well for another problem), and doing an ant
    install to rebuild the jar file and see if the problem is fixed?

    Specifically the fix I made was to consume any exceptions in
    the chain following the first one. I have noticed a similar
    weirdness in Sybase, so perhaps its also there in MS SQL
    Server 2000.

    I will reply to your email as well so you know that I have
    updated this bug. It would be more convenient though if you
    signed up so I dont have to remember to update two places.

    Thanks
    Sujit

     
  • Sujit Pal

    Sujit Pal - 2005-02-16
    • assigned_to: nobody --> spal
    • status: open --> open-accepted
     
  • Kumar

    Kumar - 2005-02-16

    Logged In: YES
    user_id=1219688

    Hi Sujit -

    Thanks for the prompt response.

    I tried your fix, but the problem persisted. Here's what I've
    done to get around it and a suggestion for a fix:

    My test was something like this:

    <call>
    <stmt>{? = call sp_AddTestSchedule
    (?,?,?,?,?,?,?,?,?,?,?,?)}</stmt>
    <param id="1" type="INTEGER" inout="out">${rc}
    </param>
    <param id="2" type="VARCHAR">1</param>
    <param id="3" type="VARCHAR">1</param>
    <param id="4" type="VARCHAR">1</param>
    <param id="5" type="VARCHAR">1</param>
    <param id="6" type="VARCHAR">1</param>
    <param id="7" type="VARCHAR">1</param>
    <param id="8" type="VARCHAR">1</param>
    <param id="9" type="VARCHAR">1</param>
    <param id="10" type="VARCHAR">1</param>
    <param id="11" type="VARCHAR">1</param>
    <param id="12" type="VARCHAR">1</param>
    <param id="13" type="VARCHAR">1</param>
    </call>
    <result>
    <exception>
    <code>50000</code>
    <message>[Microsoft][SQLServer 2000
    Driver for JDBC][SQLServer]sp_AddTestSchedule. Just a
    sqlunit test for exceptions</message>
    </exception>
    </result>

    I think the following code 'erased' the exception, by causing
    another:

    in SqlHandler.Java
    (lines 149-159)

    <<<<<<<<
    } catch (SQLException e) {
    ConnectionRegistry.invalidate(connectionId);
    result.resetAsException(
    (new Integer(e.getErrorCode())).toString(),
    e.getMessage());
    setOutputParameters(ps, params, result);
    // walk through the exception chain and consume
    all
    while (e != null) {
    e = e.getNextException();
    }
    } catch (Exception e) {
    clrOutputParameters(ps, params);
    >>>>>>

    If there's a SQLException, can you make assumptions about
    the output parameters? i.e. will they be properly populated -
    it certainly seems that in the MSSQL 2000 case, you can't.

    At any rate, the work around is to change the outparam in
    the test spec to in.
    I think a fix would be comment out:
    setOutputParameters(ps, params, result); (Line 154,
    SqlHandler.java)

    - In other words - if a SqlException occurs, don't look at the
    output parameters...

    A possible testcase for this:

    (MSSQL2000 Proc)
    Procedure sp_ExceptionProc
    AS
    BEGIN
    raisError ('Just a sqlunit test for exceptions',16,1);
    END

    and the sqlUnit test:

    <test name="Exception Test">
    <call>
    <stmt>{? = call sp_ExceptionProc()}</stmt>
    <param id="1" type="INTEGER" inout="out">${rc}
    </param>
    </call>
    <result>
    <exception>
    <code>50000</code>
    <message>[Microsoft][SQLServer 2000
    Driver for JDBC][SQLServer]Just a sqlunit test for
    exceptions</message>
    </exception>
    </result>
    </test>

    Best Regards
    Kumar

     
  • Sujit Pal

    Sujit Pal - 2005-02-18

    Logged In: YES
    user_id=184803

    Hi Kumar,

    Actually, that is a requested feature so I cannot remove it.
    Can you try another thing (after uncommenting your fix)? Set
    the reconnect-on-failure attribute of the connection element
    to true. This will close the connection if it sees a failure
    and reopen a new connection. This was a problem in the
    Sybase ASA driver implementation.

    Thanks for the detailed report and lthe fix though. Sorry I
    cannot use it as is, I will have to find another way to
    accomodate both the original requester's and your use case
    if the suggestion above does not work.

    -sujit

     
  • Kumar

    Kumar - 2005-02-18

    Logged In: YES
    user_id=1219688

    Hi Sujit -

    I understand the problem. Wouldn't want my problem solved
    at the expense of another.

    I tried your suggestion - it yielded a similar result - i.e. not
    quite working. (Would have attached a debug log - but it
    seems like I can't at this stage of the bug's life - or am I
    missing something?)

    The exception is now:
    [sqlunit] System error (java.sql.SQLException): [Microsoft]
    [SQLServer 2000 Driver for JDBC]Object has been closed.

    In the mean time I'll be setting the inout attribute for the
    param tag as in, when I do throw exceptions. It's a
    temporary work around without needing to change the code.

    It would be great, though, if a fix could be implemented.

     
  • Sujit Pal

    Sujit Pal - 2005-02-20

    Logged In: YES
    user_id=184803

    Hi Kumar,

    Thanks for checking the reconnect-on-failure option. This
    makes sense that once the connection is closed, an attempt
    to work on the PreparedStatement object associated with the
    Connection will also fail, so I moved the call to
    setOutputParameters in the catch block to before the
    ConnectionRegistry.invalidateConnection(). Secondly I put
    the call inside a try/catch block, so I can trap the warning
    and output a log.warn() message. So in the cases where the
    exception does not put the Connection object in a weird
    state, the output parameter would be accessible and would
    show, whereas in the case of those drivers that do put the
    Connection in a weird state (so you will have to use
    reconnect-on-failure to re-instantiate a Connection object
    instead of reusing it for the next run), it will get the
    output parameter and be able to display it.

    Its a little variation of your approach of commenting the
    code out that will provide an acceptable alternative to both
    situations we discussed.

    The following cut and paste consists of changes I made to
    the code for both your bugs, and I have described the change
    for that bug in there.
    Checking in src/net/sourceforge/sqlunit/ConnectionRegistry.java;
    /cvsroot/sqlunit/sqlunit/src/net/sourceforge/sqlunit/ConnectionRegistry.java,v
    <-- ConnectionRegistry.java
    new revision: 1.17; previous revision: 1.16
    done
    Checking in src/net/sourceforge/sqlunit/SymbolTable.java;
    /cvsroot/sqlunit/sqlunit/src/net/sourceforge/sqlunit/SymbolTable.java,v
    <-- SymbolTable.java
    new revision: 1.38; previous revision: 1.37
    done
    Checking in
    src/net/sourceforge/sqlunit/handlers/SqlHandler.java;
    /cvsroot/sqlunit/sqlunit/src/net/sourceforge/sqlunit/handlers/SqlHandler.java,v
    <-- SqlHandler.java
    new revision: 1.17; previous revision: 1.16
    done
    Checking in
    src/net/sourceforge/sqlunit/handlers/TeardownHandler.java;
    /cvsroot/sqlunit/sqlunit/src/net/sourceforge/sqlunit/handlers/TeardownHandler.java,v
    <-- TeardownHandler.java
    new revision: 1.4; previous revision: 1.3
    done

    Marking this bug fixed. Please verify and close. Thanks for
    catching the bug.

    BTW, could you let me know your full name? I normally give
    credit to people who have caught or fixed bugs or suggested
    improvements on the SQLUnit website when I do the release
    that contains their fixes or changes, so yours would be on
    the 4.5 release notes.

    Thanks
    Sujit

     
  • Sujit Pal

    Sujit Pal - 2005-02-20
    • status: open-accepted --> open-fixed
     
  • Kumar

    Kumar - 2005-02-22

    Logged In: YES
    user_id=1219688

    Hi Sujit -

    Thanks a lot!

    This works perfectly:-)

    I would close this, but I don't seem to have rights to do this
    (maybe because I submitted it anonymously?)

    Anyways - I would be happy for you to close it.

    Again - thanks for a brilliant tool - I'm already using it
    extensively.

    My name is Kumar Krishnan.

    Kind Regards
    Kumar

     
  • Sujit Pal

    Sujit Pal - 2005-02-24
    • status: open-fixed --> closed-fixed
     
  • Sujit Pal

    Sujit Pal - 2005-02-24

    Logged In: YES
    user_id=184803

    Hi Kumar,

    Thanks for the compliment, and I hope you find the tool
    useful. I am going to close the bug per your feedback.

    -sujit

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks