Connect to SQLite database

Question

I am trying to connect to a SQLite database and since there is no officially supported driver; I decided to use the sqlite-jdbc driver and port the respective code into Mathematica. However, the DriverManager refuses to connect to the database, the error message is:

Java::excptn: A Java exception occurred: java.sql.SQLException: 
No suitable driver found for jdbc:sqlite:C:/sqlite/test.db
at java.sql.DriverManager.getConnection(DriverManager.java:602)
at java.sql.DriverManager.getConnection(DriverManager.java:207).

The driver & the code that I am trying to port is at: https://bitbucket.org/xerial/sqlite-jdbc and the respective lines that I am having issue is:

....
Class.forName("org.sqlite.JDBC");

Connection connection = null;
try
{
  // create a database connection
  connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
....

I noticed the code uses a Class.forName() and I used the JLinkClassLoader to load the same class. Here are my efforts so far:

<<JLink`
(*location of the driver jar*)
AddToClassPath["C:\\drivers\\sqlite"];

LoadJavaClass /@ 
 {"java.sql.Connection", "java.sql.DriverManager",  "java.sql.ResultSet", 
  "java.sql.Statement", "java.sql.SQLException","org.sqlite.JDBC"}

LoadJavaClass["com.wolfram.jlink.JLinkClassLoader"];
cls=Class`forName["org.sqlite.JDBC",True,JLinkClassLoader`getInstance[]];

(*Errors out with the message above*)
DriverManager`getConnection["jdbc:sqlite:C:/sqlite/test.db"]

I used DriverManager.getDrivers() and it shows me that only the sun.jdbc.odbc driver has been loaded; so the error message is justified.

I am at this point not sure how to proceed /debug the issue and will greatly appreciate any assistance.

If you have any suggestions, I will be very grateful.

Answer 1

As of Mathematica v10, SQLite seems officially supported (at least on OS X):

Needs["DatabaseLink`"]
Select[JDBCDriverNames[], StringMatchQ[#, ___ ~~ "SQLite" ~~ ___] &]

(* {"SQLite", "SQLite(Memory)"} -> not in v9, new in v10! *)

Testing with the Chinook example database:

conn = OpenSQLConnection[JDBC["SQLite", "/Users/username/Downloads/Chinook_Sqlite.sqlite"]];
SQLTables[conn]

(* {SQLTable["Album", "TableType" -> "TABLE"], 
     SQLTable["Artist", "TableType" -> "TABLE"], 
     SQLTable["Customer", "TableType" -> "TABLE"], 
     SQLTable["Employee", "TableType" -> "TABLE"], 
     SQLTable["Genre", "TableType" -> "TABLE"], 
     SQLTable["Invoice", "TableType" -> "TABLE"], 
     SQLTable["InvoiceLine", "TableType" -> "TABLE"], 
     SQLTable["MediaType", "TableType" -> "TABLE"], 
     SQLTable["Playlist", "TableType" -> "TABLE"], 
     SQLTable["PlaylistTrack", "TableType" -> "TABLE"], 
     SQLTable["Track", "TableType" -> "TABLE"]} *)

Answer 2

edit march 2017

in version 11.1 the Database` functions as described in the original answer following have been removed. As Jerome mentioned in his answer since version 10 there is a SQLite JDBC driver coming with DatabaseLink` which is of course the best (and officially supported) way to access SQLite databases in all versions newer than 9.

original answer

I'm not sure but don't think the problem is with your setup of the JDBC driver. There is reasonable documentation how you can make JDBC drivers available in the DatabaseLink documentation, which you can find here: JDBC Connections. It is basically as easy as putting the jar that you can download into the right directory (It might well be that what Stefan did would also have worked just as well). I did put mine into FileNameJoin[{$UserBaseDirectory, "Applications", "SQLite", "Java"}]. That was all that was necessary to make this succeed:

Needs["DatabaseLink`"]
db = OpenSQLConnection[JDBC[
  "org.sqlite.JDBC",(* driver classname *)
  "jdbc:sqlite:C:\\path\\to\\database.sqlite" (*connection string*)
]]

Just as Stefan, I could then also evaluate SQLTables[db] succesfully but any kind of select does then give the same error that he sees ("JDBC::error: ResultSet closed."). One could try to use some more low level approch and accessing the result set directly which can be done using DatabaseLink, but I wouldn't be very optimistic that you can get that working. There is certainly something going wrong here...

On the other hand, the use of the internal functions to access SQLite databases as cormullion has suggested in a comment for the same connection did work alright:

db = Database`OpenDatabase["C:\\path\\to\\database.sqlite"]

Database`QueryDatabase[db, "SELECT * FROM sqlite_master WHERE type=\"table\""]

Database`QueryDatabase[db, "SELECT * FROM webappsstore2"]

Database`CloseDatabase[db]

To me that looks like some incompatibility of the given JDBC driver for SQLite and what DatabaseLink tries to do with it. If you don't explicitly need to make access with a JDBC driver and DatabaseLink I'd suggest to just use the internal functionality, which I think is used for internal stuff and is there since version 7 so it might not go away anytime soon (but of course that could happen). There is even a recipe how you can compile another sqlite library and replace the one that ships with Mathematica here, in case that's of interest (I certainly wouldn't suggest to do that if not for very good reasons).

If you need this to work with a JDBC driver and DatabaseLink you might need to find another driver, either another vendor (there is a long list in the sqlite-wiki) or another version. Of course there are no guarantees that any of them will work, but if you have the time, you could try them (if you do, I'm sure there users of this site would appreciate if you come back and report). There are also listed some OBDC wrappers in that wiki, which is what you could try as a last resort, but using a bridge cascade like DatabaseLink -> JDBC -> ODBC -> SQLite doesn't sound like a straightforward and reliable solution and I'd not be surprise if that wouldn't work either.

Of course there are plenty of other possibilities to access the database nevertheless when bypassing DatabaseLink and e.g. access the JDBC Java object directly or use LibraryLink for direct access to the SQLite C-library -- but then one probably is better off to just use the internal functionality...

Edit: I played with this some more and found that even a SQLResultSetOpen will give the same error. I also tried the preview for the newest version of that driver with the same result. If one doesn't set Mode -> "ForwardOnly" for SQLResultSetOpen one will see another error message indicating that SQLite only supports forward only. So it seems that either that driver or SQLite in general only support a part of the full JDBC functionality and that DatabaseLink tries to use some of the nonexisting functionality. I'm not enough of an expert about DatabaseLink, JDBC and SQLite to know whether that is a restriction which could in principle be removed or not. Maybe it isn't by accident that there is internal functionality for the access to SQLite but no JDBC driver coming with the standard DatabaseLink package...

Answer 3

What I achieved so far is this:

1. Set the correct ClassPath

   Needs["JLink`"];
   
   ReinstallJava[ClassPath->"~/:sqlite-jdbc-3.7.2.jar"];
   

2. Load DatabaseLink

   Needs["DatabaseLink`"];
   

3. establish a connection with JDBC

   conn = DatabaseLink`OpenSQLConnection[
       DatabaseLink`JDBC["org.sqlite.JDBC",     
       "jdbc:sqlite:/somewhere/mydata.db"]]
   

4. proof the connection was established successfully

   SQLConnections[]
   
   (* -> SQLConnection[1,Open,TransactionIsolationLevel->Serializable] *)
   

5. List the tables (this database has a table memos. Created as described in the man-page for sqlite3)

   SQLTables[conn]
   
   (* -> {SQLTable["memos", "TableType" -> "TABLE"]} *)
   

6. Execute a Select

   SQLSelect[conn, "memos"]
   

But, at this point I immediately get a JDBC::error : ResultSet closed.

Maybe someone has more success at this specific step...