Re: DBD::Oracle: "" vs NULL, and a possible change to the ChopBlanks attribute

On Mon, Jan 21, 2002 at 10:52:06AM -0800, Jared.Still_at_radisys.com wrote:
> Tim,
>
> I've always chosen to just deal with the nulls rather than worry about
> padded strings or storing a single space. Personally I can't stand
> storing a single space to represent an empty string and prefer the null.
>
> Not all share my opinion obviously, but I've just gone along with
> the trinary logic of working with nulls whenever working with Oracle.

Personally I'm very happy with NULLs and trinary logic. I like NULLs, in their place. But there is a _world_ of difference between a NULL and an empty string.

But since we all know that here let's not start a long thread rehashing that old topic :)

> If I were to use other databases extensively and were concerned
> about writing portable code, that sentiment would likely change.
>
> The authors of modules that provide a generic interface to many
> different databases will no doubt thank you for this.

I hope so.

Certainly it, or something like it, will be important to me migrating from MySQL to Oracle (an ongoing background project).

Tim.

> Sent by: root_at_fatcity.com
> 01/20/02 03:15 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc:
> Subject: DBD::Oracle: "" vs NULL, and a possible change to the
>ChopBlanks attribute
>
>
> It's well know that Oracle#s state of the art database server can
> rarely tell the difference between an empty string and a NULL
> (I presume that's not changed recently, though I'd be very happy
> to be told otherwise).
>
> So people who don't want empty strings being stored as NULLs in
> their fields tend to use some specific non-empty string insead,
> a single space being very common.
>
> The DBI has an attribute called ChopBlanks that, when set true,
> enables the automatic removal of trailing spaces from fetched CHAR
> field data (which Oracle has 'helpfully' padded out to the declared
> fixed width).
>
> I'm considering extending the definition of ChopBlanks to include
> VARCHAR type fields. This has been requested several times over the
> years by DBI users. The change would neatly hide the use of a space
> to represent an empty string.
>
> I'd like to get some feedback on this idea from DBD::Oracle users.
>
> I'm especially interested in the risk of changing the behaviour of
> existing code. That would _only_ happen if you explicitly set
> ChopBlanks, and fetch VARCHAR data that has trailing spaces, and the
> removal of those spaces would change the behaviour of your application.
>
> An alternative approach would be to add a new private attribute
> that just translates a single space value into an empty value.
> That would be "safer" but less generically useful.
>
> Tim
>
> p.s. I've sent this to both oracle-l_at_fatcity.com and dbi-users_at_perl.org.
> Please delete at least one of these addresses when replying. Thanks.
>
> p.p.s. A corresponding mechanism to optionally automatically treat
> empty strings bound to placeholders as a single space will probably
> also be added to DBD::Oracle and enabled via private attribute.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Tim Bunce
> INET: Tim.Bunce_at_pobox.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Bunce
  INET: Tim.Bunce_at_pobox.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).