Guide to accessing MS SQL Server and MySQL server on Mac OS X

Native GUI client access to MS-SQL and MySQL

We can use Oracle SQL Developer with the jTDS driver to access Microsoft SQL Server. Note: jTDS version 1.3.0 did not work for me; I had to use version 1.2.6. Detailed instructions can be found here.

We can use MySQL Workbench to access MySQL server. Setup is intuitively obvious.

Overview of ODBC on Mac OS X

Mac OS X has iODBC installed as it’s default ODBC manager. Most other Linux/UNIX system uses unixODBC to manage the ODBC drivers. This is the main reason why there’s so much confusion on getting ODBC to work on Mac OS X.

ODBC is kind of like an API for any software to access any DBMS easily, regardless of what DBMS it is and what OS it’s running on. Different software (e.g., R or Python) can utilize ODBC to access different DBMS through the following logic: Software -> ODBC Manager -> ODBC Driver for the DBMS -> DBMS Server (Software: R, Python, etc.; DBMS: MySQL, MS-SQL, etc.).

It doesn’t matter whether you use iODBC or unixODBC. Whichever one you use, just make sure the DBMS Driver and software you are using are configured/compiled to use with the same ODBC manager (usually set through the configure flags). For example, the R package RODBC and Python package pyodbc are compiled by default to use iODBC on Mac OS X. The DBMS drivers used must be compiled for use with iODBC. For iODBC, one could add data source names (DSN’s) at ~/Library/ODBC/odbc.ini. For unixODBC, one could add DSN’s at ~/.odbc.ini.

My current setup utilizes iODBC. I will outline the instructions for setting up MySQL and freeTDS (MS-SQL) drivers for use with RODBC and pyodbc through iODBC.

MySQL and FreeTDS with iODBC on Mac OS X

Install the MySQL Connector/ODBC driver. Driver should be at /usr/local/lib/libmyodbc5.so or /usr/local/lib/libmyodbc5w.so. Note: I’m unable to compile the driver from source on Mac OS X.

FreeTDS is an open source ODBC driver to access MS SQL Server. Install via Home Brew:

## install homebrew
ruby -e "$(curl -fsSL https://raw.github.com/mxcl/homebrew/go)"

## install freetds
brew install freetds

Driver should be at /usr/local/lib/libtdsodbc.so (symbolic linked).

Create ~/Library/ODBC/odbc.ini:

[sqlserver01]
Driver=/usr/local/lib/libtdsodbc.so
TDS_Version=4.2
Server=ip.address
Port = 1433
Trace = Yes
Description=my description
# Database=
# can't specify username and password for freetds

[mysql01]
Driver=/usr/local/lib/libmyodbc5.so
Server=hostname
Port=3306
charset=UTF8 
User=username
Password=password
# Database=
## can specify an actual database to each DSN

Install pyodbc via sudo pip install pyodbc. Test connections in python:

import pyodbc as p

con1 = p.connect("DSN=sqlserver01;UID=username;PWD=password")
con1.execute("select name from master..sysdatabases").fetchall()

con2 = p.connect("DSN=mysql01;UID=username;PWD=password")
con2.execute("show databases;").fetchall()

Install R using the installer. Install RODBC in the R interpreter via install.packages("RODBC"). Test connections in R:

library(RODBC)

ch1 <- odbcConnect(dsn="sqlserver01", uid="username", pwd="password")
odbcQuery(ch1, "select name from master..sysdatabases")
odbcFetchRows(ch1)

ch2 <- odbcConnect(dsn="mysql01", uid="username", pwd="password")
odbcQuery(ch2, "show databases;")
odbcFetchRows(ch2)

More on unixODBC on Mac OS X

If one wants to use unixODBC on Mac OS X instead, note the following:

  • First install unixODBC via Homebrew with brew install unixodbc.
  • Compile R from source to have it work with unixODBC (R binaries from the installer uses iODBC by default).
  • Can choose --with-odbc-manager=odbc when compiling RODBC.
  • When compiling freeTDS, include the argument with-unixodbc (pass to Homebrew or when compiling manually).
  • I’m unable to compile the MySQL Connector driver on Mac OS X from source (Homebrew or manually). Thus, it won’t work with unixODBC. I believe I tried unixODBC and MySQL Connector from macports, and those work.
  • pyodbc only works with iODBC on Mac OS X (inspect setup file). Currently I can’t get pyodbc to work with unixODBC on Mac OS X.

More differences between unixODBC and iODBC

unixODBC comes with the isql command to access different DBMS from the command line interpreter. iODBC comes with the iodbctest and iodbctestw commands. The command isql works for me on Mac OS X when I set freeTDS up to work with unixODBC (e.g., accessing MS SQL Server). I couldn’t access MySQL server because the MySQL Connector driver was compiled for use with iODBC.

