QueryBySQL

The QueryBySQL activity retrieves the entries that match the filter string or criteria specified in the datagrid. Before using the QueryBySQL activity, you must supply a filter string as an argument. This filter specifies the content of the query, that is, criteria for selecting a subset of rows from the datagrid.

General

In the General tab, you can specify the activity name and a datagrid connection.

The following table describes the fields in the General tab of the QueryBySQL activity:

Field Module Property? Description
Name No Specifies the name to be displayed as the label for the activity in the process.
DataGrid Yes Click to select a DataGrid shared resource. If the required datagrid is not found, in the Select FireflyResource Resource Template dialog box that opens, click Create Shared Resource to create a datagrid connection first. See Creating a DataGrid for details.
Timeout (sec) No Specifies the timeout value for the activity. The value specified here overrides the ActiveMatrix BusinessWorks™ default timeout value.

Description

In the Description tab, you can enter a short description for the QueryBySQL activity.

Advanced

In the Advanced tab, you must select the Insert/Replace Query check box if you want to execute either an INSERT or an INSERT OR REPLACE SQL query statement. Only after this check box is selected, you can execute the query and add new rows or replace existing rows in a table. When you select this check box, the output schema changes.

Input

In the Input tab, you can specify the SQL command.

The following table describes the fields in the Input tab of the QueryBySQL activity:
Input Item Data Type Description
sql String Specifies a SQL query statement. You can use CASE expressions in the SELECT statements for the QueryBySQL activity.
Note: You cannot use OrderBy, GroupBy, and Aggregate functions in the CASE expressions.
PrefetchNo Specifies the units of data to be transferred in a row.
Note: If you set a value in the "prefetch" field when you created a DataGrid connection, then the value you provide here overrides that value.
Snapshot LevelNo Specifies the snapshot level to be displayed for a row in the process. You can select either global snapshot or local snapshot.

Note: If you set a value in the "Global_Snapshot_Required_for_Iterator" field when you created a DataGrid connection, then the value you provide here overrides that value.

TimeoutNo Specifies the timeout value for the activity. The value specified here overrides the ActiveMatrix BusinessWorks™ default timeout value.
Statement Fetch TimeoutNo Specifies the timeout value for the statement to be executed and the results to be fetched.
Note: If you set a value in the Timeout field when you created a DataGrid connection, then the value you provide here overrides that value if the value provided here is greater than that provided in the DataGrid configuration.

Output

In the Output tab, you can find the operation results.

The following table describes the fields in the Output tab of the QueryBySQL activity:

Output Item Data Type Description
Activity output String Specifies the HasError value, the error, the total count, and the rows that match the SQL query statement.
  • It specifies the error message, the total count, and select fields of rows if the HasError value is true. The HasError value is true when one or more results have errors.
  • It specifies the total count of rows and select fields of the rows if the HasError value is false. The HasError value is false when no error occurs.
The ROW element can have multiple Field elements which contains Name, Type, and Value elements.
Note: The output schema changes if you selected the Insert/Replace Query check box in the Advanced tab of this activity.

Fault

In the Fault tab, you can find the error code and error message of the QueryBySQL activity. See Error Codes for more detailed explanation of the errors.

The following table describes the errors in the Fault tab of the QueryBySQL activity:

Error Schema Element Data Type Description
msg String The plug-in error message.
msgCode String The plug-in error code.