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.
> 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.
> 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()).
> 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.
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.
