How to map MS SQL Server onto the Entity Framework?

Question

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.

Answer 1

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:

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

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.