Mailing List Archive

Re: Building the %attribs for bind_param ?
> From: mhm@austin.ibm.com
>
> Tim Bunce <Tim.Bunce@ig.co.uk> writes:
> |> > Ok, my perl ignorance is showing....If I don't have a reference how
> |> > do I tell DB2 which memory location to get its values from? DB2 CLI
> |> > does everything by reference and nothing by value. Thus, I need the
> |> > address of the perl variable containing the value of the parameter.
> |> >
> |> Take a look at DBD::Oracle (again). Note that the bind_param function
> |> takes a local copy of the value and passes the address of that to Oracle.
>
> Ok, I think I finally understand why we keep going round and round over
> the value versus reference issue with bind. When you do a bind in
> Oracle does that call actually associate the value of the variable with
> the parameter at that point?

No. Only the memory address.

> In DB2, SQLBindParameter associates a
> memory location with a parameter in a prepared statement and SQLExecute
> retrieves the value from the memory location to substitute for the
> parameter.
>
Same as Oracle.

> That is, if I do something like the following:
>
> $sth->bind_param(1,\$order_num,$attrib_char);
>
> while (<INPUT>) {
> $order_num = unpack($template,$_);
> $sth->execute();
> }
>
> [...]. Since the variables never change (just their contents change),
> there is no need to rebind on every iteration of the loop.
>
You *cannot* make this work reliably by giving DB2 the address of the
$order_num variable's buffer. Perl may change the address of its string
buffer every time it's assigned a new value. Core dump city!

Currently the DBI spec does not even mention bind_param but if it did
it would say that portable code must explicitly rebind any values that
change between execute's (but only those that change need rebinding).

The only way to do what you want is to have your execute code rebind
all the values transparently and automatically per call. But that
would obviously defeat the efficiency objective.

Give it up for now Mike. Put the bind_params's inside the loop.

One day perl may acquire a way to 'freeze' (but not make constant)
a variable so that it's not allowed to change it's type or the memory
location of it's buffer. I've already mentioned this on perl5-porters
and I've CC'd this to them now as an example of the problem.

Till then you just can't do it without getting core dumps.

One final point, the issue of passing a reference of a value to
bind_param only relates to output parameters which, for the same
reasons given above, would have to have their values updated by
execute() after the underlying database call returns.

Right now the DBD::Oracle 'reference' implementation of bind_param()
does *not* take a reference. It's probable that another method would be
defined for specifying output (or in/out) parameters. Then again the
whole issue may be overtaken by the 'migration' to a SQL CLI style
interface. Hey, this is still alpha software!

> Mike
>
Tim.