This page applies to Appian Cloud only. It may not reflect the differences with Appian Government Cloud. |
The Query Database smart service is used to retrieve information from an external database using a SQL query, and update or create process variables using the query results. This activity also permits you to create tables and select, insert, and update rows in database tables from your process model.
It is important that you manage the memory consumption (RAM and HD) of your processes by archiving or deleting processes that contain large datasets.
Tip: As a best practice, we recommend placing the Query Database Smart Service in a subprocess.
The user running the smart service must have initiator rights to the process model that they attempt to start. Attempting to complete this task without the proper user rights generates an error message for the process administrator and pauses the process.
Category: Data Services
Icon:
Assignment Options: Unattended/Attended - Note: When this smart service is attended, the form that the user sees is the form configured on the node, not the start form for the specified process model.
An RDBMS is configured as a predefined data source for Appian Cloud customers. To use this database, select Use a pre-defined data source and select the Appian provided data source from the list of data sources.
Appian Cloud can also integrate with additional external relational databases. You can use any of the supported databases that are made available over the Internet.
Note: Appian Technical Support is not responsible for administering any of the data you store in the Cloud database other than performing periodic backups. Additionally, customers are responsible for securing and managing the data stored in all external databases.
This section contains tab configuration details specific to this smart service. For more information about common configurations see the Process Node Properties page.
Configure the connection to the database; then enter one or more SQL statements.
In the Database Connection
group box, you have two options:
For the Driver field, select the driver that corresponds with your database.
Enter the appropriate database account's credentials in the Username and Password fields.
For the URL field, make sure that the right-side text box is completed with the proper information. The syntax to configure a supported database is shown below.
MySQL, MariaDB, PostgreSQL, and DB2
1
<machine_name>:<port_number>/<database_name>
Oracle
1
<machine_name>:<port_number>:<database_name>
SQL Server
1
<machine_name>:<port_number>;databaseName=<database_name>
In the Database Queries
group box, configure your SQL statements for the database.
SQL statements are executed in the order that they are listed in Database Queries group box, from top to bottom. The following table summarizes the options for configuring your SQL statements and the order in which they execute.
To... | Click |
---|---|
Add a new query | New Query |
Delete a query | Delete Query after selecting the desired query |
Move a query up the list | Move Up after selecting the desired query |
Move a query down the list | Move Down after selecting the desired query |
ac!
to the beginning of the node input name.ORA-00942
error when executing the smart service. When enclosing names in quotes the casing in the SQL statement must match the casing of name in the database exactly.LOAD DATA LOCAL INFILE
command against a MySQL/MariaDB database by default from a Query Database smart service in the Process Model. For Appian Cloud customers, this command is already disabled at the database level for the Appian Cloud database and at the JDBC level for configured MySQL/MariaDB databases to provide higher security. Customers with self-managed environments who wish to use this command must set the conf.data.mysql.loaddata.enabled
property to true
and conf.data.load.infile.paths
property as a comma-separated list of paths to allowed directories that contain the files to be loaded. The properties can be set in custom.properties
file located at <APPIAN_HOME>/conf/
. If the required properties are not set, any Process Model using LOAD DATA LOCAL INFILE
command will pause by exception at runtime.The following examples illustrate required syntax for your SQL statements (which may vary according to the database queried):
1
insert into new person values (ac!name,ac!age,ac!birthdate)
1
SELECT CustomerName, BusinessUnit, ProductLine FROM customertable where CustomerName=ac!condition
1
SELECT CustomerName, BusinessUnit, ProductLine FROM customertable where CustomerName like ac!condition
%
, append the wildcard to the value of a process variable using a script task that is upstream in the process flow from this query activity. The process variable can then be specified as the default value of a node input used by the activity.You cannot create a table with the value of an activity class parameter. You may, however, incorporate the value of an activity class parameter into a select statement. This requires that you provide an explicit value or a process variable's value for the node input on the inputs property sheet of the Data tab.
All SQL statements defined in a node are treated as a single transaction. If an error occurs on any one of the SQL statements defined for the node, not one of the statements is executed. Otherwise, a commit is executed after successfully running all SQL statements defined by the node.
You can have only one "SELECT" statement per Database Access node. Wildcard (*) selections are not permitted. Do not include a semicolon (;) at the end of your statement.
When a database is unavailable, the process pauses by exception. The transaction data is not lost, but the node keeps the information until the administrator restarts the node manually.
To handle database availability issues, you can configure a parallel flow with a Timer Event and a rule that checks for timeout exceptions. If a timeout is detected, you could then reroute the process to take an appropriate action. For example, if a variable populated by the Query Database node is null, and a Timer Event of one minute has been triggered, send an alert to the process owner that the database is unavailable.
The Data tab displays the node inputs and node outputs for the activity.
The following Inputs are listed. You can add additional node inputs, as needed. These properties can also be configured on the Setup tab.
Keep in mind that expressionable fields allow you to reference constants and variables in addition to calculating values with functions and logic statements. We recommend setting these values using constants, to enhance application portability.
Error Occurred
Output indicates true if an error has occurred during execution. Save this value to a process variable to enable exception processing on the subsequent activity in the process flow.Error Text
Output lists the text of any error message.1
and SQL Statement SELECT name, age FROM people
, then the following outputs will be generated:AC!~1<name
AC!~1<age
If you receive validation errors for your query prior to running the smart service for the first time (aside from possible problems with the query itself) it may indicate that the database driver is not configured properly.
The Query Database smart service activity allows process designers to transfer data between external data sources and the process engine. When mapping variables in Appian to fields in a database it is important that the data types used by your process variables, and the fields in your database tables match one another.
The table below summarizes the different types of variables in Appian and the supported equivalents in each supported database.
Type | Display Name | Data Type | MySQL/MariaDB Datatypes | SQL Server Datatypes | Oracle Datatypes | PostgreSQL Datatypes | DB2 Datatypes |
---|---|---|---|---|---|---|---|
Date | Date | Timestamp | Date | DateTime SmallDateTime |
Date | Date | Date |
DateTime | Date & Time | Timestamp | DateTime | DateTime SmallDateTime |
Timestamp | Timestamp | Timestamp |
Double | Decimal | Double | Float Double Decimal |
Decimal Numeric Float Real |
Number Number(precision,scale) Float Decimal |
Float4 Float8 Numeric |
Float Double Real Decimal Numeric |
Long | Number | Long | Int BigInt TinyInt MediumInt SmallInt |
Int BigInt TinyInt SmallInt |
(None) | Int2 Int4 Int8 |
Int Bigint Smallint |
String | Text | String | Char Varchar Text Long |
Char Varchar Text Nchar Nvarchar |
Varchar2 Varchar Nvarchar2 Char Nchar Long |
Varchar | Varchar Nvarchar Char Graphic Vargraphic |
Time | Time | Timestamp | Timestamp | DateTime SmallDateTime |
Timestamp | Time | Time |
Boolean | Yes/No | Long | TinyInt(1) | Bit | Bit | Bool | SmallInt |
Note: A datetime value is converted to the GMT time zone when it is saved in Appian. Therefore, when it is used in a SQL statement (for inserts and selects for example) the GMT value is used. The datetime value is only converted to the end user's time zone when it is displayed in the browser.
You can save a datetime value as text, number, date, or time value if you do not want this conversion to take place.
If you use this smart service to add, update, or delete data in a database table, and that table is the source of a record type with data sync enabled, those changes will not be automatically synced in Appian.
To sync changes made by this smart service, use the Sync Records smart service. Any data changed by this smart service will also be synced during the next scheduled sync or if you trigger a manual sync.
To automatically sync changes made to a database table, consider using the following smart services instead:
Query Database Smart Service