Clarity

Expand all | Collapse all

Need to include specail character in gel update to sql

  • 1.  Need to include specail character in gel update to sql

    Posted 03-09-2017 10:52 AM

    Whenever there is a name change in my PSD object, I update the name of it's child object (COG) so they are always the same.  The update works except with special characters (like apostrophe).  Can someone please help me fix my code so that it wont fail when there is an apostrophe in the parent name?

     

    update odf_ca_zc_cogs
        set name = '${psd_name}'
            where ODF_PARENT_ID = ${gel_objectInstanceId}

     

    Thanks in advance for your help!

    Rob



  • 2.  Re: Need to include specail character in gel update to sql

    Posted 03-09-2017 11:03 AM

    Is your SQL update inside a CDATA block - that might help?



  • 3.  Re: Need to include specail character in gel update to sql

    Posted 03-09-2017 11:13 AM

    I think this thread is the same problem ; GEL Script XOG - Handling Special Characters  but I'm not sure that the answer there (handle the problem in SQL with replace statements) is the optimal one - however I guess if it works then it works!



  • 4.  Re: Need to include specail character in gel update to sql

    Posted 03-09-2017 11:43 AM

    Hi David,

    Thanks for your help!  Yes, the update is inside a CDATA block.  Setting name = '${psd_name}' works for most cases, but when there is an apostrophe it fails every time.  The query that gets submitted to SQL does not compensate for the apostrophe with a double apostrophe, so the actual error is incorrect syntax (update cog set name = 'It's a Miracle' where odf_parent_id = 1234567).  "It" is the string, and "s a Miracle" is incorrect syntax (the WHERE clause becomes a syntax error as well).

     

    Thanks,

    Rob



  • 5.  Re: Need to include specail character in gel update to sql

    Posted 03-09-2017 12:14 PM

    Yeah so thats what the other thread ended up saying ; you could change your GEL to explicitly replace (util:replace) ' by '' in the variable that is the put into the SQL statement ( '' being the SQL code for ' )  - similar for any other troublesome characters.

     

    Ugly solution though.



  • 6.  Re: Need to include specail character in gel update to sql

    Posted 03-09-2017 12:29 PM

    So within the CDATA and assuming that psd_name is java string object, try executing the replaceAll method on the string.

     

    update odf_ca_zc_cogs
        set name = '${psd_name.replaceAll("'","''")}'
            where ODF_PARENT_ID = ${gel_objectInstanceId}

     

    If the jelly complains about the replaceAll expresssion, you might have to do the replace into a new string via core:set.

     

    V/r,

    Gene



  • 7.  Re: Need to include specail character in gel update to sql

    Posted 03-09-2017 12:37 PM

    ^ was thinking that there is probably more than just ' that would be troublesome so might need a series of 'replace' commands (in 'GEL' rather than SQL, since the problem seems to be how the SQL is passed from GEL to the sql engine).

     

    Anyway I'm GUESSing a bit here ; there should be enough info for reugene to experiment with!



  • 8.  Re: Need to include specail character in gel update to sql

    Posted 03-09-2017 12:52 PM

    Agreed.

     

    replaceAll allows one to use regex to match search condition and do more complex replacements.  A couple of examples.

     

    <?xml version="1.0" encoding="utf-8"?>
    <gel:script xmlns:core="jelly:core"
         xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
         xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

         
         <core:set value="whatever this is 999 piggy Rebaseline v12" var="testOne" />
         <gel:log>testOne   = ${testOne}</gel:log>
         
         <core:set var="testTwo" value='${testOne.replaceAll(".*(?:\D|^)(\d+)", "$1")}' />
         <gel:log>testTwo   = ${testTwo}</gel:log>
         
         <core:set value="${testTwo + 1}" var="testThree" />
         <gel:log>testThree = ${testThree}</gel:log>
         
         <core:set value='${testOne.replaceAll("(.*)(?:\D|^)(\d+)", "$1" + "v" + testThree)}' var="testFour" />
         <gel:log>testFour  = ${testFour}</gel:log>
         
    </gel:script>

     

    V/r,

    Gene