This documentation supports the 22.1 version of Action Request System.
To view an earlier version, select the version from the Product version menu.

Defining Direct SQL filter, or escalation actions

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

Important

We do not recommend using custom Direct SQL actions. However, if you are using custom Direct SQL actions, use the BMC-provided framework and replace the Direct SQL action with a Service action.


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

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.

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 AR System server to store object definitions or form data. It can result in data corruption. Use this action to push data only to non- AR System server database tables.
  • You can use Direct SQL action only through filters in Developer Studio . 

Making sure your SQL commands are secure

Use care when configuring the Direct SQL action. The 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 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 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.


Warning

BMC does not recommend the second method as this can leave your system vulnerable to hackers with malicious and harmful intent.

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 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 AR System server  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.
Was this page helpful? Yes No Submitting... Thank you

Comments