How to map MS SQL Server onto the Entity Framework?

7

2

I created a reference using the following:

reference=DatabaseReference[<|
    "Backend"->"MicrosoftSQL",
    "Host"->FileNameTake[databasePath,1],
    "Port"->1433,
    "Name"->FileNameTake[databasePath,-1],
    "Username"->"xxxx",
    "Password"->"************"
|>]

Then I feed that reference to the RelationalDatabase but I get the following error:

schema=RelationalDatabase[reference]

"This platform currently does not support SQL Database functionalities."

Failure["DatabaseFailure", Association[ "MessageTemplate" :> MessageName[RelationalDatabase, "nvldplatform"],
"MessageParameters" -> Association[]]]

This is weird since I am able to connect to the database fine using DatabaseLink`:

OpenSQLConnection[JDBC["Microsoft SQL Server(jTDS)",databasePath],
"Username"->"xxxx",
"Password"->"************"]

Also can run all the SQL Queries via the DatabaseLink` but can't map the same database to the Entity Framework.

I suspect maybe I am not specifying the DatabaseReference association properly.

I am on windows 10 1903 with Mathematica 12.

user13892

Posted 2019-12-23T19:34:18.123

Reputation: 7 041

Try single URL string, should look like this: DatabaseReference["mssql://username:password@host:1433/dbname"], where you will need to use correct settings for username, password, dbname and host. I double-checked (on Mac for now) on a different database, that this worked for Mathematica 12.0. – Leonid Shifrin – 2019-12-24T19:45:24.097

Answers

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 Mathematica installation, I evaluated the first expression as indicated in the posting. I received a paclet-update message which brought down version 0.3.3 of the Databases paclet. I then evaluated the second expression and received the same error as reported.

I tracked down the error message to the paclet file Python.m, specifically in the definition of the symbol $PythonDistribution. It occurs when the support file distribution.exe cannot be located.

But here is the problem... if I inspect the in-memory definition of this symbol, it looks like this:

in-memory definition

Now compare this with the on-disk paclet source in Python.m:

on-disk definition

Note how the latter looks in the executables subdirectory for the EXE whereas the former does not. The file in question does indeed appear under the executables subdirectory so it is no wonder that the in-memory definition fails.

It is not clear how these definitions got out of synch.

Fix?

I exited Mathematica and relaunched it. When I evaluated the first expression, I again saw a paclet-update message. It apparently downloaded the Databases paclet v0.3.3 again as no other paclets were touched. But the second download appears to have fixed up the broken definition so that the RelationalDatabase expression now works as advertised.

The fact that the same paclet was downloaded twice is strange. Perhaps it is malformed in some way? Or perhaps it was just coincidence and the original paclet update simply did not update the in-memory definitions which would get updated on relaunch anyway.


Original Response

When I first ran into this problem, I had noticed that the issue was loading Python so I had installed it manually and the problem went away. I think that this fix was merely coincidence given the description of events above. But here is the original response anyway...

At time of writing (Mathematica v12, Databases paclet v0.3.3), the RelationalDatabase functionality requires a functioning Python installation. The error message indicates that no such installation is available.

Python comes preinstalled on MacOS, but on Windows it must be installed manually. To set up a suitable environment, follow the steps described in Configure Python for ExternalEvaluate. If you choose to use an installer from python.org as suggested in that article, beware that you must use Customize installation to install pip and to add Python to the system path.

Once you have Python external evaluation working, DatabaseReference should start working as well.

WReach

Posted 2019-12-23T19:34:18.123

Reputation: 62 787

The availability of Python should not matter, since we ship a custom bundled python interpreter with Databases, which is always available. It is actually a part for the paclet, for version 12 it is a single executable located in FileNameJoin[{$InstallationDirectory, "SystemFiles", "Links", "Databases", "Python"}]. That file has been bundled by PyInstaller tool and contains python part of Databases code base plus most of the db drivers plus all necessary dependencies plus python interpreter. So the fact that there is a problem is still puzzling. – Leonid Shifrin – 2019-12-25T14:46:37.947

@LeonidShifrin: I have updated my response to include detailed reproduction steps. Something weird is happening with the Databases paclet update. I hope the information is useful. – WReach – 2019-12-25T17:53:50.857

@WReach & @Leonid I don't know what happened but I reran the same DatabaseReference code today and now it is working fine. One of my computer had python distribution registered and the other one doesn't have any python at all. Both of the machines got the same failure but now without any changes to the code or the machines or the server, both are able to connect without any failure. Very mysterious! (I can't remember whether an automatic paclet update happened or not) – user13892 – 2019-12-25T21:44:07.417

@WReach Well, thanks. I must be ashamed. That change in paths to distribution you noticed was introduced by me, for reasons that are perhaps not too interesting to discuss, but have to do with a problem of platform-dependent paclet updates to initially platform-independent original paclet (we ended up producing a "fat" all-platform paclet 0.3.3 just for this update, there will be no such issues for future updates to 12.1+). I couldn't reproduce the problem but I could recall that that could be the possible reason. – Leonid Shifrin – 2019-12-26T16:43:00.767

Identical problem and fix on Mathematica 12 on MacOs too – Chris F Carroll – 2020-10-31T12:49:01.107