Randy, I had done this same code just this morning as a workaround to get by. - Thx for validating my decision...
All, As in the case of most of us here..... I am not a person that likes "workarounds" when there is a bug, so while I have "working" code, it still does not answer what is really broken. Interestingly, for all who are following the thread, if I take it out of an expression and use the full value (rather than left 5 char's), it works fine and escapes the quote perfectly. This appears to be an expression evaluation problem in MX. HOWEVER, their tech support claims it is a "coding syntax" problem, not an MX problem - even though it is easily recreated to show that it is not. SHEEESH!!! -Doug -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Ellis, Randy Sent: Monday, August 12, 2002 10:49 AM To: '[EMAIL PROTECTED]' Subject: RE: [KCFusion] CFMX Possible Bug HELP! If PreserveSingleQuotes doesn't work, you may try replacing single quotes within the variable with two singles quotes. Example: SELECT ID FROM TABLE2 WHERE NAME LIKE '%#Replace(left(table1query.name,5),"'","''","ALL")#%' This should fix the example you provided. I'm not sure what would happen if two singles quotes ("''") appeared together in your name string. It may still work. Randy Ellis Senior Programmer Analsyt City of Overland Park -----Original Message----- From: Doug Teetzen [mailto:[EMAIL PROTECTED]] Sent: Monday, August 12, 2002 10:29 AM To: [EMAIL PROTECTED] Subject: [KCFusion] CFMX Possible Bug HELP! I am having terrible luck trying to resolve an issue that came up this weekend. It appears that using certain expressions within CFMX are not properly escaping quotes in strings. Objective= Find possible dupe records before inserting a record from Table1 into Table2 Sample Data In Database #1 and #2: Name= Dick's Sporting Goods ZIPCode=00101 I have a loop over the data in table1 that queries against table2 in the following way Sample Query1: SELECT ID,NAME FROM TABLE1 Loop Start Sample Query2: SELECT ID FROM TABLE2 WHERE NAME LIKE '%#left(table1query.name,5)%#' (checking the first 5 chars only in comparison) **So the resulting query is as follows from debug output** Problem Query Sample: SELECT ID FROM TABLE2 WHERE NAME LIKE '%Dick'%#' ** End Loop What happens is that it blows up when there is a single quote in the name result from table1 and it is used as a parm to query table2 (in an expression). >From the debug output, it appears that it may be an error in how it is passed from CFMX to the JDBC:SqlServer driver. Any help or contacts anyone can offer would be GREATLY appreciated..... Doug Teetzen Bushnell Performance Optics ______________________________________________________________________ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives........ http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe.................... mailto:[EMAIL PROTECTED] To Unsubscribe................ mailto:[EMAIL PROTECTED] ______________________________________________________________________ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives........ http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe.................... mailto:[EMAIL PROTECTED] To Unsubscribe................ mailto:[EMAIL PROTECTED] ______________________________________________________________________ The KCFusion.org list and website is hosted by Humankind Systems, Inc. List Archives........ http://www.mail-archive.com/cf-list@kcfusion.org Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED] To Subscribe.................... mailto:[EMAIL PROTECTED] To Unsubscribe................ mailto:[EMAIL PROTECTED]
