Phil Oertel wrote:
>
> Does DBD::ADO in any way support null insertion placeholders? The following
> example code illustrates the problem:
>
> my $name = "";
> $sth = $dbh->prepare('INSERT INTO Staff (name) VALUES(?)');
> $sth->execute($name);
But "" isn't null, it's an empty string. We use undef to insert a null
value.
> That works fine under DBD::ODBC, but fails under DBD::ADO when
> accessing the same data source. Any ideas?
I agree that nulls don't appear to work with DBD::ADO placeholders. The
attached test script uses the sample Microsoft Access 'Northwind'
database. Using DBD::ODBC it fails on test 'c2', quite rightly because
the field doesn't permit empty strings. But using DBD::ADO it also fails
on test 'c3' - wrongly. This is the associated error:
Inserting 'c3',[undef]: DBD::ADO::st execute failed: Can't execute
statement 'INSERT INTO Shippers (CompanyName, Phone) VALUES(?,?)':
Lasterror: -2146824580: OLE exception from "ADODB.Command":
Parameter object is improperly defined. Inconsistent or incomplete
information was provided.
Win32::OLE(0.1502) error 0x800a0e7c
in METHOD/PROPERTYGET "Execute" at - line 18.
I'm running $DBD::ADO::VERSION 2.4_05
--
Simon Oliver
use strict;
use DBI;
my @test_data = (
['c1', '800123'],
['c2', ''],
['c3', undef],
);
my $dbh =
DBI->connect('dbi:ADO:Northwind','','',{RaiseError=>1,PrintError=>1})
or die DBI::errstr;
my $sth = $dbh->prepare('INSERT INTO Shippers (CompanyName, Phone)
VALUES(?,?)');
foreach my $data (@test_data) {
print "Inserting ",
join(',', map {defined($_) ? "'$_'" : '[undef]'} @$data), ': ';
eval { $sth->execute(@$data) };
print $@ ? 'Error' : 'Ok', "\n";
}
$dbh->disconnect;
