13
$\begingroup$

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.

$\endgroup$
6
  • $\begingroup$ That's odd, on Mac OS X, Mathematica version 9, there's a sqlite3 library (in Mathematica.app/SystemFiles/Kernel/Binaries/MacOSX-x86-64/libsqlite3.dylib), and I can open and fetch information from it using DatabaseQueryDatabase`... I wonder why that's not available on Windows? $\endgroup$
    – cormullion
    Jun 1, 2013 at 7:31
  • $\begingroup$ @cormullion: what you describe is an undocumented (?) feature (direct access to sqlite databases) which also exists on Windows and is described e.g. here. It might well serve the purposes of the OP but isn't related to accessing them via ``DatabaseLink```, which is what the OP tried. I think that should also be possible with a decent setup... $\endgroup$ Jun 1, 2013 at 9:44
  • $\begingroup$ Did you test that the Java code you're trying to port works on your system? $\endgroup$
    – celtschk
    Jun 1, 2013 at 9:59
  • $\begingroup$ I'm not quite sure but my suspection is that what you do is correct for making it available to Java, but that might not be enough to make it available to DatabaseLink. There is a description on how to make available third party JDBC drivers in the documentation of DatabaseLink. I had to do that once and remember that it was somewhat simpler than what you do, it certainly wasn't necessary to fiddle with all that low level Java stuff. It's not entirely clear from your question, but have you tried what is described in the documentation? $\endgroup$ Jun 1, 2013 at 10:16
  • $\begingroup$ @AlbertRetey yes, true, but I wasn't sure if the OP had rejected that option, so thought it worth mentioning. There's sometimes an overlap between "officially supported" "undocumented", and "works just fine" ...:) $\endgroup$
    – cormullion
    Jun 1, 2013 at 10:45

3 Answers 3

12
$\begingroup$

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"]} *)
$\endgroup$
1
  • $\begingroup$ +1: very good finding, I just checked that it is also available on Windows. $\endgroup$ Jan 23, 2015 at 17:12
5
$\begingroup$

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

$\endgroup$
3
  • $\begingroup$ Useful answer - I wonder whether the OP will end up going the built-in SQLite route... $\endgroup$
    – cormullion
    Jun 2, 2013 at 8:45
  • $\begingroup$ @cormullion: thanks, it is by large parts yours as well :-) $\endgroup$ Jun 2, 2013 at 9:18
  • $\begingroup$ I'll also note (for those that might run into the same problem) that the connections need absolute paths, not relative paths. For instance, on OS X/Linux, a path like ~/foo/bar/qux.db won't work, while /Users/abc/foo/bar/qux.db will. $\endgroup$
    – rm -rf
    Apr 8, 2014 at 16:05
1
$\begingroup$

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

$\endgroup$
2
  • $\begingroup$ I've the same error ! Did someone find the problem ? $\endgroup$
    – msalese
    May 12, 2014 at 12:13
  • $\begingroup$ I am distressed by how 'close Mathematica and reopen it' sometimes turns out to be a solution to errors. $\endgroup$ Nov 14, 2020 at 0:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.