Snowflake transfer objects

Snowflake databases are available to you as both sources and destinations. The required database must be configured as a plug-in instance. Create one of the Snowflake transfer objects within the connection as described. The following transfer objects are available:

• Snowflake Select: Queries one or more records (from a table or view). You must define filter and sort criteria accordingly. The transfer object thus serves as a data source.
• Snowflake Insert: During data transfer, a new record is inserted into the table. The transfer object thus acts as the transfer destination. Incomplete arrays are filled in during a database insert. Example: If a single entry from a timestamp and several entries from an array are to be read into a database, the timestamp can be used for the individual variables of the array. The timestamp is therefore automatically used multiple times until all variables have been read. Therefore, the following applies: During an insert, the highest InsertCount is always determined. Entries that have a smaller count are then extended by a factor of X so that the values in the table are filled in. Also selectable for views.
• Snowflake Update: During data transfer, one (or more) existing records in the table are updated. You must define a filter accordingly. The transfer object thus acts as the transfer target. Also selectable for views.
• Snowflake Delete: The data transfer deletes one or more records from the table. You must define a corresponding filter. Also selectable for views.
• Snowflake Stored procedure: Triggers a stored procedure as soon as the trigger condition is met. You can use input parameters and return values as targets or sources here. Select the required TO according to your requirements and set the properties queried in the respective window:

Property	Insert	Update	Select	Delete	Stored Procedure	Explanation
Data source	✔	✔	✔	✔	✔	Data source in which the following table, view or stored procedure can be found
Table	✔	✔	✔	✔		Table on which the TO-dependent operation is to be performed. Table on which the selected operation is performed, or name of the stored procedure. Note: The specified table must have a primary key. Tables without a primary key are not supported.
Stored Procedure					✔	Stored procedure to be executed
Columns/Parameters	✔	✔	✔		✔	Select the columns/parameters that are to be connected to the TO as sources or targets
Query result set					✔	If return values are expected, they can be read out with ‘Query result set’ and can then be selected to offer return values as a source at the TO.
Filter		✔	✔	✔		Configure a filter that suits your requirements to narrow down which data is to be selected/updated/deleted. There are options for linking the conditions using AND and OR operators. An explanation of the various filter operators can be found here.
Filter/Filter group Edit		✔	✔	✔		The possible entries change depending on which element is selected under ‘Filter’. Link: Defines the AND and OR operators between the filters/filter groups. Column: Column to be filtered Comparison operator for value comparison
Expected number of incoming data	✔					Optional: A check is performed at runtime to determine whether the appropriately configured expected number of records was provided for the insert.
Query			✔			Configurations for record count, sorting and marking. Details in the chapter Select-TO-Tab ‘Query’

warning

Important: If ‘Stored Procedure’ is selected as the type, the OPC Router calls the procedure immediately once with default parameters (String = NULL; Int = 0, etc.) to read the structure of the result set. A rollback is performed. The procedure must therefore be transaction-safe and must not return any errors during the standard call.

note

Note: If the type is set to ‘Update’, the transfer object provides the number of updated rows (as the data source).

Special features in Select-TO

Select-TO tab ‘Query’

Sort order	Ascending or descending.
Sort by column	Column by which the query result is sorted.
Do not change records	(only possible for queries from tables) No changes are made to the transferred records.
Mark records after transfer	(only possible for queries from tables) In a corresponding column of the source table, ‘1’ is entered for each transferred record and ‘2’ for each faulty transfer. Note: A simple primary key on the numeric column ‘ID’ is required for this in the relevant table.
Column for marking	(only possible for queries from tables) Column in which the marker for the successful or faulty data transfer is entered. The column should be automatically filled with ‘0’ as the default value.
Marking values	Here you can specify the values to be written in the marking column. The marking values must be unique.
Count failed transfers	Counts the failed transfers. Note: Under ‘Error column’, select the column in which the number of failed transfers is to be counted up.
Delete records after successful transfer	(only possible for queries from tables) Of course, transferred records can also be deleted from the table. Caution: The records are irrevocably deleted from the source table.
Cancel transfer (if query result is empty)	Cancels the data transfer if the query result is empty.
Trigger read error (if query result is empty)	Cancels the data transfer if the query result is empty and triggers the error log (in the router status and the log file).
Return empty data record	Returns an empty data record if the query result is empty.
Limit number of data records to	Only transfers the number of data records specified here. The sorting must be set accordingly. Example: Only the most recent data record is to be transferred from a query result. The sorting would have to be set to a field with a ‘timestamp’ in descending order, and the number of data records would have to be limited to ‘1’.

Mark transferred data records

Mark transferred data records. Provide columns in your tables in which OPC Router can mark transferred data records (‘transfer flag’ not transferred, transferred, transfer error). The transfer flag columns should be assigned the default value ‘0’ and must not accept NULL so that correct assignment to the three statuses is always guaranteed.

Sort select results

Sort the table in ascending order by the transfer flag. This way, the non-transferred data records are transferred first; faulty individual transfers are only retried at the end of the transfer. For performance optimisation, you can filter by the transfer flag column (transfer flag = 0 or = 2).