Direct SQL action

Use the Direct SQL action in:

• Active links
• Filters
• Escalations

Use the Direct SQL action to submit any legal SQL command to a non-BMC Remedy AR System server database. Use this command only if it is required for integration with another database.

The Direct SQL action has a different result than the SQL command you use to set a field value in the Set Fields action. In the Set Fields action, you use SQL to query the database for information and then use the returned value to set a field. With the Direct SQL action, the SQL command is not expected to return data. For example, you can define a Direct SQL action to perform inserts or updates to a non-BMC Remedy AR System server database.

You must know SQL syntax and concepts to create a Direct SQL action. For the most effective use of direct SQL commands, you must also have a general understanding of relational databases and a specific understanding of the relational database underlying your BMC Remedy AR System.

BMC Remedy AR System passes direct SQL commands to the database without checking the syntax. You must make sure that all submitted commands achieve the needed result. Your SQL commands should comply with ANSI SQL standards, so that single quotes are reserved for strings and double quotes are reserved for use with database object names only.

SQL commands can be generic or specific to the DBMS. If an SQL command is specific to one DBMS, you might not be able to move the workflow object that contains it to another environment.

Making sure your SQL commands are secure

Use care when configuring the Direct SQL action. The BMC Remedy AR System server uses double quotation marks to substitute single quotation marks unless the entire command is a substitution parameter where you leave the value as is. The following rules explain this concept:

• If parameters are used when constructing the SQL command, the BMC Remedy AR System server substitutes the values and escapes single quotation marks by doubling the quotation marks.
• If you enter the entire SQL command in a field and access the command with the $Field$ reference, the BMC Remedy AR System server does not examine or alter the contents of the command, and any single quotes in the SQL command are not escaped. If you use this technique, make sure the command can safely run on the database.

To define the Direct SQL active link, filter, or escalation action

1. Right-click the If Action or the Else Action panel header.
2. Select Add Action > Direct SQL.
3. From the Server list, select the BMC Remedy AR System server where you want to perform the SQL command.
4. In the SQL Command field, enter the SQL command that you want to submit to the database.
    To use the Expression Editor dialog box to build the SQL command, click the ellipsis button. As shown in the following figure, the Expression Editor enables you to select SQL commands, fields from the form, and BMC Remedy AR System server keywords.
5. Save the active link, filter, or escalation.

 Expression Editor dialog box for Direct SQL action
 (Click the image to expand it.)



In this example expression, the elements Bug_ID, First_Name, and Tech are columns in the table Customer_Info_Order. The contents of the $Short Description$ field will be used to determine the name of the last column.