Use Database Automation Activities

In previous versions of Toad, the Database Connection activity was required in order to specify database connection details.

In the current version of Toad, you can specify the database connection details within each activity, eliminating the need to use the Database activity. You may continue to use the Database activity if you prefer.

Notes:

• If the connection was open before the script ran, it closes the connection once the activities in the connection box are complete.
• You can include connections for multiple databases in the same automation script.

Execute an existing SQL script, Query Builder file, or Editor file and export the results to an Excel, HTML, or CSV file. You can then use additional activities to add the exported result file to a zip file, attach the file to an email, or copy the file.

1. Select a database connection.
2. Select a SQL file or enter a SQL statement.
   • 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 in the Editor where you can modify the SQL file.
   • You can manually compose a SQL statement in the activity editor, but you must first deselect the Link SQL file option.

3. Export file—Enter a file name and path, or click to open the Export Options dialog. You can select an existing file or create a new one. You can use a variable in this field by entering the number sign before and after the variable (#VariableName#).

   Note: You can only use one variable per field or file name (a suffix counts as one variable for a file name).

Review the following for additional information:

• Row count variable—Enter a name for the row count variable. The value assigned to this variable equals the number of rows returned from the activity. You can use this variable with the If..Condition.. and While system activities. See Use Variables in Automation for more information on use cases for this field.

Execute an existing SQL script, Query Builder file, or Editor file. You can save the results of the script in a variable.

1. Select a database connection.
2. Select a SQL file or enter a SQL statement.
   • 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 in the Editor where you can modify the SQL file.
   • You can manually compose a SQL statement in the activity editor, but you must first deselect the Link SQL file option.
3. Result set variable—Enter a name for the result variable, which contains a data table with the results of the execution script. You can use the result variable with the Loop Dataset activity. See Use System Automation Activities for more information.

Review the following for additional information:

• Row count variable—Enter a name for the row count variable. The value assigned to this variable equals the number of rows returned from the activity. You can use this variable with the If..Condition.. and While system activities. See Use Variables in Automation for more information on use cases for this field.

Execute an existing SQL script or Query Builder query and save it and any results sets in a Toad editor file (.tef).

This file format is useful for building scripts that have large result sets that you do not want to continue executing or for saving results when you have not finished building a script.

1. Select a database connection.
2. Select a SQL file or enter a SQL statement.
   • 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 in the Editor where you can modify the SQL file.
   • You can manually compose a SQL statement in the activity editor, but you must first deselect the Link SQL file option.
3. Editor file—Enter an Editor file name and path, select an Editor file from an open project, or browse to and select a file. You can use a variable in this field by entering the number sign before and after the variable (#VariableName#).
   • Select Append results to include the result set in the Editor file.

Review the following for additional information:

• Row count variable—Enter a name for the row count variable. The value assigned to this variable equals the number of rows returned from the activity. You can use this variable with the If..Condition.. and While system activities. See Use Variables in Automation for more information on use cases for this field.

• Suffix—Select a suffix to append to the file name. The suffix can be the date and time that the file was generated or the content of a variable that you define with the Set Variable system activity. See Use System Automation Activities for more information.

  Note: You can only use one variable per field or file name (a suffix counts as one variable for a file name).

Export data from databases, tables, views, or a query into a CSV, Excel, or other file.

Review the following for additional information:

• Export template—You can select an existing template, edit an existing template, or create a new template.
• Use automation connection—Select this option to use the connection associated with the database activity. Clear the checkbox to use the connection associated with the export template.

Import data from a CSV, Excel, or other file into a new table or append to an existing table.

Review the following for additional information:

• Import template—You can select an existing template, edit an existing template, or create a new template.
• Use automation connection—Select this option to use the connection associated with the database activity. Clear the checkbox to use the connection associated with the import template.

Use this activity to open one or more Toad data reports and export the contents as a PDF, HTML, Excel, CSV, RTF, or MHT file. You can then add another activity to your script to zip the new file, attach it to an email, or save it to a new location.

Review the following for additional information:

• Row count variable—Enter a name for the row count variable. The value assigned to this variable equals the number of rows returned from the activity. You can use this variable with the If..Condition.. and While system activities. See Use Variables in Automation for more information on use cases for this field.
• Report files—Select one or more Toad data reports from the Project Manager or other location, or edit an existing report.
• Export file—Select the file type and location and enter the name. You can use a variable in this field by entering the number sign before and after the variable (#VariableName#).

• Suffix—Select a suffix to append to the file name. The suffix can be the date and time that the file was generated or the content of a variable that you define with the Set Variable system activity. See Use System Automation Activities for more information.

  Note: You can only use one variable per field or file name (a suffix counts as one variable for a file name).

Refresh an existing pivot grid and export results to an Excel, HTML, or CVS file. You can then add another activity to your script to zip the new file, attach it to an email, or copy it to a new location.

Review the following for additional information:

• Row count variable—Enter a name for the row count variable. The value assigned to this variable equals the number of rows returned from the activity. You can use this variable with the If..Condition.. and While system activities. See Use Variables in Automation for more information on use cases for this field.
• Pivot file—Select a Toad pivot file from the Project Manager or other location, or edit an existing pivot file.
• Export file—Select the file type and location and enter the name. You can use a variable in this field by entering the number sign before and after the variable (#VariableName#).

• Suffix—Select a suffix to append to the file name. The suffix can be the date and time that the file was generated or the content of a variable that you define with the Set Variable system activity. See Use System Automation Activities for more information.

  Note: You can only use one variable per field or file name (a suffix counts as one variable for a file name).