Ivan, inquire from support about the following defect:
SW00256669 Server not escaping single quotes in Direct SQL This is from the 7.0.1 p001 release notes. I would have imagined the same were addressed in 7.0p002 since the release times were close. Axton Grams On 2/7/07, Heider, Stephen <[EMAIL PROTECTED]> wrote:
Ivan, Apostrophes are something that will need to planned for anytime you make calls to a SQL database directly. But, it's quick to do. In a Set Fields, replace the single ' with two '' using the Remedy REPLACE() function. Store the result in a tmp field. Then use the tmp field in your SQL statement. Stephen -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Ivan Runions Sent: Wednesday, February 07, 2007 11:28 AM To: arslist@ARSLIST.ORG Subject: use of ' in sql calls Situation: We have a field containing department names (called UnitName in the sql call below). Some of these names have an apostrophe (') in the name. We have an Active link that does an sql call SELECT MAX(zDepCount) FROM FS_AuditCashDeposit_Main WHERE '$UnitName$' = UnitName AND '$DepartmentNumber$' = DepartmentNumber AND '$RequestID$' != RequestID This SELECT statement worked fine under ARS 6.3, going to an Oracle 9i database (accessed via Oracle 9 client libraries). We have just upgraded our server to ARS 7.0.00 patch 2, going to an Oracle 9i database (access via Oracle 10 client libraries), and we now get errored out on these calls with the error: Arerr [552] Failure during SQL Operation to the database ORA-01756 Quoting string not properly terminated. The error only occurs when the Unitname contains an apostrophe, and obviously, it is mucking up the sql statement. Does anyone have any thoughts/pointers, as a) how to work around this b) whether it's the ARS upgrade or the Oracle 10 client library change) that might be causing this Actually, I only need help with (b) if I can't determine how to do (a). thanks -ivan- -- Ivan Runions Information Technologies University of Calgary Calgary Alberta Canada (403) 220-4437 ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
_______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
