On Tue, May 13, 2008 at 10:13 AM, Tim Bunce <[EMAIL PROTECTED]> wrote:
> On Tue, May 13, 2008 at 01:20:14PM +0100, Martin Evans wrote:
> > Tim,
> >
> > In the thread "problem with DBD::ODBC and placeholders
> [SEC=UNCLASSIFIED]"
> > on dbi-users recently you said:
> >
> > > Drivers that support named placeholders like ":N" where N is an
> > > integer, could support both forms of binding: bind_param(":1",$v) and
> > > execute($v)
> > > It's not dis-allowed. Driver docs should clarify this issue.
> >
> > Is it really your intention that to bind named parameter "fred" as in
> the
> > SQL "insert into xxx values(:fred)" you call bind_param(":fred",$v)?
>
> That's the way DBD::Oracle has always done it.
>
> > As it happens DBD::ODBC has a bug in its support of named parameters
> (other
> > than :1, :2 etc) which means it did not work at all but it ALSO expects
> the
> > name parameter ":fred" above to be passed to bind_param as "fred" i.e.
> the
> > leading ':' is treated as an introducer and not part of the parameter
> name.
> > I believe, but am prepared to be put right, other non-perl database
> drivers
> > also drop the ':' when binding.
>
> Any other driver authors/users care to comment?
>
DBD::Informix doesn't accept named or numbered placeholders because of the
pain and grief involved in parsing the SQL accurately to distinguish between
dbase:table and a named place holder, or betwen DATETIME(23:34:49) HOUR TO
SECOND and two numbered placeholders.
I may yet end up revisiting that restriction - there are moves afoot in the
Tcl/Tk world to provide a Tcl-TDBC module to replace the per-DBMS specific
stuff (basically, what DBI started a decade and more ago, and which Python
and PHP have since adopted, and now Tcl/Tk too). However, as I mentioned,
it will involve some fairly ugly parsing issues - it would require the
DBD::Informix driver to understand a lot of syntax that frankly no driver
code should have to deal with.
The use of column::INTEGER casts is relatively easy to disambiguate. The
DATETIME notation can be dealt with with a sufficiently context sensitive
parser that treats the parenthesized expression as a single string. I'm not
wholly sure what heuristics to use when distinguishing :name notation. In
Informix, there has to be a database name preceding the colon for the
database:table (or database:owner.table) cases; obviously, depending on
context, there could also be various .column extensions after the table
name. It may be sufficient to look for words before or after the :name,
but spacing is not definitive -- although it is conventionally written with
no spaces, dbase : table . column is perfectly OK, and comments or newlines
could appear between any of those elements, and so on and so forth. And
database names are not necessarily restricted to avoid keywords -- you could
(hypothetically, but insanely) have a database called 'where' leading to
problems with WHERE where:table.column = :where ,,, which has a placeholder
named :where and does not have a placeholder called :table. But a
simple-minded parse of the SQL would pick up two placeholders. And I'm not
sure whether the amenity gained by named placeholders warrants the pain of
writing enough of an SQL parser to do this job.
> As it didn't work before I can change it to be either - just let me know.
>
> I guess the colon could be made optional without any harm.
>
Optional colon makes sense - the preferred notation should be documented
(probably without colon).
--
Jonathan Leffler <[EMAIL PROTECTED]> #include <disclaimer.h>
Guardian of DBD::Informix - v2008.0229 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
