Re: Possible bug in DBD-ADO 2.94 bind_param

[Steffen Goeldner]

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

  <http://www.xray.mpe.mpg.de/mailing-lists/dbi/2004-09/thrd3.html#00235>

and documented in the latest release:

  <http://search.cpan.org/~sgoeldner/DBD-ADO-2.94/lib/DBD/ADO.pm#ADO_providers>

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.

  <http://msdn.microsoft.com/library/en-us/ado270/htm/mdprosize.asp?frame=no>

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