At 1:03 AM -0700 7/9/05, Jonathan Leffler wrote:
Can you explain which parts of the SQL:2003 mandate this notation?
I've had a moderately good poke around my copy of ISO/IEC
9075-2:2003 (SQL/Foundation) and cannot find this. I'd like a few
section numbers listed which describe this.
The various places I've looked include: 19.6 (prepare statement), 9
(Additional Common Rules), 6.4 (<value specification> and <target
specification>). I could have missed something in these places - or
I could be looking in the wrong place.
Yes, I can quote some, in different places. Look at these:
SQL:2003, 4.29 "Host parameters" (pp90,91,92)
SQL:2003, 5.4 "Names and identifiers" (pp151,152)
SQL:2003 Foundation page 152 says:
<host parameter name> ::= <colon><identifier>
See that last line in particular. I don't see how it could be more clear.
This is my main source for the SQL:2003 documentation:
http://www.wiscorp.com/SQLStandards.html
Or specifically:
http://www.wiscorp.com/sql/sql_2003_standard.zip (warning, large file)
That page is run by someone leading / high up in the SQL standards group.
Note that those urls are printed in my SQL::Routine::Language POD
file on CPAN, and the lines with specific pages and section names are
in source/reference comments/documentation for the
build_identifier_host_parameter_name() function
in my SQL::Routine::SQLBuilder module. I do tend to give sources to
back up anything important I do, which is good for third party
validation.
The IDS (IBM Informix Dynamic SQL) syntax has a number of places
where :<digits> can appear with a meaning other than placeholder,
and there are also a number of places where :<identifier> can appear
with a meaning other than placeholder. So, it would be extremely
difficult to add :<identifier> notation into IDS. (One of the bits
I had to remove from DBD::Informix was code from DBD::Oracle that
simulated :<identifier> notation - because it breaks too much else.)
A variety of databases, such as Oracle, already have support for just
the format I described, which I believe was also in SQL:1999. In
fact, I think that Oracle's own extensions of earlier SQL standards
had a lot of influence on later SQL standards such as this, though I
can think of multiple differences too.
This ties in with my previous comment, too; don't try to demand too
much of the drivers, or the driver writers. As long as you've got a
good surrogate system in DBI that can simulate those accurately for
DBMS that only support '?' (positional) placeholders, then DBI v2
can do what the heck it likes. But as soon as it is inaccurate -
translates things that should not be translated - or cannot do the
translation automatically, then you will lose drivers (or, more
accurately, driver writers).
I don't see how :foo is significantly more difficult than ? to
simulate accurately.
I'm all in favour of looking at the big picture and trying to see
where you want to go. However, you must also keep an eye out for
the marshes between where you are and where you want to go; don't
let DBI be sunk by ignoring the realities of the available DBMS.
At the same time, don't let today's technology limit what you do in
preparation for tomorrow. DBI v2 is meant to be a forward-looking
plan, as Perl 6 is, so we have to consider things that are reasonable
for inclusion; even if it isn't common for native database support
now, that isn't to say that native support won't come later, and when
it does, we'll already be ready, or they'll even take what DBI does
as a cue for what they can add.
-- Darren Duncan