Skip to content

TSTool / Command / RunSql

Overview

The RunSql command executes a Structured Query Language (SQL) statement on the specified database datastore. This command cannot be used with web service datastores because the underlying software relies on a database to execute the SQL statement. If database datastore support is not specifically provided by TSTool, a generic database datastore can be used (see the Generic Database DataStore appendix). For example, use a generic database datastore to configure a connection to a Microsoft Access database. See also the ReadTableFromDataStore, WriteTableToDataStore, and other commands specific to datastores. This command is useful when a database task needs to be automated in sequence with other TSTool commands. The SQL statement can be specified in the following ways:

  • Specify a SQL statement as a command parameter:
    • SQL must be valid for the database (syntax may vary based on database software).
    • Use ${Property} notation to insert processor property values set with SetProperty.
    • SQL syntax is not checked for validity and therefore error messages from the database need to be diagnosed based on familiarity with the database.
    • Comments in SQL can be specified using /* */ notation or -- (double dash) for end of line comments. These comments are removed by TSTool for Microsoft Access because Access does not support comments in SQL statements.
  • Specify an SQL select statement in a file:
    • Similar to the above option; however, the SQL statement is read from a file.
    • Useful if the SQL statement is also used by other tools.
  • Specify a procedure to run:
    • Available procedures are listed and can be selected.
    • Currently, only procedures that do not require parameters can be run.

General constraints on executing the statement are as follows:

  • The table, views, and procedures being queried must be readable (some databases restrict direct access to data and require using stored procedures).
  • Any SQL statement can be run; consequently, suitable authentication and permissions should be in place to protect against unintended changes to the database
  • SQL syntax varies between database software so care should be take to use standard SQL if possible.
  • Results from the statement currently are not displayed, although errors will be shown and added to the log file. Use the ReadTableFromDataStore command to process statements that return a result set.

Command Editor

The following dialog is used to edit the command and illustrates the syntax for the command, in this case creating a database index.

RunSql

RunSql Command Editor (see also the full-size image)

The following dialog is used to edit the command and illustrates the syntax for the command when using a file to specify the SQL statement.

RunSql File

RunSql Command Editor when Specifying the SQL Statement Using a File (see also the full-size image)

The following dialog is used to edit the command and illustrates the syntax for the command when running a stored procedure.

RunSql Procedure

RunSql Command Editor when Specifying a Stored Procedure to Run (see also the full-size image)

Command Syntax

The command syntax is as follows:

RunSql(Parameter="Value",...)

Command Parameters

Parameter                      Description Default                           
DataStore
required		 The name of a database datastore. None – must be specified.
Sql The SQL statement text that will be executed, optionally using ${Property} notation to insert processor property values. If specified, do not specify SqlFile or DataStoreProcedure. None.
SqlFile The name of the file containing an SQL statement to execute, optionally using ${Property} notation in the SQL file contents to insert processor property values. If specified, do not specify Sql or DataStoreProcedure. None.
DataStoreProcedure The name of the database procedure to run. Currently, only procedures that do not require parameters can be run. If specified, do not specify Sql or SqlFile. None.

Examples

See the automated tests.

Troubleshooting

See Also