Clarity PPM

Expand all | Collapse all

GEL Script XOG - Handling Special Characters

Jump to Best Answer
  • 1.  GEL Script XOG - Handling Special Characters

    Posted 08-11-2013 12:25 PM
    One of the data fields in my query occasssionally contains an ampersand and a single quote.

    This results set is then used in my GEL Script XOG.

    Example #1:
    Name = A & R

    In my SQL query I currently use to try to present the data in a way that can be used in my XOG script:
    SELECT replace(NAME, '&', '&') NAME2
    FROM ODF_CA_TABLENAMEX

    Resutls returned:
    A & R

    What is the best way to clean the data and be able to use it for XOG?

    Thanks in advance!


  • 2.  RE: GEL Script XOG - Handling Special Characters



  • 3.  RE: GEL Script XOG - Handling Special Characters

    Posted 08-14-2013 07:49 PM
    Thanks NJ!


  • 4.  RE: GEL Script XOG - Handling Special Characters

    Posted 08-14-2013 04:43 AM
    Hi,

    have you tried putting your sql in einem CDATA block?

    Something like :
    <sql:query escapeText="0" var="result"><![CDATA[
    SELECT  ... FROM ....
    ]]></sql:query>
    Search the forum after CDATA and GEL and you may find some useful examples for handling special characters in GEL.

    Best regards,

    Sergiu Gavrila

    ------------------
    Clarity Add-Ons www.itdesign.de/itd-addons/
    Real Time Workload Analytics meisterplan.com


  • 5.  RE: GEL Script XOG - Handling Special Characters

    Posted 08-14-2013 07:57 PM
    Hi Sergiu,

    Thanks for your reply! I did try to use the CDATA tag and the escapeText code around my SQL query, but when the process created the XML XOG code it still didn't XOG in the correct data. I would see &amp;amp; in the field.

    I thought maybe I could use that util:replace tag in the section of the script where the values from the SQL query are being assigned to variables, but I keep getting this error and it doesn't allow me to save my updates to the script.
    BPM-0703: Custom script syntax error at line 67, column 102: The prefix "util" for element "util:replace" is not bound.

    I saw a posting where someone replaced the amp; with "" so I thought I'd try that in my SQL query. It looks good when I run the SQL query, but when the process creates the XML XOG script it still added extra amp;amp; to the field. :sad

    thanks,
    christine


  • 6.  RE: GEL Script XOG - Handling Special Characters

    Posted 08-16-2013 11:13 AM
    Maybe you could replace your query with something like this to remove the & char.

    SELECT replace(NAME, CHAR(38), CHAR(38), CHAR(38)+'amp' + CHAR(59) NAME2
    FROM ODF_CA_TABLENAMEX

    If you have an Oracle backend, I think it is CHR instead of CHAR.

    V/r,
    Gene


  • 7.  RE: GEL Script XOG - Handling Special Characters

    Posted 08-16-2013 02:34 PM
    Hi Gene,

    Thanks for your reply and suggestion! = )

    I did try your suggestion, but I wasn't successful. = ( When I tried to run the SQL statement in my query tool it says "ORA-01722: invalid number" and doesn't run the query....same result when I try to run it through my process. = (

    If I do not use any replace function in my SQL query the results for the NAME filed is returned as :
    A &amp; R

    The above is the value that I get when I write the value of the assigned variable to the <gel:log>.

    It XOGs in as A &amp;amp; R

    I was wondering....is there a way to clean the data in the same line where I assign the data to a variable before it is used in the GEL XOG? If could somehow strip the amp; from the variable before it is used in the GEL XOG in theory I think it would be ok....?


    thanks,
    christine


  • 8.  RE: GEL Script XOG - Handling Special Characters
    Best Answer

    Posted 08-16-2013 03:26 PM
    When you tried the replace the error you received looks like you might be missing the jelly:util namespace in your <gel:script tag

    The prefix "util" for element "util:replace" is not bound.

    Gene


  • 9.  RE: GEL Script XOG - Handling Special Characters

    Posted 08-19-2013 08:18 PM
    This worked for me.

    Parse.xml contained records which had the &amp;amp;.


    <row>
    <field column="NAME2">FY13 O&amp;amp;M - BNLER</field>
    </row>
    <row>
    <field column="NAME2">FY12 RDT&amp;amp;E - Pharmacy</field>
    </row>

    Added this namespace xmlns:util="jelly:util" to the gel:script tag.
    <util:loadText file="Parse.xml" var="ToClean" />
    
    <util:replace old=";amp;" new=";" var="Cleaned" value="${ToClean}"/>>
    
    
    <core:new className="java.io.PrintWriter" var="PrintOne">
    
    
    <core:arg type="java.lang.String" value="Cleaned.xml" />
    
    
    <core:arg type="java.lang.String" value="UTF-8" />
    
    </core:new>
    
    
    
    <core:invoke method="print" on="${PrintOne}">
    
    
    <core:arg value="${Cleaned}" />
    
    </core:invoke>
    
    
    
    <!-- Close the file -->
    
    <core:invoke method="close" on="${PrintOne}" />
    After the replace Cleaned.xml contained:

    <row>
    <field column="NAME2">FY13 O&amp;M - BNLER</field>
    </row>
    <row>
    <field column="NAME2">FY12 RDT&amp;E - Pharmacy</field>
    </row>


  • 10.  RE: GEL Script XOG - Handling Special Characters

    Posted 08-20-2013 02:03 PM
    Hi Gene,

    Thank you very much for all your help! I was missing the xmlns:util="jelly:util" in my custom script. After I added it I the util:replace code worked. :smile:smile

    thank you!
    christine