Re: DBD::Informix Problem
On Mon, 17 Jul 2000, Dominic Prakash wrote:
>INFORMIX-ESQL Version 9.30.UC1
>DBI Version 1.13 (and 1.14)
>DBD::Informix Version 0.62 (and 1.00.PC1)
>OS: IBM AIX 4.3.x
>
>I am facing a peculiar problem in DBD::Informix module.
>[...big snip...]
Dominic also posted this question to comp.databases.informix and
to myself, Tim Bunce and Alligator Descartes, making three separate
requests. I'd like to say 'Patience is a virtue". I've responded
in detail in the first request I saw. I responded in the c.d.i
forum with:
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.
And I received a bounce from gulfaero.com with the response sent direct
to Dominic, which is a really good way to make me frustrated. It's bad
enough asking for and getting personal attention; then to have that
personal attention made redundant is the height of irksomeness. So, I
suppose that I have to repost the complete answer so that if, perchance,
Dominic has a working email address on the DBI list, it will get
through. And if he doesn't, he'll have to poke around the DBI mailing
list archives.
========================================================================
On Mon, 17 Jul 2000 dominic.prakesh@gulfaero.com wrote:
>I am facing a peculiar problem in DBD::Informix module.
Not peculiar; it works just as it is supposed to work.
>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
Add -w; that would give you some hints.
>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')";
$State is an empty string; this is not the same as NULL. An empty
$string is equivalent to a string of blanks. If you want null, you have
$to either say NULL (out of quotes), or use a placeholder and supply an
$undef as the value.
Note that embedding values directly in the statement as you are doing is
error-prone at best. Think what happens with the name O'Reilly!
Time to read the Cheetah book.
$SQL = "INSERT INTO UserData(Name, City, State, Zip) VALUES(?, ?, ?, ?)";
undef $State if ($State eq '');
$sth = $dbh->prepare($SQL) or die "horribly";
$sth->execute($Name, $City, $State, $Zip);
Or:
undef $State if ($State eq '');
undef $City if ($City eq '');
undef $Name if ($Name eq '');
undef $Zip if ($Zip eq '');
$nval = $dbh->quote($Name);
$cval = $dbh->quote($City);
$sval = $sbh->quote($State);
$zval = $dbh->quote($Zip);
$dbh->do("INSERT INTO UserData(Name, City, State, Zip) VALUES($nval, $cval, $sval, $zval)";
Note that the quote method takes care of NULL for undef values.
> 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.
That's the correct behaviour.
>Please help me to isolate this problem,
>
>Thanks and Regards
>
>Dominic
>Savannah, GA
========================================================================
--
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!"