SQLite and Mathematica

Question

I want to connect to a local database with Mathematica. I used DB Browser for SQLite to create the database with a single table and then exported it locally as a .sql file. To access the database I tried the following lines:

Needs["DatabaseLink`"];
OpenSQLConnection[JDBC["SQLite"]]
db = Database`OpenDatabase["C:\\db\\testDB.sql"];
results = Database`QueryDatabase[db, "SELECT * FROM `Table1`"]

The following error is output:

 Database`QueryDatabase::stmt: The statement "SELECT * FROM `Table1`" is invalid: "file is encrypted or is not a database".

The testDB.sql file looks like this:

BEGIN TRANSACTION;
CREATE TABLE "Table1" (
    `field1`    TEXT,
    `field2`    TEXT,
);
INSERT INTO `Table1` VALUES ('point1','point2');
INSERT INTO `Table1` VALUES ('dot1','dot2');
COMMIT;

Any help will be greatly appreciated. Thx.

Answer 1

DatabaseLink` and the Database` functions are actually two completely independent ways to access databases. The Database` functions are a direct and undocumented way to access SQLite databases only. It was available for version 7 to 11.0 but does not exist anymore in version 11.1. It had the advantage of being very fast to open a connection (compared to DatabaseLink`) but is undocumented and had some issues with non-ascii strings. There are various differences between the Database` and the DatabaseLink` functions in how the results are returned (and errors handled). As DatabaseLink` did not bring an SQLite driver until one of the 10.x versions, for versions 7 to 9 the Database` functions were an easy way to access SQLite databases out of the box. You could install a JDBC driver for SQLite in older versions to access a SQLite database via DatabaseLink` as well.

If using the undocumented Database` functions with one of the mathematica versions that have them, there is no reason to Needs["DatabaseLink`"]. The following should work without any Needs in a fresh kernel with versions 7 to 11.0:

 db = Database`OpenDatabase["C:/db/testDB.sql"];
 Database`QueryDatabase[db, "SELECT * FROM `Table1`;"]

Since at least version 10.3 there is a SQLite driver coming with DatabaseLink` so that seems to be the recommended way to access a SQLite database for newer versions. Especially if using version 11.1 (and presumably newer) where the Database` functions have been removed you would instead need to use:

Needs["DatabaseLink`"]
db = OpenSQLConnection[JDBC["SQLite","C:/db/testDB.sql"]]
SQLExecute[db,"SELECT * FROM `Table1`;"]

Answer 2

Thanks Szabolcs. Based on the content of the 2nd link that you posted, I realized that the code is not expected to work for Mathematica v.9 (command JDBC is not known). In any case, I was able to get the content of the database using the following lines:

Needs["DatabaseLink`"];
db = Database`OpenDatabase["C:/db/testDB.sql"];
Database`QueryDatabase[db, "SELECT * FROM `Table1`;"]