B4J Question SQLServerException: The statement is closed. jRDC2 with jServer5 and MS SQL Server database.

C

Chris2

Well-Known Member
Licensed User
Longtime User
I am encountering an error when using a jRDC2 based app that is connected to a Microsoft SQL Server (SQL Express in my case) database, when a client app queries the database.

Using jServer 4.10 or the latest 5.02 I get the following error with every second query:
cmv2cs.GooGooStatementCache:main: Problem with checked-in Statement, discarding.
com.microsoft.sqlserver.jdbc.SQLServerException: The statement is closed.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:233)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.checkClosed(SQLServerStatement.java:1148)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.clearParameters(SQLServerPreparedStatement.java:415)
at com.mchange.v2.c3p0.stmt.GooGooStatementCache.refreshStatement(GooGooStatementCache.java:690)
at com.mchange.v2.c3p0.stmt.GooGooStatementCache.checkinStatement(GooGooStatementCache.java:272)
at com.mchange.v2.c3p0.stmt.GooGooStatementCache.checkinAll(GooGooStatementCache.java:335)
at com.mchange.v2.c3p0.impl.NewPooledConnection.checkinAllCachedStatements(NewPooledConnection.java:785)
at com.mchange.v2.c3p0.impl.NewPooledConnection.markClosedProxyConnection(NewPooledConnection.java:390)
at com.mchange.v2.c3p0.impl.AbstractNewProxyConnection.close(AbstractNewProxyConnection.java:154)
at anywheresoftware.b4j.objects.SQL.Close(SQL.java:487)
at b4j.example.rdchandler._handle(rdchandler.java:375)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:569)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:632)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:569)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:117)
at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:104)
at anywheresoftware.b4j.object.JServlet$Handle.run(JServlet.java:157)
at anywheresoftware.b4a.keywords.SimpleMessageLoop.runMessageLoop(SimpleMessageLoop.java:47)
at anywheresoftware.b4a.StandardBA.startMessageLoop(StandardBA.java:43)
at anywheresoftware.b4a.shell.ShellBA.startMessageLoop(ShellBA.java:121)
at anywheresoftware.b4a.keywords.Common.StartMessageLoop(Common.java:184)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:309)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:569)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:117)
at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:104)
at b4j.example.main.main(main.java:29)
Click to expand...
Interestingly, I do not encounter this error if I use jServer 4.02.

I have attached a couple of test apps. Can anyone help me find the problem here please?

You will of course have to adjust the config.properties file to suit your SQL Server connection (I have it configured to use WIndows auth and have included a couple of version of the necessary auth files - mssql-jdbc_auth-12.6.5.x64.dll - in the jRDC zip). The database just needs a table named table1 for the test app.

To demo the problem after you've set up your SQL Server database and configured the config.properties file to suit your connection:
1. Run the jrdc2 server app in Debug.
2. Run the client app in Release, and click the 'Test RDC Query' button. If you have anything in yout table1 then it should be shown in the textarea.
3. Click the 'Test RDC Query' button again and this time you'll get the error above.
The error occurs on every second query.

If you can dig out an old copy of jServer 4.02 (or probably 4.3) and use that instead of jServer 5, then the error won't appear and the client will happily repeat the query successfully. But jServer 4.10 also generates the error.

Thanks in advance.
 

Attachments

Sort by date Sort by votes
Interestingly:
Code: 
Dim jo As JavaObject = pool ' Assuming 'pool' is your ConnectionPool
Dim  MaxStatements As Int = jo.RunMethod("getMaxStatements")
Log(MaxStatements)                                         '<--------- 150
jo.RunMethod("setMaxStatements", Array(0))
Dim  MaxStatements As Int = jo.RunMethod("getMaxStatements")
Log(MaxStatements)                                         '<--------- 0
under all the version of jServer I have (4.02, 4.10, 5.02), MaxStatements is set at 150.
So I'm not sure why I haven't needed to specifically call jo.RunMethod("setMaxStatements", Array(0)) before.
 
Last edited:
Upvote 0
From https://www.mchange.com/projects/c3p0/#configuring_statement_pooling;
Note: If you enable statement caching and see log mesages like "Problem with checked-in Statement, discarding.", and then stack traces indicating a Statement is unexpectedly closed, your JDBC driver may be automatically closing unclosed Statements at the end of marked user sessions. This behavior interferes with Statement caching. Try setting markSessionBoundaries to if-no-statement-cache or never to address the problem.
Click to expand...
markSessionBoundaries
Default: always
One of always, never, or if-no-statement-cache. Defines whether session boundaries will be marked by calling the beginRequest and endRequest methods of Connection on connection checkout and checkin respectively. These methods are optional hints to the JDBC driver, helping them manage user session. Almost always the default of, um, always will be best, but occasionally JDBC drivers respond to these hints undesirably. In particular, some JDBC drivers close unclosed Statement objects when endRequest is called. Setting this parameter to never disables session-boundary marking entirely. Setting this parameter to if-no-statement-cache enables session-boundary marking if and only if no statement cache is configured (that is if both maxStatements and maxStatementsPerConnection are configured to their defaults of zero).
Click to expand...

I can also fix this particular problem by leaving MaxStatements at the jServer default (which is set at 150 in the ConnectionPool source), but setting instead:
Code: 
Dim jo As JavaObject = pool
jo.RunMethod("setMarkSessionBoundaries", Array("if-no-statement-cache"))
I guess this might be a better fix given that it will leave statement caching turned on.
 
Last edited:
Upvote 0
You must log in or register to reply here.
Back
Top