In perl/python DBI APIs have a mechanism to safely interpolate in parameters to an sql query. For example in python I would do:
cursor.execute("SELECT * FROM table WHERE value > ?", (5,))
Where the second parameter to the execute method is a tuple of parameters to add into the sql query
Is there a similar mechanism for R's DBI compliant APIs? The examples I've seen never show parameters passed to the query. If not, what is the safest way to interpolate in parameters to a query? I'm specifically looking at using RPostgresSQL.
Just for completeness, I'll add an answer based on Hadley's comment. The DBI package now has the function
sqlInterpolate
which can also perform this. It requires a list of function arguments to be named in the sql query that all must start with a?
. Excerpt from the DBI manual belowIndeed the use of bind variables is not really well documented. Anyway the ODBC commands in R work differently for different databases. One possibility for
postgres
would be like this:Hope it helps.