I just ran across something important to know regarding the cfsqltype cf_sql_decimal. Here’s the code I was testing:

1
2

params.insert( 'previousNumericDuration',
  { value: preNDuration, cfsqltype: 'cf_sql_decimal' } );

Here’s what it would look like if you are using the cfqueryparam tag:

1

<cfqueryparam value='#preNDuration#' cfsqltype='cf_sql_decimal' />

The database column this is persisted to is defined as a decimal(4,2), so 2 digits are reserved for the whole number portion of the value and 2 digits reserved for the decimal portion of the value. Logging showed values being calculated such as 4.25 and 4.5, but these were being persisted to the database rounded to the nearest integer, but with 2 decimal places none the less, so 4.25 was persisted as 4.00 and 4.5 was persisted as 5.00. Definitely not what I wanted. What’s the point of a decimal datatype if the decimal portion is always 0?

Digging into the documentation for cfqueryparam I found a scale attribute that until today I hadn’t realized was absolutely essential to use for decimal fields like this. Scale defines the … “Number of decimal places in parameter. Applies to CF_SQL_NUMERIC and CF_SQL_DECIMAL.” Its default is 0, so my values were being rounded to 0 decimal places. After changing my code to the following:

1
2

params.insert( 'previousNumericDuration',
  { value: preNDuration, cfsqltype: 'cf_sql_decimal', scale: 2 } );

the decimal portion of the values was then persisted correctly.

So the scale attribute is absolutely essential for a decimal cfsqltype. I might have tripped over this before, I’m not sure. Perhaps this blog post will help me remember it next time. Now I should search my code to see if I’ve neglected this attribute elsewhere!