FW: Inserting spaces into a NOT NULL column

[Kokarski, Anton]

-----Original Message-----
From: Kokarski, Anton 
Sent: Tuesday, February 05, 2002 11:00 AM
To: '[EMAIL PROTECTED]'
Subject: RE: Inserting spaces into a NOT NULL column


Well an easy way woult be change the column to null and insert null where
your variable is all spaces.

on the other hand that column is set to be not null for a reason.  You need
to look at it from design point of view as well.

Good luck,
Anton

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 05, 2002 10:57 AM
To: [EMAIL PROTECTED]
Subject: Inserting spaces into a NOT NULL column


Here's my problem:
I am interfacing an Oracle 8i system with DBI.
I want to insert a record into a table that I got from a file.  The
column that I want to insert into the table is all spaces.  When DBI
passes the insert statement to Oracle I get an error saying 'cannot
insert NULL into' error.  However when I call the quote method on the
variable I get an 'inserted value too large for column' error.
>From checking the dbitrace log file I see that the value passed to
oracle is '<data>'.  It seems that the field is counting the quotes of
the field when computing the field size.
The only solution I have come up with is to do a search/replace on
spaces and put another character in place of them.  However, a field of
all spaces is a valid data condition for my application and needs to be
there.

code:

$sth = $DBH->prepare("INSERT INTO $table VALUES(?, ?, ?, ?, ?, ?, ?,
?)") or die log_message("ERROR: Prepare: " . $DBH->errstr());
while(<INFILE>) {
        # foreach used to quote all space only fields.
        # I've tried it with and without this giving the two errors
above
        foreach $item (@data) {
                if ($item =~ /^\s+$/) {
                        $item = $DBH->quote($item);
                }
        }
        @data = split /$DELIMITER/, $_;
        $sth->execute(@data) or die log_message("ERROR executing insert:
" . $DBH->errstr());
}
        
If anyone has any ideas on how to solve this problem let me know.

Thanks,
Gordon