I'm working on upgrading my Firebird database from 3.0 to 4.0. I've completed the migration steps and I'm now testing with my web application and I ran into an issue. I'm using ColdFusion and the Jaybird 5.0.6 for Java 8 driver, and where before I could do a INSERT INTO [table] ..... RETURNING IDCOLUMN_NAME, I am now getting nothing.

I can run the same query in IBExpert, and it works fine, so it must be something that has changed with the Jaybird driver that I need to adjust or do differently, but I'm not sure what they would be. I looked through the release notes for this version, but all I see is a reference to a bug where in 5.0.0 if RETURNING specified a BLOB column it would return the BLOB column ID and not the content. That's not what I'm doing here though. My column is integer, primary key, generator based (old from Firebird 2.5).

Table example:

CREATE TABLE REPORT_QUEUE (
    REPORT_ID        INTEGER NOT NULL,
    FIELD1           VARCHAR(50),
    FIELD2           VARCHAR(50),
    FIELD3           BLOB SUB_TYPE TEXT SEGMENT SIZE 80
);

Query example:

<cfquery name="query" datasource="#datasource#">
INSERT INTO REPORT_QUEUE (REPORT_ID, FIELD1, FIELD2, FIELD3 )
VALUES (
     GEN_ID(REPORT_ID_GEN,1),
     'TESTVALUE1',
     'TESTVALUE2',
     'TESTBLOBCONTENT'
) RETURNING REPORT_ID
</cfquery>

With previous Jaybird driver, I could then do a <cfdump var=#query#> and see the results, or access it via query.REPORT_ID. However, I get an error stating that "query" (or whatever I variable I assign to my <cfquery>) is undefined. If I change it to RETURNING *, I get

java.lang.NullPointerException at .firebirdsql.gds.ng.AbstractFbBlob

Can anyone point me in the right direction here? This table does have BLOB columns, but the primary key is an integer, and is set with GENERATOR value.

Full Stacktrace below

java.lang.NullPointerException at .firebirdsql.gds.ng.AbstractFbBlob.(AbstractFbBlob.java:52) at .firebirdsql.gds.ng.wire.AbstractFbWireBlob.(AbstractFbWireBlob.java:38) at .firebirdsql.gds.ng.wire.AbstractFbWireInputBlob.(AbstractFbWireInputBlob.java:37) at .firebirdsql.gds.ng.wire.version10.V10InputBlob.(V10InputBlob.java:47) at .firebirdsql.gds.ng.wire.version16.Version16Descriptor.createInputBlob(Version16Descriptor.java:103) at .firebirdsql.gds.ng.wire.AbstractFbWireDatabase.createBlobForInput(AbstractFbWireDatabase.java:192) at .firebirdsql.gds.ng.FbDatabase.createBlobForInput(FbDatabase.java:228) at .firebirdsql.gds.impl.GDSHelper.openBlob(GDSHelper.java:136) at .firebirdsql.jdbc.FBBlob.openBlob(FBBlob.java:200) at .firebirdsql.jdbc.FBBlobInputStream.(FBBlobInputStream.java:53) at .firebirdsql.jdbc.FBBlob.getBinaryStream(FBBlob.java:373) at .firebirdsql.jdbc.field.FBLongVarCharField.getBytes(FBLongVarCharField.java:125) at .firebirdsql.jdbc.field.FBLongVarCharField.getCachedData(FBLongVarCharField.java:145) at .firebirdsql.jdbc.FBCachedFetcher.cacheBlobsInRow(FBCachedFetcher.java:166) at .firebirdsql.jdbc.FBCachedFetcher.(FBCachedFetcher.java:116) at .firebirdsql.jdbc.FBResultSet.(FBResultSet.java:268) at .firebirdsql.jdbc.FBStatement.getGeneratedKeys(FBStatement.java:420) at coldfusion.server.j2ee.sql.JRunStatement.getGeneratedKeys(JRunStatement.java:426) at coldfusion.sql.Executive.getRowSet(Executive.java:733) at coldfusion.sql.Executive.executeQuery(Executive.java:1586) at coldfusion.sql.Executive.executeQuery(Executive.java:1317) at coldfusion.sql.Executive.executeQuery(Executive.java:1247) at coldfusion.sql.SqlImpl.execute(SqlImpl.java:427) at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:1211) at coldfusion.tagext.sql.QueryTag.startQueryExecution(QueryTag.java:841) at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:794) at cftest2ecfm1195641456.runPage(C:\websites\test.cfm:13) at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:262) at coldfusion.tagext.lang.IncludeTag.handlePageInvoke(IncludeTag.java:735) at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:565) at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65) at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:555) at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:43) at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40) at coldfusion.filter.PathFilter.invoke(PathFilter.java:162) at coldfusion.filter.IpFilter.invoke(IpFilter.java:45) at coldfusion.filter.LicenseFilter.invoke(LicenseFilter.java:30) at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:96) at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38) at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:60) at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38) at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:62) at coldfusion.CfmServlet.service(CfmServlet.java:226) at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:311) at .apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227) at .apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:46) at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:47) at .apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at .apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at .apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) at .apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at .apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at .apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197) at .apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) at .apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) at .apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135) at .apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) at .apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) at .apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:377) at .apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:463) at .apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) at .apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:889) at .apache.tomcat.util.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743) at .apache.tomcat.util.SocketProcessorBase.run(SocketProcessorBase.java:49) at .apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) at .apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) at .apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.base/java.lang.Thread.run(Thread.java:844)

