Re: RFC: support for named-placeholders in DBI ?

[Jim Cromie]

Dean Arnold wrote:

Tim Bunce wrote:
 

Teradata supports named placeholders. The syntax is
:NAME, ie, SELECT * FROM TABLE WHERE COL1 = :col1;
     

So it matches Oracle syntax. Does DBD::Teradata allow
$sth->bind_param(":col1", $foo);
like DBD::Oracle? If not, could it?
   

No, it currently doesn't, but Yes, it (fairly easily) could. 
I've been considering it for some time, but (like so many
things) time/resources didn't permit.

 

BTW: I don't see anything in DBI 1.38 that checks for
numeric parameter ID in bind_param() (tho bind_param_array() does),
so is it safe to assume individual drivers *could* just implement such 
(non-portable) support on their own
     

Yes. DBD::Oracle always has.

   

(excluding array-binding support, and the "default"
binding via execute()/execute_array()). Which also raises the issue of how
the default bindings would map to named PHs (perhaps a simple arrayref
of the names supplied as an attribute ?)
     

No. The only thing I'd be happy with is what DBD::Oracle does.
Use implicit placeholder names for the value so
$sth->execute($foo, $bar);

is the same as

$sth->bind_param(":p1", $foo)
$sth->bind_param(":p2", $bar)
$sth->execute();
   



Explicit binding in this case is OK by me...is there any collision here with
the on-going row-wise vs. column-wise array binding thread ?
 

Interestingly, the way it does that is to translate 'normal'
placeholders into :p1, :p2 etc when the SQL is prepared.
Then bind_param(1, ...) is treated as bind_param(":p1",...)
which then means the Driver.xst for execute() "just works".
Tim.
   

Which is effectively what DBD::Teradata does, ie, the 1st named PH
occurance becomes PH 1, the 2nd becomes 2, etc. Actually, DBD::Teradata
currently doesn't pay much attention to what the app binds in that case, as there's
an explicit USING clause required to declare the order/type of the named
PHs. Formal support of named PHs would permit the DBD to generate the
USING clause internally if it was missing.
Jim Cromie wrote:
 

Does this preclude the following ? (as I proposed in 1st message of thread)

   $sth->execute( { ":p1" => $foo, ":p2" => $bar } );

such an argument looks to be non-interfering with current usage,
and would be an error w.o named placeholders in the statement's SQL.
Multiple hashrefs could supply values for all the bindings, with later ones
supplying defaults not previously given.
   

Alas, since execute() currently doesn't support an attribute hash,
might it break some existing drivers ? I can certainly sympathize with
the desire for brevity, but I fear the existing function i/f isn't readily backward
compatible with your particular usage (at least in a portable manner)...
interestingly, execute_array() could be made compatible, since it does have an 
attribute hash.
 

I think you misunderstood my proposal.

execute currently accepts only an array of bind-values, which may be empty.
no \%attr is important, if it was optional, adding my proposed \%bind-vals
it would muddle the interface prohibitively.
        $rv = $sth->execute;
        $rv = $sth->execute(@bind_values)
Since placeholders cannot stand in for multiple values,
a [EMAIL PROTECTED] arg would be nonsense.
I infer that \%bind-vals would also be currently nonsense,
which makes them fair game for mapping to named-PHs ( NmPHs )
IIUC: this would bomb, on execute, with every DBD.

   @inlist = qw ( alpha beta gamma );
   $sth=$dbh->prepare("select * from A where B in ( ? ) ");
   $rv = $sth->execute([EMAIL PROTECTED]);
NB: binding a scalar to that PH would be ok, but the in() clause would 
be pointless,
unless you had more elements in set; either constants or other PHs

Also, Tim pointed out the advantage of NPH - position independence..

The only things named placeholders give you are:

1. The ability to bind one value into multiple placeholders with
   one method call by using the same placeholder name in
   multiple places in a statement.
2. More readable SQL statements by using meaningful placeholder names.

To get 1, you need  to use named-binding,

   either:              bind_param ( "name1", $val1 );
   or-easier:       execute ( \%named_bind_vals );
2 is for programmers, and the association (pun intended) between the NPHs
and the NBVs (named bind values) is a further advantage.
3. since NPHs, NBVs give us position independence, Im also suggesting that
multiple NBVs be allowed, with 1st NBV winning the bind, others provide 
fillin successively
ie layered defaults.
This lets your NBVs reflect the join logic in the SQL, or for other 
reasons...

Ex:
   $sth->execute (
       {%lock},         # if web-app uses hidden fields, PKs in %lock 
(saved on page send) this could protect against hacks
       {%web},         # legit data updates
       {%cache},      # for fields not sent, but computed and stored on 
updates..
       {%nulls},         #
       {%dflts},
       {%seq} );         # sequence generator for DBs that dont have 
internal implementations


Dean Arnold
Presicient Corp.
www.presicient.com
.

 

jimc