11
$\begingroup$

Connecting to our corporate SQL servers using JDBC never worked for me, perhaps related to the Windows Authentication method that is necessary in our network. However, I found out that configuring the connection using the ODBC tool on Windows and using the JDBC-to-ODBC bridge together with a configuration in DatabaseExplorer worked for me.

I'm currently moving my stuff to a newer 64 bit VDI (old one was 32 bit) and changing Mathematica version from 10.3 to 11.01 and found that the ODBC route doesn't work for me anymore. I get an error: JDBC::classnotfound sun.jdbc.odbc.jdbcOdbcDriver.

Some googling learns me that in Java 8 the whole JDBC-to-ODBC bridge was removed and it looks like Mathematica 11 runs on Java 8, so that might explain it.

Anyone got a solution to this problem? JDBC on its own (that is, without the bridge to ODBC) still does not work for me either.

$\endgroup$
  • 1
    $\begingroup$ Which (MS?) SQL server version are you using? The jtds-1.3.1.jar file distributed with DataBaseLink seems dated. Why don't you try to use the official MS one: microsoft.com/en-us/download/… $\endgroup$ – Rolf Mertig Oct 19 '16 at 22:08
  • $\begingroup$ @RolfMertig Thanks, I'll try that tomorrow. $\endgroup$ – Sjoerd C. de Vries Oct 19 '16 at 23:06
  • $\begingroup$ @SjoerdC.deVries not sure if my below answer works in your case as you have asked to connect specifically to SQLServer using ODBC-JDBC bridge. As mentioned in M documentation jTDS driver is strongly recommended to use over ODBC. $\endgroup$ – dsingh Oct 19 '16 at 23:34
  • $\begingroup$ @SjoerdC.deVries dsingh's answer was the workaround (straight from Wolfram) as of v11. I wasn't aware that this was included in 11.0.1. I've pushed in the past for WRI to develop a native database connection framework. Nearly all my work I do involves pushing/pulling info to DBs, and the java framework is a mess IMO. $\endgroup$ – kale Oct 20 '16 at 0:36
  • 2
    $\begingroup$ @RolfMertig I followed your tip and got it to work. Thanks! Took me about 6 hours, though. I'll write an answer tonight. $\endgroup$ – Sjoerd C. de Vries Oct 20 '16 at 13:45
8
$\begingroup$

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'll need to have evaluated Needs["DatabaseLink`"] first for a few functions below to work.

First, I downloaded Microsofts newest JDCB driver (not the jTDS one) from this download page.

Download the Microsoft JDBC Driver 6.0, 4.2, 4.1, or 4.0 for SQL Server, a Type 4 JDBC driver that provides database connectivity through the standard JDBC application program interfaces (APIs) available in Java Platform, Enterprise Editions.

We need enu\sqljdbc_6.0.7728.100_enu.exe from the download selections.

This zipped package contains the following drivers:

  • Sqljdbc.jar requires a JRE of 5 and supports the JDBC 3.0 API

  • Sqljdbc4.jar requires a JRE of 6 and supports the JDBC 4.0 API

  • Sqljdbc41.jar requires a JRE of 7 and supports the JDBC 4.1 API

  • Sqljdbc42.jar requires a JRE of 8 and supports the JDBC 4.2 API

Since Mathematica 11 uses Java 8 we need Sqljdbc42.jar.

Double clicking the installer unpacks the files to a folder. The location is unimportant as we will just be copying two files from it.

