I recently implemented C3P0 in my database testing program (i'm using it to test different queries on our data in different DB formats... sqlite, mariadb, etc). The program was initially set up using a single sustained connection to do all queries. This worked fine with SQLite as I had to do an initial ATTACH on another table. When moving to C3P0 where it is necessary to open and close the connection on every query, how can one issue an ATTACH command and have it apply to subsequent queries? In my failure I did notice that the first query after the attach it seemed to apply.
Do I really need to interlace ATTACH test as TESTDB for EVERY query???
Running into a similar issue with setCatalog() for MariaDB. I get a "No Database selected" for every subsequent query after the first.
Do I really need to interlace ATTACH test as TESTDB for EVERY query???
No. As @MarkRotteveel suggested in a comment to the question, we can use a c3p0 connection customizer to tweak each connection as it is acquired for the pool. For example, if we create the class OurSQLiteConnectionCustomizer ...
package com.example.sqlite_pooled;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import com.mchange.v2.c3p0.AbstractConnectionCustomizer;
public class OurSQLiteConnectionCustomizer extends AbstractConnectionCustomizer {
public void onAcquire(Connection c, String pdsIdt) throws SQLException {
try (Statement st = c.createStatement()) {
st.execute("ATTACH DATABASE 'C:/__tmp/SQLite/test.sqlite' AS test");
}
}
}
... and we tell our ComboPooledDataSource to use it ...
cpds = new ComboPooledDataSource();
cpds.setConnectionCustomizerClassName("com.example.sqlite_pooled.OurSQLiteConnectionCustomizer");
... then whenever c3p0 acquires a new SQLite connection for the pool it will automatically perform the ATTACH DATABASE for us.