upgrade to cfm 6 from cfm 5

[mikey999]

I am having allot of issues with my site since I upgraded to cfm 6.0. One big
issue is fields with large sets of data is erroring out when I add a new record
to a database.
--------------------------------------------------------------------------------
---------------------------- Here is an example error
--------------------------------------------------------------------------------
---------------------------- Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 3: Incorrect syntax near
's'. The error occurred in D:\login\secure\news\EditNews.cfm: line 72
Called from D:\login\secure\news\EditNews.cfm: line 17 Called from
D:\login\secure\news\EditNews.cfm: line 1 Called from
D:\login\secure\news\EditNews.cfm: line 72 Called from
D:\login\secure\news\EditNews.cfm: line 17 Called from
D:\login\secure\news\EditNews.cfm: line 1 70 : Description = <CFIF
#GetConfig.UseBRSpace# is '1'>'#Replace('#Form.Description#', Chr(10), '<br>',
'ALL')#',<cfelse>'#Form.Description#',</cfif> 71 : Keywords =
'#TextKeyword#' 72 : WHERE NewsID = #NewsID# 73 : </cfquery> 74 :
--------------------------------------------------------------------------------
SQL UPDATE NewsMaster SET Source = 'EarthVision Reports', Article =
'WASHINGTON, Feb. 17, 2005 - The U.S. Department of Energy is joining forces
with major industry players to fund 12 new projects that will increase the
energy efficiency of passenger and commercial vehicles while maintaining low
emissions. The projects, with a total value of $175 million (50%, or $87.5
million contributed by the private sector) will focus on development of
advanced combustion engine and waste heat recovery
technologies.<br><br>'Together with our private sector partners, the U.S.
Department of Energy is pursuing innovative new technologies to improve vehicle
fuel efficiency and reduce vehicle emissions,? Secretary of Energy Samuel
Bodman. ?Partnerships like these are a critical component of our strategy to
protect the environment and reduce our nation's dependence on foreign sources
of oil.?<br><br>The projects selected support the Bush Administration's goal of
improving efficiency of internal combustion engines from 30% (2004 baseline) to
45% by 2012 for passenger vehicles and from 40% (2002 baseline) to 55% by 2013
for commercial vehicles, while meeting cost, durability, and emissions
constraints. Vehicles utilizing these technologies would use 10% to 15% percent
less fuel than current vehicles, reducing America's dependence on foreign
sources of oil and improving the environment through reduced
emissions.<br><br>Seven of the twelve projects will conduct research in
advanced combustion technology. These projects have shown potential to achieve
efficiency goals for cars and trucks while maintaining cost and high durability
with near-zero emissions. The remaining five projects will develop technologies
to convert waste heat from engines to electrical or mechanical energy;
improving overall thermal efficiency and emissions reductions. <br><br>'These
two sets of projects complement each other,' assistant secretary for Energy
Efficiency and Renewable Energy (EERE) David Garman said. 'By moving both
development strategies forward, we can look toward a future with better, more
efficient engines that produce less waste heat and fewer emissions. We at the
Department of Energy are proud to help bring these projects to
fruition.'<br><br>The projects were selected from a solicitation by EERE's
Office of FreedomCAR and Vehicle Technologies to develop enabling technologies,
components, methods and fuels for 'high-efficiency clean combustion,' as well
as utilization of engine waste heat for useful work.<br><br>The 12 projects are
as follows:<br><br><i>Enabling High-Efficiency Clean
Combustion</i><br><ul><li><b>Caterpillar, Inc.</b> in Peoria, Ill., will
identify and/or advance the required technologies to enable a low temperature,
high efficiency combustion solution for 2010 on-highway truck or 2014 non-road
machine applications. The work includes implementation of HCCI combustion using
a combination of enhanced engine sensors, 'intelligent' engine control,
variable compression ratio, and fuel composition. Team members include
ExxonMobil, Sandia National Laboratory and IAV Automotive. DOE cost:
$10,379,349. Industry cost share: $10,379,360.<br><br><li><b>Cummins, Inc.</b>
in Columbus, Ind., will develop variable valve timing and premixed charge
compression ignition (PCCI) technologies, and integrate them into a
production-viable engine. The project will include the demonstration of engines
for both passenger and commercial vehicles and the compatibility of the
technology with renewable fuels. Team members include International,
Daimler-Chrysler, British Petroleum, Lawrence Livermore National Laboratory and
Oak Ridge National Laboratory. DOE cost: $15,040,547. Industry cost share:
$15,040,547.<br><br><li><b>Detroit Diesel Corporation</b> in Detroit, Mich.,
will combine several processes that enhance engine combustion individually into
one system that enables high efficiency clean combustion. They will also
investigate fuel matrix effects, including renewable fuels. This project will
develop engine system hardware and controls to improve thermal efficiency of
commercial engines while meeting emissions levels of 2010 and beyond Team
members include Oak Ridge National Laboratory, Sandia National Laboratory,
Freightliner, Schneider, Shell and DaimlerChrysler. DOE cost: $19,468,000.
Industry cost share: $21,850,000.<br><br><li><b>General Motors Corporation
Powertrain Division</b> in Pontiac, Mich., will develop parallel paths to
improve passenger vehicle efficiency for both spark ignition and diesel
engines. The project will focus on variable valve timing technologies for
short-term and long-term applications to allow implementation of homogeneous
charge compression ignition operation. Team members include Sturman Industries.
DOE cost: $6,249,008. Industry cost share:
$6,249,010.<br><br><li><b>International Truck and Engine Corporation</b> in
Warrenville, Ill., will conduct research for the development and application of
HCCI combustion over as large an operating range as possible by integrating
commercial or near-commercial fuel, air, and engine technologies (variable
valve timing, variable compression ratio, variable nozzle turbocharging, and
fuel injection equipment) with advanced controls. Demonstration will be in a
commercial diesel engine. Team members include Ricardo, Borg-Warner Turbo,
Jacobs Vehicle Systems, Siemens, Mahle, Lawrence Livermore National Laboratory,
UC Berkeley, Argonne National Laboratory and Conoco- Phillips. DOE cost:
$6,405,202. Industry cost share: $8,137,390.<br><br><li><b>John Deere Product
Engineering</b> in Waterloo, Iowa, will develop a stoichiometric
compression-ignition engine with low-pressure loop cooled exhaust gas
recirculation (EGR) and a diesel particulate filter followed by a three-way
catalyst. Combustion will be similar to conventional diesel combustion with
lower peak temperatures. A commercial diesel engine will be modified with a
high injection pressure fuel system, variable valve timing and advanced
electronic controls along with aftertreatment and low pressure loop EGR system.
Team members include Sturman Industries, Ricardo Technologies and Purdue
University. DOE cost: $2,656,527. Industry cost share:
$2,656,527.<br><br><li><b>Mack Trucks, Inc.</b> in Hagerstown, Md., will
develop and demonstrate an air-power-assist (APA) engine for improving fuel
efficiency by 15% with emissions meeting the 2010 regulations. During braking,
the engine would utilize braking energy to work as a compressor, pumping
compressed air into an on- board tank. During acceleration the engine is
powered by the compressed air with or without burning diesel fuel until the
compressed air is depleted. The key technology development required for the APA
engine is a fully-flexible engine valve actuation system. The technology will
be tested on a commercial diesel engine. Team members include UCLA, Sturman
Industries and Advanced Energy Systems. DOE cost: $1,807,674. Industry cost
share: $1,807,674.</ul><i>Exhaust Energy Recovery</i><br><ul><li><b>Caterpillar
Inc.</b> in Peoria, Ill., will develop a new air management and exhaust energy
recovery system for commercial diesel engines. Electric turbo-compounding and
high-efficiency air system technology will be key technology building blocks
developed. Additional system elements may be developed to support the energy
recovery system. Advanced engine system concepts that further enable exhaust
energy recovery may also be investigated. DOE cost: $3,934,862. Industry cost
share: $3,934,862.<br><br><li><b>Cummins Inc.</b> in Columbus, Ind., will
develop a waste heat recovery system to support clean and efficient combustion
and reduce heat rejection. This will include limited design and development of
components, subsystems and associated electronic controls with integration into
a commercial vehicle. Team members include International, Indiana
University-Purdue University, University of Illinois-Urbana Champagne, Oak
Ridge National Laboratory, Pacific Northwest National Laboratory and the
National Institute of Science and Technology (NIST). DOE cost: $5,545,165.
Industry cost share: $5,545,165.<br><br><li><b>Detroit Diesel Corporation</b>
in Detroit, Mich., will evaluate engine- based technologies to partially
recover and convert exhaust energy into useful mechanical and electrical work.
The focus will be on the integration of technologies to achieve synergistic
effects of the evaluated technologies. A down-select of promising technologies
is planned. Testing would be performed in a commercial diesel engine. Team
members include Freightliner, Schneider and H

