Defining Direct SQL filter, or escalation actions

Use the Direct SQL action to submit any legal SQL command to a database outside of the  database. Use this command only if it is required for integration with another database.

Use the Direct SQL action in:

• Filters
• Escalations

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- 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 .

 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.

Limitations for using Direct SQL action

• We do not support or recommend using a Direct SQL action to modify data in the tables created by the to store object definitions or form data. It can result in data corruption. Use this action to push data only to non- database tables.
• You can use Direct SQL action only through filters in . 

Making sure your SQL commands are secure

Use care when configuring the Direct SQL action. The  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  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  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 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  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  keywords.
5. Save the filter, or escalation.
   
   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.