rodbc
Parameterized queries with RODBC
Mateusz Zoltak wrote RODBCext package in 2014 (based on work by Brian Ripley and Michael Lapsley): conn = odbcConnect(‘MyDataSource’) sqlPrepare(conn, “SELECT * FROM myTable WHERE column = ?”) sqlExecute(conn, ‘myValue’) sqlFetchMore(conn) Source: http://cran.r-project.org/web/packages/RODBCext/vignettes/Parameterized_SQL_queries.html
SQL Server RODBC Connection
library(RODBC) dbhandle <- odbcDriverConnect(‘driver={SQL Server};server=mysqlhost;database=mydbname;trusted_connection=true’) res <- sqlQuery(dbhandle, ‘select * from information_schema.tables’)
Dynamic “string” in R
We can use paste: Df <- sqlQuery(ch, paste(“SELECT * FROM tblTest WHERE Id =”, Id)) c concatenates into a vector, paste is for string concatenation. Or we can use sprintf: sprintf(“SELECT * FROM tblTest WHERE Id = %s”, Id)
RODBC odbcDriverConnect() Connection Error
The correct syntax should be: odbcDriverConnect(‘driver={SQL Server};server=servername\\instancename,port;database=testing;uid=abc;pwd=123456’) If you use Windows Authentication method: odbcDriverConnect(‘driver={SQL Server};server=servername\\\\instancename,port;database=testing;trusted_connection=true’) Slash seems like an escape character, escaping slash works.
How to connect R with Access database in 64-bit Window?
Use odbcDriverConnect instead. If you have 64-bit R installed, you may have to use the 32-bit R build. odbcDriverConnect(“Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D:/SampleDB1/sampleDB1.mdb”)
Installation of RODBC/ROracle packages on OS X Mavericks
This answer will cover installing RODBC and ROracle on OS X, specifically on Mavericks and later. Both packages now need to be built (compiled) from source. This means that the first thing you need is to download XCode and the associated “command line tools” so that you actually have a compiler. How you do this … Read more
Pass R variable to RODBC’s sqlQuery? [duplicate]
Build the string you intend to pass. So instead of example <- sqlQuery(myDB,”SELECT * FROM dbo.my_table_fn (x)”) do example <- sqlQuery(myDB, paste(“SELECT * FROM dbo.my_table_fn (“, x, “)”, sep=””)) which will fill in the value of x.