18

Preamble As was already hinted in the comments, the new functionality is a much higher level. It is basically a compiler from entity query language to SQL, specialized to a given SQL dialect of the underlying database engine. You can view the new functionality more or less as ORM for WL. It brings similar benefits as e.g. Django ORM brings to python web ...


16

A few tricks are required, but it is possible to create a database in LibreOffice and read the data via DatabaseLink in MMA. Here is a summary of worked for me with MMA 11.0, LibreOffice 5.0 and GNU/Linux. Database Creation The trick in creating the database is to use all capital letters for table names and column names. When I used lower case names, MMA ...


14

I would like to link this W Community thread here where I asked for functionality like this in 2015 September, and explained why it's critical to have it. I can't link to individual posts but you can find it by searching the page for "StringToDouble". As Leonid mentioned, there is Internal`StringToDouble. This function is very fast, but it does not report ...


14

Summary The Northwind database was originally an MS Access database, so the pictures are BMP images wrapped within OLE objects. We must strip the OLE header before parsing them. The following function will perform that operation upon a SQLBinary column: fromOleBmp[SQLBinary[data_]] := data[[79;;]] // ByteArray // ImportByteArray[#, "BMP"]& These ...


12

As of Mathematica v10, SQLite seems officially supported (at least on OS X): Needs["DatabaseLink`"] Select[JDBCDriverNames[], StringMatchQ[#, ___ ~~ "SQLite" ~~ ___] &] (* {"SQLite", "SQLite(Memory)"} -> not in v9, new in v10! *) Testing with the Chinook example database: conn = OpenSQLConnection[JDBC["SQLite", "/Users/username/Downloads/...


11

After some useless hours in Google trying to understand jTDS, I discovered that DatabaseLink implementation is a open Package, and that SQLInsert implementation is in the file DatabaseLink/Kernel/SQL.m. Inside It you can check that SQLInsert implementation is just a special application of SQLExecute. And that SQLExecute has two new parameters in V10 called ...


10

