SQL Node

Execute SQL statements at any point in a workflow. Data from Process Fields can be injected into the SQL request, and responses can be set back into Process Fields.

The SQL node (formerly SQL Set Field) allows workflow builders to communicate with external data sources for read or write operations.

Supported Database Engines:

Microsoft SQL Server
- SQL Connection string parameters can be found here.
PostgreSQL
- PostgreSQL Connection string parameters can be found here.

Note, there are minor syntax differences between SQL Server and Postgres. Be sure you SQL is valid for the chosen database backend.

Node Properties

Title

The Title of your node should be brief but descriptive about what action is being done. Titles are important when revisiting workflows in the future and when migrating workflows. The title of the node will be displayed when resolving conflicts during imports.

Description

The Description of your node should provide notes about this node. This could include information about intended use, details of the SQL connection, etc. Descriptions can be very useful when revisiting workflows in the future.

Connection String

The Connection String determines where in SQL we want to pull the information from. This should include the Data Source and Initial Catalog.

Ex. Data Source=(local)\SQLEXPRESS;Initial Catalog=SmartSearch;

SQL Statement

The SQL Statement is the SQL query that we can use to pull back information for a specific value. The SQL Statement provided can be any valid SQL Command.

Ex. Select * from [NORTHWND].[dbo].[Suppliers] where CompanyName = 'Tokyo Traders'

Return Field

Responses from SQL commands can be returned back to Process Fields. A Return Field will be set to the return’s first row/column value. When performing CSV data loads, a Return Field must be set. The Return field will collect the number of rows inserted as part of a data load operation.

Map return values to fields automatically

Fields can be “auto-mapped” when this option is enabled. When enabled, all fields added to the process will be compared to the SQL response. Matching names will have values from the SQL return set automatically.

Note that you can write SQL commands to tune the SQL response to meet the workflow’s configuration. A SQL column called id could be auto-mapped to a Process Field named “Employee ID” with a SQL statement like:
select id as [Employee ID] from employees
Starting in version 1.3, the Node Settings value for Return Field is no longer required as a result of this behavior.

Bulk Load data from in-process csv

When enabled, the SQL Node will treat the document being processed as a csv file, then load its contents into the Data Table. Use this feature for automated ingestion tasks.

Important Notes:

Your process must contain only a single file, and that file must be a comma separated list of values.
PostgreSQL and SQL Server handle CSV data ingestion differently. Refer to the Import CSV section for each database type for more detail.
You may not run standard SQL operations and import tasks in a single step.

Imports for SQL Server and PostgreSQL are setup differently. Refer to the configuration details below.

SQL Server CSV Import

SQL Server handles file imports automatically without any configuration specifics. Make sure your CSV matches the table definition.

PostgreSQL - Loading CSV Data

PostgreSQL has native support for data loads from CSV using the COPY command. A COPY command must be specified for imports. Note this is different than how SQL Server imports work, so be sure you are using the correct configuration for the database platform.
- An example copy command:
  COPY "<SchemaName>"."<TableName>" (id,name) FROM STDIN WITH DELIMITER ',' HEADER
- The CSV file layout should match the field definition specified in the COPY command. In the above example, the CSV is expected to have 2 columns (id and name).

GlobalAction System Values

Please note that these replacement values are case-sensitive

The node has specific support for embedding variables related to GlobalSearch into SQL commands. These variables are:

#ARCHIVEID#
#DOCUMENTID#
#DOCID#
#DATABASEID#

Use these variables in any SQL command when working with GlobalAction processes.

Use Case

Using the SQL node to backfill vendor information

In this example, I've configured the SQL node to pull vendor information from the Northwind SQL database.

This is useful for automatically querying a SQL database and table, without having to run Scheduled SQL Tasks or worry about user interaction.