8
$\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$
10
  • 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$ Feb 6, 2015 at 23:09
  • 1
    $\begingroup$ @SjoerdC.deVries Perhaps he is referring to this and this? $\endgroup$
    – Szabolcs
    Feb 6, 2015 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$ Feb 6, 2015 at 23:14
  • $\begingroup$ 2 cents: rarely use anything other than SQLExecute when working with SQL databases. $\endgroup$ Feb 6, 2015 at 23:17
  • $\begingroup$ @Szabolcs ok, so it seems v10 should be able to do it. Must try it once. $\endgroup$ Feb 6, 2015 at 23:18

2 Answers 2

6
$\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$ Database` functions are not present in 12.0. I cannot find where they moved to. $\endgroup$
    – Szabolcs
    Jan 30, 2020 at 14:20
  • 1
    $\begingroup$ Never mind, there is some published stuff: reference.wolfram.com/language/ref/DatabaseConnect.html $\endgroup$
    – Szabolcs
    Jan 30, 2020 at 14:21
  • $\begingroup$ As mentioned in the post the Database` have been removed in 11.1. The new functionality around RelationalDatabase and Entities as a third, newer and AFAIK independent way to access databases... $\endgroup$ Jan 31, 2020 at 15:26
5
$\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$
1
  • $\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$ Mar 9, 2015 at 15:37

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.