How to set DB options for each hibernate DB connection.

[Saugata Guha]

Hi All,
I am new to Hibernate.

This is my scenario:
For each database connection we open we have to set a DB option
to make the connection work. We use a temporary option for Sybase ASA.

"SET TEMPORARY OPTION CONN_AUTH='Company=MicroView;Application=MyDB;Sign key=1E042938-AD90-4008-B213-as0CED2FF4DD'"

We do it for security.
Any way, with JDBC stuff it works fine, but when hibernate opens a
connection it fails to update any table content.

I get the following error.
JZ0BE: BatchUpdateException: Error occurred while executing batch statement: ASA Error -98: Authentication violation (JDBCExceptionReporter.logExceptions)
Could not synchronize database state with session (AbstractFlushingEventListener.performExecutions)

Where do I put the "SET TEMPORARY OPTION...", statement so that
Hibernate can execute it, after opening each new db connection.

Any help is appreciated.
Thanks
sguha.

[karldmoore]

Does this statement only want to be executed once? Are you using a connection pool? If so, is it not the pool that need to execute this statement when creating a new connection? If your not using a pool how are you wrapping your jdbc connection?

[Arthur Loder]

Hi Saugata,

This might work, or you might be able to set something in your DataSource configuration. The following class extends DelegatingDataSource. You simply have to inject your current DataSource, and then every connection obtained on the target DataSource will be configured appropriately.

package org.spring.forum;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import org.springframework.jdbc.datasource.DelegatingDataSource;

public class PostProcessingDataSource extends DelegatingDataSource {

    public static final String TEMPORARY_OPTION_CONN_AUTH = 
        "SET TEMPORARY OPTION CONN_AUTH='Company=MicroView;Application=MyDB;Sign key=1E042938-AD90-4008-B213-as0CED2FF4DD'";
    
    @Override
    public Connection getConnection() throws SQLException {
        Connection connection = super.getConnection();
        postProcessConnection(connection);
        return connection;
    }

    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        Connection connection =  super.getConnection(username, password);
        postProcessConnection(connection);
        return connection;
    }
    
    /**
     * Post-process using the given connection.
     * 
     * @param connection the connection to use for post-processing.
     * @throws SQLException
     */
    protected void postProcessConnection(Connection connection) throws SQLException  {
        Statement statement = connection.createStatement();
        statement.execute(TEMPORARY_OPTION_CONN_AUTH);
    }

}

[Saugata Guha]

Thanks a lot for the code snippet.
Saugata.