Re: perl+varchar in informix and oracle
In part 1, 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.
>
> perl is 5.6
> IDS is 7.31
First of all, Informix distinguishes between a null value in a varchar
and a zero-length non-null varchar. Only Oracle thinks zero-length
strings are null automatically.
Secondly, DBD::Informix distinguishes between nulls (represented by
Perl's undef) and values (represented by strings of the appropriate length).
In part2, dcruncher4@aim.com wrote:
> In article <fohuuf$s1v$1$8300dec7@news.demon.co.uk>, Ben Thompson says...
>
>> I've done a fair bit of Perl DBI programming and never come across this
>> problem.
>
> can you test what I mentioned.
>
>> What method are you using for your fetch? Could you post a code snippet?
>
> my $i_sth = $i_dbh->prepare("select * from table");
> $i_sth->execute() ;
> while ( my @row_data = $i_sth->fetchrow_array() )
> {
> ...
> $row_data[3] returns '' and not ' '
> }
>
>
>> Your Perl version is old, are you using the latest Perl DBI? What about
>> DBD-Informix? Please post versions.
>
> DBD Informix is 2005.02
Can we also see your insert code?
Here's my test case and result...
Black JL: cat chk.pl
#!/bin/perl -w
use DBI;
use strict;
my $dbh = DBI->connect('dbi:Informix:stores','','',{ RaiseError => 1})
or die 'an awful death';
$dbh->do('create temp table t(i integer, v varchar(10))');
$dbh->do('insert into t values(0, "")');
$dbh->do('insert into t values(1, " ")');
$dbh->do('insert into t values(2, " ")');
my $sth = $dbh->prepare('select i, v from t order by i');
$sth->execute;
while (my @row = $sth->fetchrow_array)
{
printf "%d: <<%s>>\\n", $row[0], $row[1];
}
Black JL: perl chk.pl
0: <<>>
1: << >>
2: << >>
Black JL:
Please try this test case with your setup. If you don't get the results
I show, please upgrade your software until you do.
Solaris 10 (SPARC); Perl 5.10.0; DBI 1.601; DBD::Informix 2007.0914;
ESQL/C 3.00.FC2; IDS 11.10.FC1.
If you think you have a bug, why not create a nice simple test case like
that and report it through the documented channels?
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2007.0914 -- http://dbi.perl.org/
publictimestamp.org/ptb/PTB-2477 sha224 2008-02-09 03:00:05
67EED9F68A1E28E0CEECE759F3B65CDEBACA06EAE5F14E0DBE452C29