not implemented by SQLite JDBC driver

Question

I have successfully created a ColdFusion datasource to a SQLite database using the sqlitejdbc-v056.jar. Then I added this query to my .cfm page

<cfquery name="qry" datasource="Spiceworks">
SELECT
id AS Ticket_Number,
summary AS Summary,
status AS Status,
created_at AS Created_At,
assigned_to AS Assigned_to

FROM tickets
WHERE status = 'open' AND assigned_to IS NULL
ORDER BY Created_At DESC   
</cfquery>

When I browse to my .cfm page in IE and Firefox, I get the error:

not implemented by SQLite JDBC driver

The error occurred in C:/Inetpub/wwwroot/intra/SmartTV/UnassignedTickets/Tickets.cfm: line 204

202 :
203 :
204 :
205 : SELECT
206 : id,

SQL SELECT id, summary, status, created_at, assigned_to FROM tickets WHERE status = 'open' AND assigned_to IS NULL ORDER BY created_at DESC DATASOURCE Spiceworks Resources:

Check the ColdFusion documentation to verify that you are using the correct syntax.
Search the Knowledge Base to find a solution to your problem.

Stack Trace

at cfTickets2ecfm1617356019.runPage(C:/Inetpub/wwwroot/intra/SmartTV/UnassignedTickets/Tickets.cfm:204) at cfTickets2ecfm1617356019.runPage(C:/Inetpub/wwwroot/intra/SmartTV/UnassignedTickets/Tickets.cfm:204) 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:1442)
    at coldfusion.sql.Executive.executeQuery(Executive.java:1201)
    at coldfusion.sql.Executive.executeQuery(Executive.java:1131)
    at coldfusion.sql.SqlImpl.execute(SqlImpl.java:406)
    at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:1056)
    at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:685)
    at cfTickets2ecfm1617356019.runPage(C:\Inetpub\wwwroot\intra\SmartTV\UnassignedTickets\Tickets.cfm:204)
    at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:244)
    at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:444)
    at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65)
    at coldfusion.filter.IpFilter.invoke(IpFilter.java:64)
    at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:428)
    at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48)
    at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40)
    at coldfusion.filter.PathFilter.invoke(PathFilter.java:112)
    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:46)
    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:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42)
    at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46)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:1442)
    at coldfusion.sql.Executive.executeQuery(Executive.java:1201)
    at coldfusion.sql.Executive.executeQuery(Executive.java:1131)
    at coldfusion.sql.SqlImpl.execute(SqlImpl.java:406)
    at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:1056)
    at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:685)
    at cfTickets2ecfm1617356019.runPage(C:\Inetpub\wwwroot\intra\SmartTV\UnassignedTickets\Tickets.cfm:204)
    at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:244)
    at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:444)
    at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65)
    at coldfusion.filter.IpFilter.invoke(IpFilter.java:64)
    at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:428)
    at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48)
    at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40)
    at coldfusion.filter.PathFilter.invoke(PathFilter.java:112)
    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:46)
    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:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42)
    at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46)

Update:

I have my .jar file under C:\ColdFusion10\cfusion\lib\ and C:\ColdFusion10\cfusion\wwwroot\WEB-INF\lib. In ColdFusion Datasources:

• CF Data Source Name: Spiceworks
• JDBC URL: jdbc:sqlite:\spiceworks\C$\Program Files\Spiceworks\db\spiceworks_prod.db
• Driver Class: org.sqlite.JDBC
• Driver Name: sqlite-jdbc-3.7.15-M1.jar
• User name: <user>
• Password: <password>

As suggested in the comments, I tried simpler statements:

• SELECT * FROM tickets
• SELECT id, summary, status, assigned_to FROM tickets

... and different driver versions

• sqllite-jdbc4-3.8.2-SNAPSHOT.jar
• sqlite-jdbc-3.7.15-M1.jar

but still got the same error.

Answer 1

I think the original error means exactly what it says. The datasource invoked a method that is not implemented by the driver you are using. Specifically the execute(..) method of org.sqlite.jdbc3.JDBC3Statement. The source for this driver shows that method does nothing. It simply throws the error message you are seeing.

However I think you have another version of the SQLite driver jar in your class path. The sqlitejdbc-v056.jar you posted could not cause that error because it does not even contain a JDBC3Statement class. What is probably happening is that CF is loading another other version of the driver (that is in your class path) instead.

In my tests, the sqlitejdbc-v056.jar worked fine with CF10. Before adding that jar again, make sure you have removed all versions of the SQLLite jar from your CF class path, and restart CF. Otherwise, CF will still load them automatically and you will continue to get that error message.

To ensure you have removed all of the jars, after restarting, try and create an instance of the main class. If the line below throws a "Class not found" error, you know have successfully removed them all. Once you have done that, go ahead an reinstall the sqlitejdbc-v056.jar.

<cfset obj = createObject("java", "org.sqlite.JDBC")>
If you see this text, you still have a version of the jar in your class path.

Here is what worked for me:

1. Created empty database C:/temp/sqlite/products.db
2. Downloaded sqlitejdbc-v056.jar into C:\ColdFusion10\cfusion\wwwroot\WEB-INF\lib
3. Restarted CF
4. Created "Other" DSN
5. DSN Settings
   • DS Name: SQLite
   • JDBC URL: jdbc:sqlite:C:/temp/sqlite/products.db
   • Driver Class: org.sqlite.JDBC