Re: More problems with bound parameters in DBD::ODBC

On 04/09/12 23:15, Tim Bunce wrote:
> On Tue, Sep 04, 2012 at 11:28:17AM +0100, Martin J. Evans wrote:
>> This issue cropped up because runrig posted a "DBI bind_param_inout trick" node (http://perlmonks.org/?node_id=989136) on perl monks which when I ran through DBD::ODBC did not work. The code is basically:
>>
>> my @cols = qw(foo bar);
>> my $sql = <<SQL;
>> SELECT :foo, :bar
>> SQL
>>
>> my $sth = $dbh->prepare($sql);
>> my %hsh;
>> for (@cols) {
>>    $sth->bind_param_inout( "$_" => \$hsh{$_}, 0 );
>> }
>> $hsh{foo} = 'abc';
>> $hsh{bar} = 123;
>> $sth->execute();
>> while (my @arr = $sth->fetchrow_array) {
>>    print "@arr\n";
>> }
>> $hsh{bar} = 456;
>> $sth->execute();
>> while (my @arr = $sth->fetchrow_array) {
>>    print "@arr\n";
>> }
>>
>> which should output:
>> abc 123
>> abc 456
>>
>> but actually outputs:
>> Use of uninitialized value $arr[0] in join or string at /tmp/x.pl line 33.
>>   123
>> Use of uninitialized value $arr[0] in join or string at /tmp/x.pl line 39.
>>   456
>>
>> The problem is that DBD::ODBC binds the parameter initially when bind_param_inout is called then when execute is called it needs to determine if the parameters need to be rebound (if something significant has changed). It uses the following test:
>>
>> if (SvTYPE(phs->sv) != phs->sv_type /* has the type changed? */
>>      || (SvOK(phs->sv) && !SvPOK(phs->sv)) /* is there still a string? */
>>      || (SvPVX(phs->sv) != phs->sv_buf) /* has the string buffer moved? */
>>     ) {
>>        /* rebind the parameter */
>>       }
>>
>> I have some issues with this (in addition to it not working):
>>
>> 1. DBD::ODBC always calls SvUPGRADE(phs->sv, SVt_PVNV) on output parameters so the type is unlikely to change.
>
> Anything can happn to the sv between the bind_param_inout and the execute.

Yes, I realised that but in the majority of cases (all of them in the test suite for a start) the test always ends up is if 6 != 6 (Svt_PVNV) - more coverage required.

>> 2. DBD::ODBC always calls SvGROW on output parameters to grow them to
>> 28 chrs (some magic about 28 I don't know) to avoid mutation in most
>> cases. As a result, if you change the test code so the first param is
>> bigger than 28 chrs it works.
>
> I vaguely recall some magic about the value, but not the specifics.

It just meant it looked a little confusing when strings > 28 chrs worked and ones < 28 did not.

  
>> 3. I don't understand what the (SvOK(phs->sv) && !SvPOK(phs->sv)) is
>> for. I know what those macros do but not why that test is present. Any
>> ideas? It is in other DBDs too.
>
> That's saying "rebind if the sv is defined but doesn't have a string".
> I think the "not defined" case is (or should be) handled elsewhere, so
> the test is mainly to check that the sv still contains a string.
> (Before then testing SvPVX()).

Of course, been away on holiday for too long and not back in the swing of it properly.

>> 4. I'm unsure how to make this work although if I simply add a test to
>> say has SvOK(phs->sv) changed since binding it makes this example
>> work. Anyone any ideas if this is sufficient?
>
> Something along those lines should be fine. Looking at DBD::Oracle,
> in which I probably first wrote that code (perhaps around 1996 :-)
> I see a few lines higher up:
>
>      /* is the value a null? */
>      phs->indp = (SvOK(sv)) ? 0 : -1;
>
> so I presume that oracle 'indicator parameter' handles the 'is currently
> null' case, so the if() statement only has to deal with the not-null case.
> Oracle's need for rebinding probably differs in subtle ways from ODBC's.

In this case ODBC has already bound it with an indicator saying is NULL and needs to rebind it. I changed it to test if SvOK had changed since the last bind.

> This particular situation is transitioning from an undef to defined.
> And this particular kind of undef has SvTYPE == 0, in case that's relevant.
>
> Hope that helps.
>
> Tim.
>

Thanks for the help.

This is released on the CPAN as 1.40_1 + some other fixes.

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