Darren
Coldfusion will automaticlay escape the single quotes, which you dont want
to be doing in your case, so you will need to use the PreserveSingleQuotes()
function when you output the query in the query tags
so
<cfquery name="qrySQLtest2" datasource="DARREN">
#PreserveSingleQuotes(tempQuery)#
</cfquery>
Now with that, if your values in your query actually have single quotes like
col = 'it's my name'
you need to replace ' with '' so it becomes
col = 'it''s my name'
so with your query
<cfset cmpy = 10>
<cfset dayCode = 4>
<CFSET tempQuery = "SELECT day_desc
FROM ttdays
WHERE cmpy_code= '#Replace(cmpy,"'","''","ALL")#'
AND day_code = '#Replace(dayCode',"'","''","ALL")#">
Regards
Steve Onnis
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Darren
Tracey
Sent: Thursday, March 04, 2004 6:23 PM
To: CFAussie Mailing List
Subject: [cfaussie] Oddness when sending a query to a database
I'm investigating moving our code from an ODBC connection to a JDBC
connection. There are a few small date related issues that require code
changes.
I came across something very odd when I was testing for broken SQL
statements under both connection types.
I've distilled it down to this code snippet. It doesn't matter if I use JDBC
or ODBC.
<cfquery name="qrySQLtest1" datasource="DARREN">
SELECT day_desc
FROM ttdays
WHERE cmpy_code= '10'
AND day_code = '4'
</cfquery>
<CFDUMP var="#qrySQLtest1#"><br>
<CFSET tempQuery = "SELECT day_desc
FROM ttdays
WHERE cmpy_code= '10'
AND day_code = '4'">
<cfquery name="qrySQLtest2" datasource="DARREN">
#tempQuery#
</cfquery>
<CFDUMP var="#qrySQLtest2#"><br>
Note that both queries are identical, with the sole exception that the first
is hard coded into the CFM file and the second is inserted via a variable.
The SQL in the variable contains single quotes and is surrounded by double
quotes when it is put in the variable.
The first CFQUERY works and the cfdump shows its output.
The second CFQUERY throws the following error:
----------------------
Error Executing Database Query.
A syntax error has occurred. The error occurred in
D:\Inetpub\wwwroot\Development\test2.cfm: line 16
14 :
15 : <cfquery name="qrySQLtest2" datasource="DARREN">
16 : #tempQuery#
17 : </cfquery>
18 :
SQL SELECT day_desc FROM ttdays WHERE cmpy_code= ''10'' AND day_code = ''4''
DATASOURCE DARREN
VENDORERRORCODE -201
SQLSTATE 42000
----------------------
Note that the SQL shown to have an error in the error message has double
quotes around the string values, even though they were put in as having
single quotes around them.
This is the debugging output a little further down the page.
----------------------
Exceptions
11:55:56.056 - Database Exception - in
D:\Inetpub\wwwroot\Development\test2.cfm : line 16
Error Executing Database Query.
SQL Queries
qrySQLtest1 (Datasource=DARREN, Time=0ms, Records=1) in
D:\Inetpub\wwwroot\Development\test2.cfm @ 11:55:56.056
SELECT day_desc
FROM ttdays
WHERE cmpy_code= '10'
AND day_code = '4'
----------------------
This shows the error and the successful SQL code. Note the single quotes
around the string values.
The delimiter around the string values was set in the code as being single
quotes in both, yet when the sql is put in the CFQUERY via a variable, the
delimiter is changed to a double quote and this breaks the SQL engine.
We are using an Informix database.
Has anyone ever seen this before?
Can anyone explain why this is happening?
Can anyone suggest how to stop this?
Can anyone verify whether this breaks on other SQL engines?
Will the use of CFQUERYPARAM solve this?
Regards
Darren Tracey
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