Locate sqljdbc42.jar in subfolder sqljdbc_6.0\enu. Also pick up sqljdbc_auth.dll from sqljdbc_6.0\enu\auth\x64\sqljdbc_auth.dll for 64 bits PCs (which I tried) or sqljdbc_6.0\enu\auth\x86 for 32 bits PCs (which I haven't tried).

The first file should be copied to one of the following three folders:

  • $InstallationDirectory/SystemFiles/Links/DatabaseLink/Java
  • $UserBaseDirectory/Applications/Oracle/Java
  • $BaseDirectory/Applications/Oracle/Java

where the $names are Mathematica variables indicating a base directory. Replace them with the result of evaluating that variable. I used the second one.

sqljdbc_auth.dll can be copied to the same directory or to C:\Windows\System32. Both locations seem to work.

Next a driver resource file is necessary. This file provides some details on the driver, among which the class name (I needed quite some googling before I found the correct one) and the exact calling protocol (this differs from the jTDS one).

JDBCDriver[
 "Name" -> "Microsoft SQL Server (MS6 driver)", 
 "Driver" -> "com.microsoft.sqlserver.jdbc.SQLServerDriver", 
 "Protocol" -> "jdbc:sqlserver://", 
 "Description" -> "Microsoft SQL Server using Microsoft's jdbc 6.0 driver", 
 "Location" -> "C:\\Users\\vriessc\\AppData\\Roaming\\Mathematica\\DatabaseResources\\sqljdbc6.m"
]

This file should be located in one of the folders you get upon executing:

DatabaseResourcesPath[]

{
  {"C:\\Program Files\\Wolfram Research\\Mathematica\\11.0\\SystemFiles\\Links\\DatabaseLink\\DatabaseResources", "DatabaseLink"}, 
  {"C:\\Users\\vriessc\\AppData\\Roaming\\Mathematica\\DatabaseResources", None}, 
  {"C:\\ProgramData\\Mathematica\\DatabaseResources", None}
}

The first result contains similar (standard) resource files, but it is chained to the version 11 directory which I don't like. I choose the second one, which holds my own predefined connection defintions.

The resource file has extension ".m" but it should not be edited with Mathematica because it adds a header to the file which is invisible in Mathematica itself and causes the resource to be skipped by DatabaseLink. Use a plain tekst editor for this. Note that the file contains a link to its own location, so make sure to make that it matches its actual location.

With this file in place, and with the kernel restarted, a connection can now be made using:

conn = 
  OpenSQLConnection[
    JDBC["Microsoft SQL Server (MS6 driver)", "ServerName"],
   "Properties" -> {"integratedSecurity" -> "true"},
   "Catalog" -> "DatabaseName"
  ]

The "integratedSecurity" -> "true" part is very important. It makes Windows Authentication work for me (note the lack of credentials in the call?). This didn't work with the old jTDS drivers, so this is really why the new JDCB drivers are necessary.

A last, optional step, would be to make a resource file that makes the above a named connection. Previously, I had been using DatabaseExplorer for that goal, but for some reason I got weird results in this case. So I just copied an existing connection file and modified it appropriately in a text editor.

SQLConnection[
  JDBC["Microsoft SQL Server (MS6 driver)", "ServerName"], 
  "Location" -> "C:\\Users\\vriessc\\AppData\\Roaming\\Mathematica\\DatabaseResources\\MyArbitraryConnectionName.m", 
 "Name" -> "MyArbitraryConnectionName", 
 "Catalog" -> "DatabaseName",
 "Description" -> "My descripion", 
 "Password" -> "", 
 "Properties" -> {"integratedSecurity" -> "true"}, 
 "ReadOnly" -> Automatic, 
 "RelativePath" -> False, 
 "TransactionIsolationLevel" -> Automatic, 
 "UseConnectionPool" -> Automatic, 
 "Username" -> ""
]

The connection can now be opened using the simple:

conn = OpenSQLConnection["MyArbitraryConnectionName"]
$\endgroup$
  • $\begingroup$ I took a little bit of a shortcut compared to yours. I dropped the applicable .jar and .dll in $BaseDirectory/Applications/Oracle/Java. Then I did OpenSQLConnection[JDBC["com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://sqlservername;databaseName=DBname;integratedSecurity=true"]]. This worked well. Your solution is definitely more thorough. $\endgroup$ – kale Oct 21 '16 at 2:18
  • $\begingroup$ (Sorry for the ugly comment above). Also, connecting to a Microsoft Access file directly also works now with the UCanAccess protocol. However, I've learned that you need to set memory=False for large databases (or increase Java heap space) to get it to work quickly. $\endgroup$ – kale Oct 21 '16 at 2:26
  • 1
    $\begingroup$ @kale My goal was not having to change my existing programs, which was achieved this way. Anyway, how does Access access helps accessing an SQL server? That just isn't the same thing, or is it? $\endgroup$ – Sjoerd C. de Vries Oct 21 '16 at 6:17
  • 1
    $\begingroup$ @SjoerdC.deVries Well done! Now please send an email to Wolfram Support demanding that this new driver be part of Mathematica 11.1 or whatever the next version is called. $\endgroup$ – Rolf Mertig Oct 21 '16 at 8:53
  • $\begingroup$ @SjoerdC.deVries Yep, not related, just an FYI since that has also been another headache for me in the past. Great job putting this together, too. $\endgroup$ – kale Oct 21 '16 at 12:23
5
$\begingroup$

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 Access DB without relying on JDBC-to-ODBC bridge. e.g.

In[32]:= << DatabaseLink`

In[34]:= $Version

Out[34]= "11.0.1 for Mac OS X x86 (64-bit) (September 21, 2016)"

In[36]:= JDBCDrivers["Microsoft Access(UCanAccess)"]

Out[36]:JDBCDriver["Name" -> "Microsoft Access(UCanAccess)", 
"Driver" -> "net.ucanaccess.jdbc.UcanaccessDriver", 
 "Protocol" -> "jdbc:ucanaccess://", "Version" -> 3.1, 
 "Description" -> "Open-source Java JDBC driver implementation that allows Java \developers and JDBC client programs to read/write Microsoft Access databases.", 
 "Location" ->"/Applications/Mathematica11.0.1.app/Contents/SystemFiles/Links/DatabaseLink/DatabaseResources/accessjdbc.m"]

In[37]:= conn = 
 OpenSQLConnection[
 JDBC["Microsoft Access(UCanAccess)", 
  "/Users/damanjits/databaselink/Tests/AccessDB.accdb"]];

In[38]:= SQLCreateTable[conn, SQLTable["TEST"], 
 SQLColumn["id", "DataTypeName" -> "integer", "PrimaryKey" -> True]]

Out[38]= 0

In[39]:= SQLTables[conn]

Out[39]= {SQLTable["TEST", "TableType" -> "TABLE"]}

There is a small section in documentation which describes about this driver at DatabaseLink/tutorial/DatabaseReference.

$\endgroup$
  • $\begingroup$ I noticed the addition of this driver, but its description lists Access as target whereas I need MS SQL Server. I didn't have the feeling that it would work for that application, or does it? $\endgroup$ – Sjoerd C. de Vries Oct 20 '16 at 5:27
1
$\begingroup$

I am not sure this will be helpful, as I am running Mathematica 9, but maybe the issue is the same, as move from 32 bit to 64 bit.

I use this code as I move from Windows 32 bit to Windows 64 bit, working with Microsoft SQL-Server:

Needs["DatabaseLink`"];
openConn[]:=Block[
  {},
  If[$System==="Microsoft Windows (64-bit)",
    Return[OpenSQLConnection[JDBC["Microsoft SQL Server(jTDS)",
      "SERVERNAME/DATABASENAME"],
      "Username"->"USERNAME",
      "Password"->"PASSWORD"]]];
  If[$System==="Microsoft Windows (32-bit)",
    Return[OpenSQLConnection[JDBC["ODBC(DSN)",
      "DRIVER={SQL Server};"<>
        "SERVER=SERVERNAME;"<>
        "DATABASE=DATABASENAME"],
      "Username"->"USERNAME",
      "Password"->"PASSWORD"]]];
  Null
]
$\endgroup$
  • 1
    $\begingroup$ Thanks for the effort, but it doesn't work for me. Given that you use a username and password it looks like your network doesn't use Windows Authentication. In my case, with the network using WA, my Windows credentials are automatically passed to the server without the need to enter them. $\endgroup$ – Sjoerd C. de Vries Oct 20 '16 at 20:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.