18 votes
Accepted

What are the benefits of Entity Classes for Databases?

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 ...
user avatar
16 votes
Accepted

DatabaseLink and Libreoffice odb

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 ...
user avatar
  • 11.7k
14 votes

How to speed up interpreting numbers from strings?

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 ...
user avatar
  • 227k
14 votes
Accepted

How to extract Bitmap image from SQL records?

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 ...
user avatar
  • 67.3k
12 votes

Connect to SQLite database

As of Mathematica v10, SQLite seems officially supported (at least on OS X): ...
user avatar
  • 517
11 votes
Accepted

Slow work of SQLInsert with MS SQL Server in V10

After some useless hours in Google trying to understand jTDS, I discovered that DatabaseLink implementation is a open Package, and that ...
user avatar
  • 25.6k
10 votes
Accepted

How to do a database query on substrings?

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), ...
user avatar
8 votes

JDBC-to-ODBC bridge gone. How to connect to SQL Server now in case of Windows Authentication?

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'...
user avatar
7 votes
Accepted

How to update bundled SQLite library (or driver?) in V10 for OS X

Mathematica 10 ships with version 3.7.2 of the Xerial SQLite JDBC driver. Let me first verify your findings. ...
user avatar
  • 208
7 votes

Slow work of SQLInsert with SQLite

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/...
user avatar
  • 1,179
7 votes
Accepted

Insert large amount of data in HSQL

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 ...
user avatar
  • 68.9k
7 votes
Accepted

Why has this SQLInsert behaviour changed?

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 ...
user avatar
7 votes
Accepted

Maintaining SQLConnections

Moving my comments into an answer: If you create forceConnection[conn_]:=If[.. then you don't have to worry about replacing conn...
user avatar
  • 133k
6 votes
Accepted

SQLite and Mathematica

DatabaseLink` and the Database` functions are actually two completely independent ways to access databases. The ...
user avatar
  • 23.1k
6 votes
Accepted

Retrieve column data from MongoCollection

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 ...
user avatar
6 votes
Accepted

Local Storage of Periodically Collected Data (without using DataDrop)

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 ...
user avatar
  • 68.9k
6 votes
Accepted

How to map MS SQL Server onto the Entity Framework?

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, *...
user avatar
  • 67.3k
5 votes

SQLite and Mathematica

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 ...
user avatar
  • 449
5 votes

JDBC-to-ODBC bridge gone. How to connect to SQL Server now in case of Windows Authentication?

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 ...
user avatar
  • 619
5 votes
Accepted

How to speed up interpreting numbers from strings?

Import and ImportString handle the e number format okay. You might be able to Import ...
user avatar
  • 83.6k
5 votes
Accepted

Context of SQLBinary in Package

In short, the reason for the behaviour you observe is this: Some DatabaseLink functions will only work if "DatabaseLink`" is in ...
user avatar
  • 227k
5 votes
Accepted

How to access an English-Chinese dictionary database file correctly?

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 ...
user avatar
  • 388
5 votes
Accepted

Can't connect to MS SQL using Active Directory Integrated Services via DatabaseLink

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\...
user avatar
  • 9,148
4 votes
Accepted

How can I "ORDER BY" using SQLSelect?

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: ...
user avatar
  • 999
4 votes

SQLInsert'ing Null into a table (not SQLite)

Using the built in SQLite you may try the following ...
user avatar
  • 946
4 votes

Slow work of SQLInsert with SQLite

Using SQLBeginTransaction works fast with the SQLite version 3.7.2 distributed with Mathematica 10 : ...
user avatar
  • 16.8k
3 votes
Accepted

Is there a way to define primary keys using SQLCreateTable?

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"...
user avatar
  • 946
3 votes

How to check if a database connection is open?

I just checked the documentation in V10 and accidentally stumbled upon a built in command: SQLConnectionOpenQ[conn] This seems to do the trick.
user avatar
  • 701
3 votes
Accepted

Connection to SQL server - Failed

The error message indicates that the server host name is in error. This is because the instance name has been tacked onto the end of server host name (wwe**pe.**.***.ds\MSSQL***). While this is ...
user avatar
  • 67.3k
3 votes

I have a bigdata CSV file as a single table of five columns and almost 10 million rows

So more and more I think what you're trying to do is simply infeasible via Mathematica. The bundled Microsoft Access driver (UCanAccess) won't touch Excel and the old Microsoft Excel driver has ...
user avatar
  • 45.6k

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