Why is loop required for pulling data from a DB in AA 5.5.1

[trentonponder]

Prior to this version of AA, I could execute the following task:

Execute SQL Select Query
If DataSet(1)='blank" Then
Run Command
End If

This no longer works. Now, in order to use the DataSet Variable, you must access it in a loop, so the above would become:

Execute SQL Select Query
Start Loop each row of SQL
If DataSet(1)='blank" Then
Run Command
End If
End Loop

This becomes an issue, because when if testing to see if there are any results for a Select query and there isn't then there is nothing to loop through. This makes the task more complex as you need to add tests for 'counter.' In other words, there are workarounds, but why was this changed?

[forumsupport]

Hello,

The reason is, the database connection maintains specific session name.
From version 5.5, we have added nested loop support for the Database commands using session functionality so you can retrieve data using nested loops from different databases.

To achieve the mentioned task, you can assign zero to any user variable and inside 'Loop for Each row in a SQL query dataset of Session" increment the variable value to get total number of records fetched. The text format of commands would appear something like this,

1 Connect to 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDB.mdb;Persist Security Info=False' Session: 'Session1'
2 Execute SQL Statement: Select * from MyTable Session: 'Session1'
3 Variable Operation : 0 To $Prompt-Assignment$
4 Start Loop " Each row in a SQL query dataset of Session = Session1"
5 Variable Operation : $Prompt-Assignment$+1 To $Prompt-Assignment$
6 End Loop
7 If $Prompt-Assignment$ Equal To "0" Then
8 Message Box: "Zero records returned"
9 Else
10 Message Box: "$Prompt-Assignment$ records returned"
11 End If

Hope that helps.

[trentonponder]

I know how to get around this shortcoming, but it still needlessly adds complexity to simple tasks.

I have many tasks that run multiple SQL aggregate SELECT statements that will never have more then one result. There is no need to use loop as there is nothing to loop through. Upgrading to 5.5 caused these tasks to cease functioning and I was forced to switch back to 5.0

The ability to use nested loops was a much needed addition, but in order for it to be fully functional you should be able to define the SQL session in the DataSet() variable not just within the loop.