Clarity

  • 1.  Process with GEL Script

    Posted Feb 14, 2018 07:20 PM

    Good Afternoon Community,

     

    I could use some quick help here (an fresh set of eyes).  I have a gel script that I have put into a process.  The end goal is to get a CSV full of data (5 columns wide).  I have not yet limited the number of records as I am still in the testing process and have not yet learned how to introduce a input parameter. 

     

    I realize the answer is written within the code and that I just am not seeing it.  I can create the output file to the specified directory, but I get no data.  The query works as I have tested in in SQL Server Management studio and it returns over 15,000 records in a second or two.

     

    My code:

    <gel:script
        xmlns:core="jelly:core"
        xmlns:email="jelly:email"
        xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
        xmlns:ftp="jelly:com.niku.union.gel.FTPTagLibrary"
        xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
        xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
        xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
        xmlns:sql="jelly:sql"
        xmlns:util="jelly:util"
        xmlns:xog="http://www.niku.com/xog"
        xmlns:xsd="http://www.w3.org/2001/XMLSchema"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <core:catch var="gException">
        <!--This determines the directory where all output files will be saved-->
        <gel:parameter var="outputFileBaseDir" default="D:\\mictog\\geltest"/>
        <core:new className="java.util.Date" var="currDate"/>
        <core:set var="currDate">
          <gel:formatDate format="yyyy-MM-dd" dateVar="currDate"/>
        </core:set>
        <core:set var="outputFile" value="${outputFileBaseDir.concat('.csv')}" />
        <gel:log category="User-defined process" level="INFO" message="Running on file ${outputFile}" />
     <gel:setDataSource dbId="NIKU"></gel:setDataSource>   
    <!-- Start writing CSV file-->
        <file:writeFile delimiter="," fileName="${outputFile}" >
         <gel:log category="User-defined process" level="INFO" message="Writing column headers " />
          <file:line>
          <file:column value="NoteID"/>
            <file:column value="IdeaNbr"/>
        <file:column value="IdeaID"/>
            <file:column value="DiaryEntry"/>
        <file:column value="LatestEntryDate"/>
          </file:line>
          <!--User Details-->
          <sql:query escapeText="false" var="user_query">
            <![CDATA[
            SELECT
              n.id as NOTEID
              , i.code
              , SRNBR.ph_cherwell_sr_id
              , ide.id
              , n.diary_entry
              , max(n.entry_date) as LatestEntryDate

         FROM   NIKU.INV_INVESTMENTS i JOIN NIKU.INV_IDEAS IDE
               ON i.ID = IDE.ID
            LEFT JOIN [niku].[ODF_CA_IDEA_NOTES] N  
               INNER JOIN (
                                  SELECT
                                    MAX(N.last_updated_date) AS MaxLastUpdateNote
                                    , N.ODF_CNCRT_PARENT_ID
                                  FROM niku.odf_ca_idea_notes N
                                  GROUP BY N.ODF_CNCRT_PARENT_ID
                                 ) AS UniqueNote
              ON uniquenote.ODF_CNCRT_PARENT_ID = N.odf_parent_id
                AND uniquenote.maxLastUpdateNote = N.last_updated_date
              ON IDE.id = N.ODF_CNCRT_PARENT_ID
           LEFT JOIN NIKU.ODF_CA_IDEA AS SRNbr
            ON IDE.id = srnbr.id

         WHERE I.ODF_OBJECT_CODE='IDEA'
               AND SRNBR.PARTITION_CODE = 'IST'
              AND (SRNBR.PH_CHERWELL_SR_ID IS NOT NULL
                   OR SRNBR.PH_CHERWELL_SR_ID IS NULL)

         GROUP BY
             N.ID,
             , I.CODE
             , IDE.ID
             , N.DIARY_ENTRY
             , SRNBR.PH_CHERWELL_SR_ID
          ]]>
          </sql:query>
          <core:forEach items="${user_query.rowsByIndex}" trim="true" var="row">
            <file:line>
              <core:forEach indexVar="c" begin="0" end="15000" >
              <gel:log category="User-defined process" level="info" message="inside file loop " />
                <file:column value="${row[c]}"/>
              </core:forEach>
            </file:line>
          </core:forEach>
        </file:writeFile>
         <gel:log category="User-defined process" level="info" message="End of the process " />
      </core:catch>
      <core:if test="${!empty(gException)}">
        <gel:log>
          ${gException}
        </gel:log>
      </core:if>
    </gel:script>

     

    I think my problem is in the Core:forEach segment, I feel this is not as well defined (covering the 5 columns) as it should be, but at the same time I do not (yet) have the knowledge.

     

    I appreciate the help.

     

    Thank you.

     

    MT



  • 2.  Re: Process with GEL Script
    Best Answer

    Posted Feb 15, 2018 02:15 AM

    The created file is empty or just has commas?

    Is the message "inside file loop" seen in process messages?

    Also, your inner forEach should loop just 5 times(number of columns), but it appears to loop 15001 times.



  • 3.  Re: Process with GEL Script

    Posted Feb 15, 2018 11:31 AM

    Sridhar,

    Thank you for your reply; I have made the correction to my forEach as you mentioned.  After than I reran the process with the following results:

     

    The file created is empty, no data, no comma separators

     

    I am running on 15.3, it is set up to log errors only as part of the messaging.  The process (job) did not fail; it just does not return data. I believe this can be changed to include more logging (based on: https://communities.ca.com/thread/241785274-gel-script-log-not-displayed-153).  I will talk with my administrative partner to change this, at least temporarily in our DEV environment to see if more information can be gathered.

     

    Thank you again.

     

    MT



  • 4.  Re: Process with GEL Script

    Posted Feb 15, 2018 12:10 PM

    GUESSING : Maybe looping (and writing log output) 15000 times for each record returned by the query is blowing some limit somewhere? How many records should your query return - if its a lot then that is a lot of log messages (in fact just one record might be considered a lot of log messages!)

     

    I wouldn't loop there anyway, just reference the row fields directly, row[0], row[1] etc



  • 5.  Re: Process with GEL Script

    Posted Feb 15, 2018 01:17 PM

    I cut back the looping to (5) based on and did turn on the logging based on Sridhar's response.  At this time I am the only person in dev and I will revert the logging back to errors only when we get passed this process.

     

    Right now, the query returns 15k rows, 5 columns wide.  And, yes, the log exploded to one line per row--we will be turning that off soon. 

     

    Thanks to both Sridhar and Dave for their replies which led me to the successful completion of this phase.

     

    MT