I am one of the developers for the new relational database connectivity features. Very soon, a rather extensive tutorial will be added to the current documentation (via documentation paclet update), which would contain, in particular, examples of the kind you are interested in. What you need is: EntityList @ FilteredEntityClass[ "table", ...


8

I am using Windows 7 Professional, SQL 2008 and Mathematica v9. I had endless problems connecting and getting things to work, but after a lot of back and forward between Wolfram support and myself I got the following to work: Needs["DatabaseLink`"] Connection1=OpenSQLConnection[JDBC["net.sourceforge.jtds.jdbc.Driver", "jdbc:jtds:sqlserver://...


8

Following Rolf Mertig's tip I examined the merits of Microsoft's latest JDBC driver (not included in Mathematica 11). It worked, but it was quite a trip. The itenary can be found below. Note that you'll need to have evaluated Needs["DatabaseLink`"] first for a few functions below to work. First, I downloaded Microsofts newest JDCB driver (not the jTDS one) ...


7

Firstly, a bit about SQLite insert performances: https://stackoverflow.com/questions/1711631/how-do-i-improve-insert-per-second-performance-of-sqlite https://stackoverflow.com/questions/3852068/sqlite-insert-very-slow Both of these state the fact that SQLite wraps every insert statement with a transaction and that the run times can be up to 270x faster ...


7

Two possible solutions are Use MySQL, SQLite or any other database that is not an in-memory database. HSQL keeps all its data in the memory at all times. I'm leaving this for future visitors since there is a version of HSQL that is built in that is in-memory, although that was not the case for the OP. Increase the Java heap size by using the same technique ...


7

It looks like a bug to me. Any input larger than 2^31 -1 is truncated to 32 bits, whereas a BIGINT should be able to hold 64 bit numbers. What you see is the interpretation of that remainder in two's complement. I've checked this for all three available drivers in V11.1 (Connector/J, Drizzle, and MariaDB), so a driver issue seems unlikely. Furthermore, if ...


7

Moving my comments into an answer: If you create forceConnection[conn_]:=If[.. then you don't have to worry about replacing conn inside in future. You need to add HoldFirst attribute to forceConnection if you want to modify its value. Additionally, I don't like nested Ifs: forceConnection // Attributes = {HoldFirst}; forceConnection[conn_] := conn = ...


6

Mathematica 10 ships with version 3.7.2 of the Xerial SQLite JDBC driver. Let me first verify your findings. SQLiteVersion[] := Module[{v, c = OpenSQLConnection[JDBC["SQLite(Memory)", ""]]}, v = First@First@SQLExecute[c, "SELECT sqlite_version();"]; CloseSQLConnection[c]; v ]; SQLiteVersion[] 3.6.23.1 There are a few SQLite databases in that ...


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 ...


6

I've written this solution as an aggregation because aggregations are likely to be what you want to do more often, so will show you how to set it up. disclaimer: I don't have your data so I am doing this without testing. tradesDB = MongoGetCollection[db, "trades"] create an aggregation in which you project the two fields that you are wanting aggregate = ...


6

This is not so much a solution as a description of reproduction steps... but the short version of my story was to exit Mathematica, relaunch and try again. analysis current as of Mathematica v12, *Databases* paclet v0.3.3 There seems to problem in the way that Databases paclet locates a supporting file, distribution.exe on Windows. Starting from a clean ...


5

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, "...


5

Import and ImportString handle the e number format okay. You might be able to Import directly from file, or use ImportString to process the data you've already read in: res = fromReadList ~StringRiffle~ "\n" ~ImportString~ "TSV" ~Cases~ {__,"Cake"|"Cookies",_};


5

Yes you are right, JDBC-to-ODBC support is gone as Mathematica uses java8. However 11.0.1 mathematica comes with a support for using open source JDBC driver which can be used to connect Microsoft Access DB without relying on JDBC-to-ODBC bridge. e.g. In[32]:= << DatabaseLink` In[34]:= $Version Out[34]= "11.0.1 for Mac OS X x86 (64-bit) (September 21,...


5

In short, the reason for the behaviour you observe is this: Some DatabaseLink functions will only work if "DatabaseLink`" is in $ContextPath at the time of their evaluation. It does not matter how DatabaseLink was loaded. All that matters is whether its context is in $ContextPath at the time when SQLInsert is called. The detailed reason is that part of ...


5

I will show an example of how easy it is to set up a database, sometimes things seem complicated or overkill because one hasn't seen a good example of how simple it can be. Start by connecting to a database: Needs["DatabaseLink`"]; JDBCDrivers["SQLite"] conn = OpenSQLConnection[ JDBC["SQLite", "/path/to/database.db"] ]; If database.db does not ...


5

This SQL file is not a sqlite3 database file. I don't know whether Wolfram Mathematica includes a complete MySQL program. I installed the latest version of MariaDB/MySQL on my computer. 1. Install MariaDB/MySQL To access this database file, you may need to install MySQL/MariaDB. Then you need to start the MySQL process and import the SQL file to MySQL. In ...


5

I downloaded the MS SQL driver from Microsoft https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15 and installed it under C:\Users\myuser\AppData\Roaming\Mathematica\Applications\Oracle\Java also placed the following dll driver in mssql-jdbc_auth-9.2.1.x64.dll in the same directory. sqlMS = ...


4

Using SQLBeginTransaction works fast with the SQLite version 3.7.2 distributed with Mathematica 10 : Needs["DatabaseLink`"]; testdb = FileNameJoin[{$TemporaryDirectory, "test1.db"}]; If[ FileExistsQ[testdb], DeleteFile[testdb] ]; db = JDBC["SQLite", testdb]; conn = OpenSQLConnection[db]; SQLCreateTable[conn, SQLTable["TEST"], {SQLColumn["COL1", "...


4

You can use "SortingColumns" in SQLSelect or simply use the SQL statement inside SQLExecute. In the second case you simply write the SQL statement in the string: SQLExecute[dbConn, "SELECT id, measurement, timestamp FROM myTable WHERE id IN (1, 2) ORDER BY timestamp"] And you are returned triplets with {id, measurement, timestamp}.


4

Using the built in SQLite you may try the following Needs["DatabaseLink`"] conn = OpenSQLConnection[JDBC["SQLite(Memory)", ""]]; SQLCreateTable[conn, SQLTable["TEST"], {SQLColumn["i", "DataTypeName" -> "INTEGER", "Nullable" -> True]}]; SQLInsert[conn, "TEST", {"i"}, {Null}] SQLSelect[conn, "TEST"] SQLDropTable[conn, "TEST"]; ...


3

Straight from the Help documents "DatabaseLink/ref/SQLCreateTable" SQLCreateTable is a SQLTable with list of SQLColumn(s). Now again from the Help document "DatabaseLink/ref/SQLColumn" the "PrimaryKey" is an attribute (option) of SQLColumn. There is a primary key example in the help docs using JDBC SQLite. Whether it works or not is another issue. Needs["...


3

If you examine Information[SQLSelect] you'll see the various input patterns allowed for SQLSelect. It's quite long but the relevant parts are: DatabaseLink`SQL`Private`columns:_SQLColumn|{__SQLColumn} DatabaseLink`SQL`Private`columns:_String|{__String} DatabaseLink`SQL`Private`columns:{{_String,_String}..} Indeed, it allows either strings, lists of ...


3

I just checked the documentation in V10 and accidentally stumbled upon a built in command: SQLConnectionOpenQ[conn] This seems to do the trick.


Only top voted, non community-wiki answers of a minimum length are eligible