10 Replies Latest reply on Mar 11, 2010 3:03 PM by anthony burks

    Using a FOR loop to read a SQL query

    anthony burks

      Hello all;

       

      I am trying to run a scheduled job that is basically designed to query a system for any items with attribute "approved" as their status.  This query will typically return a full list of items that qualify, and I'm having some trouble figuring out how to work the FOR loop to cycle through that list the query outputs...the doc is pretty limited on this aspect.  I think this is probably a fairly easy task, but can someone help out with this?

       

      So, basically, it's this:

       

      1.  Query to <db> for all items in "approved" status returns 5 items <abcde>

      2.  For loop takes items and creates individual context for line 1, runs rest of process, line 2, runs process, etc.

       

      Thanks in advance!

       

      -Tony

        • 1. Re: Using a FOR loop to read a SQL query

          Double click on the for icon

          Then as your input use the xml from your query. Now create and xpath //item and assign it to a context of item.

           

          Use the contex of item within the loop.

          • 2. Re: Using a FOR loop to read a SQL query
            anthony burks

            Thanks Jake.

             

            Do i need to format the data coming back from the query in XML , or does the app do it for me?  The query response should just be a straight list, right?

            • 3. Re: Using a FOR loop to read a SQL query

              If u are using the database actor adapter then the result set will come back as xml. Take a look at the results and see if u can use it efficiently..if not transform it into something useful.

              • 4. Re: Using a FOR loop to read a SQL query
                anthony burks

                Ok, I think I'm just confused on how to use the xpath portion of this...I have got the results coming in, the query runs, and i have an Output Mapping set with input as adapter response and output as global context "TicketList".   In the Basic Transform area, I used the standard SQL query adapter response template and replaced it with the resultant text from the query, then set the function to "XML to Text (newlines)".  It doesn't give me any preview though, so I think this might be where i'm going wrong (as well, having it set this way fails to return any context items during testing).

                 

                XML to Text doesn't offer the option of operands, which i'm assuming is OK, but is this the proper function?

                • 5. Re: Using a FOR loop to read a SQL query

                  If you have a SQL SELECT that returns the following response, then in the For Loop you could apply a "BasicTransform" where you would specify one condition "Element Name Matches" with a value of "row".

                   

                  <sqlAdapterResponse>

                    <row>
                      <column name="NAME" label="NAME">George W. Bush</column>
                      <column name="YEARS_IN_OFFICE" label="YEARS_IN_OFFICE">8</column>
                      <column name="FIRST_INAUGURATION" label="FIRST_INAUGURATION">2001</column>
                      <column name="AGE_INAGURATION" label="AGE_INAGURATION">54</column>
                      <column name="STATE_ELECTED_FROM" label="STATE_ELECTED_FROM">Texas</column>
                      <column name="POLITICAL_PARTY" label="POLITICAL_PARTY">Republican</column>
                      <column name="OCCUPATION" label="OCCUPATION">Businessman</column>
                      <column name="PROCESSED" label="PROCESSED">false</column>
                    </row>
                    <row>
                      <column name="NAME" label="NAME">Barack Obama</column>
                      <column name="YEARS_IN_OFFICE" label="YEARS_IN_OFFICE">0</column>
                      <column name="FIRST_INAUGURATION" label="FIRST_INAUGURATION">2009</column>
                      <column name="AGE_INAGURATION" label="AGE_INAGURATION">47</column>
                      <column name="STATE_ELECTED_FROM" label="STATE_ELECTED_FROM">Illinois</column>
                      <column name="POLITICAL_PARTY" label="POLITICAL_PARTY">Democrat</column>
                      <column name="OCCUPATION" label="OCCUPATION">Lawyer</column>
                      <column name="PROCESSED" label="PROCESSED">false</column>
                    </row>

                   

                  This would setup the For Loop so that the output context item defined in the Opening For loop Icon, will have values similar to that shown below for each <row> returned by the query:


                  <row>
                    <column name="NAME" label="NAME">Grover Cleveland</column>
                    <column name="YEARS_IN_OFFICE" label="YEARS_IN_OFFICE">4</column>
                    <column name="FIRST_INAUGURATION" label="FIRST_INAUGURATION">1885</column>
                    <column name="AGE_INAGURATION" label="AGE_INAGURATION">47</column>
                    <column name="STATE_ELECTED_FROM" label="STATE_ELECTED_FROM">New York</column>
                    <column name="POLITICAL_PARTY" label="POLITICAL_PARTY">Democrat</column>
                    <column name="OCCUPATION" label="OCCUPATION">Lawyer</column>
                    <column name="PROCESSED" label="PROCESSED">false</column>
                  </row>

                   

                  An alternative apporach here is to use the SQL Monitor Adapter with a configuration like:

                   

                  <config>
                    <targets>
                      <target name="target1" default="true">
                        <url>jdbc:oracle:thin:@127.0.0.1:1521:XE</url>
                        <password>admin123</password>
                        <user-name>BAO</user-name>
                        <driver>oracle.jdbc.driver.OracleDriver</driver>
                        <validate-connections-on-return>true</validate-connections-on-return>
                        <validate-idle-connections>true</validate-idle-connections>
                        <validate-query>select sysdate from dual</validate-query>
                      </target>
                    </targets>
                    <event-statements>
                      <event-statement>
                        <query>SELECT * FROM US_PRESIDENTS WHERE PROCESSED = 'false'</query>
                        <poll-interval>30</poll-interval>
                        <single-monitor-event>false</single-monitor-event>
                      </event-statement>
                    </event-statements>
                  </config>

                   

                  The example above runs every 30 Seconds and can create multiple single row or a single multi-row inputevent (set with the <single-monitor-event> value of true or false).  Below is an example of the inputevent value when the single-monitor-event is set to false:

                   

                  <sql-monitor-event>
                    <row>
                      <column name="NAME">Barack Obama</column>
                      <column name="YEARS_IN_OFFICE">0</column>
                      <column name="FIRST_INAUGURATION">2009</column>
                      <column name="AGE_INAGURATION">47</column>
                      <column name="STATE_ELECTED_FROM">Illinois</column>
                      <column name="POLITICAL_PARTY">Democrat</column>
                      <column name="OCCUPATION">Lawyer</column>
                      <column name="PROCESSED">false</column>
                    </row>
                    <metadata>
                      <target-name>target1</target-name>
                      <query-executed>SELECT * FROM US_PRESIDENTS WHERE PROCESSED = 'false'</query-executed>
                      <event-timestamp-long>1268238933616</event-timestamp-long>
                      <event-timestamp-string>2010-03-10 10:35:33.616</event-timestamp-string>
                      <execution-milliseconds>94</execution-milliseconds>
                      <row-number>21</row-number>
                      <response-row-count>21</response-row-count>
                      <response-column-count>8</response-column-count>
                      <status>success</status>
                    </metadata>
                  </sql-monitor-event>

                  • 6. Re: Using a FOR loop to read a SQL query
                    Richard De Vries

                    Hello tonality,

                     

                    From my personal experience I'd recommend you try to stick to "advanced" transformations as much as possible. Basic Transformations are nice, but may cause occasional problems.

                     

                    Let's say you are passing the following list as a CTX input to the for-loop:

                     

                    <sql-result>

                      <row index="1">result1</row>

                      <row index="2">result1</row>

                      <row index="3">result1</row>

                    </sql-result>

                     

                    To loop over the rows, you would apply the following xpath transformation:

                     

                    //row

                     

                    And assign that to an output CTX, for instance "Individual_Result".

                     

                    Then, within your Loop, your "Individual_Result" CTX item would contain row1 in your first iterations. row2 in your second iteration etc.

                     

                    Does this make any sense? If not, let me know.

                     

                      R.

                    • 7. Re: Using a FOR loop to read a SQL query
                      anthony burks

                      That was very helpful Richard, thanks!  Everything makes sense except for the //row piece (which is basically where i was stuck before as well, and is likely a lack of understanding on my part) in the xpath.  When setting up an advanced transform editor, where does that place? Is it a token?

                       

                       

                      As well, I have the SQL query running successfully and a CTX output set, but my Context Values are not being populated upon running the script, although I can see the results just fine in the text.  I should be using adapter response as the input on my output CTX right?

                      • 8. Re: Using a FOR loop to read a SQL query
                        Richard De Vries

                        Hold on, I will get you an example!

                        • 9. Re: Using a FOR loop to read a SQL query
                          Richard De Vries

                          Let me know if the attached screenshots help:

                           

                          Sample workflow:

                          workflow.png

                           

                          Sample SQL Result:

                           

                          sample input.png

                           

                          Feeding into For-Loop:

                           

                          forloop.png

                           

                          Row within for-loop:

                           

                          row-within-loop.png

                          • 10. Re: Using a FOR loop to read a SQL query
                            anthony burks

                            Thanks everyone for your help, this is exactly what I needed!