On 16/08/12 16:13, Charles Jardine wrote:
On 16/08/12 14:00, Martin J. Evans wrote:
On 15/08/12 17:39, Tim Bunce wrote:
On Wed, Aug 15, 2012 at 04:14:52PM +0100, Martin J. Evans wrote:
I've just been given an rt
https://rt.cpan.org/Ticket/Display.html?id=78838 and am at a loss to
explain exactly what is happening. I wonder if anyone can help?
Some background:
DBI says for bind_param:
"The bind_param method takes a copy of $bind_value and associates it
(binds it) with a placeholder"
As far as I am aware DBD::ODBC does not copy the scalar given to it -
so perhaps DBI does this. The problem I'm seeing in the provided
example is the pointer passed to ODBC's SQLBindParameter at the time
bind_param is called no longer points to a valid string when execute
is called. However, changing the call to bind_param to pass $obj as
"$obj" appears to fix the problem.
The driver should take a copy of the value when bind_param is called.
Perhaps stored by the ParamValues attribute.
(bind_param_inout takes a reference instead and binds at execute.)
Can anyone say if DBD::ODBC should
work with either example and explain what might be happening here:
use DBI;
my $dbh =
DBI->connect("dbi:ODBC:DSN=xxx;UID=xx;PWD=yy;MARS_Connection=No;");
my $obj = new Object();
my $sql = q(SELECT ? AS result);
my $sth = $dbh->prepare($sql);
# interesting - is the following use of $obj really as a string?
# in the call to bind_param here, DBD::ODBC binds the pv ptr of the
scalar
# it is given in a SQLBindParameter call but by the time execute is
called
# the string at that address is no longer valid. I kind of expect
that as
# what to_s returns is only required in the bind_param statement and yet
# DBI says "bind_param takes a copy".
# However if the following is changed to "$obj" it works
$sth->bind_param(1, $obj);
$sth->execute();
while (my $row = $sth->fetchrow_hashref()) {
print $row->{'result'}, "\n";
}
package Object;
use overload '""' => 'to_s';
sub new() { bless { }, shift };
sub to_s() { my $self = shift; ref($self); }
The driver needs to make a copy and ensure that 'get magic' is called.
SvPV will call get magic but won't make a copy. Since you need an SV for
the ParamValues attribute maybe newSVsv() followed by SvPOK_only_UTF8()
on the copy would be a suitable incantation.
Tim.
It appears dbd_bind_ph does this:
/* newvalue is the scalar param passed in to dbd_bind_ph */
/* phs is a structure to hold the param details */
if (!is_inout) { /* normal bind to take a (new) copy of current
value */
if (phs->sv == &PL_sv_undef) /* (first time bind) */
phs->sv = newSV(0);
sv_setsv(phs->sv, newvalue);
} else if (newvalue != phs->sv) {
if (phs->sv)
SvREFCNT_dec(phs->sv);
phs->sv = SvREFCNT_inc(newvalue); /* point to live var */
}
so as far as I can see a copy is taken but I don't think sv_setsv does
any magic. Similar code exists in DBD::Oracle and a number of other DBDs
I looked at. DBD::ODBC does use SvPV later so I'm guessing that is where
the magic happens and the scalar is stringified.
This made me wonder what happens if you pass a reference without any
AMAGIC and DBD::ODBC allowed that but you got rubbish so I've now
disallowed references without AMAGIC.
So now I'm only not sure why in the example provided what passing $obj
(and not "$obj") is doing. it appears when it gets the sv_setsv above
SvPV on phs->sv is 'Object' and it is address passed to SQLBindParameter
but later when execute is called the contents of that address have
changed. It seems every time you call SvPV on the copied scalar which is
the object you get a different address and because DBD::ODBC binds at
bind time and not execute time by execute time the original address has
changed.
I thought it might be interesting to see what DBD::Oracle does.
I ran
use strict;
use DBI;
my $dbh =
DBI->connect("dbi:Oracle:", '', '');
my $obj = new Object();
my $sql = q(SELECT ? AS result from dual);
my $sth = $dbh->prepare($sql);
$sth->bind_param(1, $obj);
$sth->execute();
while (my $row = $sth->fetchrow_hashref()) {
print $row->{'RESULT'}, "\n";
}
package Object;
use overload '""' => 'to_s';
sub new() { bless { }, shift };
sub to_s() { my $self = shift; ref($self); }
and it printed
Object
Because DBD::Oracle does not bind the parameters until execute time whereas
DBD::ODBC binds them at bind_param time. If I change DBD::ODBC to bind at
execute time (there is an internal flag defer_bind which is no longer used) I
get the same result. I'm trying to understand why this makes a difference here.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
