So I have Adobe ColdFusion 11 Update 5 installed on a Windows 2012 server. I added a Firebird datasource using the Jaybird 2.2.5 JDBC driver. I created a query using that datasource like so:
<cfquery name="rawdata" datasource="RSReport">
select * from rptpatientstudy
where statusorder >= 200
and issuer = 'RWHG'
and studydatetime >= '2015-01-01'
and studydatetime < '2015-04-01';
</cfquery>
but when I run the page, I get a Dynamic SQL error mentioning an unknown token.
Here's the stack trace:
org.firebirdsql.gds.GDSException: Dynamic SQL Error
SQL error code = -104 Token unknown - line 2, column 1 RETURNING
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.iscDsqlPrepare(AbstractJavaGDSImpl.java:1465)
at org.firebirdsql.gds.impl.GDSHelper.prepareStatement(GDSHelper.java:190)
at org.firebirdsql.jdbc.AbstractStatement.prepareFixedStatement(AbstractStatement.java:1441)
at org.firebirdsql.jdbc.AbstractStatement.internalExecute(AbstractStatement.java:1423)
at org.firebirdsql.jdbc.AbstractStatement.execute(AbstractStatement.java:867)
at org.firebirdsql.jdbc.AbstractStatement.execute(AbstractStatement.java:441)
at coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:359)
at coldfusion.sql.Executive.executeQuery(Executive.java:1479)
at coldfusion.sql.Executive.executeQuery(Executive.java:1229)
at coldfusion.sql.Executive.executeQuery(Executive.java:1159)
at coldfusion.sql.SqlImpl.execute(SqlImpl.java:406)
at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:1185)
at coldfusion.tagext.sql.QueryTag.startQueryExecution(QueryTag.java:814)
at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:767)
at cfmarla2ecfm1504149200.runPage(C:\inetpub\WebSites\intranet\marla.cfm:1)
at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:246)
at coldfusion.tagext.lang.IncludeTag.handlePageInvoke(IncludeTag.java:736)
at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:572)
at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65)
at coldfusion.filter.IpFilter.invoke(IpFilter.java:45)
at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:466)
at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40)
at coldfusion.filter.PathFilter.invoke(PathFilter.java:142)
at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:94)
at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28)
at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38)
at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:58)
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:219)
at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42)
at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:422)
at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:199)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
Here's a list of the fields in the table:
STUDYINSTANCEUID
PATIENTID
PATIENTNAME
STUDYDATETIME
STUDYDESCRIPTION
PRIORITY
READINGPHYSICIAN
REFERINGPHYSICIAN
PERFORMINGPHYSICIAN
TECHNOLOGIST
TRANSCRIPTIONIST
ACCESSIONNUMBER
INSTITUTIONNAME
STATUSORDER
SCHEDULEDBODYPARTEXAMINED
SCHEDULEDMODALITY
UTCOFFSET
HL7BILLED
SCHEDULEDEXAMROOM
PATIENTBIRTHDATE
MODALITIES
BODYPARTS
IMAGES
AGE
SEX
CONFLICT
STUDYID
FILESETID
ISSUER
SCHEDULEDLATERALITY
LOCATION
PROCEDURECODE
DATETIMEREAD
DURATION
FILM
BURNCD
MAIL
COURIER
AUTOFAXLIST
AUTOEMAILLIST
SCHEDULEDRESOURCE
ACCOUNTSTATUS
LANGUAGE
FINANCIALTYPE
STATE
INSURANCEPAYERS
INSURANCEEXPIRIES
COMMENTS
ALLERGIES
CANCELLATIONREASON
CELLPHONE
HOMEPHONE
AUTHORIZATIONNUMBER
DATEOFSURGERY
RECEIVEDTIME
INTERNALVISITID
SMOKINGSTATUS
I'm really not sure where this might have come from, and I can't seem to find something directly applicable on Google; most of the results there seem to deal with using derived tables or SELECT * in the query.
TL;DR: It is a bug in Jaybird. I have created bug JDBC-391 and this has been fixed in Jaybird 2.2.8.
The execute method called in the stacktrace is execute(String, int) which is usually only called for update/insert that should return generated keys. It looks like Cold Fusion always calls it this way (with value Statement.RETURN_GENERATED_KEYS).
The implementation that handles generated keys in Jaybird assumes that its internal statement parser throws an exception when confronted with a SELECT statement; if that happens the query should be processed unmodified. Unfortunately there is no test for this assumption and the parser actually doesn't throw an exception when errors occur during parsing (they are signaled in a different way). As a result the SELECT query is modified by appending a RETURNING clause, which is a syntax error when the query is then sent to the server.
I am not sure if there is a workaround for this, except maybe reverting to Jaybird 2.1.6 as this problem has been there since the initial implementation in Jaybird 2.2.0. You could also check if Cold Fusion has any configuration that would modify the way it executes queries.
This has been fixed in Jaybird 2.2.8.
Disclosure: I am one of the developers of Jaybird.