SQL database access

Using SQL, third-party applications can read data from the  database. Similarly, both AR System client and server processes can read and write to external databases using SQL.

Accessing data externally

Any process that has permission to query the database engine can read  data. A third-party application writing to the  database is not supported because there is no way to ensure data integrity. In addition, external applications reading  data directly from the database are not subject to  permissions, nor do they trigger any  workflow. If this is not acceptable, data should be read through the Remedy AR System API.  For more information, see How-the-AR-System-database-manages-data.

Pulling data into with SQL

To pull information from external tables, you can use the Set Fields action with the Read Value for Field From field set to SQL. This allows you to send an SQL SELECT command to the database and assign the return values to AR System fields. 

Observe the following general rules for using SQL commands:

• You need not use every value that is returned from the SQL command, but you must use at least one.
• You can use the same value in more than one field.
• You can issue only one SQL command per action. You cannot enter two commands separated by a semicolon and have both commands run. To run a set of commands, create separate actions, or create a stored procedure and run that. (Stored procedures do not return values.)
• Turn on  SQL logging to resolve problems with the SQL syntax if it returns unexpected values or results. A good strategy is to start an SQL interpreter (for example, SQL*Plus for Oracle, Query Analyzer for Microsoft SQL Server) and to enter the same SQL command directly into the database to verify its validity.
• Because there is no error checking on the SQL statement, run the SQL statement directly against the database (as a test) before you enter it into the SQL Command field. You can then copy and paste the tested SQL command directly into the SQL Command field.
• If the SQL operation fails, an AR System error message and the underlying database error message appear.

For more information, see Set-Fields-action-and-structures.

Pushing data from with SQL

All three  workflow components-active links, filters, and escalations-can send data to external tables and even external databases using the Direct SQL action. The SQL command must be created by the administrator and entered into the SQL Command field on the If Action or Else Action tab. The  performs no pre- or post-processing on the SQL command or the results. The administrator must make sure that the command is correct. When the action is triggered, the passes the SQL command directly to the SQL database server on which it is running. For more information about the Direct SQL action, see Defining-Direct-SQL-filter-or-escalation-actions.

SQL database considerations

Consider the following issues when working directly with an SQL database:

• The typically has full administrator access to the database for reading and writing any data. AR System users have permissions to read and write specific data using an AR System client, and these permissions are managed by the . If users access the database directly through a database client, they are bypassing the AR System security model.
•  stores some data in the database in formats that can cause third-party applications to become confused. For example, AR System date/time fields store values as timeticks, which are the number of seconds from 1 January 1970 at midnight until the current time. These numbers are stored as integer numbers, and typically need to be converted by the third-party application.
• All SQL commands are sent to the database server that holds the AR System database. To access databases that are external to this DB server, you must have the appropriate conduit installed and issue the SQL commands needed to use the conduit for your SELECT statement.