[Duane McGuire]

I'll take a shot at this. It appears that indeed, your code SHOULD work. I
don't think that the problem is related to lenght of text, but to the fact that
single quotes are present in the text. What is not happening, is that the
single quotes are not being escaped as they are passed to the database. That
is the builtin functionality of CFQUERY, so I'm surprised it isn't happening.
I notice that you say CF 6.0. That was a buggy release. If you haven't
upgraded to 6.1, you certainly should for multiple reasons. I suspect that
this is an issue of database driver, not core CF.

Anyway, in terms of your coding I also suspect that you can cure the problem
by using CFQUERYPARAM:



In your current server configuration I'd expect the following to fail, based
on your description:

<cfset text = "I'm thinking this won't work">

<cfquery name="uD" datasource="...">
update theTable set notes = '#text#'
where id = 0
</cfquery>

However, I'd expect this to work, even with the database driver/CF problem:


<cfset text = "I'm thinking this can't fail.">

<cfquery name="uD" datasource="...">
update theTable set notes = <cfqueryparam value="#text#"
cfsqltype="cf_sql_longvarchar">
where id = 0
</cfquery>

[Duane McGuire]

I got to thinking about my reply from this morning, and realized that the root
of your problem had to lie in the complexity of an expression that you are
asking CF to interpret within the CFQUERY. I've run across that issue in the
past. The escaping of single quotes does not work with complex expressions.
Here's what's going on in your code:



This will fail.

<cfset text = "I'm thinking this won't work">
<cfquery name="uD" datasource="...">
update agents set agt_notes = '#replace('#text#',chr(10)," ","all")#'
where agt_id = 0
</cfquery>

The first argument of the replace function is has double interpretation within
it.
Remove the double interpretation, and it will work.

This will work:

<cfset text = "I'm thinking this can't fail">
<cfquery name="uD" datasource="...">
update agents set agt_notes = '#replace(text,chr(10)," ","all")#'
where agt_id = 0
</cfquery>

The nested interpretation on the variable "text" is removed, and the code
works,
because CF is looking at a simple expression.

(Having said all this, I still like the use of the cfqueryparam as outlined
above.
It is more robust and efficient. )

[mikey999]

Ok tried what you had but I got an error. But then I tried it like they had it
on this page
[url]http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=tn_19617[/url]
<cfscript> Form.Description = Replace(Form.Description, chr(8217), chr(39));
Form.Description = Replace(Form.Description, chr(8216), chr(39));
Form.Description = Replace(Form.Description, chr(8220), chr(34));
Form.Description = Replace(Form.Description, chr(8221), chr(34));
Form.Description = Replace(Form.Description, chr(8211), '-'); Form.Description
= Replace(Form.Description, Chr(10), '<br>'); Form.Description =
Replace(Form.Description, Chr(13), '<br>'); // replaces line feed/carriage
return with a break </cfscript> and it worked. Is this an ok way to do this?

[mikey999]

ok so it does not work after all. it will not keep the BR's This is the part
that is causing issue in cfm 6.1 Custom20 = '#Replace('#Custom20#', Chr(10),
'<br>', 'ALL')#',<cfelse>Custom20 = '#Custom20#', so it is supose to change
the Chr(10) to BR's but this no longer works. I thought this would do this
but it does not work Form.Description = Replace(Form.Description, Chr(13),
''); any ideas how how I can do this?