Chat now with support
Chat with Support

Toad Data Point 5.6 - User Guide

Use File Automation Activities

You can use the following activities in automation scripts.

Tip: You can save an activity and its settings as a template to reuse in other scripts. Right-click the activity in the design window and select Save As Template. After you create the first template, a Templates toolbox displays.

Activity Description

Copy File

Copy or move one or more files to another location (for example, to a shared folder where they can be accessed by your business community).

Review the following for additional information:

  • Source file—Select the file to copy.

    In this field, you can also use the asterisk (*) and question mark (?) wildcard characters, as well as variables, in the file name to help you select one or more files (in the same directory) to copy. To use this method:

    1. Specify the file directory by browsing to and selecting one of the files.
    2. Then modify the file name in the Source file field using wildcard characters and/or variables.
  • Copy to folder—Select the destination folder into which you want to copy or move the file or files.

  • Copy name—To rename the copied or moved file, enter a new name.

    Note: This field is only applicable when copying a single source file.

  • Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

  • Delete source file—Select to move the file from the source folder to the destination folder. Deselect to copy the file to the destination folder.
  • Retry—Specify the number of times to retry the copy action if an error or timeout occurs.
  • Wait—Specify the number of seconds to wait between retries.

Delete File

Delete one or more files from one or more specified locations. You can also use this activity to delete files that are created previously in the script.

