truncated data after INSERT with undef in DBI - bug?

From: iain (irhm_at_clara.net)
Date: 03/17/04 

Date: Wed, 17 Mar 2004 18:00:27 -0000

With DBI and SQL Server ODBC driver I have prepared an INSERT statement with
placeholders. But if the first execute has a undef (aka NULL), the execute
for the following row is truncated to just the first character for the same
field.

Has anyone else found this, or got a workaround? Is it a problem with DBI,
the DBD::ODBC driver or the SQL Server ODBC driver or SQL Server itself?
Is there a fix?

The code below demonstrates the problem.

Any ideas welcome
thanks, Iain

use DBI;
use strict;
my $dbserver="homer"; my $dbdatabase="Marshali"; my $dbWinAuth=1;
my $dsn="driver={SQL
Server};SERVER=$dbserver;DATABASE=$dbdatabase;trusted_connection=yes";
my $dbh = DBI->connect("dbi:ODBC:$dsn") or die "cannot connect to database:
$DBI::errstr \n";
$dbh->{AutoCommit}=1;
$dbh->do("DROP TABLE testbug");
$dbh->do("CREATE TABLE testbug (
         col1 varchar(10) NOT NULL,
         col2 varchar(5) NULL)");
my $sth = $dbh->prepare("insert into testbug (col1, col2) values (?, ?)")
     or die "Could not prepare sql : ".$DBI::errstr."\n";
# note 1 - works if this included here: $sth->execute("K0","ABC_0");
$sth->execute("K1",undef);
$sth->execute("K2","ABC_2"); # col 2 gets truncated to just one char
$sth->execute("K3","ABC_3");
my $sqlHdl = $dbh->prepare("select * from testbug order by Col1");
$sqlHdl->execute;
$sqlHdl->dump_results;
$dbh->disconnect;

Output obtained is:
'K1', undef
'K2', 'A'
'K3', 'ABC_3'
3 rows

i.e. the second record is only 'A' and should be 'ABC_2'

If the line labelled "Note 1" is included, the output is (correctly):

'K0', 'ABC_0'
'K1', undef
'K2', 'ABC_2'
'K3', 'ABC_3'
4 rows

Relevant Pages