Have a look at PreserveSingleQuotes() which is a CFMX function.
<cfquery name="qrySQLtest2" datasource="DARREN">
#PreserveSingleQuotes(tempQuery)#
</cfquery>
Cheers.
S.
-----Original Message-----
From: Darren Tracey [mailto:[EMAIL PROTECTED]
Sent: Thursday, 4 March 2004 18: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
IMPORTANT NOTICE: This e-mail and any attachment to it are intended only to be read or
used by the named addressee. It is confidential and may contain legally privileged
information. No confidentiality or privilege is waived or lost by any mistaken
transmission to you. The RTA is not responsible for any unauthorised alterations to
this e-mail or attachment to it. Views expressed in this message are those of the
individual sender, and are not necessarily the views of the RTA. If you receive this
e-mail in error, please immediately delete it from your system and notify the sender.
You must not disclose, copy or use any part of this e-mail if you are not the intended
recipient.
---
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
