Solved

getting wierd decimal values when updating MySQL database

Posted on 2014-02-20
8
662 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 82

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
 
LVL 82

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

707 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now