Parameterised queries
On Wed, Feb 11, 2015 at 2:24 PM, Hadley Wickham <h.wickham at gmail.com> wrote:
I deliberately chose not to allow vector parameters because I think it's reasonable to say: "Each R function only ever generates a single query to the database", leaving the onus of looping on the user, and forcing them to think about how to ensure the vectors don't contain bad values. This the same principle behind disallowing multiple queries (separated by ";") in a single string.
However, there are optimisation opportunities that the db driver could exploit if multiple values are to be inserted at once. For example, a database connection in auto commit mode could switch of auto commit,
and try
to insert all values in a transaction to get all-or-nothing semantics. Another opportunity would be a bulk load if the vectors are large.
Agreed, but it's also fundamentally dangerous. I think this should be a separate function with that clearly describes the performance-safety trade off, maybe dbBindAll()? Alternatively, you could have an additional `vectorise` argument that defaulted to FALSE.
I don't understand what is dangerous about repeatedly executing a
prepared
query. Can you give a scenario?
It gives a new attack vector - to introduce additional data into the database, you just need to figure out how to turn a length 1 vector in to a length 2 vector. It's dangerous in the same way that allowing dbGetQuery() to execute multiple queries is dangerous.
I'd rather hope that if it were a case that mattered, the user would not rely on the api as a substitute for appropriate checks. THK
That said, can you think of a use case apart from doing bulk inserts? In RSQLite, I use an internal function to get optimal performance for dbReadTable without generally exposing a more dangerous api.
Other than the convenience, and it being sort of R-like, yes, bulk
inserts
is the main application in my case.
Great. Hadley -- http://had.co.nz/
http://www.keittlab.org/ [[alternative HTML version deleted]]