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
The
iodbctest
program indicates it can't find my_db; that's your clue that it's not configured correctly.Have another look at the FreeTDS User Guide, specifically at what properties can and can't be in
odbc.ini
. You need a server or servername property in yourodbc.ini
, depending on whether or not you want to use yourfreetds.conf
file to identify the server.