firebird : SQL error code = -504 Invalid cursor reference

Question

we use firebird, gwt ( DATANUCLEUS, persistence objet) to develop an Web Applicaiton.

Map<String, String> props = new HashMap<String, String>();
        props.put("javax.jdo.PersistenceManagerFactoryClass",
                "org.datanucleus.api.jdo.JDOPersistenceManagerFactory");
        props.put("javax.jdo.option.ConnectionDriverName",
                "org.firebirdsql.jdbc.FBDriver");               
        props.put("javax.jdo.option.ConnectionURL",             "jdbc:firebirdsql://localhost/D:/ XXX fdb");
        props.put("javax.jdo.option.ConnectionUserName", "SYSDBA");
        props.put("javax.jdo.option.ConnectionPassword", "masterkey");
        props.put("javax.jdo.option.NontransactionalRead", "true");
        props.put("javax.jdo.option.NontransactionalWrite", "true");
        props.put("javax.jdo.option.RetainValues", "true");
        props.put("datanucleus.autoCreateSchema", "true");
        props.put("datanucleus.query.sql.allowAll", "true");
        pmfInstance = JDOHelper.getPersistenceManagerFactory(props);}
    private PMF() {
    }
    public static PersistenceManagerFactory getFactory() {
        return pmfInstance;
    }
    public static PersistenceManager getManager() {
        return pmfInstance.getPersistenceManager();
    }}}

in the JDO class, we have this function, where Zone is a "class JDO" :

public static  List<Zone> queryAllZone() {

        PersistenceManager pm = PMF.getManager();
        Zone result =null;
        List<Zone> ListResult = null;//new ArrayList<Zone>();

        try {
            Query queryJDOQL =pm.newQuery(Zone.class, ***"ID_Zone > value"***);

            queryJDOQL.declareParameters("int value");

            ListResult =        (List<Zone>) queryJDOQL.execute(0);
        } finally {
            pm.close();
        }
    return  ListResult;
    }

I have the result error :

Starting Jetty on port 0
   [WARN] Exception while dispatching incoming RPC call
com.google.gwt.user.server.rpc.UnexpectedException: Service method 'public abstract java.util.List com.exatelys.superviser.client.common.ZoneService.getAllZone()' threw an unexpected exception: javax.jdo.JDOUserException: Exception thrown while loading remaining rows of query
NestedThrowables:
javax.jdo.JDODataStoreException: Failed to read the result set : GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -504
Invalid cursor reference
NestedThrowables:
org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -504
Invalid cursor reference
    at com.google.gwt.user.server.rpc.RPC.encodeResponseForFailure(RPC.java:389)
    at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:579)
    at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:208)
    at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:248)
    at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
    at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:362)
    at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
    at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
    at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:729)
    at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
    at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
    at org.mortbay.jetty.handler.RequestLogHandler.handle(RequestLogHandler.java:49)
    at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
    at org.mortbay.jetty.Server.handle(Server.java:324)
    at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
    at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843)
    at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:647)
    at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211)
    at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
    at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
    at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)
Caused by: javax.jdo.JDOUserException: Exception thrown while loading remaining rows of query
NestedThrowables:
javax.jdo.JDODataStoreException: Failed to read the result set : GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -504
Invalid cursor reference
NestedThrowables:
org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -504
Invalid cursor reference
    at org.datanucleus.api.jdo.JDOAdapter.getUserExceptionForException(JDOAdapter.java:1139)
    at org.datanucleus.store.rdbms.query.ForwardQueryResult.closingConnection(ForwardQueryResult.java:272)
    at org.datanucleus.store.query.AbstractQueryResult.disconnect(AbstractQueryResult.java:107)
    at org.datanucleus.store.rdbms.query.AbstractRDBMSQueryResult.disconnect(AbstractRDBMSQueryResult.java:68)
    at org.datanucleus.store.rdbms.query.JDOQLQuery$2.managedConnectionPreClose(JDOQLQuery.java:718)
    at org.datanucleus.store.rdbms.ConnectionFactoryImpl$ManagedConnectionImpl.close(ConnectionFactoryImpl.java:510)
    at org.datanucleus.store.connection.AbstractManagedConnection.release(AbstractManagedConnection.java:77)
    at org.datanucleus.store.rdbms.ConnectionFactoryImpl$ManagedConnectionImpl.release(ConnectionFactoryImpl.java:347)
    at org.datanucleus.store.rdbms.query.JDOQLQuery.performExecute(JDOQLQuery.java:834)
    at org.datanucleus.store.query.Query.executeQuery(Query.java:1786)
    at org.datanucleus.store.query.Query.executeWithArray(Query.java:1672)
    at org.datanucleus.api.jdo.JDOQuery.execute(JDOQuery.java:243)
    at com.exatelys.superviser.server.jdo.Zone.queryAllZone(Zone.java:163)
    at com.exatelys.superviser.server.ZoneServiceImpl.getAllZone(ZoneServiceImpl.java:25)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:561)
    ... 22 more
