Gel script: getting error BPM-0704 : An expression of non-boolean type specified in a context where a condition is expected, near ';'.

[Anon Anon]

Hello,

I'm trying to execute a process basing on a gel script, the script enable finance to resources, but i get an error :

 

BPM-0704: An error ocurred while executing custom script: org.apache.commons.jelly.JellyTagException: null:13:50:

<sql:query> select id id, unique_name unique_name, last_name last_name, first_name first_name, email email, person_type person_type from srm_resources where id in(select distinct prresourceid from prteam where prprojectid in ( select id from inv_investments where schedule_finish &gt;='2016-01-01' ) ) and person_type in (300,301): [CA Clarity][SQLServer JDBC Driver][SQLServer]An expression of non-boolean type specified in a context where a condition is expected, near ';'

 

here is my script:

 

<gel:script xmlns:core="jelly:core"

    xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"

    xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"

    xmlns:jxml="jelly:xml"

    xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"

    xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"

    xmlns:sql="jelly:sql" xmlns:util="jelly:util"

    xmlns:x="jelly:org.apache.commons.jelly.tags.xml.XMLTagLibrary"

    xmlns:xog="http://www.niku.com/xog"

    xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <!--   Paramètres des accès Clarity  -->

    <gel:parameter default="http://127.0.0.1" var="hostClarity"/>

    <gel:parameter default="admin" var="XOGUsername"/>

    <gel:parameter default="*********" secure="true" var="XOGPassword"/>

    <!--   Base de données  -->

    <gel:setDataSource dbId="Niku"/>

    <!--   Instance du projet en cours  -->

    <!--récupération des infos de la ressource -->

    <sql:query var="infoResource">

       

            <![CDATA[

                     select id id,

                            unique_name unique_name,

                            last_name last_name,

                            first_name first_name,

                            email email,

                            person_type person_type

                           

                    from srm_resources

                    where id in(select  distinct prresourceid

                                from prteam

                                where prprojectid in ( select id

                                                       from inv_investments

                                                       where schedule_finish >='2016-01-01'

                                                       )

                                )

                    and person_type in (300,301)

            ]]>

           

               

        </sql:query>

 

    <core:if test="${infoResource.rowCount>0}">

        <!-- connection Xog  ______________________________________________________________________________  -->

        <soap:invoke endpoint="${hostClarity}/niku/xog" var="auth">

            <soap:message>

                <soapenv:Envelope

                    xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">

                    <soapenv:Header/>

                    <soapenv:Body>

                        <xog:Login>

                            <xog:Username>${XOGUsername}</xog:Username>

                            <xog:Password>${XOGPassword}</xog:Password>

                        </xog:Login>

                    </soapenv:Body>

                </soapenv:Envelope>

            </soap:message>

        </soap:invoke>

        <gel:log>connexion à ${hostClarity} en ${XOGUsername}</gel:log>

        <gel:set asString="true" select="$auth//xog:SessionID/text()" var="SessionID"/>

        <core:choose>

            <core:when test="${SessionID == null}">

                <gel:out>Log in impossible. Vérifiez le hostname/username/password.</gel:out>

                <gel:log>Log in impossible. Vérifiez le hostname/username/password.</gel:log>

                <core:set value="true" var="stepError"/>

            </core:when>

            <core:otherwise>

                <gel:log>Login réussi. Session ID = ${SessionID}</gel:log>

            </core:otherwise>

        </core:choose>

        <core:if test="${!stepError}">

            <!-- ===================== -->

            <!-- construction du xog   -->

            <!-- ===================== -->

            <gel:parse var="XML_all">

               <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_resource.xsd">

                    <Header action="write" externalSource="NIKU" objectType="resource" version="14.2.0.237"/>

                       

                    <Resources>

            <core:forEach items="${infoResource.rows}" trim ="true" var ="data" xmlns:core="jelly:core">

                        <core:if test="${data.person_type==300}">

                            <Resource

                                    externalId="${data.unique_name}"

                                    resourceId="${data.unique_name}"

                                    resourceType="LABOR">

                                    <PersonalInformation

                                        emailAddress="${data.email}"

                                        firstName="${data.first_name}" lastName="${data.last_name}"/>

                                    <FinancialInformation>

                                        <SupplementalInformation active="1" department="92"

                                            location="Puteaux" resourceClass="Interne" transactionClass="Labor"/>

                                        <RatesNCosts targetbillingRate="0" targetbillingRateCurrency="EUR"/>

                                        <Expenses reimbursementCurrency="EUR"/>

                                    </FinancialInformation>

                            </Resource>

                        </core:if>

                        <core:if test="${data.person_type==301}">

                            <Resource

                                    externalId="${data.unique_name}"

                                    resourceId="${data.unique_name}"

                                    resourceType="LABOR">

                                    <PersonalInformation

                                        emailAddress="${data.email}"

                                        firstName="${data.first_name}" lastName="${data.last_name}"/>

                                    <FinancialInformation>

                                        <SupplementalInformation active="1" department="92"

                                            location="Puteaux" resourceClass="Interne" transactionClass="Labor"/>

                                        <RatesNCosts targetbillingRate="0" targetbillingRateCurrency="EUR"/>

                                        <Expenses reimbursementCurrency="EUR"/>

                                    </FinancialInformation>

                             </Resource>

                        </core:if>

            </core:forEach>

                    </Resources>

                   

                </NikuDataBus>

            </gel:parse>

            <gel:serialize fileName="xogResource.xml" var="${XML_all}"/>

            <soap:invoke endpoint="${hostClarity}/niku/xog" var="retour_xog">

                <soap:message>

                    <soapenv:Envelope

                        xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">

                        <soapenv:Header>

                            <xog:Auth>

                                <xog:SessionID>${SessionID}</xog:SessionID>

                            </xog:Auth>

                        </soapenv:Header>

                        <soapenv:Body>

                            <gel:include select="$XML_all"/>

                        </soapenv:Body>

                    </soapenv:Envelope>

                </soap:message>

            </soap:invoke>

            <core:set value="" var="errorStatus"/>

            <core:set value="" var="errorSeverity"/>

            <core:if test="${retour_xog!= null}">

                <core:if test="${inProcess==0}">

                    <gel:serialize fileName="xogretour.xml" var="${retour_xog}"/>

                </core:if>

                <gel:set asString="true"

                    select="$retour_xog//Status/@state/text()" var="errorStatus"/>

                <core:catch>

                    <gel:set asString="true"

                        select="$retour_xog//ErrorInformation/Description/text()" var="errorDescription"/>

                </core:catch>

                <core:catch>

                    <gel:set asString="true"

                        select="$retour_xog//ErrorInformation/Severity/text()" var="errorSeverity"/>

                </core:catch>

            </core:if>

            <core:set

                value="${errorStatus=='FAILURE' or errorSeverity=='FATAL'}" var="errorXog"/>

            <core:if test="${errorXog}">

                <gel:out>Erreur xog :${errorDescription}</gel:out>

                <gel:log>Erreur xog :${errorDescription}</gel:log>

            </core:if>

            <core:if test="${errorSeverity=='WARNING'}">

                <gel:out>Warning xog :${errorDescription}</gel:out>

                <gel:log>Warning xog :${errorDescription}</gel:log>

            </core:if>

            <core:if test="${!errorXog}">

                <gel:out>Ressources activées financièerement</gel:out>

                <gel:log>Ressources activées financièerement</gel:log>

            </core:if>

            <!-- Logout Xog    ______________________________________________________________________________  -->

            <soap:invoke endpoint="${hostClarity}/niku/xog" var="logout">

                <soap:message>

                    <soapenv:Envelope

                        xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">

                        <soapenv:Header>

                            <xog:Auth>

                                <xog:SessionID>${SessionID}</xog:SessionID>

                            </xog:Auth>

                        </soapenv:Header>

                        <soapenv:Body>

                            <xog:Logout/>

                        </soapenv:Body>

                    </soapenv:Envelope>

                </soap:message>

            </soap:invoke>

            <gel:log>logout  ok</gel:log>

        </core:if>

    </core:if>

</gel:script>

 

In Clarity 14.2.0.237 05 12 / Sql Server

 

What's wrong with my query ??

 

Many thanks

[navzjoshi00]

Can you put this as the query and check if it works ?

 

select id id,

                            unique_name unique_name,

                            last_name last_name,

                            first_name first_name,

                            email email,

                            person_type person_type

                           

                    from srm_resources

                    where id in(select  distinct prresourceid

                                from prteam

                                where prprojectid in ( select id

                                                       from inv_investments

                                                       where schedule_finish &gt; '2016-01-01'

                                                       )

                                )

                    and person_type in (300,301)

 

 

NJ

[David Morton]

I would convert the date sting to a actual date though, that might be the problem?

[navzjoshi00]

Oh... ok

[Eugene Greiff]

I think Dave is right.  Even though your query will run in SSMS and do the implicit DATETIME conversion, I think the JDBC driver is having issues.

 

The query is wrapped in a CDATA tag so I don’t think you need to escape the greater than sign.

 

On a side note, the implicit conversion for the date will be base off the language set in SQLServer. So ‘2016-05-01’ will be May 1 2016 on my sqlserver but if I SET LANGUAGE FRENCH then it is January 5 2016.

 

I think your SQL should be:

 

select id id,
    unique_name unique_name,
    last_name last_name,
    first_name first_name,
    email email,
    person_type person_type
   
