Re: perl+varchar in informix and oracle

On Fri, 2008-02-08 at 07:33 -0800, dcruncher4@aim.com wrote: > No I am not starting a flame war here. > > I found something strange while using perl dbi. > > We have some rows in a varchar(80) not null column, where the user > has entered a single space to beat the not-null constraint. While > fetching it via Perl DBI, the single space gets truncated and > perl gets '' in the $ variable. The only way to distinguish that > with a null varchar value is that the $ variable is set to defined > in case of single space and undefined in case of NULL. > > Oracle, otoh sends it back to perl $ variable as a single space > data only, which is correct. > > The same data when unloaded from dbaccess puts a single space > correctly. > > So is this a bug with Perl DBI. This is not a Perl problem per se, it has to do with Informix's traditional space padding of character data. In varchars, this leads to the schizophrenic behavior that the database knows how many trailing spaces there are, but it doesn't care. For many purposes, an empty string is equivalent to a string containing a single space, or any number of spaces for that matter. Running the following script in dbaccess illustrates this schizophrenic behavior: ################################################## create temp table tmp1 ( x varchar(80), description varchar(80) ); insert into tmp1 values ("", "empty string"); insert into tmp1 values (" ", "single space"); insert into tmp1 values (" ", "two spaces"); select * from tmp1 where x = ""; select * from tmp1 where x = " "; select * from tmp1 where x = " ";select length(x), description from tmp1; select "<"||x||">", description from tmp1; unload to "tmp1.unl" select * from tmp1;################################################## If you run this, you will see that "", " ", and " " are treated very much the same. The only places where a difference is evident is in the case where you append something to the column and in the unload file. Perl simply embodies the "we don't care how many trailing spaces there are" semantics by clipping any trailing spaces. Python with InformixDB does the same, by the way, and in the almost three years of me maintaining that module, I have not yet been presented with a use case for changing that behavior. Now, it seems to me that you don't care about the trailing spaces, either. It looks like you only need to distinguish an empty string (or equivalent) from a null value. As you said, Perl makes this distinction by setting the host variable to "undefined". (Python has the object None for this purpose). Hope this helps, -- Carsten Haese http://informixdb.sourceforge.net