Connecting Mathematica to SQL Server: Part I – Using DatabaseExplorer

December 11, 2010

Filed under Mathematica, SQL Server
Tags: Mathematica, Microsoft SQL Server, SQL, Wolfram Research

2 responses

Note: check out Part II on how to connect Mathematica to SQL Server as well!

Mathematica is a powerful computing tool that can be used for calculation, visualization, and simulation. Mathematica isn’t free but if you’re a student or using the program at home on a non-professional basis, you may get it for a very nice price. Since I’m not using Mathematica in a professional capacity, I purchased the $295 Home Edition.

If you’ve never used Mathematica before, take a moment and watch this video.

To get started, we will want to import a package into our notebook file; specifically, we’re going to import the DatabaseLink package. To do this, open a notebook file if you don’t have one open already and merely type in Needs["DatabaseLink`"] and hit SHIFT + ENTER. Congratulations, you’ve loaded your first package (if you haven’t done it before). Next up, we’ll need to configure a database connection. The easiest way to do this is to do it via a graphical user interface (GUI). That is, instead of typing out a command in the notebook file, we’re going to invoke the connection using a module in Mathematica. We’ll begin by entering the command: DatabaseExplorer[] and typing SHIFT + ENTER.

If you’ve typed it in correctly, you’ll get a screen that looks like the below picture.

Let’s go ahead and configure a connection to SQL Server now. Click on File/Open Connection and then click on the “New” button. What we will get is a datasource wizard to step through. Just hit next to get started.

Give it a friendly name and description. I’m going to call mine AdventureWorks so I know what I’m connected to.

Once you hit next and are on to the page, it’s going to ask for you whether or not this configuration will be user or system level. I just select, “User Level” and hit next.

Now it’s time to select the driver. In this case, you want Microsoft SQL Server(jTDS) and hit the, “Next” button. As an aside, you’ll notice that this version of jTDS driver officially works with versions of SQL Server prior to 2008. This means that we will only be able to connect to the AdventureWorks database, not the AdventureWorks2008 database. I am working on getting Mathematica to play nice with Microsoft’s JDBC 3.0 driver. It’s a learning process. I know little of how JDBC works. If only there was some vast network of information available at my fingertips…

For our purposes, we will connect to the AdventureWorks database and not AdventureWorks2008R2 which uses FILESTREAM so we should be fine. Specify your hostname, username, password, and database in the next screen that follows. My information is entered below:

Hit the test button if you wish or hit next. I change the isolation level to, “ReadCommitted” but it doesn’t matter much. If you want to know more about isolation levels in SQL Server, you can read this link. Populate the catalog with the name of the database and test it and make sure you can connect.

At this point, you’re probably ready to see some data from SQL Server populating your Mathematica notebook. Let’s fire up a quick prebuilt query from Mathematica’s Database Link and then we’ll call it quits for now. Once you’ve completed the set up wizard to connect to SQL Server within Database Explorer, you will be able to use it. Within the Database Explorer window, click on “File” and then “Open Connection”. You should see your connection listed in the window that pops up.

Enter the username and password you set up for the connection and click “Connect”. If you successfully connect, you should see a window as follows:

Select a table in the left column and the columns that you wish to receive output from on the right, then click on “File” and “Save to Notebook”. We’re going to send the query to a Mathematica notebook. This is a great way to see what Mathematica’s syntax looks like for querying a database. For example, I selected the following below:

When I clicked on, “Save to Notebook”, I had the following output:

If you hit, SHIFT + ENTER and try to evaluate this notebook, you’ll note that you receive an error like the following:

Do not be alarmed. We’re just missing the schema name from the Table name. We add the prefix and we’ll be able to get a result set. So let’s modify the SQLExecute statement to reflect what we need. We double check in SQL Server Management Studio and we see that the schema this table belongs to is “HumanResources”.

So we modify the statement so that the table reads HumanResources.Employee:

Finally! Let’s run this statement and see if we get some love:

VOILA! STUFF! We’ve successfully built a connection to SQL Server in Mathematica and performed a query that returned results. This is a great place to quit. I have some homework of my own to do. I need to figure out how to get the JDBC drivers Microsoft built for SQL Server 2008 R2 loaded into Mathematica so we can use to the AdventureWorks2008R2 database. Coming up, more sophisticated queries.

Comments Feed