Parameterized Queries Extension for RODBC

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).

See XKCD - Exploits of a Mom

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:

install.packages('RODBCext')

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.

  1. 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.

  2. Install the devtools package (if necessary). In R, paste the following into the console:

install.packages('devtools')
  1. Install RODBCext from the Github source code.
devtools::install_github('zozlak/RODBCext')
  1. Obtain recent gcc, g++, and gfortran compilers (see above instructions).

  2. Install the git command line tools.

  3. 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 clone https://github.com/zozlak/RODBCext.git
R 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.:

  • Postgresql - make sure that Use Server Side Prepare configuration option is checked (if you are unix/linux user check for UseServerSidePrepare parametr in your odbc.ini file and make sure it is equal to 1)

In parameterized queries, query execution is splitted into three steps:

  1. Query preparation, where database plans how to execute a query.
  2. Query execution, where database actually executes a query. If query has parameters, they are passed in this step.
  3. Fetching results (if there are any).

RODBC already has a functions responsible for the 3rd step - sqlGetResults(), sqlFetch(), sqlFetchMore(). RODBCext adds two functions responsible for the 1st and 2nd step:

  1. sqlPrepare()
  2. sqlExecute()

See examples:

library(RODBCext)
conn = odbcConnect("MyODBCSource")
 
# Run a parameterized query
sqlPrepare(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)
 

News

Changes in RODBCext 0.2.7 (2016-09-17)

  • Added support for column types reporting 0 as its length (most notably Ms SQL Server's "varchar(max)" column type).
    • When passing data from R into a database, data of any size are supported (the available memory for ODBC buffers is the only limit).
    • When reading data from database into R a default RODBC's buffer length of 255 characters is used to assure compatibility with RODBC (RODBCext relies on RODBC functions on data retrieval). This means strings of type "varchar(max)" will be truncated to 255 charactes.

Changes in RODBCext 0.2.6 (2016-07-11)

  • Number of parameters passed to sqlExecute() is now strictly checked against number of parameters specified in the query. It avoids segfaults when provided data have to many columns (https://github.com/zozlak/RODBCext/issues/3)

Changes in RODBCext 0.2.5 (2015-06-30)

  • force_loop=FALSE parameter added to sqlExecute() as a workaround for queries wchich have to be planned again before each ODBC'a sqlExecute() call (e.g. "EXEC"" queries on MS SQL Server). Setting force_loop=TRUE allows to use a vectorized form of the sqlExecute() despite this limitation by forcing sqlExecute() to plan a query again for each row of data.

Changes in RODBCext 0.2.4 (2014-07-31)

  • SQL_RESULT_CHECK macro doesn't rise errors on SQL_NO_DATA state anymore. This prevents rising errors on queries which return SQL_NO_DATA state on execution (e.g. UPDATE queries which update no records)

Changes in RODBCext 0.2.3 (2014-07-29)

  • A Vignette about paremeterized SQL queries added.

Changes in RODBCext 0.2.2 (2014-07-07)

  • Some minor changes in RODBCext.c to avoid compilation errors on Solaris

Changes in RODBCext 0.2.1 (2014-07-04)

  • Some minor changes to eliminate notices raised on a package check:
    • too long line in sqlExecute() examples splitted
    • @import annotations added

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():

    • sqlPrepare() now takes only channel, query and errors arguments
    • sqlExecute() now takes additional query=NA argument
  • 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)

  • First version of the package.

Reference manual

It appears you don't have a PDF plugin for this browser. You can click here to download the reference manual.

install.packages("RODBCext")

0.3.1 by Mateusz Zoltak, 4 months ago


https://github.com/zozlak/RODBCext


Browse source code at https://github.com/cran/RODBCext


Authors: Mateusz Zoltak [aut, cre], Brian Ripley [aut], Michael Lapsley [aut], Will Beasley [ctb], Juergen Altfeld [ctb]


Documentation:   PDF Manual  


GPL-2 | GPL-3 license


Depends on RODBC

Suggests knitr, rmarkdown, testthat

System requirements: An ODBC3 driver manager and drivers.


Suggested by REDCapR.


See at CRAN