Caused by: javax.jdo.JDODataStoreException: Failed to read the result set : GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -504
Invalid cursor reference
NestedThrowables:
org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -504
Invalid cursor reference
    at org.datanucleus.api.jdo.JDOAdapter.getDataStoreExceptionForException(JDOAdapter.java:1150)
    at org.datanucleus.store.rdbms.query.ForwardQueryResult.nextResultSetElement(ForwardQueryResult.java:209)
    at org.datanucleus.store.rdbms.query.ForwardQueryResult$QueryResultIterator.next(ForwardQueryResult.java:379)
    at org.datanucleus.store.rdbms.query.ForwardQueryResult.processNumberOfResults(ForwardQueryResult.java:137)
    at org.datanucleus.store.rdbms.query.ForwardQueryResult.advanceToEndOfResultSet(ForwardQueryResult.java:158)
    at org.datanucleus.store.rdbms.query.ForwardQueryResult.closingConnection(ForwardQueryResult.java:260)
    ... 39 more
Caused by: org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -504
Invalid cursor reference
    at org.firebirdsql.jdbc.FBStatementFetcher.fetch(FBStatementFetcher.java:206)
    at org.firebirdsql.jdbc.FBStatementFetcher.next(FBStatementFetcher.java:137)
    at org.firebirdsql.jdbc.AbstractResultSet.next(AbstractResultSet.java:273)
    at org.datanucleus.store.rdbms.datasource.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207)
    at org.datanucleus.store.rdbms.datasource.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207)
    at org.datanucleus.store.rdbms.datasource.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207)
    at org.datanucleus.store.rdbms.query.ForwardQueryResult.nextResultSetElement(ForwardQueryResult.java:191)
    ... 43 more
Caused by: org.firebirdsql.gds.GDSException: Dynamic SQL Error
SQL error code = -504
Invalid cursor reference
    at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.readStatusVector(AbstractJavaGDSImpl.java:2092)
    at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.receiveResponse(AbstractJavaGDSImpl.java:2042)
    at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.iscDsqlFetch(AbstractJavaGDSImpl.java:1322)
    at org.firebirdsql.gds.impl.GDSHelper.fetch(GDSHelper.java:265)
    at org.firebirdsql.jdbc.FBStatementFetcher.fetch(FBStatementFetcher.java:201)
    ... 49 more

when w use == than > in the expression "ID_Zone == value", we obtain th good result. but if we try the obtain a list ( more than object ) we have always the same problme.

more than, after eatch connection ( use of PMF class) we wil have a new empty table in the B.D.D named deletedxxxxx. can you help me please !!!

Answer 1

The problem is that in Jaybird TYPE_FORWARD_ONLY result sets are by default CLOSE_CURSORS_AT_COMMIT and are fetched on demand.

When no explicit transaction is used, DataNucleus commits the connection after initialization (but not processing) of the result set, and as a result the ResultSet is closed.

Subsequently, DataNucleus closes/releases the (managed) connection, which triggers a fetch from the ResultSet. This results in an error as the transaction has been committed and the result set is already closed. Due to a bug in Jaybird this ResultSet close is not 'handled' locally and only when the request hits the server an error is triggered as the cursor handle is no longer valid. It looks like single item fetches work by accident due to that same bug.

I'd think that DataNucleus should perform this fetch on commit, not on close of the connection. And it looks like it does exactly that when using an explicit transaction, but not without.

The workarounds are:

1. Use an explicit transaction
2. Specify the defaultHoldable=true connection property in the connection string to make result sets HOLD_CURSORS_OVER_COMMIT by default
3. Set the fetch size to eager: queryJDOQL.getFetchPlan().setFetchSize(FetchPlan.FETCH_SIZE_GREEDY);

Note: I tested this with Jaybird 2.2.4 (the latest released version).

I have reported this bug as NUCRDBMS-779 (bug has been fixed, see last comment in ticket)