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 $dbh = DBI->connect;
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 );
}
# Set constants...
$hsh{foo} = 'abc';
# Set changing values
$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";
}
$dbh->disconnect();
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.
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.
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.
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?
Thanks.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
