Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
alkuma opened this issue Aug 6, 2020 · 2 comments

Comments

@alkuma
Copy link

alkuma commented Aug 6, 2020

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
Copy link

bfmyr4 commented Oct 27, 2020

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
Copy link
Owner

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)?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

3 participants