Query Database Smart Service

Overview

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.

To query data, refer to Querying Data From an RDBMS

To insert or update data, refer to Write to Data Store Entity and [Write to Multiple Data Store Entities

To delete data, refer to Delete from Data Store Entities

  • It is important that you manage the memory consumption (RAM and HD) of your processes by archiving or deleting processes that contain large datasets.
As a best practice, we recommend placing the Query Database Smart Service in a sub-process.

Permissions Needed

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.

Properties

  • Category: Connectivity Services

  • Icon: Query Database

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

RDBMS Support

In addition to the database listed in the System Requirements, others that are not listed there are also supported for use with the Query Database Smart Service, as long as the database provides a JDBC compliant database driver.

In order to configure and use this process activity, the appropriate Appian configuration settings must be established.

The following information applies to Appian Cloud users only.

  • The standard relational database deployed for every hosted Appian site is MySQL.
  • The RDBMS is configured as a predefined data source.
  • To use the database, select Use a pre-defined data source. Select the MySQL database from the list of data sources.
    • Appian Cloud can integrate with additional external relational databases hosted by the customer. If you wish to use an external database as a data store, it must be setup as a data source by completing the Data Source Worksheet.
    • Customers are responsible for securing and managing the data stored in all external databases.
    • Customers can optionally provide a SQL query (e.g., select count(*) from x) to ensure that Appian Cloud maintains a valid connection to their data source.
  • You can use any of the supported databases that are made available over the Internet.
  • In order to allow Appian customers in the cloud access to resources, such as an RDBMS, on a private network from their Appian Cloud site, an IPSec VPN connection can be configured.
  • The standard administrative interface is phpMyAdmin, available through the Application Designer Home Tab.
  • Both MySQL and phpMyAdmin are widely used tools, with abundant documentation. Appian Technical Support does not provide general assistance with using those tools.
  • Appian Technical Support is not responsible for administering any of the data you store in the database other than performing periodic backups.

Configuring the Query Database Smart Service

  • Right-click the activity on the designer canvas. Select Properties. The General tab of the Configure Query Database dialog box is displayed.
  • Users must be members of the Process Model Creators group in order to configure this smart service.

General Tab

The General tab allows you to name and describe the node.

Process Designer Configuration Options

The following tabs are only displayed in the Process Designer view.

Setup Tab

Configure the connection to the database; then enter one or more SQL statements.

dbaccess.gif

Configuring the Database Connection

In the Database Connection group box, you have two options:

Use a predefined data source
Selecting this option allows you to select a defined data source from the drop-down menu.
Use a custom data source
This option allows you select a supported RDBMS as a data source.

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 used for configuring MySQL, Oracle, SQL Server, and DB2 are shown below.

MySQL

 <machine_name>:<port_number>/<database_name>

Oracle

 <machine_name>:<port_number>:<database_name>

MS SQL Server

 <machine_name>:<port_number>;databaseName=<database_name>

IBM DB2

 <machine_name>:<port_number>/<database_name>

Creating Your SQL Statements

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



Delete a query

after selecting the desired query

Move a query up the list

after selecting the desired query

Move a query down the list

after selecting the desired query

Query Requirements

  • SQL Statements used in the Query Database smart service cannot update process variables directly.
  • To create or update a process variable, you must define a node input (an Activity Class Parameter – or ACP) which must then be mapped to the process variable you want to create or update.
  • The node input must be defined within the SQL Statement itself. This is accomplished by adding ac! to the beginning of the node input name.
  • When querying an Oracle database, you may need to enclose the column and table names in double-quotes if you receive an 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.

Query Examples

The following examples illustrate required syntax for your SQL statements (which may vary according to the database queried):

  • This example creates three inputs. Each of these node inputs can then be assigned to a process variable.
insert into new person values (ac!name,ac!age,ac!birthdate)
  • This example invokes a simple equals condition. Notice that quotation marks (") and apostrophes (') are not used to enclose the node input specified in the condition.
SELECT CustomerName, BusinessUnit, ProductLine FROM customertable where CustomerName=ac!condition
  • This example invokes a SELECT statement using the like condition.
SELECT CustomerName, BusinessUnit, ProductLine FROM customertable where CustomerName like ac!condition
  • To use a wildcard, such as %, 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.

Activity Execution

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.

  • By default, a query will return a maximum of ten rows of data. Also, node and process variables that take multiple values are restricted to 1000 indices.

Database Availability

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.

Data Tab

The Data tab displays the node inputs and node outputs for the activity.

Inputs

The following Inputs are listed. You can add additional node inputs, as needed. These properties can also be configured on the Setup tab.

Runtime Url
(Optional) Expressionable setting for the database driver URL.
Runtime Username
(Optional) Expressionable setting for the database username.
Runtime Password
(Optional) Expressionable setting for the database password.
Runtime Password
(Optional) Expressionable setting for the database password.
  • This node input:
    • Does not display the Save Into option, when it appears on the Data Tab
    • Does not appear as an ACP for use in the expression editor
    • Is not exported
  • Using a password ACP in an expression returns an empty string instead of the password value.
  • If this activity is attended the following Other Tab settings ensure that the value is stored only during the time that the activity is executing.
    • Select the Delete previously completed/canceled instances checkbox.
    • Clear the Keep a record of the form as submitted for future reference checkbox.
Runtime Datasource
(Optional) Expressionable setting for the Data Source Name.
Pause Node on Error
(Required) If you set this input to false, the process flow continues when an error occurs.

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.

Outputs
  • The 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.
  • The Error Text Output lists the text of any error message.
  • When the smart service activity is configured with a valid query, an output is automatically generated for your query results. Return value names are derived from the query you enter. For example, if you have a query with Name 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.

Mapping Data within a Query Database Smart Service

Data Mappings within the Query Database Smart Service

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 MySQL, SQL Server, Oracle, and DB2 databases.

Type Display Name Data Type MySQL Datatypes SQL Server Datatypes Oracle Datatypes DB2 Datatypes
Date Date Timestamp
  • Date
  • datetime
  • smalldatetime
  • Date
  • date
Datetime Date & Time Timestamp
  • DateTime
  • datetime
  • smalldatetime
  • Timestamp
  • timestamp
Double Decimal Double
  • Float
  • Double
  • Decimal
  • decimal
  • numeric
  • float
  • real
  • Number
  • Number(precision,scale)
  • Float
  • Decimal
  • float
  • double
  • real
  • decimal
  • numeric
Long Number Long
  • Int
  • BigInt
  • TinyInt
  • MediumInt
  • SmallInt
  • Int
  • BigInt
  • TinyInt
  • SmallInt
(None)
  • int
  • bigint
  • smallint
String Text String
  • char
  • varchar
  • text
  • long
  • char
  • varchar
  • text
  • nchar
  • nvarchar
  • varchar2
  • varchar
  • nvarchar2
  • char
  • nchar
  • long
  • varchar
  • nvarchar
  • char
  • graphic
  • vargraphic
Time Time Timestamp
  • Timestamp
  • datetime
  • smalldatetime
  • Timestamp
  • time
Boolean Yes/No Long
  • TinyInt(1)
  • bit
  • bit
  • smallint
  • 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.

Additional Options

See Also

17.1

On This Page

FEEDBACK