calling a transaction in BLS MII 12.0

[Former Member]

What is the best way to call an existing transaction because I do not want to place the same action 5-6 times in one transaction.

Situation...

For example, I have 5 sequences.

Seq1 -> SQL qry has five parameters.

Seq2 -> executes

Seq3 -> executes

Seq4 -> executes a tag qry, but would like to insert data into the Seq1 action.

Seq5 -> executes

In Seq4 I have a tag qry that executes normally, but I would like to have the parameter data inserted into Seq1 SQL qry. Doing this, will it execute the the SQL qry or because we are further down the ladder, it will not execute. Is this good practice and if there is a better methods, please let me know.

[sidnooradarsh]

Hi,

If my understanding is right about your situation what you are trying to do is get 5 rows of data from Tag Query and insert all of them using one single call to SQL query.

In this case,

1) Do a tag query get all rows

2) Add a repeater

3) Prepare a string list of row data

4) SQL call after end of repeater and pass this string list as single input to Insert query

Or

if you are sure about 5 rows to be returned then

use a switch block and assign repeater output to each 5 different variables

finally make an SQL call passing all these 5 variables as input params to SQL

(I would prefer the first one)

Hope this helps!!

Regards,

Adarsh

[Former Member]

No, what I am trying to do is take data from certain actions at any point of the transaction and insert them into an sql qry that was executed in the first sequence. I am just simply trying to eliminate the need for creating an sql qry action five different times in the transaction.

[sidnooradarsh]

Ooops!! I completely missed it.

As Chanti has said it all boils down to the logic you are trying to implement like the need for executing same query multiple times.

Yes for sure the control doesn't back to Seq 1 unless its in a loop else go for an transaction call placing the commonly executed SQL query in separate transaction.

Regards,

Adarsh

[Former Member]

I agree, without knowing what you are trying to achieve, it is hard to give you a definitive answer.

I had a similar situation, where I put the SQL action under a repeater, but the transaction turned into a real dog... Lots of SQL actions created.

Here is one way to cut down on the extra work.

--Execute SQL Action for a larger data set (remove some or all of your parameters)

--Use Repeaters with XPath expression to filter results from your query.

Col1 Col2 Col3 Col4

X----1

---

1----2

X----2

---

6----8

Y----3

---

5----7

Z----7---9-----1

Z----4---8-----6

Lets say your SQLQuery returned the result set above. You then wanted to query it based on a tag query that returns a value for Col1.

With the result set above, this could yield more than one row, in that case a repeater would be used.

For simplicity, lets say you use an output link of your tag query to assign the tag query return value to Local.TagValue

To iterate through all of the SQL Query result rows where Col1 = Local.TagValue, I would use the following XPath expression in my repeater.

sqlMyQuery.Results{/Rowsets/Rowset/Row[Col1='#Local.TagValue#']}

If Local.TagValue were 'X', then at runtime the part in brackets would be [Col1='X']

This repeater would iterate through the first two rows of the result set above

One trick, once you have figured out your XPath expression, copy it and paste it into the Action Description, then remove the square bracket section from the XPath. Otherwise you won't get the structure and result set preview for the repeater in the link editor. Replace once you are ready to test

Hope this helps to get you going in the right direction. Let me know if you have any questions.

Rod Hoffman

[Former Member]

Can't say for sure unless we know the details of your logic. But, you could use a 'Repeater' or 'For Next Loop' actions.

May be you can have a transaction for the SQL query and use a 'Transaction Call' when needed.

Chanti.

[Former Member]

The transaction call still would have to be called every time throughout the transaction. I would have to call it roughly 5 times. I am trying to reduce support and repetition.