sybperl-l Archive

From: "Avis, Ed" <avised at kbcfp dot com>
Subject: Different result from convert(varchar(255), a) and convert(a)
Date: Nov 24 2003 5:18PM

When selecting a 'real' column you get a different string value in
Perl from 'select convert(varchar(255), a)' and 'select a':

    $dbh->{AutoCommit} = 1;
    $dbh->do('create table #a (a real not null)');
    $dbh->do('insert into #a (a) values (.51645678281784058)');
    my $sql = "select convert(varchar(255), a), a from #a";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my @r = $sth->fetchrow_array()) {
        print "@r\n";
    }
    disconnect $dbh;

This prints, on my system:

.51645678281784058 0.516456782817841

The trailing zero doesn't matter but the loss of precision is bad.  I
know that 'real' is an approximate numeric type in Sybase but here
Perl is giving me less precision than Sybase offers.  It's
understandable that if you perform arithmetic operations on the value
it gets converted to a float and some accuracy is lost.  But if you
just treat it as a string I'd expect the database value to be
untouched.  (Suppose for example you were using some bignum package
and initializing a bignum from the value selected.)

Since in Perl you can read in a long number as a string and it doesn't
lose any accuracy until and unless you start doing arithmetic on it,
selecting a real column should behave the same way.  Or is this not
possible with the Sybase libraries?

I use DBD::Sybase 1.01, DBI 1.38, perl 5.8.0, OCS 12.5, Red Hat 8.

-- 
Ed Avis