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! Rick -----Original Message----- From: Baron Schwartz [mailto:[EMAIL PROTECTED] 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? Baron 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 > CURRENT_TIMESTAMP, > PRIMARY KEY (`debit_id`) > ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1; > > 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:[EMAIL PROTECTED] > Sent: Sunday, September 30, 2007 11:03 AM > To: Rick Faircloth > Cc: mysql@lists.mysql.com > 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 http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
