Aurora_Gaimon

CA PPM: How does "getRowCount()" work in GEL?

Blog Post created by Aurora_Gaimon Employee on Mar 29, 2017

I'm pretty sure in one or more occasions you need to get a total count of rows returned, either for debugging, for control or just for a conditional case. How does it work?

 

You can either use getRowCount()  or just rowCount. It does the same thing

.

 

Basically, when a SQL query runs against database (DB), it will return "null" or "Blank" results. Therefore, 0 records.

 

 

When getting the total row count from a SQL query in GEL scripting:

  • Iit will always return '0' (if no records)
  • It will always return greater value (if there are records)
  • It will never return "null" or "blank"

 

Sample with getRowCount():

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

     <gel:setDataSource dbId="niku"/>

     <sql:query escapeText="false" var="test">
      select id from inv_investments where id=9999
     </sql:query>
     <gel:log> Results: ${test.getRowCount()}</gel:log>

     <core:if test="${test.getRowCount()!='0'}">
          <gel:log> ${test.getRowCount()} rows </gel:log>
     </core:if>

     <core:if test="${test.getRowCount()=='0'}">
          <gel:log> '0' rows </gel:log>
     </core:if>

     <core:if test="${test.getRowCount()=='null'}">
          <gel:log> 'null' rows </gel:log>
     </core:if>
         
</gel:script>

 

Sample with rowCount:

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

     <gel:setDataSource dbId="niku"/>

     <sql:query escapeText="false" var="test">
      select id from inv_investments where id=9999
     </sql:query>
     <gel:log> Results: ${test.rowCount}</gel:log>

     <core:if test="${test.rowCount!='0'}">
          <gel:log> ${test.rowCount} rows </gel:log>
     </core:if>

     <core:if test="${test.rowCount=='0'}">
          <gel:log> '0' rows </gel:log>
     </core:if>

     <core:if test="${test.rowCount=='null'}">
          <gel:log> 'null' rows </gel:log>
     </core:if>
         
</gel:script>

 

 

Results:

 

 

**Note: This did not change amongst CA PPM releases.

 

 

 

To Sum Up:

 

When there are not results...Returns:
Query against DB  

"Blank" or "nulls"

Query in GEL Script - rowcount

0

Query in Gel Script - getRowCount()

0

 

 

Counting rows is and invaluable tool and very useful when creating conditions with IF, or CASE or WHEN.. etc. If you did not start using it, I encourage to consider it for future scripts.

 

 

If you need help with GEL scripting, engage CA Services or ask questions in CA Communities.

 

That’s all. Thanks for reading until here. Did you like it? Please, don’t be shy and share it.

Outcomes