RE: Inserting spaces into a NOT NULL column

[Kong, Alan]

I think there is something to do with quote place holder on your following
statement.

Oracle will treat the statement like "Insert into $table values (A,      ,
B, C, D, E, F, G)" if the 2nd value inside the () is spaces, then it will be
passed as null value this case.

If you add single quotes to every of the fields, it may be working ok.  I
didn't try that, but it should work.

$sth = $DBH->prepare("INSERT INTO $table VALUES('?', '?',' ?', '?',' ?','
?', '?','?')")

good luck.
Alan

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 05, 2002 1:57 PM
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