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
