This post will walk you through establishing a database connection in Mathematica to SQL Azure using Microsoft’s SQL Server JDBC driver. SQL Azure is Microsoft’s cloud based database service. I’ve personally enjoyed using SQL Azure for working with data where latency isn’t a factor. A personal example of where it’s made my life easier is that I’ve used SQL Azure in the classroom to expedite the grading process, dropping the amount of time from days to hours between work done by the students and their reflected grade. I’ve received a lot of compliments from my students for being able to do so.
This walk through connects to the SQL Azure AdventureWorks database which you can download here.
Once the driver is unpacked, I navigated to sqljdbc_3.0 and removed the sqljdbc.jar file and left sqljdbc4.jar. When I was working with Wolfram support (who, by the way, are extremely knowledgable and professional), we had some issues with ClassPath and the two drivers. The way we resolved this is by removing that file.
Once you’re just left with sqljdbc4.jar, open up Mathematica and create a new notebook file. The notebook will require both Jlink and DatabaseLink so use the Needs command to import both of those.
Next, we’ll need to add the Microsoft JDBC driver to the class path so execute:
AddToClassPath["C:\\Program Files\\Microsoft SQL Server JDBC Driver\\sqljdbc_3.0\\enu\\sqljdbc4.jar"];
Now it’s time to connect to SQL Azure with Mathematica. The command and parameters you’ll use look like the following:
queryazure = conn=OpenSQLConnection[JDBC["com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://<SERVER>.database.windows.net;databaseName=AdventureWorksLTAZ2008R2;user=<USERNAME>@<SERVER>;password=<PASSWORD>;"],”Username”->”<USERNAME>@<SERVER>”,”Password”->”<PASSWORD”]
My connection appears as the following (I’ve blotted out the sensitive information):
Once the connection is made, you can confirm your connection by executing
Finally, let’s query for something specifically. Below is a screenshot looking at the SalesLT.Product table via the web interface:
One may can also connect with SQL Server Management Studio 2008 R2 as well:
The query we will want to execute is a simple one: “SELECT * FROM SalesLT.Product”. To do so, we just execute the following:
query =SQLExecute[queryazure, "SELECT *
The output is as follows:
That’s it! Enjoy playing with Mathematica and SQL Azure! There’s lots of blog posts on different things to do and they can be applied to SQL Azure just the same. Some of my favorites are: Graphing a Hierarchy from a Common Table Expression Using TreePlot, Graphing Foreign Keys in SQL Server Using Mathematica, and Foreign Keys as an Incidence Matrix: A High Level Look.
Finally, I want to credit Wolfram support for their assistance in getting me to connect. They worked with me in fixing my erronious JDBC configuration in Mathematica. Without them, this post wouldn’t be possible. Thank you Wolfram support!