getting wierd decimal values when updating MySQL database

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?
spectrumcareAsked:
Who is Participating?
 
Dave BaldwinConnect With a Mentor Fixer of ProblemsCommented:
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
 
Dave BaldwinFixer of ProblemsCommented:
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
 
spectrumcareAuthor Commented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
_agx_Commented:
> 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
 
_agx_Commented:
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
 
_agx_Commented:
Do yourself a favor and don't use raw values in a query. That's just asking to be hacked.
0
 
spectrumcareAuthor Commented:
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
 
_agx_Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.