SQL database access

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

Related topic

Integrating-data-by-using-Developer-Studio-forms

Defining-Direct-SQL-in-filters-or-escalation-actions

How the AR System database manages data

Accessing AR System server data externally

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

Pulling data into AR System server 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 Systemfields. 

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 AR System server SQL logging to resolve problems with the SQL syntax if it returns unexpected values or results.

Best Practices

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 AR System server with SQL

All three AR System server workflow components, active links, filters, and escalations can send data to external tables and even external databases by 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 AR System server 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 AR System server 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-in-filters-or-escalation-actions.

SQL database considerations

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

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

Important

If you set an empty string, the SQL database considers it as an empty value and persists it as it is. Whereas, if you set an empty string in the Oracle database, the Oracle database stores it as a null value. As a result, you might face some discrepancies in the behavior.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*