Bug 56779

Summary: Allow multiple connection initialization statements
Product: Tomcat Modules Reporter: Wes Clark <wclark>
Component: jdbc-poolAssignee: Tomcat Developers Mailing List <dev>
Status: NEW ---    
Severity: normal CC: wclark
Priority: P2    
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: All   

Description Wes Clark 2014-07-28 17:22:40 UTC
Tomcat JDBC Enhanced Attribute initSQL is a string documented as " A custom query to be run when a connection is first created."  Often multiple statements (not really "queries") need to be executed to initialize a connection, and you only want to do this once.  There are rather involved workarounds to pass a single "statement" which executes multiple statements, but the interface would be improved if more than one statement could be specified. See DBCP2 BasicDataSource.setConnectionInitSqls(Collection<String> connectionInitSqls).
Comment 1 Christopher Schultz 2014-07-28 19:22:38 UTC
The problem with putting multiple statements into the "initSQL" attribute value is that tomcat-pool then needs to basically implement a SQL statement parser in order to determine where each statement starts and ends.

If a special character could be used between statements that is extremely unlikely to be used in one of the SQL statements (for instance, vertical tab) such statements could be separated by that instead of something more natural such as a semicolon.
Comment 2 Filip Hanik 2014-07-28 19:40:15 UTC
well, I like the idea of copying the API
BasicDataSource.setConnectionInitSqls(Collection<String> connectionInitSqls)

after all, the copied the idea of init SQL from us :)
that way programmatically it works, and more sophisticated DI frameworks have no problems injecting collections.

In Tomcat server.xml, we can still use a single string, like the work around
BEGIN
  statement1;
  statement2;
  statement3;
END

that folks have been using today