prepared statement: automatically removed trailing spaces

From: Frank Steinhauer (Frank.Steinhauer.External_at_eads.com)
Date: 11/24/04 

To: "'dbi-users@perl.org'" <dbi-users@perl.org>
Date: Wed, 24 Nov 2004 13:55:43 +0100

Hello,

I had for some days a problem using a prepared statement. One value was
simply a space, the row had a NOT NULL constraint, and i always got the
error message "... cannot insert NULL into ... (DBD ERROR: OCIStmtExecute)".
Well, after more than two days trying to specify the problem and then to fix
it, I found following in the newsgroup perl.dbi.users:

From: Michael A Chase (mchase@ix.netcom.com)
Subject: Re: inserting a single space into a non NULL field aborts with
error
Newsgroups: perl.dbi.users
...
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.
...
See
http://groups.google.com/groups?hl=en&lr=&threadm=E3A8A8F741B2D611ACA800508B
6F33D4381467%40chitmd03.nt.il.nbgfn.com&rnum=2&prev=/groups%3Fq%3Dbind_param
%2Bspace%26hl%3Den%26lr%3D%26selm%3DE3A8A8F741B2D611ACA800508B6F33D4381467%2
540chitmd03.nt.il.nbgfn.com%26rnum%3D2

Why there is no hint about something important like that in the
documentation of DBI and DBD-Oracle? I think that's a really important
issue!!!

Regards,

Frank

PS:
I'm using DBI 1.30 and Oracle 9i, but I think this problem is version
immanent (there's no hint in the DBI or DBD-Oracle releasenotes/changes
about something like this)

Relevant Pages

  • RE: oracle win2k ORA-1222 problems
    ... AS does not provide a package, but you can and should install both DBI and DBD::Oracle from ftp.esoftmatic.com. ... Oracle is listed as "failed" if I recall correctly. ... Data Source is DBI:Oracle:Space ...
    (perl.dbi.users)
  • RE: DBI problem
    ... I had Oracle 8 all along, I upgraded to ActiveState Perl 5.8.7 from ... DBD 1.16 and DBI 1.48. ... Did you install DBD::Oracle? ...
    (perl.dbi.users)
  • RE: (Fwd) DBD:: Oracle Problems
    ... The error is being thrown inside the pl/sql procedure itself not the DBI! ... Subject: DBD:: Oracle Problems ... are receiving the following error when we execute any stored procedures. ... This email has been scanned by the MessageLabs Email Security System. ...
    (perl.dbi.users)
  • Re: OCIEnvNlsCreate error with DBD::Oracle 1.21 and Oracle 10g client in Cygwin
    ... If not: you could reinstall DBI and DBD::Oracle and make sure that your Oracle environment is set correctly. ... If you have multiple Oracle installations: set your $ENVusing the ORACLE_HOME with which you compiled your DBD::Oracle. ... If you have multiple perl installations (with DBI, DBD::Oracle) you may have to set the correct path to perl (first line of your script). ...
    (perl.dbi.users)
  • RE: Unix: Oracle User Identified Externally
    ... Subject: Unix: Oracle User Identified Externally ... use DBI; ... intended recipient, please be aware that any disclosure, copying, distribution ... sender of the delivery error by replying to this message, or notify us by ...
    (perl.dbi.users)