-
1. Re: Using a FOR loop to read a SQL query
Jake Morgan Mar 9, 2010 6:23 PM (in response to anthony burks)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 Mar 9, 2010 6:26 PM (in response to Jake Morgan)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
Jake Morgan Mar 9, 2010 6:50 PM (in response to anthony burks)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 Mar 10, 2010 10:29 AM (in response to Jake Morgan)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
Robin Elliott Mar 10, 2010 10:39 AM (in response to anthony burks)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 VriesMar 10, 2010 10:38 AM (in response to anthony burks)
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 Mar 10, 2010 11:05 AM (in response to Richard De Vries)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 VriesMar 10, 2010 11:05 AM (in response to anthony burks)
Hold on, I will get you an example!
-
9. Re: Using a FOR loop to read a SQL query
Richard De VriesMar 10, 2010 11:25 AM (in response to Richard De Vries)
-
10. Re: Using a FOR loop to read a SQL query
anthony burks Mar 11, 2010 3:03 PM (in response to anthony burks)Thanks everyone for your help, this is exactly what I needed!