Skip navigation
All People > Georgy N Joseph > Georgy's Clarity PPM Blog > 2017 > July > 28

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