Connect to MSSQL using DBI

Question

I can not connect to MSSQL using DBI package. I am trying the way shown in package itself

    m <- dbDriver("RODBC") # error

Error: could not find function "RODBC"

    # open the connection using user, passsword, etc., as
    # specified in the file \file{\$HOME/.my.cnf}
    con <- dbConnect(m, dsn="data.source", uid="user", pwd="password"))    

Any help appreciated. Thanks

Plectron

Answer 1

It looks like there used to be a RODBC driver for DBI, but not any more:

http://cran.r-project.org/src/contrib/Archive/DBI.RODBC/

A bit of tweaking has got this to install in a version 3 R but I don't have any ODBC sources to test it on. But m = dbDriver("RODBC") doesn't error.

> m = dbDriver("RODBC")
> m
<ODBCDriver:(29781)> 
> 

Suggest you ask on the R-sig-db mailing list to maybe find out what happened to this code and/or the author...

Patinous

Answer 2

As an update to this question: RStudio have since created the odbc package (or GitHub version here) that handles ODBC connections to a number of databases through DBI. For SQL Server you use:

con <- DBI::dbConnect(odbc::odbc(),
                      driver = "SQL Server",
                      server = <serverURL>,
                      database = <databasename>,
                      uid = <username>,
                      pwd = <passwd>)

You can also set a dsn or supply a connection string.

Earthly

And the major advantage of odbc compared to RODBC is the improved insert performance using e. g. dbWriteTable, see Feature request: Support for ODBC bulk insert and update operations – Market

(read the odbc GitHub readme file)... In addition to installing the R package, you need to install an ODBC driver on your machine. If you install the ODBC SQL Server driver from Microsoft, then set driver = "ODBC Driver 17 for SQL Server" instead. Look at what drivers are available via odbcinst -j find the DRIVERS line location and view that file, e.g., more /usr/local/etc/odbcinst.ini – Domingadomingo

Answer 3

Solved. I used library RODBC. It has great functionality to connect sql and run sql queries in R.

Loading Library:

library(RODBC)

# dbDriver is connection string with userID, database name, password etc.

dbhandle <- odbcDriverConnect(dbDriver)

Running Sql query

sqlQuery(channel=dbhandle, query)

Thats It.

Plectron