How to use dbPool with RODBC?


#1

I am currently connecting to a SQL Server DB with RODBC::odbcDriverConnect(…)

How do I create a pool object that connects through ODBC? Must I use the ‘odbc’ package or can I stick to the RODBC package?

Thanks,
Hector


#2

I believe that you need a DBI “compliant” package, to use pool. That’s why odbc is needed, unless you are connecting to a database that has a DBI-backed package, like postgres


#3

That was my understanding as well, but I'm having issues using pool with the odbc package using a MySQL driver. When I run the code below, I see the following error - these seems to indicate that the odbc package/drive is not fully DBI compliant. I am able to make a working odbc connection when I call dbConnect directly. I also saw this SO post with the same error for SQL Server.

# define a pool object
pool <- dbPool(
  odbc::odbc(),
  Driver = "MySQL Driver",
  Database = "database-name",
  Server = "server",
  UID = "user",
  PWD = "password",
  Port = 3306
)

warning messages from top-level task callback '1'
Warning message:
Could not notify connection observer. trying to get slot "info" from an object (class "Pool") that is not an S4 object


#4

This may be due to package version mismatch, can you check to make sure you have the latest versions?


#5

Thanks @edgararuiz -- you were right! I was previously on odbc 1.1.3 and DBI 0.7. I upgraded to the current CRAN versions 1.1.5 and 0.8 respectively (with pool at the current 0.1.4), and the dbPool call is working now.

Thanks again.