Provide interfaces and support for Parameterised Queries

[byapparov]

I would like to have functionality in Rmarkdown files that is similar to BigQuery Magics via https://github.com/yihui/knitr/issues/1867

here a some interfaces that I think are missing in DBI package for it to be work:

1. Extract variables from sql Function that can extract parameter names from sql based on different notations, as opposed to current DBI::sqlParseVariables implementation will support different options:

database	notation
postgres	$param_name
bigquery	@param_name
other	?param_name

2. Each package that supports DBI interfaces should provide information about notation used by the corresponding database Interface in the DBI package that will allow to access notation for parameters used in the engine

3. Each package should implement a flag that indicates if database supports parameterised queries Interface in the DBI package that will define if interpolation should be done or not, e.g. whether DB engine supports parameterised queries

[krlmlr]

Thanks, good points. What is the difference between items 2 and 3? Item 1 might be available already but needs better documentation.

[byapparov]

2 is more details, I guess you could implement 2 in a way that allows you to check if database supports parameters in queries:

Function returns a vector of possible characters that identify parameter. if that vector has zero length it means that parameters are not supported

[pnacht]

As someone who is a beginner in the subtleties of query safety, is glue::glue_sql() sufficiently safe? It doesn't use the actual SQL parameterization methods which keep the query and the data separate, but it might be simple enough.

Hell, I've written a package myself which does precisely this (the package is for access to a specific database, so the connection pool is internalized so we don't need to keep copying it all over the place):

myPackage::getQuery("
  SELECT *
  FROM foo
  WHERE a = { a }
    AND b IN ({ b* })")