5
$\begingroup$

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.

$\endgroup$
  • 1
    $\begingroup$ Your mathematica database functions (Database`*) deviate considerably from what I am used to. The result of OpenSQLConnection needs to be assigned to a variable which will be used in future access to the database. I would do the 4th line with an SQLExecute. I believe the backticks shouldn't be in that line. $\endgroup$ – Sjoerd C. de Vries Feb 6 '15 at 23:09
  • 1
    $\begingroup$ @SjoerdC.deVries Perhaps he is referring to this and this? $\endgroup$ – Szabolcs Feb 6 '15 at 23:13
  • $\begingroup$ SQLite is also fundamentally different from other SQL engines, as it doesn't work in a client server configuration. Are you sure it can actually work with mathematica? $\endgroup$ – Sjoerd C. de Vries Feb 6 '15 at 23:14
  • $\begingroup$ 2 cents: rarely use anything other than SQLExecute when working with SQL databases. $\endgroup$ – Mike Honeychurch Feb 6 '15 at 23:17
  • $\begingroup$ @Szabolcs ok, so it seems v10 should be able to do it. Must try it once. $\endgroup$ – Sjoerd C. de Vries Feb 6 '15 at 23:18
5
$\begingroup$

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`;"]
$\endgroup$
3
$\begingroup$

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`;"]
$\endgroup$
  • $\begingroup$ I think you won't need Needs["DatabaseLink`"] when you use the Database`* functions. These undocumented functions are completely unrelated to the DatabaseLink functionality AFAIK. $\endgroup$ – Albert Retey Mar 9 '15 at 15:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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