from niku.srm_resources
where id in(select  distinct prresourceid
        from niku.prteam
        where prprojectid in ( select id
                               from niku.inv_investments
                               where schedule_finish >= convert(DateTime,'01/01/2016',103)
                               )
        )
and person_type in (300,301)

 

V/r,

Gene

[Andrew LERNER]

But An expression of non-boolean type specified in a context where a condition is expected, near ';'. is a SQL Server error. SQL Server would only be able to return the "near ';'" bit, if it were passed a semi colon.

 

I'm betting the the escapeText="false" solves the problem. (hint: I tried it myself).

[Eugene Greiff]

Wow.  So the CDATA tag is being ignored! 

Taking a re-look at the error message seems to point this out ( where schedule_finish &gt;='2016-01-01' ).  The CDATA tag should have prevented the &gt; escaping unless I am missing something.

V/r,

Gene

 

[David Morton]

The originally posted query didn't have the &gt; in it though - so does the CDATA 'wrapper' implicitly convert the > to &gt; behind the scenes?

 

( must admit, whenever I get this sort of problem in GEL, I just try the various combinations of < &lt; and escape_text stuff until it works - debugging by brute force!  )

[Nick Darlington]

CDATA can't really be ignored, but you will find that XSLT operations (which happen frequently inside Clarity code) on XML content (e.g. gel scripts) will drop the CDATA portion after the first parse** of the XML document/fragment, and any subsequent parses if needed will then be performed without it present.

 

CDATA (handlers) also will not convert anything itself, it is more of a 'pass through untouched' operation that acts as a convenient bypass of the handling of any XML entity codes or other characters that would be illegal unless they were themselves encoded.

 

However, this will only get you so far and it's not a silver bullet, due to the potential of multiple parses amongst other reasons.

 

 

** Note: It's an oddity of XSLT processing, but the parsers will often handle CDATA and strip it when handing the document off to the next processor in the pipeline, or if expressly stated in the XSLT, the parsers will ADD a CDATA block around the body of specified elements whether you (as the document author) want to have them in every element of that name or not.

[Andrew LERNER]

I suppose the CDATA tag is just telling the DOM parser to not parse/do anything with its contents. The escapeText directive indicates to the GEL/jelly what to do with XML type characters within the tag.

[Eugene Greiff]

Well I cracked open the SQL Tag code to see why the CDATA tag wasn’t doing what I expected.

 

Nick hit it on the head. The <sql:query> tag processes the SQL statement through two XML processors via a pipeline so the CDATA tag is gobble up in the first processor and not passed onto the second processor. Hear I was thinking all my SQL statements where safe using the CDATA tag.

 

As Andrew’s Correct Answer pointed out the escapeText="false" attribute sets the setEscapeText to false on every XML processor that is use in the pipeline.

 

V/r,
Gene

[Andrew LERNER]

Try adding escapeText="false" to your sql:query element

 

 

<sql:query var="infoResource" escapeText="false">

[Andrew LERNER]

I would also explicitly force the string->date conversion as David suggests.

[Anon Anon]

Many thanks,

the issue escapeText="false" works perfectly.