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 !!!

The error suggests that datanucleus executed a query that wasn't a `SELECT`. Do you know what query was generated? If possible could you supply me with a minimal program+database to reproduce this at http://tracker.firebirdsql.org/browse/JDBC — Mark Rotteveel, Mar 21 '14 at 11:32
we use the only query in the my program : try { Query queryJDOQL =pm.newQuery(Zone.class, ***"ID_Zone > value"***); queryJDOQL.declareParameters("int value"); ListResult = (List) queryJDOQL.execute(0); we don't have any other query !!! — user3445750, Mar 21 '14 at 14:47
The SQL query is in the LOG; the first thing that people should look at when debugging — DataNucleus, Mar 21 '14 at 15:28
I was referring to the generated query that is actually sent to firebird. — Mark Rotteveel, Mar 21 '14 at 18:02
I have been able to somewhat reproduce this, but not with the exact exception. Which Jaybird version are you using. The workaround seems to be to explicitly use a transaction. — Mark Rotteveel, Mar 23 '14 at 11:24

Mark Rotteveel · Answer 1 · 2020-12-31T14:45:34.353

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:

Use an explicit transaction
Specify the defaultHoldable=true connection property in the connection string to make result sets HOLD_CURSORS_OVER_COMMIT by default
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)

thank for you reponse, it's OK now, I can obtain a list of objects. but I have The same probleme with the BDD. After each connexion a new table appares in the BDD named DELETMExxxxx !!! — user3445750, Mar 24 '14 at 09:02
@user3445750 I have no idea what BDD is; but it sounds like a totally separate question. — Mark Rotteveel, Mar 24 '14 at 09:29
BDD = Data Base ( Base de donnée en francais). after each use of PMF class (PersistenceManagerFactory). may be, I should public my question in other page — user3445750, Mar 24 '14 at 10:03

firebird : SQL error code = -504 Invalid cursor reference

1 Answers1