[closed] FC7 not saving numeric data after decimal when using dbPrecision and type="numeric"

bug
#1

This was working in FC6, but doesn’t seem to be working in FC7 (at least in my tests so far).

I have type=“numeric” fields that I use for latitude and longitude. The dbPrecision is set for “8,6” using MSSQL. When I save the record, the data is being truncated to only the number before the decimal (treating it more like an integer). Everything after the decimal is saved as zeros.

Example:

property name="latitude" type="numeric" dbPrecision="8,6" ftSeq = 110 ftLabel="Latitude" ftType = "numeric";

The only other thing I can think of is that I’m using MSSQL 2016 (on the other platform where I have it working on FC6 I am using MSSQL 2008). I doubt it makes a difference, but I figured it was worth mentioning. I did verify that my FarCry dbType is set to mssql2012 and have tried mssql2005 as well. I’ve also verified that the precision was correctly configured in MSSQL. If I dump the data in beforeSave(), it is the correct value (just covering my bases). The last thing I can think of is that FarCry is somehow truncating the data before it saves to the database.

#2

I have run into this as well

<cfcomponent output="false" extends="farcry.core.packages.types.types" displayname="Test" hint="" bFriendly="false" bObjectBroker="true">
    <cfproperty
        ftSeq="110"
        ftFieldset="General"
        ftLabel="Fee"
        name="fee"
        type="numeric"
        ftType="numeric"
        default="0"
        ftDefault="0"
        required="true"
        ftIncludeDecimal="true"
        dbPrecision="18,2"
    />
</cfcomponent>

deploy it, then run this test script

<cfscript>
o = application.fapi.getcontenttype("spcTest");

st = {
    fee = 149.99
};

result = o.createData(o.beforeSave(st, {}));
o.afterSave(o.getdata(result.objectid));
newst = o.getData(result.objectid);
writedump(var = newst);
</cfscript>

For me, it rounds the 149.99 up to 150 when saving.

I have narrowed this down to a core issue. Core uses cf_sql_decimal without passing the scale attribute to cfqueryparam so it uses the default scale of 0 which causes SQL Server to round it.

The best solution would be for core to pass the scale parameter based on the dbPrecision you specify when you create the property. I imagine that getValueForDB() in the various gateways should be modified to also include a scale key in the result struct, and then when those are passed to cfqueryparam instead of setting them individually simply change it to <cfqueryparam attributecollection="#stVal#" /> where stval is the result from getValueForDB().

In the meantime, I have fixed this by overriding the DB Gateway and modifying getValueForDB() so that if it is one of the various currency properties we are using, I change it to cf_sql_float if I see cf_sql_decimal.

I’m also going to log this as a bug in Jira.

#3

I’ve performed the following test with Lucee Express 5.1 and the jTDS SQL Driver, using the 7.2.8 Community distribution from www.farcrycore.org:

  1. Installed Fandango on SQL2014 using the SQL2012 DB gateway;

  1. Installation completed successfully;

  1. Added the spcTest.cfc component to types, as provided by Sean above, and deployed it;

  1. Ran the test script, as provided by Sean, and received the following dump;

  1. Double checked the spcTest table in the database to confirm that the value 149.99 was stored;

Considering we’ve not run into any issues with decimals in the recent past, I wonder if this bug is specific to Adobe ColdFusion, or CF2016 in particular? It would be disappointing if it’s a regression in ACF…

In the mean time I’ll take a look at your pull request, thanks a lot for providing a fix Sean :smiley:

1 Like
#4

Certainly could be ACF 2016 but it is documented that the default scale for cfqueryparam is zero. I did find other with the problem in the past (not with FarCry) using Google-fu. That’s what lead me to realize it was the missing scale attribute.

#5

Done.

Thanks for that @seancoyne – much appreciated!

#6

Confirmed fixed in core now. Thanks for accepting the PR.

1 Like