Re: use of ' in sql calls

[Ivan Runions]

Thanks for this info... I'll check this out.

-ivan-



Axton wrote:

**
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] <mailto:[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:arslist@ARSLIST.ORG <mailto:arslist@ARSLIST.ORG>] On
    Behalf Of Ivan Runions
    Sent: Wednesday, February 07, 2007 11:28 AM
    To: arslist@ARSLIST.ORG <mailto: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
    <http://www.arslist.org> ARSlist:"Where
    the Answers Are"

    
_______________________________________________________________________________
    UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
    <http://www.arslist.org> ARSlist:"Where the Answers Are"

__20060125_______________________This posting was submitted with HTML in it___

--
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"