Re: Strange bind_param issue

On 16/08/12 14:00, Martin J. Evans wrote:
> On 15/08/12 17:39, Tim Bunce wrote:
>> On Wed, Aug 15, 2012 at 04:14:52PM +0100, Martin J. Evans wrote:
>>> I've just been given an rt
>>> https://rt.cpan.org/Ticket/Display.html?id=78838 and am at a loss to
>>> explain exactly what is happening. I wonder if anyone can help?
>>>
>>> Some background:
>>>
>>> DBI says for bind_param:
>>>
>>> "The bind_param method takes a copy of $bind_value and associates it
>>> (binds it) with a placeholder"
>>>
>>> As far as I am aware DBD::ODBC does not copy the scalar given to it -
>>> so perhaps DBI does this. The problem I'm seeing in the provided
>>> example is the pointer passed to ODBC's SQLBindParameter at the time
>>> bind_param is called no longer points to a valid string when execute
>>> is called. However, changing the call to bind_param to pass $obj as
>>> "$obj" appears to fix the problem.
>>
>> The driver should take a copy of the value when bind_param is called.
>> Perhaps stored by the ParamValues attribute.
>> (bind_param_inout takes a reference instead and binds at execute.)
>>
>>> Can anyone say if DBD::ODBC should
>>> work with either example and explain what might be happening here:
>>
>>> use DBI;
>>> my $dbh =
>>> DBI->connect("dbi:ODBC:DSN=xxx;UID=xx;PWD=yy;MARS_Connection=No;");
>>> my $obj = new Object();
>>> my $sql = q(SELECT ? AS result);
>>> my $sth = $dbh->prepare($sql);
>>>
>>> # interesting - is the following use of $obj really as a string?
>>> # in the call to bind_param here, DBD::ODBC binds the pv ptr of the
>>> scalar
>>> # it is given in a SQLBindParameter call but by the time execute is
>>> called
>>> # the string at that address is no longer valid. I kind of expect
>>> that as
>>> # what to_s returns is only required in the bind_param statement and yet
>>> # DBI says "bind_param takes a copy".
>>> # However if the following is changed to "$obj" it works
>>> $sth->bind_param(1, $obj);
>>> $sth->execute();
>>>
>>> while (my $row = $sth->fetchrow_hashref()) {
>>>    print $row->{'result'}, "\n";
>>> }
>>>
>>> package Object;
>>> use overload '""' => 'to_s';
>>> sub new() { bless { }, shift };
>>> sub to_s() { my $self = shift; ref($self); }
>>
>> The driver needs to make a copy and ensure that 'get magic' is called.
>>
>> SvPV will call get magic but won't make a copy. Since you need an SV for
>> the ParamValues attribute maybe newSVsv() followed by SvPOK_only_UTF8()
>> on the copy would be a suitable incantation.
>>
>> Tim.
>>
>
> It appears dbd_bind_ph does this:
>
> /* newvalue is the scalar param passed in to dbd_bind_ph */
> /* phs is a structure to hold the param details */
>
>     if (!is_inout) {    /* normal bind to take a (new) copy of current
> value */
>         if (phs->sv == &PL_sv_undef)             /* (first time bind) */
>             phs->sv = newSV(0);
>         sv_setsv(phs->sv, newvalue);
>     } else if (newvalue != phs->sv) {
>        if (phs->sv)
>            SvREFCNT_dec(phs->sv);
>        phs->sv = SvREFCNT_inc(newvalue);       /* point to live var */
>     }
>
> so as far as I can see a copy is taken but I don't think sv_setsv does
> any magic. Similar code exists in DBD::Oracle and a number of other DBDs
> I looked at. DBD::ODBC does use SvPV later so I'm guessing that is where
> the magic happens and the scalar is stringified.
>
> This made me wonder what happens if you pass a reference without any
> AMAGIC and DBD::ODBC allowed that but you got rubbish so I've now
> disallowed references without AMAGIC.
>
> So now I'm only not sure why in the example provided what passing $obj
> (and not "$obj") is doing. it appears when it gets the sv_setsv above
> SvPV on phs->sv is 'Object' and it is address passed to SQLBindParameter
> but later when execute is called the contents of that address have
> changed. It seems every time you call SvPV on the copied scalar which is
> the object you get a different address and because DBD::ODBC binds at
> bind time and not execute time by execute time the original address has
> changed.

I thought it might be interesting to see what DBD::Oracle does.

I ran

use strict;
use DBI;
my $dbh =
DBI->connect("dbi:Oracle:", '', '');
my $obj = new Object();
my $sql = q(SELECT ? AS result from dual);
my $sth = $dbh->prepare($sql);

$sth->bind_param(1, $obj);
$sth->execute();

while (my $row = $sth->fetchrow_hashref()) {
   print $row->{'RESULT'}, "\n";
}

package Object;
use overload '""' => 'to_s';
sub new() { bless { }, shift };
sub to_s() { my $self = shift; ref($self); }

and it printed

Object



-- 
Charles Jardine - Computing Service, University of Cambridge
[email protected]    Tel: +44 1223 334506, Fax: +44 1223 334679