problem with DBD::ODBC and placeholders [SEC=UNCLASSIFIED]

Hello

I having trouble using placeholders (named and numerical), using DBD::ODBC
connecting to Teradata. I cant easily use ordinary '?', as I refer to the
same placeholder several times in the SQL. For example, this is the
statement I prepare (a contrived example):
INSERT INTO ATABLE
(COLA,COLB,COLC,COLD,COLE,COLF)
VALUES
(:1,:2,:3,:3,:4,:4);

This is the error I get when using numerical place-holders.
DBD::ODBC::st execute failed: [NCR][ODBC Teradata][Teradata Database]
There is a mismatch between the number of parameters specified and the
number of parameters required.  (SQL-07001)(DBD: st_execute/SQLExecute
err=-1)

!!NOTE!! The above error is being returned by the database, not DBI. I am
sure I am calling with the correct number of ags, which means DBI is
stuffing up the call to Teradata. This is the error I would have got if I
passed the wrong number of args:
DBD::ODBC::st execute failed: called with 3 bind variables when 4 are
needed

I get a different error when using named placeholders (and yes im sure the
statement was prepared OK with a parm called :COLA):
Can't bind unknown placeholder ':COLA'

Help please, as Im stuck using DO which is very slow!

 ****************************************
******************************
IMPORTANT:  This e-mail is intended for the use of the addressee and may con
tain information that is confidential, commercially valuable or subject to l
egal or parliamentary privilege.  If you are not the intended recipient you 
are notified that any revie
w, re-transmission, disclosure, use or dissemination of this communication i
s strictly prohibited by several Commonwealth Acts of Parliament.  If you ha
ve received this communication in error please notify the sender immediately
 and delete all copies of t
his transmission together with any attachments.
 ****************************************
******************************

michael.potter@centrelink.gov.au wrote:
> Hello
>
> I having trouble using placeholders (named and numerical), using DBD::ODBC
> connecting to Teradata. I cant easily use ordinary '?', as I refer to the
> same placeholder several times in the SQL. For example, this is the
> statement I prepare (a contrived example):
> INSERT INTO ATABLE
> (COLA,COLB,COLC,COLD,COLE,COLF)
> VALUES
> (:1,:2,:3,:3,:4,:4);
>
> This is the error I get when using numerical place-holders.
> DBD::ODBC::st execute failed: [NCR][ODBC Teradata][Teradata Database]
> There is a mismatch between the number of parameters specified and the
> number of parameters required.  (SQL-07001)(DBD: st_execute/SQLExecute
> err=-1)
>
> !!NOTE!! The above error is being returned by the database, not DBI. I am
> sure I am calling with the correct number of ags, which means DBI is
> stuffing up the call to Teradata. This is the error I would have got if I
> passed the wrong number of args:
> DBD::ODBC::st execute failed: called with 3 bind variables when 4 are
> needed
>
> I get a different error when using named placeholders (and yes im sure the
> statement was prepared OK with a parm called :COLA):
> Can't bind unknown placeholder ':COLA'
>
> Help please, as Im stuck using DO which is very slow!
>
>  ****************************************
******************************
> IMPORTANT:  This e-mail is intended for the use of the addressee and may contain i
nformation that is confidential, commercially valuable or subject to legal or parlia
mentary privilege.  If you are not the intended recipient you are notified that any 
rev
iew, re-transmission, disclosure, use or dissemination of this communication
 is strictly prohibited by several Commonwealth Acts of Parliament.  If you 
have received this communication in error please notify the sender immediate
ly and delete all copies of
this transmission together with any attachments.
>  ****************************************
******************************
>
>

You can send me a level 15 trace of this happening with numerical
placeholders and I'll take a look.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Martin Evans wrote:
> michael.potter@centrelink.gov.au wrote: 
>
> You can send me a level 15 trace of this happening with numerical
> placeholders and I'll take a look.
>
> Martin

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. If you
change the execute to:

$sth->execute('p','q','r','r','s','s')

it should work 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?

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

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.

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 :)

> 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.

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.

> 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.

Tim Bunce wrote:
> On Tue, Apr 29, 2008 at 09:29:43AM +0100, Martin Evans wrote: 
>
> 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".
 
>
> 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.
 
>
> 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