search

swaldman / c3p0

a mature, highly concurrent JDBC Connection pooling library, with support for caching and reuse of PreparedStatements.
http://www.mchange.com/projects/c3p0
Other
1.28k stars 338 forks source link

issue with setCatalog() using c3p0 for ms sqlserver, but works fine with a direct Connection without a pool #152

Open alkuma opened 4 years ago

alkuma commented 4 years ago

We are setting up a multitenant application with a single ms sqlserver database server having multiple databases, one per site.

c3p0 0.9.5.5 is being used for connection pooling.

We do a setCatalog() call to set the database to use. In the example program we are using a simple "select a from test_table" query. However after the second setCatalog() call, we start getting the error of this form:

com.microsoft.sqlserver.jdbc.SQLServerException: The prepared statement handle 1 is not valid in this context.  Please verify that current database, user default schema, and ANSI_NULLS and QUOTED_IDENTIFIER set options are not changed since the handle is prepared.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:467)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:409)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:331)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:1471)
    at test.TestSqlServerMT.executeQuery(TestSqlServerMT.java:144)

However with the same test query without c3p0 (directly acquiring a connection), we never face this issue. What are we doing wrong, or is there a known issue in c3p0 for this?

bfmyr4 commented 3 years ago

We are experiencing this as well. It seems that it doesn't pay attention to the catalog that the prepared statement is tied to, so when you create the preparedstatement after you change the catalog, it doesn't create a new one to reflect the new catalog, it just gives the last one with whatever catalog it happens to have.

swaldman commented 6 months ago

Do you have PreparedStatement caching turned on? (Is maxStatements and/or maxStatementsPerConnection greater thann zero in your config?) Do you see the issue if statement caching is off (both maxStatements and maxStatementsPerConnection set to their defaults of zero)?