Re: Strange bind_param issue

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.