Thank you for this post Tim, it seemed to lay out the issues well and make a lot
of things clear.
I'm now inclined to support your backslash-escape proposal at the DBI driver
level in principle.
(I also agree that the doubling method is nasty.)
Or alternately I suggest variation on that proposal that brings in shades of the
vendor escape clauses.
I suggest a variant where instead of a single backslash character indicating an
escape, we have some multi-character thing to indicate it, ideally involving
delimiters so it is more clear on how far the effect of the new escape feature
is supposed to go.
For example, using \{?} rather than \? or \{:foo} rather than \:foo.
One benefit of that is if you have some SQL that contains ? or : numerous times,
you only need to surround the whole fragment with \{} and not individually
escape each occurrence, making for neater code.
As to dealing with say literal { or } in the SQL as I could see reasonably
happening, the quoting mechanism could be made more generic like Perl's "q"/etc,
so for example it would take the form \X...X where whatever character appears
after the \ is what is matched, and it could be a multiplicity if necessary
within reason, eg \{{{...}}} would just work. For that matter, heredocs or the
"quoted printable" feature you can see in email messages or such, eg you have
\"foo"...foo or some such.
I'm speaking in principle here, I'm not proposing a specific feature set, but
both "q" as well as Perl 6's related quoting mechanism is useful for guidance,
and I think something involving delimiters is best.
But if some of that sounds unduly complicated, I have a better idea.
I propose that the DBI include an API for users to tell the driver what possible
escape delimiters they are using. For example, doable at least at a statement
level (and optionally on a connection/etc level for defaulting if that makes sense).
The API could involve an 'attr' given when preparing a SQL statement or other
appropriate places.
placeholder_escape_delimiters => [ '\{','}' ]
placeholder_escape_delimiters => [ '\{','}','\[',']' ]
placeholder_escape_delimiters => [ '\{{{','}}}' ]
placeholder_escape_delimiters => [ '{{{','}}}' ]
In this way, the backslash is no longer special, or necessary, though I
anticipate it would often still be used for the mnemonics.
Rather, when the driver is parsing the SQL for placeholders, if it encounters
any left delimiter strings, if will leave the following SQL unaltered until it
encounters the corresponding right delimiter string, and then it looks for
placeholders again.
(As to numbered placeholders, which are effectively a special case of named
placeholders, not being directly composable in SQL::Abstract, I see that as
being a problem itself. It would be a great help to developers in principle if
the native way for working with parameters was named rather than positionally.
However, that is really a separate matter to deal with and I think it is a good
idea for Tim's proposal in some form to happen regardless of dealing with this
separate matter.)
-- Darren Duncan
