升级Jaybird 5.0.6后使用RETURNING子句获取插入ID失败

环境信息 #

• Firebird版本：从3.0升级至4.0
• 开发环境：ColdFusion
• Jaybird驱动版本：5.0.6 for Java 8

问题现象 #

此前通过INSERT ... RETURNING IDCOLUMN_NAME语句可以正常获取插入的主键ID，升级后执行相同查询，ColdFusion提示<cfquery>绑定的变量未定义，无法通过query.REPORT_ID访问返回结果。但该查询在IBExpert中执行完全正常。

若将语句改为RETURNING *，则抛出NullPointerException，异常堆栈指向BLOB相关操作。

表结构示例 #

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

查询示例 #

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

错误详情 #

• 执行RETURNING REPORT_ID：ColdFusion报错<cfquery>绑定的变量未定义，无法获取返回值
• 执行RETURNING *：抛出异常：

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

完整堆栈跟踪：

java.lang.NullPointerException at
org.firebirdsql.gds.ng.AbstractFbBlob.(AbstractFbBlob.java:52)
at
org.firebirdsql.gds.ng.wire.AbstractFbWireBlob.(AbstractFbWireBlob.java:38)
at
org.firebirdsql.gds.ng.wire.AbstractFbWireInputBlob.(AbstractFbWireInputBlob.java:37)
at
org.firebirdsql.gds.ng.wire.version10.V10InputBlob.(V10InputBlob.java:47)
at
org.firebirdsql.gds.ng.wire.version16.Version16Descriptor.createInputBlob(Version16Descriptor.java:103)
at
org.firebirdsql.gds.ng.wire.AbstractFbWireDatabase.createBlobForInput(AbstractFbWireDatabase.java:192)
at
org.firebirdsql.gds.ng.FbDatabase.createBlobForInput(FbDatabase.java:228)
at org.firebirdsql.gds.impl.GDSHelper.openBlob(GDSHelper.java:136) at
org.firebirdsql.jdbc.FBBlob.openBlob(FBBlob.java:200) at
org.firebirdsql.jdbc.FBBlobInputStream.(FBBlobInputStream.java:53)
at org.firebirdsql.jdbc.FBBlob.getBinaryStream(FBBlob.java:373) at
org.firebirdsql.jdbc.field.FBLongVarCharField.getBytes(FBLongVarCharField.java:125)
at
org.firebirdsql.jdbc.field.FBLongVarCharField.getCachedData(FBLongVarCharField.java:145)
at
org.firebirdsql.jdbc.FBCachedFetcher.cacheBlobsInRow(FBCachedFetcher.java:166)
at
org.firebirdsql.jdbc.FBCachedFetcher.(FBCachedFetcher.java:116)
at org.firebirdsql.jdbc.FBResultSet.(FBResultSet.java:268) at
org.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
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at
org.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
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:377)
at org.apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:463)
at
org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at
org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:889)
at
org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743)
at
org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at
org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at
org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at
org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.base/java.lang.Thread.run(Thread.java:844)

解决方向 #

• 检查驱动与ColdFusion兼容性：Jaybird 5.x对JDBC的处理逻辑有变更，可尝试在查询后显式调用getGeneratedKeys()，或检查ColdFusion数据源配置中是否有开启返回生成键的参数。
• 避免使用RETURNING *：堆栈显示问题源于BLOB列读取，Jaybird 5.0.0后对BLOB的RETURNING处理有调整，即使包含主键，也会因BLOB读取异常导致结果无法获取，坚持只返回需要的主键列。
• 调整Firebird连接参数：在JDBC连接URL中添加useGeneratedKeys=true或returnGeneratedKeys=true，强制驱动返回生成的主键值。
• 升级Jaybird版本：当前使用的5.0.6可能存在BLOB相关的BUG，尝试升级至Jaybird最新稳定版，查看是否修复了该NullPointerException问题。
• 修改主键生成方式：将GEN_ID(REPORT_ID_GEN,1)替换为Firebird 4.0支持的IDENTITY列，虽然需要修改表结构，但可能解决驱动对生成键的识别问题。

内容的提问来源于stack exchange，提问作者Phil