FAQ
Hi

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 );
}

Best regards
Knut Tvedten

Search Discussions

  • Steffen Goeldner at Sep 27, 2005 at 12:40 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedSep 22, '05 at 1:40p
activeSep 27, '05 at 12:40p
posts2
users2
websitedbi.perl.org

2 users in discussion

Steffen Goeldner: 1 post Knut Tvedten: 1 post

People

Translate

site design / logo © 2022 Grokbase