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
Re: AnyData patches
On Sun, 02 Sep 2012 08:56:04 +1000, Sven Dowideit svendowid...@home.org.au wrote: Heya I used git send-emails to post some patches to rehs...@cpan.org and this list, but I suspect they haven't made it due to my server setup before I send them again, I thought I'd test, and ask if anyone has received them :) Does all of this has some git repo we could clone so we can run some (more) tests on it? -- H.Merijn Brand http://tux.nl Perl Monger http://amsterdam.pm.org/ using perl5.00307 .. 5.14 porting perl5 on HP-UX, AIX, and openSUSE http://mirrors.develooper.com/hpux/http://www.test-smoke.org/ http://qa.perl.org http://www.goldmark.org/jeff/stupid-disclaimers/
Re: More problems with bound parameters in DBD::ODBC
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.
Re: AnyData patches
yup :) http://github.com/SvenDowideit/AnyData Sven On 04/09/12 21:05, H.Merijn Brand wrote: On Sun, 02 Sep 2012 08:56:04 +1000, Sven Dowideitsvendowid...@home.org.au wrote: Heya I used git send-emails to post some patches to rehs...@cpan.org and this list, but I suspect they haven't made it due to my server setup before I send them again, I thought I'd test, and ask if anyone has received them :) Does all of this has some git repo we could clone so we can run some (more) tests on it?