I'm working on upgrading my Firebird database from 3.0 to 4.0. I've completed the migration steps and I'm now testing with my web application and I ran into an issue. I'm using ColdFusion and the Jaybird 5.0.6 for Java 8 driver, and where before I could do a INSERT INTO [table] ..... RETURNING IDCOLUMN_NAME, I am now getting nothing.

I can run the same query in IBExpert, and it works fine, so it must be something that has changed with the Jaybird driver that I need to adjust or do differently, but I'm not sure what they would be. I looked through the release notes for this version, but all I see is a reference to a bug where in 5.0.0 if RETURNING specified a BLOB column it would return the BLOB column ID and not the content. That's not what I'm doing here though. My column is integer, primary key, generator based (old from Firebird 2.5).

Table example:

CREATE TABLE REPORT_QUEUE (
    REPORT_ID        INTEGER NOT NULL,
    FIELD1           VARCHAR(50),
    FIELD2           VARCHAR(50),
    FIELD3           BLOB SUB_TYPE TEXT SEGMENT SIZE 80
);

Query example:

<cfquery name="query" datasource="#datasource#">
INSERT INTO REPORT_QUEUE (REPORT_ID, FIELD1, FIELD2, FIELD3 )
VALUES (
     GEN_ID(REPORT_ID_GEN,1),
     'TESTVALUE1',
     'TESTVALUE2',
     'TESTBLOBCONTENT'
) RETURNING REPORT_ID
</cfquery>

With previous Jaybird driver, I could then do a <cfdump var=#query#> and see the results, or access it via query.REPORT_ID. However, I get an error stating that "query" (or whatever I variable I assign to my <cfquery>) is undefined. If I change it to RETURNING *, I get

java.lang.NullPointerException at .firebirdsql.gds.ng.AbstractFbBlob

Can anyone point me in the right direction here? This table does have BLOB columns, but the primary key is an integer, and is set with GENERATOR value.

Full Stacktrace below

java.lang.NullPointerException at .firebirdsql.gds.ng.AbstractFbBlob.(AbstractFbBlob.java:52) at .firebirdsql.gds.ng.wire.AbstractFbWireBlob.(AbstractFbWireBlob.java:38) at .firebirdsql.gds.ng.wire.AbstractFbWireInputBlob.(AbstractFbWireInputBlob.java:37) at .firebirdsql.gds.ng.wire.version10.V10InputBlob.(V10InputBlob.java:47) at .firebirdsql.gds.ng.wire.version16.Version16Descriptor.createInputBlob(Version16Descriptor.java:103) at .firebirdsql.gds.ng.wire.AbstractFbWireDatabase.createBlobForInput(AbstractFbWireDatabase.java:192) at .firebirdsql.gds.ng.FbDatabase.createBlobForInput(FbDatabase.java:228) at .firebirdsql.gds.impl.GDSHelper.openBlob(GDSHelper.java:136) at .firebirdsql.jdbc.FBBlob.openBlob(FBBlob.java:200) at .firebirdsql.jdbc.FBBlobInputStream.(FBBlobInputStream.java:53) at .firebirdsql.jdbc.FBBlob.getBinaryStream(FBBlob.java:373) at .firebirdsql.jdbc.field.FBLongVarCharField.getBytes(FBLongVarCharField.java:125) at .firebirdsql.jdbc.field.FBLongVarCharField.getCachedData(FBLongVarCharField.java:145) at .firebirdsql.jdbc.FBCachedFetcher.cacheBlobsInRow(FBCachedFetcher.java:166) at .firebirdsql.jdbc.FBCachedFetcher.(FBCachedFetcher.java:116) at .firebirdsql.jdbc.FBResultSet.(FBResultSet.java:268) at .firebirdsql.jdbc.FBStatement.getGeneratedKeys(FBStatement.java:420) at coldfusion.server.j2ee.sql.JRunStatement.getGeneratedKeys(JRunStatement.java:426) at coldfusion.sql.Executive.getRowSet(Executive.java:733) at coldfusion.sql.Executive.executeQuery(Executive.java:1586) at coldfusion.sql.Executive.executeQuery(Executive.java:1317) at coldfusion.sql.Executive.executeQuery(Executive.java:1247) at coldfusion.sql.SqlImpl.execute(SqlImpl.java:427) at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:1211) at coldfusion.tagext.sql.QueryTag.startQueryExecution(QueryTag.java:841) at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:794) at cftest2ecfm1195641456.runPage(C:\websites\test.cfm:13) at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:262) at coldfusion.tagext.lang.IncludeTag.handlePageInvoke(IncludeTag.java:735) at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:565) at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65) at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:555) at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:43) at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40) at coldfusion.filter.PathFilter.invoke(PathFilter.java:162) at coldfusion.filter.IpFilter.invoke(IpFilter.java:45) at coldfusion.filter.LicenseFilter.invoke(LicenseFilter.java:30) at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:96) at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38) at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:60) at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38) at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:62) at coldfusion.CfmServlet.service(CfmServlet.java:226) at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:311) at .apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227) at .apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:46) at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:47) at .apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at .apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at .apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) at .apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at .apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at .apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197) at .apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) at .apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) at .apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135) at .apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) at .apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) at .apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:377) at .apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:463) at .apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) at .apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:889) at .apache.tomcat.util.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743) at .apache.tomcat.util.SocketProcessorBase.run(SocketProcessorBase.java:49) at .apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) at .apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) at .apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.base/java.lang.Thread.run(Thread.java:844)

