Re: Rounding errors storing numbers in MySQL text fields using DBI

[Robbie Armour]

Hello again - thanks for the speedy answers (and for DBI, more to the point) 
.  Please find below some more detail on this:

# Misc info ======================
Perl 5.8.0
DBD/mysql.pm version 2.1020
DBI.pm version 1.32
# code ===========================
$dbh->trace(2, "./tracefile");
$qst = $dbh->prepare("insert into test (t1) values (?)");

$qst->execute(0.4444);
$qst->execute('0.3333');
$dbh->disconnect();

# tracefile ======================

$ cat tracefile
   DBI::db=HASH(0x275124) trace level set to 2 in DBI 1.32-ithread
   -> prepare for DBD::mysql::db (DBI::db=HASH(0x27519c)~0x275124 'insert 
into test (t1) values (?)') thr#11b618
Setting mysql_use_result to 0
   <- prepare= DBI::st=HASH(0x275298) at t1.pl line 23
   -> execute for DBD::mysql::st (DBI::st=HASH(0x275298)~0x2752e0 0.4444) 
thr#11b618
   -> dbd_st_execute for 00278374
     Binding parameters: insert into test (t1) values (0.4444)
   <- dbd_st_execute 1 rows
   <- execute= 1 at t1.pl line 25
   -> execute for DBD::mysql::st (DBI::st=HASH(0x275298)~0x2752e0 '0.3333') 
thr#11b618
   -> dbd_st_execute for 00278374
     Binding parameters: insert into test (t1) values (0.3333)
   <- dbd_st_execute 1 rows
   <- execute= 1 at t1.pl line 26
   -> disconnect for DBD::mysql::db (DBI::db=HASH(0x27519c)~0x275124) 
thr#11b618
&imp_dbh->mysql: 269328
   <- disconnect= 1 at t1.pl line 28
   -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x2752e0)~INNER) thr#11b618
   <- DESTROY= undef during global destruction
   -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x275124)~INNER) thr#11b618
   <- DESTROY= undef during global destruction

# results ========================

mysql> select * from test;
+------+
| t1   |
+------+
| 0.44 |
| 0.33 |
+------+
2 rows in set (0.00 sec)
My guess from a scan through the DBI man page is that it's some kind of 
typeinfo thing (I'm floundering now)?

Thanks again, Robbie Armour



From: Tim Bunce <[EMAIL PROTECTED]>
To: Michael A Chase <[EMAIL PROTECTED]>
CC: Robbie Armour <[EMAIL PROTECTED]>, [EMAIL PROTECTED]
Subject: Re: Rounding errors storing numbers in MySQL text fields using DBI
Date: Mon, 23 Jun 2003 09:56:34 +0100
On Sun, Jun 22, 2003 at 04:10:23PM -0700, Michael A Chase wrote:
> On Sun, 22 Jun 2003 03:29:39 +0100 Robbie Armour 
<[EMAIL PROTECTED]> wrote:
>
> > I use MySQL text fields as general repositories for code, data etc
> > but find that numbers are rounded to two decimal places.  This does
> > not happen with a similarly constructed Oracle table - eg
> >
> >   my $c = $dbh->prepare("insert into test (t1) values (?)");
> >   $c->execute(0.4444);
> >   $c->execute(0.5555);
> >
> > results in 0.44 and 0.56 being stored in the text field.  If field t1 
is
> > varchar this does not happen.
>
> If you are storing those values in text fields, you should probably
> pass them to execute() as strings rather than numbers.  Since
> DBD::mysql simulates placeholders, they are probably being pasted into
> the SQL as numbers which get rounded or truncated as the driver sees
> fit.

Perhaps. I'd like to know where and why though. Robbie, please use
trace() to see what's going on and let us know. Thanks.
Tim.

>    $c->execute( "0.4444" );
>    $c->execute( "0.5555" );
>
> --
> Mac :})
> Give a hobbit a fish and he eats fish for a day.
> Give a hobbit a ring and he eats fish for an age.
>
_________________________________________________________________
Express yourself with cool emoticons - download MSN Messenger today! 
http://www.msn.co.uk/messenger