Thats would break on oracle too :-(
Try putting PreserveSingleQuotes around your string:
<cfquery name="qrySQLtest2" datasource="DARREN">
#PreserveSingleQuotes(tempQuery)#
</cfquery>
Cheers,
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Stephen Bosworth
Application Development and Integration
Communication and Information Services
The University of Newcastle, Australia
Phone: 02 4921 6574
Fax: 02 4921 7087
Mobile: 0438 492518
Email: [EMAIL PROTECTED]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>> [EMAIL PROTECTED] 4/03/2004 6:23:20 pm >>>
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
