Re: Strange bind_param issue

On 16/08/12 16:13, Charles Jardine wrote:
> 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
>
>
>

Because DBD::Oracle does not bind the parameters until execute time whereas DBD::ODBC binds them at bind_param time. If I change DBD::ODBC to bind at execute time (there is an internal flag defer_bind which is no longer used) I get the same result. I'm trying to understand why this makes a difference here.

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