More problems with bound parameters in DBD::ODBC

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