Hi Hadley, > On 11 Feb 2015, at 16:08, Hadley Wickham <h.wick...@gmail.com> wrote: > > On Wed, Feb 11, 2015 at 8:39 AM, Hannes Mühleisen > <hannes.muehlei...@cwi.nl> wrote: >> Hi Hadley and list, >> >>> On 11 Feb 2015, at 15:01, Hadley Wickham <h.wick...@gmail.com> wrote: >>> As part of my work modernising R's database connnectors, I've been >>> working on improving the support for parameterised queries. I'd love >>> to get your feedback on the API I'm proposing. >>> >>> The goal is to encourage people to use parameterise queries instead of >>> pasting together SQL strings because it is much safer (no worries >>> about SQL injection attacks) and somewhat more performant (becuase the >>> database only needs to parse the query once). >> Makes a lot of sense, yes. MonetDB.R has had support for this from day one. >> Our syntax uses the list of parameters approach, e.g. >> >> dbSendUpdate(conn, "INSERT INTO sometable (a) VALUES (?)", “foobar”) >> >> of course, the parameter can be a vector, in which case the query is >> executed multiple times. > > 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.
>> Generally, I would be in favour of the “list of params” approach. Also, >> please note that the “bind by name” is not supported by all databases. >> Sticking to position-only parameter binding using ? would be most compatible >> (also used in JDBC for example). > > Yup, postgresql doesn't support names either. In that case, providing > a named list would be an error. But where named binding is supported, > I think it's better to use it because it eliminates a class of > potential errors. I think a script using DBI should not need to know which DBI implementation is running behind it. But if someone uses named parameters on a MySQL backend (possibly out of order), that script will not run with Postgres or others. Best, Hannes
smime.p7s
Description: S/MIME cryptographic signature
_______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB@r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-db
