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

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.


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.

I have run into this as well

<cfcomponent output="false" extends="farcry.core.packages.types.types" displayname="Test" hint="" bFriendly="false" bObjectBroker="true">

deploy it, then run this test script

o = application.fapi.getcontenttype("spcTest");

st = {
    fee = 149.99

result = o.createData(o.beforeSave(st, {}));
newst = o.getData(result.objectid);
writedump(var = newst);

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.

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

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.


Thanks for that @seancoyne – much appreciated!

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

1 Like