Re: preservesinglequotes
Sorry the following is so long, but I believe there is some misunderstanding
concerning the use this function. I am not an expert so if someone else has
more info please help out. Here is what I believe from my experience with
this function.
I have used it in a situation where I had variables that may or may not be
set. As a result they could not be used directly in the SQL code. Also,
when they are not set I wanted to output NULL to the record not an empty
string (i.e. ''). Thus what I wanted was:
----> INSERT .... (ColA, ColB, ...) VALUES ('#varA#', '#varB#', ...)
But only if varA and varB had values. For example, after substitution, if
varA and varB were "test1" and "test2, the result would be:
----> INSERT .... (ColA, ColB, ...) VALUES ('test1', 'test2', ...)
However if one or both variables were empty (lets say varB is empty), I
would want:
----> INSERT .... (ColA, ColB, ...) VALUES ('test1', NULL, ...) (i.e. no
single quotes around NULL)
I did not want
----> INSERT .... (ColA, ColB, ...) VALUES ('test1', 'NULL', ...)
I solved this by creating a simple custom tag that would be called as
follows (by the way, this was pre UDF's - this would be much nicer as a
UDF):
<CFSQLSTR NAME="varA">
This would return the original contents with single quotes (eg "'test1'") or
"NULL" if the variable was empty. So my solution looked like this:
<CFSQLSTR NAME="varA">
<CFSQLSTR NAME="varB">
<CFQUERY ...>
INSERT .... (ColA, ColB, ...) VALUES (#varA#, #varB#, ...) (note no single
quotes)
</CFQUERY>
I thought this was it but ColdFusion removes the single quotes for you in a
SQL statement. I think this is to help you since embedded single quotes
will cause problems. But I wanted the single quotes because they were the
quotes needed to enclose the strings. This is where preservesinglequotes
comes in. By making this change:
<CFSQLSTR NAME="varA">
<CFSQLSTR NAME="varB">
<CFQUERY ...>
INSERT .... (ColA, ColB, ...)
VALUES (#preservesinglequotes(varA)#, #preservesinglequotes(varB)#, ...)
</CFQUERY>
It made everything work because now CF does not strip out my single quotes.
One catch!! Now I am responsible for making sure my variables do not contain
embedded single quotes because they will cause a problem. For example if
varA is "test'1", the resulting SQL (after CF does its substitution) is:
INSERT .... (ColA, ColB, ...)
VALUES ('test'1', 'test2', ...)
This ('test'1') will create problems because the first ' will be seen as end
of string and then the stuff that follows becomes garbage. If you do
something like this then you must either:
- scan the string and replace or remove single quotes, OR
- scan the string and insert a second single quote for each one found (i.e.
'test''1' would be legal).
I think that should cover it. Again, sorry it is so long.
Phill Addorisio
AllianzWeb
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of rudy
Sent: May 14, 2002 1:35 PM
To: [EMAIL PROTECTED]
Subject: Re: [CFTALKTor] Memo fields
> Its not the length of the string, it seems to be all the
> special characters in the string you are trying to store.
not all of them, just the single quotes
> ... Thus the syntax error is because the string is
> being truncated before it gets to the closing single quote
that's right, because it *contains* a single quote
> I am told that single quotes, not double quotes should be used to
> surround text fields in sql.
yup
> Also, in Coldfusion, the use of single
> quotes inside a sequel string is permitted if the function
> preservesinglequotes is used as has been done here.
um, not sure that applies here
what you want to do is *double up* the single quotes inside a string that
is used in an INSERT statement
Values (
'Main Test Form for testing purposes'
, 'English'
, 'Test Form'
, 'Originals = 1 ... Shrink Wrap: 100''s;... '
notice that 100's has to have two consecutive single quotes -- 100''s
see
http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_ci
d467541_tax285649,00.html
rudy
-
You are subscribed to the CFUGToronto CFTALK ListSRV.
This message has been posted by: "rudy" <[EMAIL PROTECTED]>
To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/
Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/
This System has been donated by Infopreneur, Inc.
(http://www.infopreneur.net)
-
You are subscribed to the CFUGToronto CFTALK ListSRV.
This message has been posted by: "Phill Addorisio" <[EMAIL PROTECTED]>
To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/
Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/
This System has been donated by Infopreneur, Inc.
(http://www.infopreneur.net)
