InterFormNG2 can with the input type: From database with custom SQL monitor a table and process all records in this table, that fits a specific selection. You can consider to combine this with the workflow component, Database SQL update in order to ensure, that the records are marked as processed to prevent processing of the same records in the next run.

Enabling custom SQL

Custom SQL needs to be enabled in the global System settings. It is divided into two sections:

1. Enable custom SQL selects.
2. Enable custom SQL updates.

Here you can enable/disable SQL selects and/or updates for all tenants.

Dangers of using custom SQL in InterFormNG2

SQL is a powerful tool, but it can cause severe performances issues in InterFormNG2.

The result XML node names are from the names of the columns in the result. If these nodes contains illegal XML node names, an error will happen. Use AS NAME (depending on SQL dialect) in the select to rename the columns. Example: select usern(a)me as username, cit(y) as city from customers  - here (a) and (y) represents illegal xml characters.

SQL updates are committed automatically.

All values are treated as strings. This MIGHT be a problem with special columns such as blobs.

ALWAYS use the ? parameter replacement for dynamic data, since this is the safest option. This is especially true for data that are not machine generated. (E.g. a name entered on a website).

The parameters

The input type has these parameters:

1. Name
   The name of the workflow.
2. Group
   The workflow group.
3. Description
   An optional description of what this workflow does. This can be useful for workflow searches.
4. Input
   Selection of this specific input type.
5. Database
   This refers to an existing database connection, which has been setup for the tenant.
6. SQL Select
   This selects the records from a table, that are to be processed in this workflow. The SQL is always only a static string. The workflow will convert the records into an XML file, which will be saved into the payload for further processing.
   The same selection is executed again and again, so it is a good idea to consider to mark processed records with a specific status and exclude them in the selection.
7. Input type
   This is XML and must be set as XML only as the records selected a saved into an XML in the payload.
8. Polling frequency (seconds)
   This sets the polling interval in seconds - keeping the setting below in mind. 
9. Run at fixed rate
   For the polling you can here choose between two types of polling intervals:
     1. If run at fixed rate is enabled, then the workflow (and selection) will execute with fixed intervals (specified in the polling frequency above) independently of how long it takes to execute the workflow.
     2. If the option is disabled, then the polling frequency is the pause between the previous run ended until the workflow is to be triggered again.

If e.g. it takes 5 seconds to run the workflow and the polling frequency is 10 seconds, then the times to execute the workflow are these:

Be aware that when fixed rate is checked/enabled, multiple workflow instances may run in parallel, potentially selecting the same rows. Either the polling frequency should be long enough that this cannot happen, or the workflow should be setup in such as way that it can handle overlaps.

The XML format

After the SQL selection has run the payload will be updated with an XML, that contains the selected data. The XML file has this structure:

<result>
  <row>
    <columname1>ColumnName1Value</columname1>
    <columname2>ColumnName2Value</columname2>
    .....
  </row>
  <row ..... />
</result>

Where columnname1 is the name of the column and columnName1Value is the value from that result row.

Example, for selecting orders in the classicmodels database, the resulting XML is:

<result>
  <row>
    <orderNumber>10334</orderNumber>
    <orderDate>2004-11-19</orderDate>
    <requiredDate>2004-11-28</requiredDate>
    <status>On Hold</status>
    <comments>
      The outstanding balance for this customer exceeds their credit limit. 
      Order will be shipped when a payment is received.
    </comments>
    <customerNumber>144</customerNumber>
  </row>
  <row>
    <orderNumber>10401</orderNumber>
    <orderDate>2005-04-03</orderDate>
    <requiredDate>2005-04-14</requiredDate>
    <status>On Hold</status>
    <comments>Customer credit limit exceeded. Will ship when a payment is received.</comments>
    <customerNumber>328</customerNumber>
  </row>
</result>

The “Split XML” workflow component can be used to process each row separately (by splitting on /result/row).