#193 Cannot use check-valid-connection-sql on sybase w/o tx

closed-wont-fix
nobody
None
5
2004-09-11
2004-04-23
Antti Lehto
No

When using check-valid-connection in *-ds.xml, cannot
use EJB's without transaction.

When doing conn.createStatement() inside
non-transactional (transaction=never) sessionbean, pool
tries to set autocommit to true. This makes sybase
throw exception, because it cannot alter state of
autocommit in the middle of a transaction. The
transaction begins when check-valid-connection-sql is run.

Is it possible to run check-valid-connection-sql with
autocommit=true or perform rollback after sql is run?
It would prevent this error.

here's a snippet from xxx-ds.xml:
<local-tx-datasource>
<jndi-name>xxxDS</jndi-name>

<connection-url>jdbc:sybase:Tds:localhost:4100/xxx</connection-url>

<driver-class>com.sybase.jdbc2.jdbc.SybDriver</driver-class>
<user-name>xxx</user-name>
<password>xxx</password>
<check-valid-connection-sql>select
1</check-valid-connection-sql>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.SybaseExceptionSorter</exception-sorter-class-name>
</local-tx-datasource>

Haven't yet tried this with other databases.

Discussion

  • Antti Lehto

    Antti Lehto - 2004-04-23

    Logged In: YES
    user_id=968546

    Forgot versions:
    JBoss 3.2.3
    Sybase ASE 12.5
    JConnect 5.5

     
  • Adrian Brock

    Adrian Brock - 2004-04-23
    • labels: --> 323949
    • assigned_to: nobody --> ejort
     
  • Adrian Brock

    Adrian Brock - 2004-04-23

    Logged In: YES
    user_id=9459

    This sounds like a bug with the Sybase driver

    According to the javadocs for setAutoCommit()
    "NOTE: If this method is called during a transaction, the
    transaction is committed."
    i.e. when it does setAutoCommit(true) it should commit
    the transaction.

    I've heard of a couple of funnies with Sybase which
    <no-tx-separate-pools/> fixes (although this was meant as
    workaround
    for OracleXA - OracleXA would definitely dislike
    setAutoCommit(true) running that query)

    You can fix it yourself, simply add

    <valid-connection-checker-class-name>YourConnectionCheckerClass</valid-connection-checker-class-name>
    based on the default implementation:
    http://cvs.sourceforge.net/viewcvs.py/jboss/jbosscx/src/main/org/jboss/resource/adapter/jdbc/CheckValidConnectionSQL.java?rev=1.2&view=auto

    You might need to be careful, becuase the value of
    autocommit could
    be true or false when this routine runs.

    If you get it to work, please submit the workaround back.

     
  • Adrian Brock

    Adrian Brock - 2004-05-10
    • assigned_to: ejort --> nobody
    • milestone: 228434 -->
    • labels: 323949 -->
     
  • Scott M Stark

    Scott M Stark - 2004-09-11
    • status: open --> closed-wont-fix
     

Log in to post a comment.