Skip to main content
O
Oregon_Will
Participating Frequently
May 2, 2012
Question

cfqueryparam truncates values even when scale is specified

  • May 2, 2012
  • 2 replies
  • 3961 views

I'm connecting ColdFusion 9.0.1 with MySQL 5.5 using the MySQL 5.1 ODBC connector. I have a table with a field defined as DECIMAL(11,2) intended to store currency amounts.

When I use <cfqueryparam cfsqltype="cf_sql_decimal" scale="2" value="#nValue#"> to insert a value in that field, the value stored in the parameter (when I show debug output on the parameters after the query is run) is correct and not truncated, but the value stored in MySQL is truncated (not rounded).

I know this is probably a driver issue, but I'm hoping that this forum will be able to help me troubleshoot the problem.

This topic has been closed for replies.

2 replies

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
May 9, 2012

Oregon Will wrote:

I'm connecting ColdFusion 9.0.1 with MySQL 5.5 using the MySQL 5.1 ODBC connector. I have a table with a field defined as DECIMAL(11,2) intended to store currency amounts.

When I use <cfqueryparam cfsqltype="cf_sql_decimal" scale="2" value="#nValue#"> to insert a value in that field, the value stored in the parameter (when I show debug output on the parameters after the query is run) is correct and not truncated, but the value stored in MySQL is truncated (not rounded).

What is the value? How is it truncated?

O
Oregon_WillAuthor
Participating Frequently
May 9, 2012
BKBK wrote:

What is the value? How is it truncated?

This was occurring on several different queries for all the fields that were of type DECIMAL. I would try inserting a value of 3.67, but 3.00 would show up in the database. I would try inserting a value of 5.24, but 5.00 would show up in the database. 1.44 would go in as 1.00. 8300.68 would go in as 8300.00. 2301.75 would go in as 2301.00.

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
May 10, 2012

The issue arises perhaps from incorrect syntax. Try this:

<cfquery datasource="telcomm" name="insert_leftover_adj">

INSERT INTO bill_warehouse(keyBill,keyBillItem,keyProgram,nAmount,sDescription)

VALUES(<cfqueryparam cfsqltype="cf_sql_integer" value="#Bill.keyBill#">,

   <cfqueryparam cfsqltype="cf_sql_integer" value="#qryPoolItem.keyBillItem#">,

   #keyProgramTech#,

   <cfqueryparam cfsqltype="cf_sql_decimal" scale="2" value="#qryLeftover.nPool#">,

   'Adjustment ...'

</cfquery>

-
-__cfSearching__-
Inspiring
May 3, 2012

I rarely use ODBC anymore. But it works as expected with a jdbc connection. This inserts "11.57":

                      <cfqueryparam cfsqltype="cf_sql_decimal" scale="2" value="11.565">

Aside from switching to jdbc

1) Can you dump the generated sql? What is the value of #nValue# and the actual value inserted into the table?

2) It should not make a difference, but any change if you temporarily use hard coded values? ie INSERT INTO ... VALUES (11.56) OR use cf_sql_numeric?

O
Oregon_WillAuthor
Participating Frequently
May 8, 2012

Sorry to take so long responding to this. I have been trying to find other ways to handle this and generate the necessary reports.

Switching to JDBC isn't an option. When using JDBC in the past, several of the queries that we would use for regular maintenance would fail to run entirely.

For example, ColdFusion was running the following query:

<cfquery datasource="telcomm" name="insert_leftover_adj">
INSERT INTO `bill_warehouse`
SET
  `keyBill` = <cfqueryparam cfsqltype="cf_sql_integer" value="#Bill.keyBill#">,
  `keyBillItem` = <cfqueryparam cfsqltype="cf_sql_integer" value="#qryPoolItem.keyBillItem#">,
  `keyProgram` = #keyProgramTech#,
  `nAmount` = <cfqueryparam cfsqltype="cf_sql_decimal" scale="2" value="#qryLeftover.nPool#">,
  `sDescription` = 'Adjustment ...'
</cfquery>

The following was the generated SQL:

INSERT INTO `bill_warehouse`
SET
  `keyBill` = ?,
  `keyBillItem` = ?,
  `keyProgram` = 12742,
  `nAmount` = ?,
  `sDescription` = ?

I checked the parameters passed to the query object to make sure that they were not being truncated and the parameters were correct. When I run the query as follows, it works.

<cfquery datasource="telcomm" name="insert_leftover_adj">
INSERT INTO `bill_warehouse`
SET
  `keyBill` = <cfqueryparam cfsqltype="cf_sql_integer" value="#Bill.keyBill#">,
  `keyBillItem` = <cfqueryparam cfsqltype="cf_sql_integer" value="#qryPoolItem.keyBillItem#">,
  `keyProgram` = #keyProgramTech#,
  `nAmount` =  "#NumberFormat(qryLeftover.nPool, "9.99")#",
  `sDescription` = 'Adjustment ...'
</cfquery>

I was having the same truncation problems regardless of the value of the cfsqltype parameter, so long as it was being passed into a DECIMAL field. Once I switched the field to DOUBLE and started using cf_sql_double, it worked.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices