GEL: Logging an Array

[Legacy User]

Say I have the following statement:

 

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

  select

  *

  from

  bpm_run_processes

  where

  to_char(created_date, 'YYYY-MM-DD') = to_char(sysdate, 'YYYY-MM-DD')

</sql:query>

  <core:forEach indexVar="I" items="${myQuery.rows}" var="row">

 

Instead of looping through each row and doing a <core:set> on each column, I want to get the entire array from ${myQuery.rows} set to a string and log it out. I am not concerned about any one particular column or row, I want the entire array in a single string in the most efficient fashion; assuming that is possible.

 

Thanks.

[Eugene Greiff]

I currently can't get to my backend database to test this but I think it should look like this.

 

<core:set value="${java.util.Arrays.toString(myQuery.getRows().entrySet().toArray())}" var="theString" />

 

V/r,

Gene

[Legacy User]

Gene-

 

I tried this inside and outside the loop with no luck. I assume this would replace the need for a core:forEach as you are throwing the entire array into a single string and setting that to a variable.

 

Thanks. 

[Eugene Greiff]

Well it wasn't as straight forward as I thought:

 

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

    <!-- Connecting to Database -->
    <sql:setDataSource url="jdbc:sqlserver://localhost;databaseName=niku;integratedSecurity=true;" driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" user="niku" password="password" var="clarityDS" />
    <sql:query dataSource="${clarityDS}" var="myQuery">
        <![CDATA[    select * from niku.SRM_RESOURCES ]]>
    </sql:query>
    
    <core:new className="java.lang.StringBuilder" var="stringBuilder" />
    <core:set value="${myQuery.getRowsByIndex()}" var="rows" />
    <core:forEach var="row" items="${rows}">
        <core:invokeStatic className="java.util.Arrays" method="deepToString" var="rowString">
            <core:arg value="${row}" />
        </core:invokeStatic>
        <core:invoke method="append" on="${stringBuilder}">
            <core:arg value="${rowString}" />
        </core:invoke>
                <core:invoke method="append" on="${stringBuilder}">
            <core:arg value='${System.getProperty("line.separator")}' />
        </core:invoke>
        <gel:out>${stringBuilder.toString()}"</gel:out>
    </core:forEach>
</gel:script>

 

 

V/r,

Gene

[Eugene Greiff]

Refactor the script to insert line breaks and print out only once (not in the loop).

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

    <sql:setDataSource url="jdbc:sqlserver://localhost;databaseName=niku;integratedSecurity=true;" driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" user="niku" password="password" var="clarityDS" />
    <sql:query dataSource="${clarityDS}" var="myQuery">
        <![CDATA[    select * from niku.SRM_RESOURCES ]]>
    </sql:query>
    <core:new className="java.lang.StringBuilder" var="stringBuilder" />
    <core:forEach var="row" items="${myQuery.getRowsByIndex()}">
        <core:invokeStatic className="java.util.Arrays" method="deepToString" var="rowString">
            <core:arg value="${row}" />
        </core:invokeStatic>
        <core:invoke method="append" on="${stringBuilder}">
            <core:arg value='${rowString.concat(systemScope.getProperty("line.separator"))}' />
        </core:invoke>
    </core:forEach>
    <gel:log>StringBuilder Output</gel:log>
    <gel:log>${systemScope.getProperty("line.separator").concat(stringBuilder.toString())}"</gel:log>
</gel:script>

 

 

V/r,

Gene

[Colum Creed]

Another option is to concatenate all the fields into one string in your query.

[Legacy User]

The third option worked. I was considering doing a listagg and I suppose I could do that in a sql update (the end point of the result set will be a string field).

 

I ran it on a wildcard query for project name, and I think I got some weird characters that affected the parse and line separator.

[brimmasch]

<gel:script
  xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
  xmlns:core="jelly:core"
  xmlns:sql="jelly:sql">


  <!--Another approach-->
  
  <gel:setDataSource dbId="niku" />


  <sql:query var="someQuery">
    <![CDATA[
    select
      *
    from inv_investments
    ]]>
  </sql:query>
  
  <core:forEach items="${someQuery.rows}" var="row" >
    <core:invokeStatic var="rowString" className="org.apache.commons.lang.ArrayUtils" method="toString" >
      <core:arg type="java.lang.Object" value="${row}" />
    </core:invokeStatic>
    <gel:log level="DEBUG">${rowString}</gel:log>
  </core:forEach>


</gel:script>

 

Use the apache commons libraries that ship with Clarity.