4. All host parameters should be named (like ":foo") rather than
positional (like "?"), meeting with the SQL:2003 standard. The named
format is a lot easier to use and flexible, making programmers a lot
less error prone, more powerful, and particularly more resource
efficient when the same parameter is conceptually used multiple times in
a SQL statement (it only has to be bound once). If anyone wants to use
positional format, it could easily be emulated on top of this. Or, if
native positional support is still important, then it should be a
parallel option that can be used at the same time as named in any
particular SQL statement. See the native API of SQLite 3 for one
example that (I believe) supports both in parallel. This also means
that execute() et al should take arguments in a hash rather than an array.
Yes, native positional support is still important.
positions make it very easy to do SQL math.
To express it in overly simplistic code
$foo = [ "a = ?", "foo" ];
$bar = [ "b = ?", "bar" ];
$baz = "$foo and $bar";
# $baz now is [ "a = ? and b = ?", "foo", "bar" ];
Bearing mind a situation with an arbitrary number and complexity of
these sql fragments to be added together, doing this sort of thing using
named placeholders would be a nightmare.
and we'd just end up with every second person implementing a ? to :p1,
:p2 scheme in their database layers. If you want positional
placeholders, it needs to be an additional feature, not a replacement
for positional placeholders.
