?
Solved

getting wierd decimal values when updating MySQL database

Posted on 2014-02-20
8
Medium Priority
?
691 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 84

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 84

Accepted Solution

by:
Dave Baldwin earned 1500 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month17 days, 2 hours left to enroll

862 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