Skip to main content
Participant
June 24, 2009
Question

Different behavior with decimal data on same version of CF

  • June 24, 2009
  • 1 reply
  • 422 views

I have a linux/apache/mysql/CF MX 7 Enterprise server. I insert decimal data using the cfqueryparam that looks like

<cfqueryparam value="#BudgetSet.BUD_SET_DETAIL_COST_PER#" CFSQLType="CF_SQL_DECIMAL">.

No problems the decimal data does not get rounded, 123.45 inserts as 123.45.

I'm building applications for my neighborhood association, so I'm having godaddy.com host the web site with CF and MySQL.

Like a good developer, I took code from my CF server, moved some of the more useful programs to the godaddy.com, set up the database and started testing some apps. Now any decimal data inserted gets rounded using the same cfqueryparam syntax from my CF server (like above). The decimal data on the godaaddy.com inserts as 123.00, when I'm trying to insert 123.45, of course if I insert 123.99 I get 124.00, rounding. So I called godaddy.com and of course I get the standard technical answer "its your code". So off to testing I go. Only to determine, by not using the cfqueryparam, I could stop the rounding, more testing I determined that I need to use

<cfqueryparam value="#BudgetSet.BUD_SET_DETAIL_COST_PER#" CFSQLType="CF_SQL_MONEY"> or

<cfqueryparam value="#BudgetSet.BUD_SET_DETAIL_COST_PER#" CFSQLType="CF_SQL_DECIMAL" scale="2">. The version of CF @ godaddy.com is also MX 7 Enterprise. Finally the question, what in configuration could cause such different behavior ?

Thanks for reading my short novel.

    This topic has been closed for replies.

    1 reply

    Participating Frequently
    June 24, 2009

    The database drivers determine how the cfqueryparam values are

    interpreted (behind the scenes ColdFusion generates a prepared

    statement using the driver interface and sets the values using

    standard JDBC calls). Unfortunately, like you found out, not all

    drivers are created equal and have slightly different behavior or

    defaults.

    I would check and compare the driver versions.

    Mack