SQLite and Mathematica

7

1

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.

JMarc

Posted 2015-02-06T21:42:56.077

Reputation: 429

1

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.

– Sjoerd C. de Vries – 2015-02-06T23:09:07.373

1

@SjoerdC.deVries Perhaps he is referring to this and this?

– Szabolcs – 2015-02-06T23:13:37.267

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? – Sjoerd C. de Vries – 2015-02-06T23:14:40.850

2 cents: rarely use anything other than SQLExecute when working with SQL databases. – Mike Honeychurch – 2015-02-06T23:17:49.533

@Szabolcs ok, so it seems v10 should be able to do it. Must try it once. – Sjoerd C. de Vries – 2015-02-06T23:18:20.353

@MikeHoneychurch that's what I use mainly. I often construct SQL strings using mathematica code and feed the result to SQLexecute. – Sjoerd C. de Vries – 2015-02-06T23:20:37.667

@SjoerdC.deVries I just prefer it because you can use the same SQL requests as you would in a "normal" SQL interface. For me it makes everything more portable across applications. – Mike Honeychurch – 2015-02-06T23:32:01.900

@SjoerdC.deVries it was possible to work with SQLite databases since at least Mathematica 8. Also, related: 61116

– shrx – 2015-03-09T08:06:32.733

@shrx Yeah, I already read the stuff at that link and so discovered that v10 has SQLite as part of the delivery. Have been trying it a bit, though I'm mainly working with MS SQL Server. Not aware of SQlite being present since 8, and as far as I recall the sql information functions didn't report that eiter. – Sjoerd C. de Vries – 2015-03-09T10:23:06.310

From your question I guess you are somewhat disturbed from the fact that there are two completely independent ways to access SQLite from Mathematica, so maybe this answer is also of interest. One thing that I'd use with care in Mathematica strings is the backquotes: these do have special meanings in e.g. StringForm and similar functionality could potentially interfere with their use in SQL code, so I'd probably avoid them if not needed, I typically get along with single quotes in SQL...

– Albert Retey – 2015-03-09T15:46:53.277

Answers

6

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`;"]

Albert Retey

Posted 2015-02-06T21:42:56.077

Reputation: 22 455

Database` functions are not present in 12.0. I cannot find where they moved to. – Szabolcs – 2020-01-30T14:20:07.723

Never mind, there is some published stuff: http://reference.wolfram.com/language/ref/DatabaseConnect.html

– Szabolcs – 2020-01-30T14:21:58.463

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... – Albert Retey – 2020-01-31T15:26:01.667

4

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`;"]

JMarc

Posted 2015-02-06T21:42:56.077

Reputation: 429

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. – Albert Retey – 2015-03-09T15:37:14.347