cfparam, SQL Server and datatypes

June 02, 2008

This one always catches me out, so once and for all I'm going to post it for future reference! If you use the attribute values of "cf_sql_numeric" or "cf_sql_decimal" for decimals then they are rounded to the nearest integer. As Matt points out in the comments, this is because "cf_sql_numeric" and "cf_sql_decimal" use the scale attribute to specify the number of decimal places. If you don't add the scale attribute, then the default is 0 decimal places. This does lead me on to wonder what real benefit there is to using cf_sql_decimal with a scale attribute instead of cf_sql_float (aside from the obvious rounding of decimal places).

To test this I created a table in SQL Server with the following schema:


CREATE TABLE [dbo].[SqlType] (
    [type_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
    [type_decimal] [decimal](18, 2) NULL ,
    [type_money] [money] NULL ,
    [type_float] [float] NULL ,
    [type_name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [type_real] [real] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SqlType] WITH NOCHECK ADD 
    CONSTRAINT [PK_SqlType] PRIMARY KEY  CLUSTERED 
    (
        [type_id]
    )  ON [PRIMARY] 
GO

I then created the following ColdFusion script:


<cfquery name="qrytest" datasource="john">
    insert into [SqlType]
    (
     type_decimal, 
     type_money, 
     type_float, 
     type_real, 
     type_name
    )
    values 
    (
     <cfqueryparam value="2.6" cfsqltype="cf_sql_decimal" />, 
     <cfqueryparam value="2.6" cfsqltype="cf_sql_decimal" />, 
     <cfqueryparam value="2.6" cfsqltype="cf_sql_decimal" />, 
     <cfqueryparam value="2.6" cfsqltype="cf_sql_decimal" />, 
     'cf_sql_decimal'
    )
</cfquery>


<cfquery name="qrytest" datasource="john">
    insert into [SqlType]
    (
     type_decimal, 
     type_money, 
     type_float, 
     type_real, 
     type_name
    )
    values 
    (
     <cfqueryparam value="2.6" cfsqltype="cf_sql_double" />, 
     <cfqueryparam value="2.6" cfsqltype="cf_sql_double" />, 
     <cfqueryparam value="2.6" cfsqltype="cf_sql_double" />, 
     <cfqueryparam value="2.6" cfsqltype="cf_sql_double" />, 
     'cf_sql_double'
    )
</cfquery>

<cfquery name="qrytest" datasource="john">
    insert into [SqlType]
    (
     type_decimal, 
     type_money, 
     type_float, 
     type_real, 
     type_name
    )
    values 
    (
     <cfqueryparam value="2.6" cfsqltype="cf_sql_float" />, 
     <cfqueryparam value="2.6" cfsqltype="cf_sql_float" />, 
     <cfqueryparam value="2.6" cfsqltype="cf_sql_float" />, 
     <cfqueryparam value="2.6" cfsqltype="cf_sql_float" />, 
     'cf_sql_float'
    )
</cfquery>

<cfquery name="qrytest" datasource="john">
    insert into [SqlType]
    (
     type_decimal, 
     type_money, 
     type_float, 
     type_real, 
     type_name
    )
    values 
    (
     <cfqueryparam value="2.6" cfsqltype="cf_sql_money" />, 
     <cfqueryparam value="2.6" cfsqltype="cf_sql_money" />, 
     <cfqueryparam value="2.6" cfsqltype="cf_sql_money" />, 
     <cfqueryparam value="2.6" cfsqltype="cf_sql_money" />, 
     'cf_sql_money'
    )
</cfquery>


<cfquery name="qrytest" datasource="john">
    insert into [SqlType]
    (
     type_decimal, 
     type_money, 
     type_float, 
     type_real, 
     type_name
    )
    values 
    (
     <cfqueryparam value="2.6" cfsqltype="cf_sql_money4" />, 
     <cfqueryparam value="2.6" cfsqltype="cf_sql_money4" />, 
     <cfqueryparam value="2.6" cfsqltype="cf_sql_money4" />, 
     <cfqueryparam value="2.6" cfsqltype="cf_sql_money4" />, 
     'cf_sql_money4'
    )
</cfquery>



<cfquery name="qrytest" datasource="john">
    insert into [SqlType]
    (
     type_decimal, 
     type_money, 
     type_float, 
     type_real, 
     type_name
    )
    values 
    (
     <cfqueryparam value="2.6" cfsqltype="cf_sql_numeric" />, 
     <cfqueryparam value="2.6" cfsqltype="cf_sql_numeric" />, 
     <cfqueryparam value="2.6" cfsqltype="cf_sql_numeric" />, 
     <cfqueryparam value="2.6" cfsqltype="cf_sql_numeric" />, 
     'cf_sql_numeric'
    )
</cfquery>


<cfquery name="qrytest" datasource="john">
    insert into [SqlType]
    (
     type_decimal, 
     type_money, 
     type_float, 
     type_real, 
     type_name
    )
    values 
    (
     <cfqueryparam value="2.6" cfsqltype="cf_sql_real" />, 
     <cfqueryparam value="2.6" cfsqltype="cf_sql_real" />, 
     <cfqueryparam value="2.6" cfsqltype="cf_sql_real" />, 
     <cfqueryparam value="2.6" cfsqltype="cf_sql_real" />, 
     'cf_sql_real'
    )
</cfquery>

Here are my results

type_id type_decimal type_money type_float type_name type_real
1 2 2 2 cf_sql_decimal 2
2 2.35 2.35 2.35 cf_sql_double 2.35
3 2.35 2.35 2.35 cf_sql_float 2.35
4 2.35 2.35 2.35 cf_sql_money 2.35
5 2.35 2.35 2.35 cf_sql_money4 2.35
6 2 2 2 cf_sql_numeric 2
7 2.35 2.35 2.349999905 cf_sql_real 2.35
8 3 3 3 cf_sql_decimal 3
9 2.6 2.6 2.6 cf_sql_double 2.6
10 2.6 2.6 2.6 cf_sql_float 2.6
11 2.6 2.6 2.6 cf_sql_money 2.6
12 2.6 2.6 2.6 cf_sql_money4 2.6
13 3 3 3 cf_sql_numeric 3
14 2.6 2.6 2.599999905 cf_sql_real 2.6

So, the moral of the story is don't use "cf_sql_numeric" or "cf_sql_decimal" for decimals!

11 comments

  1. cf_sql_numeric and cf_sql_decimal have a scale attribute that defaults to 0. This is why your results came through as unexpected.

    Comment by Matt – June 02, 2008
  2. I had similar issues with Oracle, so it is likely a ColdFusion thing. I had to store money in K, so $9,212,345 was stored like 9212.345 and I had trouble finding a datatype that would not truncate or round.

    Comment by Kris Brixon – June 02, 2008
  3. Ah! Thanks Matt, a classic case of RTFM :-) It does make me wonder what the benefits of each type is, or just to use cf_sql_float all the time.

    Comment by John Whish – June 02, 2008
  4. Good post. I always forget to use the scale attribute. I have started just using the FLOAT type rather than decimal, I think for that reason.

    Comment by Ben Nadel – June 02, 2008
  5. Hi Ben,
    I've always used float and never thought about it. On the project I'm doing at the moment for some reason I used decimal instead (you'd think it would be for a decimal field!).

    Comment by John Whish – June 02, 2008
  6. If we go by the docs (livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_18.html), which I try to do unless proven wrong, then for SQL Server for "decimal, money, smallmoney": use CF_SQL_DECIMAL; and for "numeric": use CF_SQL_NUMERIC; and for "float": use CF_SQL_FLOAT.

    Then, just make sure to use the scale attribute appropriately. Or am I missing something, or are you just saying that it's too much of a pain to remember that you only need to use scale when dealing with decimal types?

    Comment by Aaron Longnion – June 02, 2008
  7. Hi Aaron,
    I agree that sticking to the live docs is normally the way to go. However what Ben and I are saying is that as there appears to be no gain from using CF_SQL_DECIMAL instead of CF_SQL_FLOAT, why not always use float and not have the problem. Nice blog BTW :)

    Comment by John Whish – June 09, 2008
  8. Good point, John. The only caveat is that maybe there's something about using the "correct" data type with cfqueryparam that we don't realize, such as maybe compatibility issues with other databases (Adobe, if you're reading, do you care to comment?) Also, if you don't go by the docs, and Adobe makes changes in how this works under the hood in future Updates/releases of CF, there may be a slight chance of new unforeseen problems. Other than that, it probably doesn't matter too much. Thanks for the compliment on my blog. I don't get many... ;}

    Comment by Aaron Longnion – June 09, 2008
  9. Hi Aaron,
    That's a valid point, I don't know the answer to that one! I guess it has more to do with the Java drivers rather than ColdFusion itself.

    Comment by John Whish – June 09, 2008
  10. Hi John,

    our database experts always get the big shivers the couple of times I suggest using a float somewhere...

    They claim it's the big evil in SQL-country and should only be used for scientific purposes and never 4 day 2 day SQL...

    So my advice is using the correct datatype for the purpose needed - if the SQL is something scientific you could use a float, otherwise use decimal or numeric with the right scale ;-)

    Comment by Sebastiaan – June 16, 2008
  11. Hi Sebastiaan,
    Interesting. I never use float in the actual database. Ask him what (s)he things about having a field type of decimal in the database, but using the CF_SQL_FLOAT type in ColdFusion. Does that send shivers down their spine? (If so - it sounds like fun!)

    Comment by John Whish – June 16, 2008

Leave a comment

If you found this post useful, interesting or just plain wrong, let me know - I like feedback :)

Please note: If you haven't commented before, then your comments will be moderated before they are displayed.