develooper Front page | | Postings from September 2012

More problems with bound parameters in DBD::ODBC

Thread Next
Martin J. Evans
September 4, 2012 03:28
More problems with bound parameters in DBD::ODBC
Message ID:
This issue cropped up because runrig posted a "DBI bind_param_inout trick" node ( 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

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;

while (my @arr = $sth->fetchrow_array) {
   print "@arr\n";

$hsh{bar} = 456;
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/ line 33.
Use of uninitialized value $arr[0] in join or string at /tmp/ line 39.

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?


Martin J. Evans
Easysoft Limited

Thread Next Perl Programming lists via nntp and http.
Comments to Ask Bjørn Hansen at | Group listing | About