If I use iODBC, I get the following for trying to access a MySQL server:

$ iodbctestw "DSN=sqlserver01;UID=username;PWD=password"
iODBC Unicode Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0607.1008
1: SQLDriverConnectW = [MySQL][ODBC 5.1 Driver]Prompting is not supported on this platform. Please provide all required connect information. (0) SQLSTATE=HY000
1: ODBC_Connect = [MySQL][ODBC 5.1 Driver]Prompting is not supported on this platform. Please provide all required connect information. (0) SQLSTATE=HY000

When I try to access SQL Server, I get

$ iodbctestw "DSN=sqlserver01;UID=username;PWD=password"
iODBC Unicode Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0607.1008
1: SQLDriverConnectW = [FreeTDS][SQL Server]Login failed for user 'username'. (18456) SQLSTATE=42000
2: SQLDriverConnectW = [FreeTDS][SQL Server]Unable to connect to data source (0) SQLSTATE=08001
1: ODBC_Connect = [FreeTDS][SQL Server]Login failed for user 'username'. (18456) SQLSTATE=42000
2: ODBC_Connect = [FreeTDS][SQL Server]Unable to connect to data source (0) SQLSTATE=08001

Don’t know why that is so. I guess it’s not too important to use an interactive interpreter. What matter is that the driver works with R and Python. Perhaps I should consider sqsh or do more searching.

Accessing MS SQL Server from Mac OS X (or Linux): FreeTDS, unixODBC, RODBC, R

This post is a followup to a recent post I wrote. The issue at hand is that data are stored on a SQL server (Microsoft Server 2005 or version 9 of MS SQL Server to be exact) and that I would like to send queries from a command line interface, either from Mac OS X or a Linux system. The data queried from the server will most often be analyzed in R.

I will outline how to get all of this set up on a Mac OS X which should be similar to a Linux platform.

NOTE: Do not use the Macports version of the following packages – they are NOT needed and can be compiled from source without a problem.

Compile unixODBC (driver manager for different SQL servers)

First, Mac OS X comes with iODBC installed by default. However, FreeTDS is only tested on unixODBC so I’ll use this instead. I didn’t get any luck with iODBC but I didn’t give it nearly as much time as I did to get unixODBC working.

Install like usual: download, extract, ./configure, make, sudo make install. I used the default install directory /usr/local.

Compile FreeTDS (driver to communicate with MS SQL Server and Sybase databases)

First, I recommend getting the development version since all the recent updates are included. I had issues with locale Mac OS X and TDSVER=8.0 (anything higher than version 4.2 actually) specification using FreeTDS v0.82. To do so:

cvs -z3 -d:pserver:anonymous@freetds.cvs.sourceforge.net:/cvsroot/freetds checkout -P freetds
cd freetds
./autogen.sh ## generate the configure and make files
./configure --with-tdsver=8.0 --with-unixodbc=/usr/local
make
sudo make install

The configure line is taken from here. From that same documentation, we DON’T have to register the driver with the manager or add in the data source name for system wide access. I just dealt with local (home directory) files.

Recall from the previous post that I had issues with locale and TDSVER on Mac OS X. I emailed the mailing list and it was partially fixed (tsql and freebcp doesn’t work but bsqldb and isql works) in the development version of FreeTDS for TDSVER=8.0. My ~/.freetds.conf file:

[adrc]
host = IP.ADDRESS
port = 1433
tds version = 8.0
client charset = UTF-8 ##needed on MAC OS X

With this, I get query outputs in the command line:

$ bsqldb -S adrc -U USERNAME -P PASSWORD -h -t 't' -i katy.uds.sql | sed -n '2!p' > katy.uds.txt

To use isql (unixODBC), I have the following in my ~/.odbc.ini:

[adrcDSN]
Driver=/usr/local/lib/libtdsodbc.so
##Description=adrc
Trace=No
##servername=adrc
Server=IP.ADDRESS
##Address=IP.ADDRESS
Port=1433
TDS_Version=8.0
Database=IBADataDictionary
## can have above database or not

Now isql adrcDSN USERNAME PASSWORD works and looks similar to this.

Note, throughout my process of debugging, I usually precede the commands osql, isql, and tsql with environment variables such as `TDSDUMP=stderr TDSVER=8.0 isql …’ to see what’s going on.

RODBC – accessing the databases in R

The installed RODBC on my R in Mac OS X uses iODBC by default. I compiled from source to get it to work with unixODBC:

R CMD INSTALL -l ~/Rlib RODBC_1.3-1.tar.gz
## in R
library(RODBC, lib.loc="Rlib")
ch <- odbcConnect("adrcDSN", "USERNAME", "PASSWORD")

I’ve outlined how to access MS SQL Server from the command line and in R, both of what I want. Only thing missing is getting output in emacs…