Hikaricp: connectionInitSql does not allow multiple statements

[baumeister]

connectionInitSql allows to set a single initialization statement. However, there is no way at the moment to issue more than one statements.

It would be good if several such statements could be configured, or for maximum flexibility if a callback can be set

This is how I do it in Bonecp:

config.setConnectionHook(new AbstractConnectionHook() {
    @Override
    public void onAcquire(ConnectionHandle con) {
                con.setReadOnly(false);
                con.setAutoCommit(false);
                if (type==TYPE.ORACLE) {
                    // -- make oracle non case sensitive
                    try (Statement s = con.createStatement()) {
                        s.addBatch("alter session set NLS_COMP=ANSI");
                        s.addBatch("alter session set NLS_SORT=BINARY_CI");
                        s.executeBatch();
                        con.commit();
                    }
                }
        }
    });

[brettwooldridge]

If you build the project, you can read the JavaDocs. There is a property, currently undocumented on the web page, called connectionCustomizerClassName. It specifies a class that implements IConnectionCustomizer, which is passed the Connection after creation to do whatever it wants before it is added to the pool.

[Menthalion]

This can be done with the current connectionInitSql and adjusting your SQL.

With Microsoft SQL and most other databases, simply concatenate your SQL commands into one statement, separated with the database's query separator (usually ;).

With Oracle you need to go one further step by wrapping these commands in a BEGIN;...;END block.
Some SQL commands that cannot be performed transactionally in such a block (like ALTER SESSION) might need to be prepended with EXECUTE IMMEDIATE followed by the quoted SQL.

Example

BEGIN;
EXECUTE IMMEDIATE q'ALTER SESSION SET NLS_SORT='BINARY_AI'';
EXECUTE IMMEDIATE q'ALTER SESSION SET NLS_COMP='LINGUISTIC'';
END

Oracle also has specific DBMS_SESSION.SET functions that allows them to be set immediately in a BEGIN;...;END block, without any need for escaping

BEGIN;
DBMS_SESSION.SET_NLS('NLS_SORT', 'BINARY_AI');
DBMS_SESSION.SET_NLS('NLS_COMP', 'LINGUISTIC');
END

[eidottermihi]

@Menthalion
thanks for sharing your solution, I had to alter your solution to support altering other NLS parameters (nls_date_format and nls_timestamp_format):

BEGIN 
DBMS_SESSION.SET_NLS('nls_date_format', '''YYYY-MM-DD HH24:MI:SS''');
DBMS_SESSION.SET_NLS('nls_timestamp_format', '''YYYY-MM-DD HH24:MI:SS'''); 
END;