Tim Bunce wrote:
On Tue, Apr 29, 2008 at 09:29:43AM +0100, Martin Evans wrote:
Looking at this again I don't think DBD::ODBC will do as you expect. I'm
guessing you are doing something like:
$sth = $dbh->prepare(
'insert into x (a,b,c,d,e,f) values(:1,:2,:3,:3,:4,:4)');
$sth->execute('p','q','r','s');
I cannot see in the DBI pod anything which says this should work.
Drivers that support named placeholders (":foo") should allow :foo to
appear in a statement more than once. The value would be bound using:
$sth->bind_param( ":foo", $value )
and the execute() method should be called with no arguments.
Oh :-(
DBD::ODBC has code to parse un-named placeholders ("?"), numbered
placeholders (:n) and named placeholders(":myph"). It also has an option
to disable any support for named placeholders as there have been
occassions (e.g., in SQL defining triggers in Oracle) where some SQL
looks like a named placeholder but isn't:
create trigger i1 before insert on xxx for each row
begin
:new.created_by_user := user;
:new.created_date_time_utc := utc_timestamp();
end;
However, all the code does is convert all numbered and named
placeholders to ? (before sending the SQL onwards) and build a hash of
placeholder name (the key) to placeholder index. As a result you cannot
use a named placeholder more than once since as far as DBD::ODBC is
concerned the number of placeholders is the number of keys in the hash
and you cannot have a key in the hash more than once. The code is
obviously flawed in this respect.
Drivers that only support un-named placeholders ("?") obviously can't
distinguish one placeholder from another other than by their index
(sequence) number: 1, 2, 3 etc. For these placeholders values can be
bound using:
$sth->bind_param( $index, $value )
where $index is a simple integer with no colon, or by passing values to
the execute() method call.
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.
The DBI docs just says "their use is not portable" about all forms of
named placeholders. Not very useful I guess :)
Well it would seem DBD::ODBC supports all of the above EXCEPT you
"cannot use a named placeholder more than once".
If you change the execute to:
$sth->execute('p','q','r','r','s','s')
it should work
Er, I don't think it should. That would only work if the driver was
treating ":N" style placeholders as "?" style placeholders, which would
be a bug anyway.
Sorry, I meant change the SQL to use ? as well. It would not work with
the execute above because DBD::ODBC has already said there are 4 named
placeholders in the SQL when in fact it means there are 4 unique
placeholders.
If the driver is capable of mapping execute() arguments to ":N" style
placeholders then the original code should work.
If it's not then a useful error message should be generated.
I'm not in a mad rush to make this case work since it is the first time
I am aware it has cropped up and although not a massively complicated
change it is a significant change. I could be persuaded otherwise but
unless there are clamours to change it then for now I will add a note
saying it only supports unique named placeholders and error non-unique
placeholder names.
but I'd change your SQL to use ? for parameters anyway as
it is more portable and quicker in DBD::ODBC. Perhaps if Tim is reading
this he could clarify whether it was ever his intention that the example
above should work?
I'll add a note to the docs (hopefully) clarifying the issue.
Tim.
Thanks for the clarification.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
