Darren,
Two things to try:
1. day_code is of data type "smallint", so no single quotes around that
variable
2. use the preservesinglequotes() function
So:
<CFSET tempQuery = "SELECT day_desc
FROM ttdays
WHERE cmpy_code= '10'
AND day_code = 4">
<cfquery name="qrySQLtest2" datasource="DARREN">
#preservesinglequotes(tempQuery)#
</cfquery>
<CFDUMP var="#qrySQLtest2#">
Should work.
Regards,
Steve C
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Darren
Tracey
Sent: Thursday, 4 March 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
