?
Solved

getting wierd decimal values when updating MySQL database

Posted on 2014-02-20
8
Medium Priority
?
681 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
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 Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

764 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