|
Passes a script of one or more SQL statements for execution by the Rules Engine.
Syntax
SqlScript( { sql-command-setting | sql-statement; }
... )
sql-command-setting := SET setting-name setting-value
A script is a sequence of commands made up of :
•Optional sql-command-settings, which are not followed by a separator symbol, •One or more sql-statements, each followed by a semicolon (;), or whatever other separator symbol has been set (see the 'separator' setting). If there is a single sql-statement, the separator symbol is optional. For a list of sql-command-settings, go to SQL command settings.
Each sql-statement is an INSERT, UPDATE, DELETE, INVOKE, or SELECT statement.
Example
SqlScript(
SET quitZeroRows False
SET quitOnError True
INVOKE RulesEngine.MessageLanguage
WITH
SELECT ...;
SELECT Usxsl.Apply2File(
...);
)
By default, if a script is executed by SqlScript() messages are NOT passed to the caller. This is a notable difference between SqlScript() and executing the script directly in the SQL Command window. You can get SqlScript() to pass messages by including the setting:
SET showErrorMessages True
If you want ALL messages to be passed, include:
SET showMessages True
|
Use the StartCatchingErrors(), StopCatchingErrors() and GetLastCaughtErrors() methods of the RulesEngine internal component if you want to check if any errors, or a specific error, have occurred during execution of the SQL script. Reported error messages can be stored in the database or saved to a file if required.
Example
SqlScript(SELECT RulesEngine.StartCatchingErrors('No'))
compute-set(:ext_set_name, :2, :3)
ActionDecision(TB_COMPUTE_ELEMENTS_STORE_REC, :4)
SqlScript(
SET quitzeroRows False
SET showMessages True
SELECT RulesEngine.StopCatchingErrors();
UPDATE t_ext_set
SET correct = DECODE(RulesEngine.GetLastCaughtErrors() , NULL, 'Y', 'N')
WHERE set_name = :ext_set_name
)
|
Using SqlScript() you can set a field in a USoft C/S interface to a value retrieved by a query. Use a column alias in the SELECT list that names the column control (the field). The following statement sets a field based on a PARTICIPANT data source item to a retrieved PERSON_NAME column value:
SqlScript(
SELECT p.person_name "PARTICIPANT"
FROM passenger p
WHERE p.person_id = ...
)
The column alias refers to the field by name. It addresses a "PARTICIPANT" field in the main column box - the column box corresponding to the window's default data source.
To address a field in a more specific location, for example, a "PARTICIPANT" field on a tab page, supply a more specific path:
Tab_1.Page_1.SqlScript(
SELECT p.person_name "PARTICIPANT"
FROM passenger p
WHERE p.person_id = ...
)
TIP: Use the tree view in the Object Activator tool to compose such a path.
|
To catch values retrieved by Decision SQL and use them in SqlScript(), you need to cast them in global variables because substitution placeholders of type :1, :2 will not be resolved by SqlScript(). The ResourceFileUpdate() action allows you to do this:
(Decision SQL:)
SELECT :1
, :2
, typename
FROM t_e_type
WHERE application_id = :2
AND is_class ='Y'
AND typename IN
(
SELECT page_name
FROM t_page_in_page_set pps
, t_e_type tt
WHERE pps.page_set_name = :1
AND tt.id = :2
AND pps.application = tt.typename
)
(Yes Action:)
ResourceFileUpdate('windowToList:' || :3)
SqlScript(
INSERT INTO windows_in_set( name )
SELECT :windowToList
FROM dual
WHERE NOT EXISTS(
SELECT ''
FROM windows_in_set
WHERE name = :windowToList
)
)
CAUTION: With this technique, you are effectively creating global variables that are available in the entire application session. This is always dangerous because it is hard to know exactly when they are next referred to.
|
Within a script, you can create a variable and then refer to it in one or more subsequent steps. Use a SELECT statement to retrieve the value and a column alias to name the variable:
SqlScript(
SELECT FLOOR( num_years / 365 ) "myDaysVar1"
FROM ...;
INSERT INTO ( ... )
SELECT 'yes'
WHERE :myDaysVar1 > 14
AND ...;
)
CAUTION: With this technique, you are effectively creating global variables that are available in the entire application session. This is always dangerous because it is hard to know exactly when they are next referred to.
|
See Also
SQL Command settings
|
