Re: Strange bind_param issue

On 16/08/12 16:37, Martin J. Evans wrote:
> On 16/08/12 16:13, Charles Jardine wrote:

>> 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.

The following test re-blesses $obj between the bind and the execute.
It still prints 'Object' even though, at the time of the execute,
$obj is of class Subject. This demonstrates that DBD::Oracle does not
just copy the reference value it has been given. If must do the actual
stringifcation as bind time.

use strict;
use DBI;

my $obj = Object->new;

my $dbh = DBI->connect('dbi:Oracle:', '', '');
my $sth = $dbh->prepare("select ? from dual");
$sth->bind_param(1, $obj);

bless $obj, 'Subject';

$sth->execute;
print scalar $sth->fetchrow_array(), "\n";

package Object;
use overload '""' => 'to_s';
sub new() { bless { }, shift };
sub to_s() { my $self = shift; ref($self); }

package Subject;
use overload '""' => 'to_s';
sub new() { bless { }, shift };
sub to_s() { my $self = shift; ref($self); }




-- 
Charles Jardine - Computing Service, University of Cambridge
[email protected]    Tel: +44 1223 334506, Fax: +44 1223 334679