Notes on JDBC connections

  • Autocommit behavior   The JDBC specification requires that, by default, a COMMIT is performed after each data modification statement. Currently, the client-side JDBC behavior is to commit (autocommit is true) and the server-side behavior is to not commit (autocommit is false). To obtain the same behavior in both client-side and server-side applications, you can use a statement such as the following:
    con.setAutoCommit( false );

    In this statement, con is the current connection object. You could also set autocommit to true.

  • Setting transaction isolation level   To set the transaction isolation level, the application must call the Connection.setTransactionIsolation method with one of the following values.

    For the SQL Anywhere JDBC 4.0 driver use:

    • sybase.jdbc4.sqlanywhere.IConnection.SA_TRANSACTION_SNAPSHOT
    • sybase.jdbc4.sqlanywhere.IConnection.SA_TRANSACTION_STATEMENT_SNAPSHOT
    • sybase.jdb4c.sqlanywhere.IConnection.SA_TRANSACTION_STATEMENT_READONLY_SNAPSHOT

    For the SQL Anywhere JDBC 3.0 driver use:

    • sybase.jdbc.sqlanywhere.IConnection.SA_TRANSACTION_SNAPSHOT
    • sybase.jdbc.sqlanywhere.IConnection.SA_TRANSACTION_STATEMENT_SNAPSHOT
    • sybase.jdbc.sqlanywhere.IConnection.SA_TRANSACTION_STATEMENT_READONLY_SNAPSHOT

    The following example sets the transaction isolation level to SNAPSHOT using the JDBC 4.0 driver.



    try
    {
        connection.setTransactionIsolation(
            sybase.jdbc4.sqlanywhere.IConnection.SA_TRANSACTION_SNAPSHOT
        );
    }
    catch( Exception e )
    {
        System.err.println( "Error! Could not set isolation level" );
        System.err.println( e.getMessage() );
        printExceptions( (SQLException)e );
    }

    For more information about the getTransactionIsolation and setTransactionIsolation, see documentation on the java.sql.Connection interface at [external link] Java.com.

  • Connection defaults   From server-side JDBC, only the first call to getConnection( "jdbc:default:connection" ) creates a new connection with the default values. Subsequent calls return a wrapper of the current connection with all connection properties unchanged. If you set autocommit to false in your initial connection, any subsequent getConnection calls within the same Java code return a connection with autocommit set to false.

    You may want to ensure that closing a connection restores the connection properties to their default values, so that subsequent connections are obtained with standard JDBC values. The following code achieves this:



    Connection con = 
        DriverManager.getConnection("jdbc:default:connection");
    
    boolean oldAutoCommit = con.getAutoCommit();
    try 
    {
         // main body of code here
    }
    finally 
    {
        con.setAutoCommit( oldAutoCommit );
    }

    This discussion applies not only to autocommit, but also to other connection properties such as transaction isolation level and read-only mode.

    For more information about the getTransactionIsolation, setTransactionIsolation, and isReadOnly methods, see documentation on the java.sql.Connection interface at [external link] Java.com.