Clarity

 View Only
  • 1.  GEL Script Question

    Posted Jun 18, 2021 03:15 PM
    Hello community,

    Could someone help me with a script, I am trying to create a query nested with gel script that I put below:

    <gel:script xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:core="jelly:core" xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sql="jelly:sql" xmlns:xog="http://www.niku.com/xog" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <gel:setDataSource dbId="niku" var="nikuDS"/>
    <gel:parameter default="" var="xog_user"/>
    <core:catch var="erro">
    <sql:query dataSource="${nikuDS}" escapeText="false" var="rs_ideia"><![CDATA[SELECT
    inv_ida.code,
    inv_ida.name,
    ida.partition_code
    FROM
    inv_investments inv_ida
    JOIN odf_ca_idea ida
    ON inv_ida.id = ida.id
    WHERE
    inv_ida.id = ?::bigint]]>
    <sql:param value="${gel_objectInstanceId}"/></sql:query>
    <sql:query dataSource="${nikuDS}" escapeText="false" var="rs_dwh_value"><![CDATA[SELECT
    RespImpact.dwh_value id_aplicacion
    FROM
    inv_investments inv_ida
    JOIN ODF_MULTI_VALUED_LOOKUPS RespImpact
    ON inv_ida.id = RespImpact.pk_id
    where RespImpact.attribute = 'pac_app_impac'
    and RespImpact.pk_id = ?::bigint]]>
    <sql:param value="${gel_objectInstanceId}"/></sql:query>
    <core:forEach items="${rs_dwh_value.rows}" var="ap">
    <sql:query dataSource="${nikuDS}" escapeText="false" var="rs_resources"><![CDATA[SELECT
    pr.prresourceid recursoid
    FROM
    PRTeam pr
    where pr.prprojectid = ${ap.id_aplicacion}]]>
    <sql:param value="${gel_objectInstanceId}"/></sql:query>

    <core:if test="${rs_resources.getRowCount() &gt; 0}">
    <core:set value="${rs_ideia.rows[0]}" var="ideia"/>
    <core:new className="com.niku.union.security.DefaultSecurityIdentifier" var="secId1"/>
    <core:invokeStatic className="com.niku.union.security.UserSessionControllerFactory" method="getInstance" var="UserSessionCtrl"/>
    <core:invoke method="init" on="${UserSessionCtrl}" var="secId3">
    <core:arg type="java.lang.String" value="${xog_user}"/>
    <core:arg type="com.niku.union.security.DefaultSecurityIdentifier" value="${secId1}"/>
    </core:invoke>
    <core:set value="${secId3.getSessionId()}" var="sessionID"/>
    <gel:parse var="xog">
    <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_customObjectInstance.xsd">
    <Header action="write" externalSource="NIKU" objectType="idea" version="15"/>
    <Ideas>
    <Idea name="${ideia.name}" objectID="${ideia.code}">
    <CustomInformation>
    <ColumnValue name="partition_code">${ideia.partition_code}</ColumnValue>
    <ColumnValue name="pac_resp_app_impac">
    <core:forEach items="${rs_resources.rows}" var="resource">
    <Value>${resource.recursoid}</Value>
    </core:forEach>
    </ColumnValue>
    </CustomInformation>
    </Idea>
    </Ideas>
    </NikuDataBus>
    </gel:parse>
    <soap:invoke endpoint="internal" var="result">
    <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="$xog"/>
    </soapenv:Body>
    </soapenv:Envelope>
    </soap:message>
    </soap:invoke>
    <gel:set asString="true" select="$result/soapenv:Envelope/soapenv:Body/XOGOutput/Status/@state" var="xog_status_state"/>
    <gel:set asString="true" select="$result/soapenv:Envelope/soapenv:Body/XOGOutput/Statistics/@failureRecords" var="xog_failure_records"/>
    <core:if test="${(xog_status_state == 'FAILURE') || (xog_failure_records &gt; 0)}">
    <gel:log level="debug">[CAS] XOG que foi executado.</gel:log>
    <gel:log level="debug">
    <gel:expr select="$xog"/>
    </gel:log>
    <gel:log level="debug">[CAS] Resultado do XOG que foi executado.</gel:log>
    <gel:log level="debug">
    <gel:expr select="$result"/>
    </gel:log>
    <gel:log level="error">[CAS] Erro na execução do xog.</gel:log>
    </core:if>
    <soap:invoke endpoint="internal" var="logout_session">
    <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>
    </core:if>
    </core:forEach>
    </core:catch>
    <core:if test="${erro != null}">
    <gel:log level="error">[CAS] Erro durante execução do script: ${erro}</gel:log>
    </core:if>
    </gel:script>

    But it gives me the following error:

    [CAS] Erro durante execução do script: org.apache.commons.jelly.JellyTagException: null:33:30: <sql:query> SELECT pr.prresourceid recursoid FROM PRTeam pr where pr.prprojectid = : The column index is out of range: 1, number of columns: 0.

    Does anyone know why this error is due?

    I must clarify that the queries do bring me data when testing them from the searches.

    Greetings,

    Daniel


  • 2.  RE: GEL Script Question

    Broadcom Employee
    Posted Jun 23, 2021 05:15 PM
    H Daniel, I would suggest you might need to introduce some logging in here so you can see your parameters and other data points.
    For example you have this query which it seems where it is erroring out
    CDATA[SELECT
    pr.prresourceid recursoid
    FROM
    PRTeam pr
    where pr.prprojectid = ${ap.id_aplicacion}]]>
    One of the reasons perhaps is that ${ap.id_aplicacion} which parameter you passing might not be resolving properly, so maybe you should try to output the value first to ensure it is what is expected.

    Regards,
    Karl