Solved

getting wierd decimal values when updating MySQL database

Posted on 2014-02-20
8
673 Views
Last Modified: 2014-02-21
I am updating a value in mysql data base in coldfusion
 
<cfquery name="updateCredit" datasource="accounting#Session.facility#">
 UPDATE othernamecredit SET AmountLeft = AmountLeft - #credAmountReceived#
 WHERE Auto = <cfqueryparam value="#credInvoiceID#" cfsqltype="cf_sql_integer">  
 </cfquery>

Open in new window

AmountLeft value in the data base is 51.37 and creditAmountReceiced value is 51.37
So when the value is saved in the data base it should have 0 but instead I am getting a value of 1.4210854715202e-014.
I do understand that when doing math with decimal you get some funky answers. Is there a way I can do this with out doing something like this
<cfquery name="getcreditAmount" datasource="accounting#Session.facility#">
     SELECT AmountLeft, Auto
     WHERE Auto = <cfqueryparam value="#credInvoiceID#" cfsqltype="cf_sql_integer">
 </cfquery>
 <cfset thenewValue = #getcreditAmount.AmountLeft# -  #credAmountReceived#>
 <cfset thenewValue = #NumberFormat(thenewValue,'.__')#>
 <cfquery name="updateCredit" datasource="accounting#Session.facility#">
     UPDATE othernamecredit 
     SET AmountLeft = <cfqueryparam value="#thenewValue#" cfsqltype="cf_sql_double">
     WHERE Auto = <cfqueryparam value="#credInvoiceID#" cfsqltype="cf_sql_integer">  
  </cfquery>

Open in new window

I would like to try to keep the database calls and code down to a minimum.

Is there any suggestions on how I can do this with out having the 2 database calls?
0
Comment
Question by:spectrumcare
  • 4
  • 2
  • 2
8 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39875275
You probably need to round off the numbers received to only two or 3 decimal places in your code.  1.4210854715202e-014 is the same as .000000000000014210854715202 which is a very small number.  It's a common problem with binary to decimal conversions.
0
 

Author Comment

by:spectrumcare
ID: 39875299
Is there a way I can do that when I am doing my subtraction in the query statement like I am doing here
<cfquery name="updateCredit" datasource="accounting#Session.facility#">
 UPDATE othernamecredit SET AmountLeft = AmountLeft - #credAmountReceived#
 WHERE Auto = <cfqueryparam value="#credInvoiceID#" cfsqltype="cf_sql_integer">  
 </cfquery>

Open in new window


I verified that the numbers for AmountLeft value is 51.37 and credAmountReceived value is 51.37. Each value only has 2 decimal places
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39875329
> cfsqltype="cf_sql_double">

If your db column is type double, that is your problem. Double is an approximate type. That means it cannot represent all numbers exactly, which is why you sometimes end up with long fractional values.  It's the closest approximation of a particular value that the double type can present. That is why it is never recommended for financial purposes.

If you need exact precision, such as for a currency amount, you should always use the decimal type instead, in conjunction with cf_sql_decimal.
0
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 500 total points
ID: 39875350
Unless your fields are specified as DECIMAL fields, your arithmetic will be floating point and your two-decimal place numbers are really floating point number that are the closest approximation to the decimal value.  'round' might do what you want.  I can't test it at the moment but try this:
<cfquery name="updateCredit" datasource="accounting#Session.facility#">
 UPDATE othernamecredit SET AmountLeft = ROUND((AmountLeft - #credAmountReceived# ), 2)
 WHERE Auto = <cfqueryparam value="#credInvoiceID#" cfsqltype="cf_sql_integer">  
 </cfquery>
 

Open in new window

https://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_round
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39875360
Always use cfqueryparam on all values, both for performance reasons and to protect your db against sql injection.

When using cfqueryparam, be sure to specify a scale (number of decimal points). The default is zero, so if you omit it you'll insert an integer.  

EDIT: For example, assuming "Amount" is a DECIMAL column, say decimal(10,2), use:

UPDATE othernamecredit 
SET AmountLeft = AmountLeft - <cfqueryparam value="#credAmountReceived#" 
                                         cfsqltype="cf_sql_decimal" 
                                         scale="2">

WHERE .....

Open in new window


Note: It will not work consistently if "Amount" is some sort of double or float column
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39876406
Do yourself a favor and don't use raw values in a query. That's just asking to be hacked.
0
 

Author Comment

by:spectrumcare
ID: 39878013
Yesterday I was looking for a quick dirty fix to the issue because our client was having this problem. Today I went through our database and changed all double to decimals and updated the code accordingly.  I do argee with -agx- about not using raw values but I was trying to stay away from extra db calls, since that page already contains several calls to the database. I am doing checks on the page to make sure that the raw number is a number and other precautions before I did the update query.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39878123
Well, I'm not sure about the source of the values but you shouldn't need an extra db call if you already have the number. Just changing the columns to "decimal" and using cf_sql_decimal with cfqueryparam should do it.  

There's another reason to use cfqueryparam: performance. When you pass raw values into a statement, the db has to compile a new execution plan every time, which incurs a performance penalty. When you use cfqueryparam, CF uses bind variables which can help the db reuse execution plans. For queries that are executed frequently, this will help boost performance, because you save the extra compilation time.
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question