DBD::Informix Problem
Hello,
I am facing a peculiar problem in DBD::Informix module.
My Database table has 4 column and all of them forms a primary key.
Database: stores
Table: userdata
Column name Type Nulls
name char(32) no
city char(20) no
state char(20) no
zip char(20) no
Data file: data
Dominic|Savannah|GA|31326|
David|Macon||12432|
When I load this data using dbload utility, it gives an error like:
In INSERT statement number 1 of raw data file data.
Row number 2 is bad.
David|Macon||12432|
Cannot insert a null into column (userdata.state).
This is Right. But When I tried to insert the same using DBD::Informix
module, it inserts the row without displaying any error.
Here is the Perlscript
##############################
#!/usr/local/bin/perl
use strict;
use DBI;
$|++;
my $Sheet = "data";
my %FileDataHash = ();
my $ErrorRecs = 0;
my ($data_source,$username,$password) = ("DBI:Informix:stores", "", "");
my $dbh = DBI->connect($data_source, $username, $password, {
PrintError => 0
}) || die "$!\\n";
my ( @Tmp, $Unique, $Recs, $sth, $LineCnt );
&CollectSheetFile();
&UpdateSheet();
$dbh->disconnect;
exit 0;
sub CollectSheetFile {
$Recs = 0;
$LineCnt = 0;
%FileDataHash = ();
if( open( SHEET, "<$Sheet" ) ) {
while( <SHEET> ) {
$LineCnt++;
$_ =~ s/\\n$//;
if( !$FileDataHash{ $_ } ) {
$Recs ++;
$FileDataHash{ $_ } = 1;
}
}
close SHEET;
printf ("Records in sheet File : % 5d\\n", $Recs );
}
else {
print "File open Error:$Sheet:$!\\n";
$dbh->disconnect;
exit 0;
}
return 0;
}
sub UpdateSheet {
my( $SQL, $New, $Line );
my ($Name, $City, $State, $Zip );
$New = $ErrorRecs = 0;
while( ($Unique, $Line) = each( %FileDataHash ) ) {
@Tmp = split( '\\|', $Unique );
($Name, $City, $State, $Zip ) = @Tmp;
$SQL = "INSERT into userdata (name, city, state, zip) values
('$Name', '$City', '$State', '$Zip')";
print "AAAA:SHEET:NEW: $SQL\\n";
$New++;
&ProcessSQL( $SQL );
}
if($New) {
print "SHEET: Records Inserted: " . ($New - $ErrorRecs ) .
"\\tFailed Recs: $ErrorRecs\\n";
}
return 0;
}
sub ProcessSQL {
my( $SQL, $lbl, $Cnt ) = @_;
$sth = $dbh->prepare( qq{$SQL});
if( $DBI::errstr ) {
$ErrorRecs++;
print "$SQL\\nCannot do SELECT statements: $DBI::errstr\\n";
}
else {
$sth->execute();
if( $DBI::errstr ) {
$ErrorRecs++;
print "$SQL\\nCan't execute statements: $DBI::errstr\\n";
}
$sth->finish;
}
return 0;
}
##############################
After inserting the records using DBD::Informix module, I tried to
export the table using dbaccess utility.
I am getting
Dominic|Savannah|GA|31326|
David|Macon| |12432|
^^^
Note the space, instead of null.
Please help me to isolate this problem,
Thanks and Regards
Dominic
Savannah, GA
Sent via Deja.com http://www.deja.com/
Before you buy.
Dominic Prakash wrote: > I am facing a peculiar problem in DBD::Informix module. > >[...snip...] This question was also sent to me direct by email, so I've responded by email. Dominic's problem is related to how Perl and DBI handle NULLs, and therefore how DBD::Informix handles nulls. I've pointed him in the right direction -- and at the Cheetah book from O'Reilly: Programming the Perl DBI by Alligator Descartes and Tim Bunce. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"