An extension for RODBC package adding support for parameterized queries.
Extension to the RODBC R package providing support for parameterized queries.
Parameterized queries are the kosher way of executing SQL queries when query string contain data from untrusted sources (especially user input).
Morover parametrized queries speed up query execution if it is repeated many times (because query planning is done only once).
In R, paste the following into the console:
Devtools package provide an easy way to install packages from the GitHub.
If you encounter problems during installation on a Linux, please check if this applies to you or switch to other installation method.
Obtain recent gcc, g++, and gfortran compilers. Windows users can install the Rtools suite while Mac users will have to download the necessary tools from the Xcode suite and its related command line tools (found within Xcode's Preference Pane under Downloads/Components); most Linux distributions should already have up to date compilers (or if not they can be updated easily). Windows users should include the checkbox option of installing Rtools to their path for easier command line usage.
devtools package (if necessary). In R, paste the following into the console:
RODBCextfrom the Github source code.
Obtain recent gcc, g++, and gfortran compilers (see above instructions).
Install the git command line tools.
Open a terminal/command-line tool. The following code will download the repository code to your computer, and install the package directly using R tools (Windows users may also have to add R and git to their path)
git cloneR CMD INSTALL RODBCext
Try to enable support for
SQLDescribeParam() in your ODBC drivers.
This is not necessary, but will speed up the query execution and lower a risk of unexpected types conversion.
Enabling support for
SQLDescribeParam() depends on the driver, e.g.:
UseServerSidePrepareparametr in your odbc.ini file and make sure it is equal to 1)
In parameterized queries, query execution is splitted into three steps:
RODBC already has a functions responsible for the 3rd step -
RODBCext adds two functions responsible for the 1st and 2nd step:
library(RODBCext)conn = odbcConnect("MyODBCSource")# Run a parameterized querysqlPrepare(conn, "SELECT * FROM table WHERE column1 = ? AND column2 = ?")sqlExecute(conn, data=data.frame('column1value', 'column2value'))sqlFetch(conn)# one-call equivalent:sqlExecute(conn,query="SELECT * FROM table WHERE column1 = ? AND column2 = ?",data=data.frame('column1value', 'column2value'),fetch=TRUE)# Insert many rows into the table:sqlPrepare(conn, "INSERT INTO table (charColumn, intColumn) VALUES (?, ?)")sqlExecute(conn, data=data.frame(c('a', 'b', 'c'), 1:3))# one-call equivalent:sqlExecute(conn,query="INSERT INTO table (charColumn, intColumn) VALUES (?, ?)",data=data.frame(c('a', 'b', 'c'), 1:3))# Run query without parameters:sqlPrepare(conn, "SELECT * FROM table")sqlExecute(conn)sqlFetch(conn)# one-call equivalent:sqlExecute(conn, query="SELECT * FROM table", fetch=TRUE)
Changes in RODBCext 0.2.7 (2016-09-17)
Changes in RODBCext 0.2.6 (2016-07-11)
Changes in RODBCext 0.2.5 (2015-06-30)
Changes in RODBCext 0.2.4 (2014-07-31)
Changes in RODBCext 0.2.3 (2014-07-29)
Changes in RODBCext 0.2.2 (2014-07-07)
Changes in RODBCext 0.2.1 (2014-07-04)
Changes in RODBCext 0.2 (2014-07-01)
Changes in API - ability to prepare and execute query in one call moved from sqlPrepare() to sqlExecute():
Added support for drivers which don't provide SQLDescribeParam() ODBC call (by applying data types from data passed to sqlExecute())
sqlExecute() now merges results of all queries if nrows(data) > 1 and fetch=TRUE
Added functions sqlFetchMore() and odbcFetchRows() which avoid calls to RODBC::sqlFetchMore() and RODBC::odbcFetchRows() when connection handle is in a "query prepared but not executed" state (which caused R to crash)
Switch from RODBC::sqlFetchMore() to RODBC::sqlGetResults() for fetching results (now we don't care about queries which do not produce any results like UPDATE, DELETE, CREATE, etc. queries)
Changes in RODBCext 0.1 (2014-05-28)