But that will only work with ORACLE, right? or some other drivers have similar problems?
Anton -----Original Message----- From: Fannin, David P. [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 05, 2002 11:29 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Inserting spaces into a NOT NULL column I had a similar problem before and was forwarded the tidbit below. I can't tell from the description of your problem if it is exactly the same as what I was experiencing. I hope this helps. This tip courtesy of Tim Bunce: use DBI qw(:sql_types); $sth->bind_param(1, $value, SQL_CHAR); The problem is that OCI "smartly" strips trailing blanks off a bind value of type VARCHAR2. Explicitly casting it to a CHAR datatype avoids this "intelligence". -dpf- ----------------------- David P. Fannin Database Administrator [EMAIL PROTECTED] UM-Rolla Computing and Information Services FAX (573) 341-4216 URL http://www.umr.edu/~dpf PHONE (573) 341-4841 ----------------------- -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 05, 2002 12: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
