R help - Jul 2008 - RODBC - problems using odbcDriverConnect without DSN

Hi,

I'm trying to use RODBC without having to set up a DSN, using hte
direct connection string in odbcDriverConnect.  My connection attempt
looks something like:
> odbcDriverConnect(connection =
"SERVER=localhost;DRIVER={/usr/lib/odbc/libmyodbc.so};DATABASE=myDB;UID=reader;PASSWORD=insecure;")
And this returns the message:
Warning messages:
1: In odbcDriverConnect(connection = conn) :
  [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver
Manager]Data source name not found, and no default driver specified
2: In odbcDriverConnect(connection = conn) : ODBC connection failed

I know this means it can't find x connection in the dsn... which means
my connection string isn't recognised as valid.  I've used ODBC and
ODBC connection strings before, but I can't work out why this wouldn't
work here.  I can successfully create the same connection using a user
DSN with the same settings as this, but this connection string won't
work.  It's fairly important for this project that the code can
connect without a DSN needing to be set on every computer that runs
it.  Does anyone know if I'm missing something in my connection
string, or can this not be done using RODBC?

Thanks,
Josiah.

As this message is from your (unstated but probably unixODBC) ODBC driver 
manager, it is nothing to do with RODBC nor R.  Most likely the syntax is 
wrong.

As for 'without a DSN needing to be set on every computer that runs it',
you need the driver and driver manager installed on every such computer,
and a proper installation will have a symbolic declaration of the driver 
in odbcinst.ini in which case you can use DRIVER=MySQL or some such.
E.g.
> library(RODBC)
> con <-
odbcDriverConnect("SERVER=localhost;DRIVER=MySQL;DATABASE=testdb")
works for me.


On Mon, 21 Jul 2008, Josiah Walker wrote:
> Hi,
>
> I'm trying to use RODBC without having to set up a DSN, using hte
> direct connection string in odbcDriverConnect.  My connection attempt
> looks something like:
>> odbcDriverConnect(connection =
"SERVER=localhost;DRIVER={/usr/lib/odbc/libmyodbc.so};DATABASE=myDB;UID=reader;PASSWORD=insecure;")
>
> And this returns the message:
> Warning messages:
> 1: In odbcDriverConnect(connection = conn) :
>  [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver
> Manager]Data source name not found, and no default driver specified
> 2: In odbcDriverConnect(connection = conn) : ODBC connection failed
>
> I know this means it can't find x connection in the dsn... which means
> my connection string isn't recognised as valid.  I've used ODBC and
> ODBC connection strings before, but I can't work out why this
wouldn't
> work here.  I can successfully create the same connection using a user
> DSN with the same settings as this, but this connection string won't
> work.  It's fairly important for this project that the code can
> connect without a DSN needing to be set on every computer that runs
> it.  Does anyone know if I'm missing something in my connection
> string, or can this not be done using RODBC?
>
> Thanks,
> Josiah.
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595