oracle-databasecoldfusionoracle10gcoldfusion-10cfqueryparam

Which cfsqltype to use for Oracle's Number(*,0) datatype in cfqueryparam?

I am a little bit confused with which datatype I should use for Oracle's Number(*,0) with zero scale and any precision?

Which one should I use CF_SQL_INTEGER or CF_SQL_FLOAT? and why?

Solution

  • According to the documentation, Number(*,0) means you are working with very large integers, ie up to 38 digits and no decimal places:

    column_name NUMBER (precision, scale)

    ... precision (total number of digits) and scale (number of digits to the right of the decimal point):

    column_name NUMBER (*, scale)

    In this case, the precision is 38, and the specified scale is maintained.

    That is too many digits to store in CF_SQL_INTEGER. To support the full range, requires a type with a much greater capacity. Looking at the standard JDBC Mappings that means either java.sql.Types.NUMERIC or java.sql.Types.DECIMAL. Both of those use java's BigDecimal for storage, which has more than enough capacity for Number(38,0).

    The cfqueryparam matrix and Oracle JDBC driver documentation both say the same thing about the DECIMAL type. Since java.sql.Types.NUMERIC is really just a synonym for java.sql.Types.DECIMAL you can use either one.

    Note: When using cfqueryparam, if you omit the "scale" attribute, it defaults to scale="0", ie no decimal places.

        <cfqueryparam type="CF_SQL_DECIMAL" scale="0" value="....">