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

From: <Jared.Still_at_radisys.com>
Date: Mon, 21 Jan 2002 11:24:20 -0800
Message-ID: <F001.003F62DF.20020121105126@fatcity.com>

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.

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.

Jared

Tim Bunce <Tim.Bunce_at_pobox.com>
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:
INET: Jared.Still_at_radisys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------