<<sql>>

The <<sql...>> function allows you to create data tags based on the data returned by a SQL statement, specifically a SELECT statement. All records returned by the statement are stored in the newly created data tag, commonly referred to as a SQL flunky. Each field of each record returned by the SELECT statement can be accessed in the flunky.

The flunky created is named the same as the argument you provide. This SQL flunky has a scope limited to the script within which it is contained.

The <<sql...>> function is normally used to retrieve a small number of records, usually consisting of one or two selected fields, to retrieve data using a simple SELECT statement, where otherwise it may be necessary to create a more complex statement within the script. The statement used in the function call should never be used to perform the main processing of the script, nor to return large numbers of records. Rather, it should be used to aid in this main processing. Furthermore, the statement should never contain UPDATE or INSERT statements. Additionally, if the SELECT statement is returning more than 10 records at a time, the design of your script should be reevaluated and adjusted so that this is not the case. The main reason for this is performance.

While the <<sql...>> function will not cause any delays or hitches in processing if used correctly, using it to return large amounts of data will slow down the processing of the Agentry Server considerably. Each record returned by the function must be processed by the Agentry Server and stored in memory until the script has completed processing. This can tie up a significant amount of the system resources in the event of a large number of records being returned.

As stated, the SQL flunky created by the <<sql...>> function call is only in scope within the script in which it is called. If the value is needed in other Steps within the same parent definition, the desired values can be assigned to a local flunky, via use of the <<local...>> function described previously.

<<sql.nameArg[.recordIndex][.fieldName]>>

All SQL flunkies are referenced beginning with sql. The nameArg is the name of the argument as you provided when calling the <<sql...>> function. The recordIndex is a numerical value indicating which record within the data set you wish to access. The records are referenced in the order in which they were returned by the database system, and are indexed starting with 0. The field name is the name of the column, or its alias, that contains the data you wish to retrieve. So, to access a field named COST in the first record of a SQL flunky named prodCost, the tag would be <<sql.prodCost.0.COST>>

Arguments

Parameters

None