Georgy N Joseph

Iterate Through SQL Query Result Set Using forEach

Blog Post created by Georgy N Joseph on Jul 28, 2017

Hi Friends,

 

We all use <sql:query> tags in our GEL scripts to retrieve values from the database. We also use forEach to iterate through the SQL query result set. But this can be done in many different ways as listed below.

  

1. Using rowsByIndex

 

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

 

<gel:setDataSource dbId="Niku"/>

 

<sql:query escapeText="false" var="resource">
   select
   sr.first_name,
   sr.last_name,
   sr.email
   from
   srm_resources sr
   where id in (5807189,5807190,5807191,5807192)
</sql:query>

 

<core:forEach trim="true" items="${resource.rowsByIndex}" var="row">
   <core:set value="${row[0]}" var="Fname"/>
   <core:set value="${row[1]}" var="Lname"/>
   <core:set value="${row[2]}" var="EmailID"/>
   <core:set var="RowDetails">${Fname}---${Lname}---${EmailID}</core:set>
   <gel:log level="INFO">${RowDetails}</gel:log>
</core:forEach>
</gel:script>

 

2. Using rows

 

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

 

<gel:setDataSource dbId="Niku"/>

 

<sql:query escapeText="false" var="resource">
   select
   sr.first_name,
   sr.last_name,
   sr.email
   from
   srm_resources sr
   where id in (5807189,5807190,5807191,5807192)
</sql:query>

 

<core:forEach trim="true" items="${resource.rows}" var="row">
   <core:set value="${row.first_name}" var="Fname"/>
   <core:set value="${row.last_name}" var="Lname"/>
   <core:set value="${row.email}" var="EmailID"/>
   <core:set var="RowDetails">${Fname}---${Lname}---${EmailID}</core:set>
   <gel:log level="INFO">${RowDetails}</gel:log>
</core:forEach>
</gel:script>

 

3. Using rows and get

 

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

 

<gel:setDataSource dbId="Niku"/>

 

<sql:query escapeText="false" var="resource">
   select
   sr.first_name,
   sr.last_name,
   sr.email
   from
   srm_resources sr
   where id in (5807189,5807190,5807191,5807192)
</sql:query>

 

<core:choose>
<core:when test="${resource.rowCount != 0}">
<core:forEach trim="true" items="${resource.rows}" var="row">
   <core:set value="${row.get('first_name')}" var="Fname"/>
   <core:set value="${row.get('last_name')}" var="Lname"/>
   <core:set value="${row.get('email')}" var="EmailID"/>
   <core:set var="RowDetails">${Fname}---${Lname}---${EmailID}</core:set>
   <gel:log level="INFO">${RowDetails}</gel:log>
</core:forEach>
</core:when>
<core:otherwise>
   <gel:log level="ERROR">No Records Returned</gel:log>
</core:otherwise>
</core:choose>
</gel:script>

 

4. Using columnNames

 

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

 

<gel:setDataSource dbId="Niku"/>

 

<sql:query escapeText="false" var="resource">
   select
   sr.first_name,
   sr.last_name,
   sr.email
   from
   srm_resources sr
   where id in (5807189,5807190,5807191,5807192)
</sql:query>

 

<core:forEach trim="true" items="${resource.rowsByIndex}" var="row">
<core:forEach items="${resource.columnNames}" var="ColName" indexVar="i">
   <core:switch on="${ColName.toUpperCase()}">
   <core:case value="FIRST_NAME">
   <core:set value="${row[i]}" var="Fname"/>
   </core:case>
   <core:case value="LAST_NAME">
   <core:set value="${row[i]}" var="Lname"/>
   </core:case>
   <core:case value="EMAIL">
   <core:set value="${row[i]}" var="EmailID"/>
   </core:case>
   <core:default>
   <gel:log level="WARN">SQL Query returned ${ColName} column</gel:log>
   </core:default>
   </core:switch>
</core:forEach>
<core:set var="RowDetails">${Fname}---${Lname}---${EmailID}</core:set>
<gel:log level="INFO">${RowDetails}</gel:log>
</core:forEach>
</gel:script>

 

 

I hope this will be useful to beginners when they start to retrieve information from the database via GEL scripts 

 

Regards,

Georgy

Outcomes