Possible bug in DBD-ADO 2.94 bind_param

Hi

Using prepared statements doesn't work correctly for driver DBD-ADO
version 2.94 on MS SQL Server 2000 (Provider=3Dsqloledb).

Given CREATE TABLE t1(c1 VARCHAR(255))

The code snippet triggers the error:
....
my $stmt =3D $dbh->prepare('INSERT INTO t1(c1) VALUES(?)');
$stmt->bind_param(1, 'A string');
$stmt->execute;
$stmt->bind_param(1, 'Another string');
$stmt->execute;
....

After execution the table t1 will have to two rows 'A string' and
'Another '. Here the string in the last row has been truncated. The
reason for this is that the first call to bind_param changes the Size
property of the parameter from 255 to 8 (length of 'A string'). This
again triggers generation of a new prepared statement in SQL server with
an input parameter only using the first 8 characters of the string. This
happens only during the first execute call, hence the later truncation.

I've included a workaround here, but it's not tested extensively.

*** ADO.pm Thu Sep 22 15:04:55 2005
--- ADO_fix.pm Thu Sep 22 15:04:47 2005
***************
*** 1026,1032 ****
$sth->trace_msg(" -- Binary: $i->{Type} $i->{Size}\n", 5 );
}
else {
! $i->{Size} =3D length $value; # $value? length $value:
$ado_type->[2];
$i->{Value} =3D $value; # $value if $value;
$sth->trace_msg(" -- Type : $i->{Type} $i->{Size}\n", 5 );
}
--- 1026,1032 ----
$sth->trace_msg(" -- Binary: $i->{Type} $i->{Size}\n", 5 );
}
else {
! # $i->{Size} =3D length $value; # $value? length $value:
$ado_type->[2];
$i->{Value} =3D $value; # $value if $value;
$sth->trace_msg(" -- Type : $i->{Type} $i->{Size}\n", 5 );
}

Best regards
Knut Tvedten

Re: Possible bug in DBD-ADO 2.94 bind_param

Knut Tvedten wrote:

> Using prepared statements doesn't work correctly for driver DBD-ADO
> version 2.94 on MS SQL Server 2000 (Provider=sqloledb).
>
> Given CREATE TABLE t1(c1 VARCHAR(255))
>
> The code snippet triggers the error:
> ...
> my $stmt = $dbh->prepare('INSERT INTO t1(c1) VALUES(?)');
> $stmt->bind_param(1, 'A string');
> $stmt->execute;
> $stmt->bind_param(1, 'Another string');
> $stmt->execute;
> ...
>
> After execution the table t1 will have to two rows 'A string' and
> 'Another '. Here the string in the last row has been truncated. The
> reason for this is that the first call to bind_param changes the Size
> property of the parameter from 255 to 8 (length of 'A string'). This
> again triggers generation of a new prepared statement in SQL server with
> an input parameter only using the first 8 characters of the string. This
> happens only during the first execute call, hence the later truncation.
>
> I've included a workaround here, but it's not tested extensively.
>
> *** ADO.pm Thu Sep 22 15:04:55 2005
> --- ADO_fix.pm Thu Sep 22 15:04:47 2005
> ***************
> *** 1026,1032 ****
> $sth->trace_msg(" -- Binary: $i->{Type} $i->{Size}\n", 5 );
> }
> else {
> ! $i->{Size} = length $value; # $value? length $value:
> $ado_type->[2];
> $i->{Value} = $value; # $value if $value;
> $sth->trace_msg(" -- Type : $i->{Type} $i->{Size}\n", 5 );
> }
> --- 1026,1032 ----
> $sth->trace_msg(" -- Binary: $i->{Type} $i->{Size}\n", 5 );
> }
> else {
> ! # $i->{Size} = length $value; # $value? length $value:
> $ado_type->[2];
> $i->{Value} = $value; # $value if $value;
> $sth->trace_msg(" -- Type : $i->{Type} $i->{Size}\n", 5 );
> }

Thanks for the report. This problem is known, e.g.:



and documented in the latest release:



Your workaround doesn't work for other ADO provider. The ADO docs say:

To prevent an error, you should explicitly set the Size property for
these parameters before executing the command.



As a workaround, I'll introduce an 'ado_size' attribute in the next
release of DBD::ADO, something like:

$i->{Size} = defined $attr->{ado_size} ? $attr->{ado_size} : length $value;


Steffen