"not implemented by SQLite JDBC driver" when executing a parameterless statement in ColdFusion

[Tomalak]

I have a ColdFusion 2016 server running on Windows, testing out the SQLite driver (sqlite-jdbc-3.21.0.jar).

Whenever a query did not contain a parameter, the driver would throw a "not implemented" exception:

"Error","ajp-nio-8016-exec-6","03/08/18","18:43:36","TestApp","Error Executing Database Query.not implemented by SQLite JDBC driver The specific sequence of files included or processed is: D:\web\test.cfm, line: 5 "
coldfusion.tagext.sql.QueryTag$DatabaseQueryException: Error Executing Database Query.
	at coldfusion.tagext.sql.QueryTag.startQueryExecution(QueryTag.java:866)
	at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:768)
	at cfbla2ecfm508018408.runPage(D:\web\test.cfm:5)
	at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:253)
	at coldfusion.tagext.lang.IncludeTag.handlePageInvoke(IncludeTag.java:737)
	at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:573)
	at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65)
	at coldfusion.filter.IpFilter.invoke(IpFilter.java:45)
	at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:505)
	at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:43)
	at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40)
	at coldfusion.filter.PathFilter.invoke(PathFilter.java:153)
	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: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:219)
	at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
	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:192)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:474)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:363)
	at org.apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:507)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:798)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1434)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	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)
Caused by: java.sql.SQLException: not implemented by SQLite JDBC driver
	at org.sqlite.jdbc3.JDBC3Statement.unused(JDBC3Statement.java:397)
	at org.sqlite.jdbc3.JDBC3Statement.execute(JDBC3Statement.java:414)
	at coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:359)
	at coldfusion.sql.Executive.executeQuery(Executive.java:1531)
	at coldfusion.sql.Executive.executeQuery(Executive.java:1281)
	at coldfusion.sql.Executive.executeQuery(Executive.java:1211)
	at coldfusion.sql.SqlImpl.execute(SqlImpl.java:406)
	at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:1181)
	at coldfusion.tagext.sql.QueryTag.startQueryExecution(QueryTag.java:815)
	... 45 more

I believe it might have to do with what's described in this StackOverflow thread, but in ColdFusion you don't really get to chose which way the statement.executeQuery() is called, there is only the <cfquery> tag.

So this works:

<cfquery name="test" datasource="sqliteDataSource">
  SELECT * FROM tableName WHERE colName = <cfqueryparam value="1" cfsqltype="CF_SQL_INTEGER">
</cfquery>

and this throws:

<cfquery name="test" datasource="sqliteDataSource">
  SELECT * FROM tableName WHERE colName = 1
</cfquery>

It's definitely the absence of a parameter that triggers it. The query runs just fine after adding a pointless parameter like WHERE 1 = <cfqueryparam value="1" cfsqltype="CF_SQL_INTEGER">.

[JackArcher2]

I had the same issue as Tomalak. It occurs on CF 10 as well as CF 2016. I tried it (sqlite-jdbc-3.27.2.1.jar) with Lucee and it worked fine though.

My only solution was to use a different outdated driver sqlitejdbc-v056.jar

[gotson]

Is this still happening on the latest version?

[Tomalak]

@gotson Yes, I'm afraid it is. I've run into this with 3.36.0.3 only recently.

[gotson]

would you know which execute call in JDBC3Statement is throwing ? I don't know which version was used for the stacktrace provided, so difficult to know which line we are talking about.

--> at org.sqlite.jdbc3.JDBC3Statement.execute(JDBC3Statement.java:414)

[Tomalak]

@gotson The stack trace was from version 3.21.0, as stated above. :)

I can dig into how the ColdFusion calling code looks like, too, if that helps?

[gotson]

JDBC3Statement

My bad, i didn't read properly.

So the not implemented method is this one: https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#execute(java.lang.String,%20int)

Would you be able to find out what is the second parameter passed? Seems it can take one of the following constants: Statement.RETURN_GENERATED_KEYS or Statement.NO_GENERATED_KEYS

[Tomalak]

OK, so the calling code on the ColdFusion side of things uses

results = statement.execute(sql, Statement.RETURN_GENERATED_KEYS);

in the "no query parameters given" case.

The sqlite-jdbc version in the classpath still is 3.21.0, and it throws in org.sqlite.jdbc3.JDBC3Statement in

public boolean execute(String sql, int autokeys) throws SQLExecption {
    throw unused();
}

There is a code path in ColdFusion that calls

results = statement.execute(sql);

which should be functional in org.sqlite.jdbc3.JDBC3Statement. The code path depends on the connection object's .getMetaData().supportsGetGeneratedKeys() method returning false. (No idea if that is desirable.)

[Tomalak]

FWIW, the behavior is the same when I swap the sqlite-jdbc JAR to version 3.36.0.3, not exactly a surprise, but I wanted to throw it in.

[gotson]

supportsGetGeneratedKeys returns true, but that is not implemented everywhere from what i can see. I think it's because the support for that in SQLite is limited via last_insert_rowid.

At least we know what needs to be done now if anyone wants to try and implement it

[Tomalak]

Maybe supportsGetGeneratedKeys should not return true if that support does not actually exist in the driver.

[gotson]

Maybe supportsGetGeneratedKeys should not return true if that support does not actually exist in the driver.

It does, but not everywhere. That's my understanding.

[Tomalak]

Hm... would there be any considerable downside to letting public boolean execute(String sql, int autokeys) call public boolean execute(String sql), silently ignoring the int autokeys? That would be a one-line change.

[gotson]

Hm... would there be any considerable downside to letting public boolean execute(String sql, int autokeys) call public boolean execute(String sql), silently ignoring the int autokeys? That would be a one-line change.

I've done just that.

Currently the generated keys are not stored at Statement level but at database level and retrieved using last_insert_rowid. Changing that to store the generated keys at Statement level would take some more work though.

[Tomalak]

I consider having that in the Statement as nice-to-have, but not essential. I'm fine with executing SELECT last_insert_rowid(); when necessary.

[gotson]

I consider having that in the Statement as nice-to-have, but not essential. I'm fine with executing SELECT last_insert_rowid(); when necessary.

You can use the JDBC way via getGeneratedKeys, it will do the same thing.

[Tomalak]

Cool, good to know. Thanks for fixing this! :)