Use the PreserveSingleQuotes() function when passing your sql string to the
database.
DC
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Thursday, April 05, 2001 10:00
Subject: Single quotes misbehaving
>
> Here is a dynamic query:
>
> <CFSET FIRSTREC=0>
> <CFSET SQL="SELECT ">
> <CFLOOP QUERY="GetHeaders">
> <CFIF FindNoCase(FIELDNAME1, Form.Display, 1) NEQ 0 OR OPTIONAL IS 0>
> <CFIF FIRSTREC IS 1>
> <CFSET SQL=SQL & ", ">
> </CFIF>
> <CFSET SQL=SQL & FIELDNAME1>
> </CFIF>
> <CFSET FIRSTREC=1>
> </CFLOOP>
> <CFSET SQL=SQL & " FROM PI.PI WHERE #CLIENT.CATEGORY# ">
> <CFIF GetCatType.FIELDTYPE1 IS "CHAR" AND GetCatType.PARTIALKEY IS 1>
> <CFSET SQL=SQL & "LIKE '%" & Ucase(Trim(CLIENT.CATVAL1)) & "%'">
> <CFELSEIF GetCatType.FIELDTYPE1 IS "CHAR" AND GetCatType.PARTIALKEY IS 0>
> <CFSET SQL=SQL & "= '" & Ucase(Trim(CLIENT.CATVAL1)) & "'">
> <CFELSEIF GetCatType.FIELDTYPE1 IS "NUM">
> <CFSET SQL=SQL & "= " & Trim(CLIENT.CATVAL1)>
> </CFIF>
>
> If I print the variable, this is what I get:
>
> SELECT APPLYTO, ACCT, SERV, SERVNAME, BMVOL, BMREV, BMPRICE, UATVOL,
UATREV, UATPRICE, (UATVOL - BMVOL) AS "NETVOL", (UATVOL - BMVOL)/BMVOL AS
> "PERVARVOL", (UATREV - BMREV) AS "NETREV", (UATREV - BMREV)/BMREV AS
"PERVARREV", (UATPRICE - BMPRICE) AS "NETPRICE", (UATPRICE -
BMPRICE)/BMPRICE AS
> "PERVARPRICE" FROM PI.PI WHERE MAINACCTNAME LIKE '%BILL BLASS%'
>
> which is great. However, here is what Oracle interprets this as:
>
> Oracle Error Code = 911
>
> ORA-00911: invalid character
>
> SQL = "SELECT APPLYTO, ACCT, SERV, SERVNAME, BMVOL, BMREV, BMPRICE,
UATVOL, UATREV, UATPRICE, (UATVOL - BMVOL) AS "NETVOL", (UATVOL -
BMVOL)/BMVOL AS
> "PERVARVOL", (UATREV - BMREV) AS "NETREV", (UATREV - BMREV)/BMREV AS
"PERVARREV", (UATPRICE - BMPRICE) AS "NETPRICE", (UATPRICE -
BMPRICE)/BMPRICE AS
> "PERVARPRICE" FROM PI.PI WHERE MAINACCTNAME LIKE ''%BILL BLASS%''"
>
> Basically, it is turning the single quotes around the LIKE '%Var%' into
double quotes.
>
> Any suggestions?
>
> Gina Shillitani
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
