On Wed, 13 Nov 2002 16:44:27 -0600 [EMAIL PROTECTED] wrote: > You need to bind the item to a SQL_CHAR type or DBI will try to insert a > null into the table. I ran into this problem awhile back and it about > drove me insane trying to figure it out. > > try this: > $sth->bind_parm($dbindex++, $pub, SQL_CHAR);
> -----Original Message----- > From: Duin, Harry [mailto:harry.duin@;bankofamerica.com] > Sent: Wednesday, November 13, 2002 4:15 PM > To: [EMAIL PROTECTED] > Subject: inserting a single space into a non NULL field aborts with > error > > > I have an Oracle field that has this constraint: > > PUBLISHERID VARCHAR2 (100) DEFAULT ' ' NOT NULL, > > In Perl I call bind_param as such: > > $sth->bind_param($dbindex++, $pub); > > where $pub is set to a single space. > > Oracle reports this error: > > DBD::Oracle::st execute failed: ORA-01400: cannot insert NULL into > ("RPASS"."DOCUMENT"."PUBLISHERID") (DBD ERROR: OCIStmtExecute) at > mk_docs.pl > line 1249. > > When I change $pub to a string with valid chars, then the error goes away. > Anyone has any suggestions? I did verify that I can insert a single space > via sqlplus. What actually happens is that the default binding for DBD::Oracle is SQL_VARCHAR. When Oracle OCI sees that type, it automatically strips trailing spaces leaving '' if that's all there was. Then '' is interpreted as a NULL by Oracle. Binding as SQL_CHAR prevents the space stripping. I'm always nervous about making distinctions that aren't visible. You may be better off making PUBLISHERID nullable if you can. -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
