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 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.

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.

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: