cfqueryparam truncates values even when scale is specified

[Oregon_Will]

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.

[-__cfSearching__-]

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?

[Oregon_Will]

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.

[BKBK]

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?

[Oregon_Will]

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]

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>

[Oregon_Will]

The INSERT INTO...SET syntax may not be standard, but it is valid syntax for MySQL. I use the INSERT INTO...SET syntax because it is much easier to read, much easier to make sure that the field-value assignments are correct. Notice that the query works fine when the parameter is passed in with cfsqltype="cf_sql_double" and when the value is hard-coded.

[BKBK]

Oregon Will wrote:

The INSERT INTO...SET syntax may not be standard, but it is valid syntax for MySQL. I use the INSERT INTO...SET syntax because it is much easier to read, much easier to make sure that the field-value assignments are correct. Notice that the query works fine when the parameter is passed in with cfsqltype="cf_sql_double" and when the value is hard-coded.

Sorry. No need to explain. I should have said non-standard instead of incorrect. The question remains: does the version with standard SQL work? If so, then we will only have to look into why the MySQL syntax leads to truncation. 

[Oregon_Will]

Believe me when I say this is frustrating... I've tried to reproduce the problem and it seems to have disappeared without me updating any of the software; probably fixed by restarting the computer.

I ran the following CFM script:

<cfset Bill.keyBill = 1>
<cfset qryPoolItem.keyBillItem = 10>
<cfset keyProgramTech = 12742>
<cfset qryLeftover.nPool = "123.45">
<cfquery datasource="telcomm" name="create_temp">
CREATE TABLE IF NOT EXISTS `bill_warehouse_temp` (
      `keyTemp` INT(11) AUTO_INCREMENT PRIMARY KEY,
      `keyBill` INT(11),
      `keyBillItem` INT(11),
      `keyProgram` INT(11),
      `nAmount` DECIMAL(11,2),
      `sDescription` TINYTEXT
) ENGINE=InnoDB
</cfquery>
<cfquery datasource="telcomm" name="insert_leftover_adj">
INSERT INTO `bill_warehouse_temp`
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 INSERT INTO...SET'
</cfquery>
<cfquery datasource="telcomm" name="insert_leftover_adj">
INSERT INTO `bill_warehouse_temp` (`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 INSERT INTO...VALUES'
)
</cfquery>
<cfquery datasource="telcomm" name="qryResults">
SELECT *
FROM `bill_warehouse_temp`
</cfquery>
<cfdump var="#qryResults#">
<cfquery datasource="telcomm" name="drop_temp">
DROP TABLE `bill_warehouse_temp`
</cfquery>

and got the following output:

query
RESULTSET	query KEYBILL KEYBILLITEM KEYPROGRAM KEYTEMP NAMOUNT SDESCRIPTION 1 1 10 12742 1 123.45 Adjustment INSERT INTO...SET 2 1 10 12742 2 123.45 Adjustment INSERT INTO...VALUES
CACHED	false
EXECUTIONTIME	0
SQL	SELECT * FROM `bill_warehouse_temp`