This was a change of behavior in Version 7. We had to correct code in 6.3 to get it to work under 7.
Richard -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: Wednesday, February 07, 2007 10:51 AM To: arslist@ARSLIST.ORG Subject: Re: use of ' in sql calls Stephen, Is this a change for ARS 7? I'm on 6.3 and the system automatically replaces the single ' with 2 singles if it is in a field inside if single quotes (like in his example). The only time I have to do the Replace myself is if my single is in hard coded text of the statement. i.e. Select SchemaID from ARSchema Where Name = '$NAME$' If my $NAME$ field contains "My Account's" the sql log on 6.3 shows Select SchemaID from ARSchema Where Name = 'My Account''s' If I had hard coded a string into the SQL like Select SchemaID from ARSchema Where Name like '%Maint''s%' then I have to do the 2 quotes myself. Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Heider, Stephen Sent: Wednesday, February 07, 2007 10:32 AM To: arslist@ARSLIST.ORG Subject: Re: use of ' in sql calls 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" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
