I'm trying to use RODBC to make a connection to a SQL Server database from R under cygwin.
I've installed libiobbc and libiodbc-devel, which I understand provide the driver manager that the RODBC package needs.
I installed freetds, which I understand provides an ODBC driver that can connect to a MS SQL server database. I used its tsql command-line tool to confirm that I can make a connection to a SQL Server.
I now understand the task to be pointing the driver manager (iODBC) to the driver (freeTDS), so that I can make a connection to the SQL Server database from R's RODBC library
The User Guide to freeTDS hints that the driver that freeTDS provides should be at /usr/local/freetds/lib/libtdsodbc.so, and that something like the following should define this driver to idobc:
;
; odbcinst.ini
;
[FreeTDS]
Driver = /usr/local/freetds/lib/libtdsodbc.so
But I don't have libtdsodbc.so on my system, and it appears that the cygwin package freetds does not provide it (listing of installed binaries and support files)
How can I configure idobc to use the freeTDS driver to connect to MS SQL?
EDIT
A colleague located /lib/cygtdsodbc.dll and /usr/lib/cygtdsodbc.dll on my system. Might this be the cygwin equivalent of libtdsodbc.so?
I created an odbcinst.ini file with the following content to define the driver:
[ODBC Drivers]
FreeTDS = Installed
[FreeTDS]
Driver = /usr/lib/cygtdsodbc.dll
and an odbc.ini file defining a data source my_db, using the driver defined above:
[ODBC Data Sources]
my_db = FreeTDS
[my_db]
Driver = /usr/lib/cygtdsodbc.dll
Database = db_name
Host = server_dns_name
Port = 1433
running iodbctest to check things out gives
$ iodbctest
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0812.0326
Enter ODBC connect string (? shows list): ?
DSN | Driver
------------------------------------------------------------------------------
my_db | FreeTDS
Enter ODBC connect string (? shows list): my_db
1: SQLDriverConnect = [iODBC][Driver Manager]Driver's SQLAllocEnv() failed (0) SQLSTATE=IM004
1: ODBC_Connect = [iODBC][Driver Manager]Driver's SQLAllocEnv() failed (0) SQLSTATE=IM004