• coldfusion
• firebird
• jaybird
• firebird-4.0

Share Improve this question Follow edited Mar 25 at 10:55 Phil asked Mar 24 at 18:14 PhilPhil 4,08999 gold badges7171 silver badges118118 bronze badges 12

• Please provide a minimal reproducible example, also, a bit of a pet peeve of mine, the driver is called Jaybird (lowercase b), not JayBird. – Mark Rotteveel Commented Mar 25 at 8:09
• @user13964273 That change doesn't apply to INSERT ... VALUES as that is still a singleton result, also that is a Firebird 5.0 change, not Firebird 4.0 – Mark Rotteveel Commented Mar 25 at 8:10
• The NullPointerException when using RETURNING * is a bug, and it references a blob because when you use RETURNING *, it returns all columns. – Mark Rotteveel Commented Mar 25 at 8:11
• I would also be helpful if you could post the entire stacktrace of that NullPointerException, so I can trace what's going down there. As to the "real" problem, I have no clue, because as far as I'm aware, it should still work the same. What was the previous Jaybird version you were using? – Mark Rotteveel Commented Mar 25 at 8:21
• 1 Since Jaybird 4.0.8 this changed. You could try to explicitly disable generated keys support with connection property generatedKeysEnabled=ignored or generatedKeysEnabled=disabled (though the latter will probably result in errors on execute). – Mark Rotteveel Commented Mar 25 at 12:00

 |  Show 7 more comments

1 Answer 1

Sorted by: Reset to default 0

As we hashed out in the chat, when you were using Jaybird 3.0.4, you probably did not have antlr-runtime-4.7.jar on the classpath, and as a result attempts to use generated keys (which ColdFusion does by default) would be effectively ignored.

This would then result in ColdFusion retrieving the result as a normal result set because you explicitly included the RETURNING clause, and things like <cfdump var=#query#> will then output that result set.

Since Jaybird 4.0.8 and Jaybird 5.0.0, the generated keys facility no longer relies on the ANTLR runtime, and so it works out of the box. See also Generated keys support always available in the Compatibility changes chapter of the Jaybird 5.0 release notes. With generated keys enabled (not ignored), executing INSERT .. RETURNING .. while requesting generated keys is not considered a result set producing statement, and this - I guess - results in <cfdump var=#query#> not having a result set to output.

To achieve the old behaviour you had with Jaybird 3.0.4, you need to configure Jaybird to ignore generated keys support, by setting connection property generatedKeysEnabled=ignored.

Alternatively, you need to change how you access the generated id. As far as I can tell from the cfquery documentation, that would involve setting the attribute result="somename" on your cfquery tag, and then accessing somename.GENERATEDKEY (or possibly somename.REPORT_ID). However, that is mostly a guess on my side, as I have no experience with ColdFusion.

The NullPointerException issue you mentioned when using RETURNING * (or RETURNING FIELD3, etc.) is a bug in Jaybird which occurs if getGeneratedKeys() is called after (auto)commit and the result has one or more blobs. I have created #844 - NullPointerException in getGeneratedKeys with blob columns after (auto)commit, and this has been fixed in Jaybird 5.0.7 and 6.0.1 (and 7.0.0).

Disclaimer: I maintain Jaybird.