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...
Database
QueryDatabase`... I wonder why that's not available on Windows? $\endgroup$