In USoft SQL, you can refer to substitution variables referred to as host variables that have previously been set using the USoft action language. USoft treats host variables in the same way that it treats resource settings read from the "usdi" and "usdiw" resource files in the \APP subdirectory of the USoft installation directory.
Functionally, the value of a host variable has global scope and remains referenceable from the time when it is set until the time when the same variable is set to a different value, or when the USoft session ends.
This help topic only shows how you set and get values of host variables generally, for example, in SQL Command. In the USoft toolset, there are multiple other, tool-specific contexts where you can set and get host variables, for example, Web Designer's $.udb.executeSQLStatement( ) call. Consult the Web Designer Guide, Windows Designer Gudie and Service Definer Guide for details.
You can pass input values as decision variables to decisions, and then refer to them by position using :1, :2 ... For details, go to " Decision variables and placeholders ".
In the USoft action language, you can set a host variable by calling the UpdateResourceFile() action and passing it a resource setting. The syntax of a resource setting is the same as in the "usdi" and "usdiw" resource files:
It is customary, but not necessary, to start name with an asterisk (*) to draw attention to the fact that the variable is globally available and will overwrite any previous settings for name. An alternative way of indicating this is to start each name with the prefix "global.".
It is customary, but not necessary, to set properties of an object by using the following spelling for name:
In value, the backslash character ( \ ) is a reserved symbol for continuing the resource setting on a new line. You can escape backslashes by calling update-resource-keep-backslash() instead of UpdateResourceFile(). This is a variant of update-resource(), an old but still supported synonym for UpdateResourceFile().
UpdateResourceFile( userName: USD91_ADMIN )
UpdateResourceFile( User.name: USD91_ADMIN )
UpdateResourceFile( *scriptFile : stdout )
UpdateResourceFile( global.scriptFile : stdout )
UpdateResourceFile( *script.File : stdout )
update-resource-keep-backslash( 'tempfolder:' || C:\Temp )
update-resource-keep-backslash( 'installfolder:' || :myFolder )
In a USoft SQL context, you can get (= reference) the value of a host variable by using a colon ( : ) followed by the variable name:
The following is an example of using host variables in a USoft SQL context, such as SQL Command. The result of this example is the first 4 records of the result set returned by the query:
action ResourceFileUpdate( *min: 1 )
action ResourceFileUpdate( *max: 4 )
SELECT RECORDS( :min, :max ) * FROM person;
The following is an example of using host variables in the Yes Action of a decision, which is a USoft action language context. The expression :1 refers to the first element of the select-list of the Decision's SQL Statement:
update-resource( global.busArea: SALES )
update-resource-keep-backslash( 'tempfolder:' || :1 )
set quitzeroRows False
set quitOnError True
UPDATE t_dl_release_frame SET temp_folder = :"tempfolder" WHERE bus_area = :"global.busArea"
SQL and the Rules Engine
Decision variables and placeholders