MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Rick Faircloth Date:October 1 2007 4:43pm
Subject:RE: Having trouble storing monetary values...
View as plain text  
It seems that way, to me, too Baron.

I actually did a test using Navicat and intput
a test debit into the table directly.  It took the
amount, 150.49, without any problem, nor did it
round it off to the nearest dollar.

I need to mention that this is working in conjunction
with ColdFusion to handle the processing.

Besides, having just started testing this code on
MySQL 5 instead of 4.1, I've just upgraded to CF 8.

The change in handling the data may be on the CF 8 end.
I had someone on that list suggest using "scale=2" as
part of the insert sql in the cfqueryparam statement.
That solved the problem.  I haven't had to use "scale" before,
but it may have to do with how either MySQL now handles
decimals in version 5, which is different according to the
documentation, or how CF 8 handles decimals, or both.

But the solution seems to be to use "scale=2" (for 2 decimals places)
which CF 8 is involved with insertion into MySQL 5.

Thanks for your help on this... I've CC'd this to this list
also, so everyone can benefit.

Thanks for your help!  It's much appreciated!


-----Original Message-----
From: Baron Schwartz [mailto:baron@stripped] 
Sent: Monday, October 01, 2007 9:47 AM
To: Rick Faircloth
Cc: MySQL List
Subject: Re: Having trouble storing monetary values...

Hi Rick,

(I'm CCing the list in again -- keeping the discussion on-list lets 
everyone see it and potentially solve their own problems by searching 
archives in future).

It looks to me like it ought to be working.  What is the exact INSERT 
INTO statement that isn't working?

Are there any triggers on the table?


Rick Faircloth wrote:
> Hi, Baron, and thanks for the reply.
> Here's the table info I got from Navicat:
> -- Table "debits" DDL
> CREATE TABLE `debits` (
>   `debit_id` int(11) NOT NULL auto_increment,
>   `debit_category_id` int(11) NOT NULL default '0',
>   `debit_name` varchar(50) default NULL,
>   `debit_amount` decimal(10,2) unsigned NOT NULL,
>   `debit_recipient` varchar(50) default NULL,
>   `debit_description` text,
>   `personal` tinyint(1) default NULL,
>   `debit_date` date default NULL,
>   `debit_time` time default NULL,
>   `debit_location` varchar(30) default NULL,
>   `contact_person` varchar(20) default NULL,
>   `contact_phone` varchar(20) default NULL,
>   `contact_email` varchar(50) default NULL,
>   `date_entered` timestamp NULL default '0000-00-00 00:00:00' on update
>   PRIMARY KEY  (`debit_id`)
> The "debit_amount" is the field I'm having problems with.
> I've been using MySQL 4.1 for a couple of years and haven't
> had any problems.  But after upgrading to 5.0.45-community-nt,
> my way of handling monetary value isn't working the way I desire.
> When the data is inserted in the db, it's being rounded up or down
> to the near whole number.  5.75 becomes 6 or 5.25 becomes 5.
> No decimal to be found.
> Ideas?
> Rick
> -----Original Message-----
> From: Baron Schwartz [mailto:baron@stripped] 
> Sent: Sunday, September 30, 2007 11:03 AM
> To: Rick Faircloth
> Cc: mysql@stripped
> Subject: Re: Having trouble storing monetary values...
> Rick Faircloth wrote:
>> Hi, all.
>> I'm having trouble storing monetary values.
>> When MySQL 5.0 stores the entered value of 5.23,
>> it storing it as 5, dropping off the values after the decimal.
>> The field is the decimal type with 2 specified as the number
>> of values to store after the decimal.
>> Can anyone provide some clues on how to set up the field
>> or any other changes I need to make to properly store
>> the monetary values?
> Please send us the exact column definition from SHOW CREATE TABLE.  You 
> should be able to use a definition like DECIMAL(10,2) or similar.
> Baron

Baron Schwartz
Xaprb LLC

Having trouble storing monetary values...Rick Faircloth30 Sep
  • Re: Having trouble storing monetary values...Baron Schwartz30 Sep
Re: Having trouble storing monetary values...Baron Schwartz1 Oct
  • RE: Having trouble storing monetary values...Rick Faircloth1 Oct