Files to delete—Click Add Files to select one or more files to delete. In the File Collection Editor, do one or both of the following:

  • To delete files created previously in the script, select a file in the left pane and click Add.

    Note: When a file name is displayed for selection in the left pane, any variable names that are used in the file name are visible (#MyVariable#), but suffixes are not visible.

  • To select other existing files, click to browse to and select the files.

    In this field, you can also use the asterisk (*) and question mark (?) wildcard characters, as well as variables, in the file name to help you select one or more files (in the same directory) to delete. To use this method:

    1. Specify the file directory by browsing to and selecting one of the files.
    2. Then modify the file name in the Files to Delete pane using wildcard characters and/or variables.

    Note: As an example of using wildcards and variables, using the file name MonthlyReport_#var#*.xls, where #var# is Feb, would delete all reports whose file name begins with "MonthlyReport_Feb" in the same directory.

Log Comment

Writes a comment to the scripts's log.

Log message—Enter the text of the comment you want to insert into the log when this activity executes.

Zip/Unzip Files

Create a zip file (compressed folder) and add files to it. You can add files created previously in the script to the zip file. Use this activity to zip multiple files and then attach the zip file to an email using the Email activity.

You can also use this activity to unzip a zip file.

Review the following for additional information:

Zip Tab

  • Archive name—Select a location and name for the zip file. You can use a variable in this field by entering the hash symbol before and after the variable name (#VariableName#).

  • Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

  • Password—Enter a password if you want to password protect the zip file.
  • Hide password—Select this option to mask password during input.

  • Encryption—Select whether to create a zip file without encryption (Standard) or to encrypt the zip file.

    Note: If you encrypt the zip file, you must have a zip utility that can support 128-bit or 256-bit decryption.

  • Zip all files in this directory—Select a directory. All files in the directory will be added to the zip file. You can use both this field and the Source files field to select files to add to the zip file.
  • Source files—Click Add Files to select one or more files to add to the zip file. In the File Collection Editor, do one or both of the following:

    • To add files created previously in the script, select a file in the left pane and click Add.

      Note: When a file name is displayed for selection in the left pane, any variable names that are used in the file name are visible (#MyVariable#), but suffixes are not visible.

    • To select other existing files, click to browse to and select the files.

      In this field, you can also use the asterisk (*) and question mark (?) wildcard characters, as well as variables, in the file name to help you select one or more files (in the same directory) to add. To use this method:

      1. Specify the file directory by browsing to and selecting one of the files.
      2. Then modify the file name in the Files to Archive pane using wildcard characters and/or variables.

Unzip Tab

  • Archive name—Select a zip file.
  • Password—If password-protected, enter the password.
  • Extract to folder—Select a location to extract the files to.

To learn how to use the Zip/Unzip Files activity, see the following:

Find and Replace

This activity opens one or more files and performs find and replace actions inside the files. You can write the results to a different location and file name.

Review the following for additional information:

  • Find/Replace—You can have multiple Find/Replace tasks within one activity. You can specify a different source file, save file, and action for each Find/Replace task.

  • Source file—Select the file in which to perform the find and replace action for the selected Find/Replace task.

    In this field, you can also use the asterisk (*) and question mark (?) wildcard characters in the file name to help you select one or more files (in the same directory). To use this method:

    1. Specify the file directory by browsing to and selecting one of the files.
    2. Then modify the file name in the Source file field using the wildcard characters.
  • Find—Enter the content to find.
  • Replace with—Select the type of replacement content: a value, contents from a file, or a variable value.

  • Set value—Enter the replacement value, select the replacement variable, or select a file containing replacement content.

  • Save file—Select a location and file name.

    The source file is selected by default.

  • Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

Note: Use drag and drop or up/down arrows to change the order of multiple Find/Replace tasks in one activity.

To learn how to use the Find and Replace activity, see the following:

FTP File

Upload or download one or more files using an FTP or SFTP connection. Review the following for additional information:

  • Select an FTP connection—Select an FTP connection or define a new connection.

    Note: You can define a new connection prior to using the FTP activity by selecting View | FTP Connections.

  • Transfer ONLY if—Select this option to set conditions for FTP transfer, and then select a condition from the list. If you select this option, the FTP activity only runs if the condition you specify is met.
    • Local directory exists—Select this option to transfer only if a local directory exists, then browse to and select the directory.
    • Local file exists—Select this option to transfer only if a local file exists, then enter the name of the local file.
    • Remote directory exists—Select this option to transfer only if a remote directory exists, then browse to and select the directory.
    • Remote file exists—Select this option to transfer only if a remote file exists, then enter the name of the remote file.
  • Define Operations—Click to specify the files and other options for the FTP upload or download task. Review the following for additional information:
    • Operation—Select Upload or Download.
    • Use file mask—Select this option if you want to select files by using a file mask. Then specify file mask filtering options in the File Mask fields at the bottom of the page.
    • Move files—Select this option to move the files from the source location to the target location instead of copying them.
  • Retry—Specify the number of times to retry the FTP connection if it fails.
  • Wait—Specify the number of seconds to wait between retries.

To learn how to use the FTP activity, see the following:

Publish Files

Publish one or more files to Intelligence Central. You can select files created by previous activities in this script or other existing files.

Review the following for additional information:

Files to Publish—Click Manage Files to select files created previously in this script or to browse for other files to publish. In the File Collection Editor do one or both of the following:

  • Select one or more previously-created files in the left pane and click Add.

  • Click and then browse to select an existing file.

Publishing Options—Click Publishing Wizard to select an Intelligence Central connection and specify publishing options. See Publish to Toad Intelligence Central for more information.

Notes: 

  • To disable an activity, right-click the activity in the design window and select Disabled. This is helpful if you want to disable an activity while testing other activities.
  • The Stop on Error option for each activity allows you to select whether to stop or continue execution if Toad encounters an error during that activity.
  • To prevent an error in one activity from causing a failure at the script level, clear the Generate Error check box (Activity Info tab) for that activity. See Automation Script Troubleshooting and Error-Handling.
  • Select the Log tab to review a script's execution log.
  • In the Suffix field, the drop-down list includes two predefined suffixes (Date and DateTime). The list also includes any suffixes you have created and saved since installing Toad. To delete a suffix from the list, select the suffix, click , delete the suffix in the Expression field, and click OK.
  • Automation does not support positional parameters denoted by a question mark.

Tips:

  • For activities in which you can add a suffix to the output file, click to build a suffix using the expression editor.
  • You can use the expression editor to select a variable created in a previous activity or select one of your stored bind variables.
  • To learn how to store a bind variable value, see Using Bind Variables.

 

Related Topics

Automate Tasks

Use Database Automation Activities

Using Variables in Automation

Use System Automation Activities

You can use the following activities in automation scripts.

Tip: You can save an activity and its settings as a template to reuse in other scripts. Right-click the activity in the design window and select Save As Template. After you create the first template, a Templates toolbox displays.

Activity Description

Set Variable

Automation variables are a powerful tool that can greatly extend your scripts by adding flexibility and customization. You can use variables to represent and store data that may be different each time the script executes. Variables can be used with almost every Automation activity.

Use this activity to define one or more variables.

Review the following for additional information:

  • Variable name—Enter a name for the variable. When you use the variable name later in the script, for example in a file name or in an expression, surround the variable name with the hash symbol (#MyVariable#).
  • Variable type—Specify a variable type.
    • SQL—Select SQL if you plan to use the result set of a query as the variable value.
    • DateTime—See Using Variables in Automation for more information about date variables.
  • Variable value—Enter a value or click to build an expression.
    • If you click to build an expression, select from the list of variables, operators, and functions. Click a category in the left pane to display a list of items in the right pane. Double-click an item to add it. Click Test to test the expression.

    • If you selected SQL as the variable type, the variable value options expand to allow you to specify a query. Select a database connection, then select a SQL file or enter a SQL statement. Toad uses the result set of the query as the value for your variable.

      See Using Variables in Automation for more information about SQL type variables.

  • Prompt during run—Select this option to instruct Toad to prompt for a new variable value during manual execution. The new value is used for the current execution only. The default value specified in Variable value remains unchanged. The default value is used for a scheduled script.

    If you plan to publish this script to Intelligence Central, see Variables in Views/Scripts and Publishing to Intelligence Central for more information.

  • Add—Click to specify an additional variable/value pair. Use Up and Down buttons to reorder list.

To learn more about variables, see the following:

Set Variable Value

Use this activity to assign a new value to a variable previously defined in the current script or current activity. You can assign a new value to any variable type, except SQL.

Review the following for additional information:

  • Variable name—Select a previously-defined variable.
  • Variable type—Displays the variable type of the selected variable.
  • Variable value—Enter a new value or click to build an expression. For more information, see Variable Value in the Set Variable section above.
  • Add—Click to select an additional previously-defined variable.

See Variable Scope in Using Variables in Automation to learn more about how to use this activity with the Set Variable activity.

If..Condition..

Execute one or more activities if a condition you specify evaluates to true. You can add several branches to the If Condition activity to simultaneously evaluate more than one condition.

To learn how to use the If Condition activity, see the following tutorial:

Use the expression editor to build an expression that evaluates a condition. Typically this includes a variable (user-defined or built-in).

  • Expression—Build your expression by selecting from the list of variables, operators, and functions. Click a category in the left pane to display a list of items in the right pane. Double-click an item to add it. Click Test to test the expression.

This activity can have one or more branches. To delete a branch, select it and press Delete. To add a branch, right-click the activity (the outside loop) and select Add Branch. If you have multiple conditions, they do not have to be mutually exclusive. Multiple branches can be true.

To learn more about variables, see the following:

Loop Dataset

Loops through each row in a dataset and performs the activities you specify. The dataset is the result set of a query you provide (the driving query) and is stored in the dataset variable. Add as many activities as you want to the loop. The Loop Dataset activity can have more than one branch.

Tip: You also have the option to select any SQL-type variable created in a previous activity to use as the dataset variable. Activities that create SQL-type variables include Execute Script, Set Variable, or Loop Dataset.

To learn how to use the Loop Dataset activity, see the following tutorial:

Review the following for additional information:

  • Dataset variable—Enter a new name for the dataset variable or use the default name.
  • Select dataset variable—(Optional) You can select one of the SQL-type variables created in a previous activity to use as the dataset variable.
  • Connection—Select a connection.
  • SQL script file—Select a SQL file to use as the driving query. After the script executes, the result set of the driving query is stored in the dataset variable.
    • Click the drop-down list to select a file from the currently open project in the Project Manager.
    • Click to browse to and select a SQL file.
    • Click to open the SQL file (you just selected) in the Editor where you can modify the SQL.

    Note: Click to preview the query results.

  • Link SQL file—Select to link the SQL script file you selected in the previous step.
  • SQL script—Enter a SQL statement to use as the driving query (if you did not select a SQL file).
  • RowCount variable—Enter a name for the row count variable or use the default. The value assigned to this variable equals the number of rows returned from the activity. See Using Variables in Automation for more information.

Tip: In the Loop Dataset, you can use the following format to filter data from the dataset variable when referencing it: <dataset variable name>.<filter parameter>.

Example: Loop_data_1_SQL.region_id, where region_id is a column name

See Using Loop Dataset Activity for an example of how to use this format.

While..

The While activity runs a loop while a condition you specify is true. The activity ends when the condition becomes false, or when the activity completes the selected number of loops. Each loop executes the activities you specify.

To learn how to use the While activity, see the following tutorial:

Use the expression editor to build an expression that evaluates a condition. Typically this includes a variable (user-defined or built-in).

  • Expression—Build your expression by selecting from the list of variables, operators, and functions. Click a category in the left pane to display a list of items in the right pane. Double-click an item to add it. Click Test to test the expression.
  • Loop—Enter the number of times to loop the activity. If the condition never becomes false, Toad ends the activity after this number of loops. The default is 10.

This activity can have one or more branches. To delete a branch, select it and press Delete. To add a branch, right-click the activity (the outside loop) and select Add Branch. If you have multiple conditions, they do not have to be mutually exclusive. Multiple branches can be true.

To learn more about variables, see the following:

Send Email

Use this activity to distribute reports produced by previous activities in the script or to send notification of script execution status.

Note: To use this activity, an SMTP service must be running.

Review the following for additional information:

  • From—Enter your email address or click to auto-populate this field with your email address.

    Note: If you encounter a security alert message from your email application, and you want to use this auto-populate feature, grant Toad access (at least temporarily).

  • To—Enter an email address or click to auto-populate this field with your email address.

    • Prompt—Select to convert the To field to a variable and instruct Toad to prompt for a new variable value during manual execution. The new value is used for the current execution only. The default value specified in To remains unchanged. The default value is used for a scheduled script.

      If you plan to publish this script to Intelligence Central, see Variables in Views/Scripts and Publishing to Intelligence Central for more information.

  • Email Server Settings—Click this button to specify your SMTP email server settings.

    • Server—Enter the address of your SMTP email server or click to auto-populate this field. If Toad is unable to auto-populate this field, contact your email administrator for the SMTP email server address.

      Note: The email server setting automatically defaults to that specified in the script's Settings (click the Settings icon and then click the Compose Email button).

    • User name / Password—If a non-SSL connection requires authentication, enter credentials. If using a secure connection, enter credentials, select Use Secure connection and select SSL or TLS.
  • Subject, Body—Enter a subject line and enter text for the body of your email.
  • Add Attachments—Click to add files created by this script or to browse for other files to add as attachments. In the File Collection Editor do one or both of the following:
    • Select one or more previously created files in the left pane and click Add.

    • Click and browse to select an existing file.

      In this field, you can also use the asterisk (*) and question mark (?) wildcard characters, as well as variables, in the file name to help you select one or more files (in the same directory) to add. To use this method:

      1. Specify the file directory by browsing to and selecting one of the files.
      2. Then, in the File Collection Editor, modify the selected file name (in the right pane) using wildcard characters and/or variables.

  • Use file for body—Select a file to use as the body of the email. This is useful if you want to include report contents in the body of the email instead of as an attached file. For example, in the Select to File activity you can select HTML as the output file format, and then use this HTML file as the body of the email.

    In another scenario, you can use a form letter as the body of your email. Create a form letter template and then customize the file with the Find and Replace activity. See Automate Find and Replace for more information.

Note: In the To, CC, Subject, and Body fields: You can use a variable in this field by entering the hash symbol before and after the variable name (#VariableName#).

To learn more about how to use email in an Automation script, see the following:

Run Program

Run a program with optional command-line arguments. Review the following for additional information:

  • Program—Select a batch file (.bat) or executable (.exe) file.

  • Arguments—Enter command-line arguments. You can also enter a variable, such as #sleep# in this field.

    Note: If you want to run a macro in Access, enter databasename /x macroname in this field. For example, if you have a macro that beeps and runs in your Northwind database, you would enter Northwind 2007.accdb /x beep.

  • Run directory—Select the directory to change to, when running the program.
  • Run style—Select the window state for the program when it runs.
  • Verb—Enter verbs used when running the program, such as "Print".
  • Wait for program to exit—Select this option to pause if there is an error. Select duration of wait time in minutes.

  • Return code variable—Enter a variable name or use the default name. You can use this variable with the If Condition activity.

    Return code (exit code) values of 16384 (4000 hex) or larger do not generate a script error in this activity. Other return code values do. This allows you to use your program to generate a custom return code value in this range and use it in subsequent activities without generating a script error.

Note: If the script opens a command-line window, you must manually close the window once the script completes.

Run Automation Script

Run another automation script in your current automation script.

Review the following for additional information:

  • Automation script—Select an automation script (.tas file).
    • After selecting a script, click to open the child script in a new window.
  • Return code variable—Enter a variable name or use the default name. The return code variable is a built-in variable. After the child script executes, the return code value is stored in this variable. Return code values are:
    • 0 = Successful execution
    • 999 = An error occurred during execution
    • 111 = An error occurred, but you selected continue on error.

    You can use this variable with the If Condition activity.

Pause

Stops an activity for a specified number of seconds before continuing. This is useful, for example, if you are using parallel activities and need to pause one branch while waiting for results or the complete execution of another branch.

For an example of how to use the Pause activity in an Automation script, see the following:

Group Activities Use this activity to group and organize two or more activities together.
Throw Error

Use this activity to generate an error and add a custom error message to the script execution log.

Select Stop On Error to stop the script after the error is logged. This option is independent of the global Stop/Continue on Error option specified in script Settings or the Stop on Error option in the Activity Info tab. This activity generates a script exit code of 555.

Parallel

Run two or more activities in parallel.

After adding the activity to the design window, hover over the activity to display a drop-down list of view options.

View Parallel—Normal view.

View Cancel Handler—Select to add cancel handler activity.

View Fault Handlers—Select to add fault handler activity.

This activity can have one or more branches. To delete a branch, select it and press Delete. To add a branch, right-click the activity (the outside loop) and select Add Branch.

Notes: 

  • To disable an activity, right-click the activity in the design window and select Disabled. This is helpful if you want to disable an activity while testing other activities.
  • The Stop on Error option for each activity allows you to select whether to stop or continue execution if Toad encounters an error during that activity.
  • To prevent an error in one activity from causing a failure at the script level, clear the Generate Error check box (Activity Info tab) for that activity. See Automation Script Troubleshooting and Error-Handling.
  • Select the Log tab to review a script's execution log.
  • In the Suffix field, the drop-down list includes two predefined suffixes (Date and DateTime). The list also includes any suffixes you have created and saved since installing Toad. To delete a suffix from the list, select the suffix, click , delete the suffix in the Expression field, and click OK.
  • Automation does not support positional parameters denoted by a question mark.

 

 

Related Topics

Automate Tasks

Use Database Automation Activities

Use File Automation Activities

Use Variables In Automation

Automation variables are a powerful tool that can greatly extend your scripts. Variables can add flexibility and customization. Variables can be used in almost every Automation activity.

Watch Using Variables in Automation for a video demonstration of this feature.

See Automation and Variables for a tutorial on how to use variables in Automation.

How to Create Variables

User-Defined Variables

You can use the Set Variable activity to create user-defined variables. See Use System Automation Activities for more information.

Built-In Variables

Several Automation activities create variables by default. The following built-in variables are created by Automation activities.

  • RowCount variable— This integer type variable is created by several activities. The value assigned to this variable is the number of rows returned by the query in the activity.

    See Automate Exception Reporting for an example of how to use the RowCount variable in Automation.

  • Return code variable— This integer type variable is created by the Run Automation Script and the Run Program activities. The variable value is assigned based on the execution status of the script or program.
  • Built-in SQL type variables—The Loop Dataset and Execute Script activities create a SQL type variable which is used to store the result set of a query. The Loop Connections activity creates a SQL type variable with only one row containing connection information.
  • Activity Result variable—This variable captures the result of each activity's execution. If an error occurs, it captures the error message. The name of this variable is _ACTIVITY_RESULT. See Automation Script Troubleshooting and Error-Handling for more information about how to use this variable.

The Automation script creates the following built-in variables. See Selected environment in Automate Tasks for more information.

  • Root path variable—This string type variable is created by the script. The value assigned to this variable is the path you specified in the Root path field in either the Test environment or the Production environment area in script Settings. The path that is applied depends on which environment (Test or Production) is selected at the time the script is executed.
  • Run mode variable—This string type variable is created by the script. The value assigned to this variable is either Test or Production, depending on the environment selected in the script Settings at the time the script is executed.

How to Use Variables

Where to Use Variables

The following are some examples of where you can use a variable (built-in or user-defined) in an Automation script.

  • In the file name of a file exported by an activity.
  • In the suffix appended to a file name.
  • In an expression used in the While or If Condition activities.
  • In a SQL statement as a bind variable for filtering data.
  • In a SQL statement as a bind variable in an Import or Export template.

How to Use a Variable

To express the value of a variable, surround the variable name with hash symbols (#), for example #myvar#. The following are some examples of how variables might be used.

#myvar#_report.xlsx

Report_#Dept_Name#.xlsx

#File_1_RCOUNT# > 0

It is important to remember that when a variable is expressed, the variable value is substituted as text in place of the variable name (#myvar#). The only exception is when you use a variable as a bind variable in a SQL statement.

To use a variable as a bind variable in a SQL statement, use the following syntax format (regardless of what the address_id column type is):

SELECT * FROM contact WHERE address_id = :myvar

You can also use the Automation variable here as text substitution, but in this case single quotes might be required (because it is a text substitution) depending on the column type in the WHERE clause.

For example, if last_name is a character column, use the following syntax (single quotes are required):

SELECT * FROM contact WHERE last_name = ‘#myvar#’

If address_id is a non-character column, use the following syntax:

SELECT * FROM contact WHERE address_id = #myvar#

How to Use a String Variable in a Comparison Expression

When comparing a string variable to a string value in an expression, always surround the variable name and the string value with single quotes, for example:

'#myvar#' = 'Dog'

About SQL Type Variables

SQL type variables are created in two ways:

  • You can create user-defined SQL type variables using the Set Variable activity.
  • A built-in SQL variable is created by the Loop Dataset activity, the Execute Script activity, and the Loop Connection activity.

If you ask just for the variable value of a SQL type variable, Toad returns the value from the first row and column of the result set. However, you can filter by column using this syntax:

<variable name>.<column name>.

For example, Region.Region_Name returns the first value (first row) in the column Region_Name.

Dataset/SQL Variable - Special Format for Filtering

You can use the following syntax format to filter data from the dataset variable (or any SQL type variable):

<dataset variable name>.<filter parameter>

Example: Loop_data_1_SQL.region_id, where region_id is one column in the dataset variable

For example, use this format in a SQL statement (in a Select to File activity) to select and export data based on each region_id value in the dataset variable. The statement is executed with each loop of the Dataset activity.

SELECT * FROM address WHERE address.region_id = :Loop_data_1_SQL.region_id

SELECT * FROM address WHERE address.region_id = #Loop_data_1_SQL.region_id#

Bind Variables

You can store global bind variable/value combinations in Toad (see Using Bind Variables). After storing a global bind variable, that variable is available to use in Automation. Your stored global bind variables appear in the list of variables in the expression editor of applicable activities.

How to Create and Use Date Variables

In Automation scripts, a common use case is to create a variable to be used later in the script in a SQL statement (such as in the Select to File activity). When creating a variable for a date value, there are two variable types to choose from in the Set Variable activity: String or DateTime. The type you choose depends on how the value is to be used in the SQL.

  • If the SQL expects a string value, use the String variable type.
  • If the SQL expects a date/time value, use the DateTime variable type.

How to Define a Date Variable

  In the Set Variable Activity
Example SQL

Variable Type

 Valid Values

SELECT * FROM contact
WHERE birth_date < to_date(:var_date, 'YYYY-MM-DD')
and CONTACT_ID < 20

String '2017-7-7'

SELECT * FROM contact
WHERE birth_date < :var_date and CONTACT_ID < 20

 DateTime

To_date('2017-07-07', 'YYYY-MM-DD')

'2017-7-7'

Using a String. For a String variable type, surround the date value in single quotes. This ensures that date delimiters, such as the dash (-) or forward slash (/), do not cause Toad to interpret the value as an expression.

‘2017-7-7’ is treated as a string

2017-7-7 is evaluated to 2003

See Using Date Formats in Automation for more information.

Variable Scope

When you are building an Automation script that uses a variable, it is important to understand the variable's scope of visibility. This section describes the concept of variable scope in Automation scripts.

Terms and Definitions

Term Definition
Container

Let's define a container as any one of the following:

  • The script itself
  • An activity that contains other activities (like the Group, While, or Loop Dataset activities)
  • A branch of an activity (for example, a branch in the If Condition activity)
Levels

Suppose that a script is composed of nested containers. Each container is a different level with respect to variable scope.

Level 1—The outermost container is the script itself. The script is comprised of activities. The script and its activities in the main workflow comprise the first level.

Level 2—Some activities can contain other activities. "Container activities" (such as the Loop Dataset) are at the second level. Level 2 is nested below level 1.

Level 3—Container activities can contain branches. A branch within a container activity is the third level. Level 3 is nested below level 2.

And so on, until we get to the innermost level.

Container Activity A container activity is an activity that contains other activities. The Loop Dataset, Group, If Condition, While, and Parallel activities are container activities.
Set Variable Activity Use this activity to define a variable.
Set Variable Value Activity Use this activity to assign a new value to a previously-defined variable.

Scope Rules

Basically, the scope of a variable includes all activities subsequent to the variable-defining activity AND either at or nested below the level at which the variable is defined.

The following are the rules for variable scope.

  • Use a variable in an expression—You can use a previously-defined variable in an expression in an activity if the following are true:
    • The expression activity is subsequent to the variable-defining activity
    • AND the expression activity is at the same level or at a level nested below the variable-defining activity

  • Reassigning a variable value—You can reassign the value of a variable (except a SQL type variable) at any point within the variable's scope. This task is accomplished using the Set Variable Value activity. See Use System Automation Activities for more information.

    Once a variable value is reassigned, the new value applies to activities subsequent to the original Set Variable activity AND either at the same level or nested below the level of the original Set Variable activity.

    Example: You can use the Set Variable Value to turn a previously-defined variable into a counter (var=var+1) in the Loop Dataset activity.

  • Define a new variable with the same name—You can redefine a new variable with the same name, but only within an activity that is nested one or more levels below the variable-defining activity. For example, if you defined a variable in the main workflow of your script (first level), you can define a new variable with the same name within a container activity (second level). The scope of this variable value is restricted to that container activity.

    You cannot define a new variable with the same name at the same level as the original.

Additional Examples to Illustrate Scope

  • Run Automation Script activity—This activity is used in an Automation script (the parent script) in order to execute another Automation script (the child script). However, a variable defined in the parent script cannot be used in the child script.
  • Loop Dataset activity—This activity is a container activity and can contain other activities, including a Set Variable activity. However, a variable defined in a Set Variable activity within the Loop Dataset cannot be used outside the Loop Dataset.

  

Related Topics

Automate Tasks

Execute/Schedule Automation Scripts

You can execute automation scripts on demand from the Automation window or the Project Manager. You can also schedule scripts to run on a recurring basis at a specific time. For example, schedule a script that performs long-running tasks to run when database activity is light.  

When you schedule a script in Toad, the Job Manager creates a Windows scheduling task. Scheduled scripts run in batch mode. If using another scheduling application, specify "-batch=true" in the command. This executes the script in non-visual (batch) mode. See the procedure below.

Tip: To learn about sharing and scheduling scripts in Toad Intelligence Central, see Publish Automation Scripts to Intelligence Central .

To execute a script from the Automation window

  1. Click Run in the Wizard bar. The script is compiled and executes immediately.

  2. To view the script's execution log, select the Log tab.

  3. If you want to schedule the script, first resolve any problems that caused activities to fail or disable failed activities. An activity can be disabled from its Activity Info window.

    Tip: To execute a script from the Project Manager, right-click the script and select Execute.

To schedule an automation script

  1. With a script open in Automation, click Schedule in the Wizard bar. A Windows Scheduler task is created for the script and a task properties dialog opens.
  2. To schedule the script, select the Triggers tab. Select the scheduling trigger for this task and click Edit.
  3. In the Edit Trigger dialog, specify scheduling details. Click OK to save your changes and close the dialog.
  4. To run the script when you are not logged on, in the General tab select Run whether user is logged on or not. Then enter your Windows credentials when prompted.
  5. When finished, click OK to save your changes and close the task properties dialog. For detailed information about the task properties dialog, see Schedule Windows Tasks.

Note: To run a scheduled script against a password-protected database, the password must be saved in the Connection Properties dialog.

To execute a script in batch mode

  • To start Toad and execute a script in batch mode (non-visual mode), enter the following command and arguments:

    <path to the toad.exe application> -batch=true “<path to Automation script>”

    For example:

    C:\Program Files\Quest Software\Toad Data Point 5.6\toad.exe -batch=true "<path\scriptfilename.tas>"

Notes:

  • Automation scripts are compatible with the version of Toad in which they are created. To learn how to upgrade your scripts and migrate scheduled tasks that were created in a previous version of Toad, see Upgrade Automation Scripts and Scheduling Tasks.

  • The Automation module is disabled if Toad was installed with the Prohibit saving passwords option selected.

 

Related Topics

Automate Tasks

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating