Freeform Select

Transport maps can be configured to execute a Select operation using a freeform format. The Freeform Select operation allows you to create complex select statements from one or more database tables and then update device variables with data from the returned results. The maximum number of rows of results is maximum specified using the Max Rows parameter. The data can only be written to device variables that you have write permission. 

Freeform Select is an advanced action that requires knowledge of general SQL as well as specifics of the particular database you're working with.
If you have administrative access to the database being queried you may get better performance defining a stored procedure, which offloads the processing of the query onto the database server.

The following assumes that you have specified the database transport

1. Using the Action down arrow, select Freeform Select.
   The bottom of the Transport Map window changes slightly to accommodate the Select operation.
   
   
2. In the Freeform field enter your query. It must start with SELECT. It cannot include SQL keywords that could modify data or definitions (e.g. DROP, ALTER, CREATE, etc) or certain other keywords depending on the database.
3. Press the Query button or press Ctrl+Enter to process the query. Note this will execute this query against the database. If the query is processed without errors you will see columns in the table.
4. To automatically add the map variables to the Output table click the MapParms button.
   You may edit these names in the Output table and changes will be reflected in the column mappings.
   
   When you define the trigger using this transport map, you will associate one or more map variables from the transport map's Output tab to device variables on the trigger's Output tab. 
   
5. Under the From Enterprise section associate the appropriate map variables on the Output tab with the table columns on the Select tab.
   
   If you need help with this task, go to Associating map variables with table columns.
   
6. Once parameters are mapped, the Input table will contain one generated variable, alternate_query.
   See the next section for details.
   The output columns of the alternate query must match the column structure of the query defined within the map
7. A value in the Max Rows box indicates the maximum number of rows that you want the Select operation to handle.
   

Alternate Query

A map created with freeform select will automatically create an input variable called alternate_query when first saved. This length of this variable can be modified but it must remain a String.

When a value is input, this string will be used as the SQL Query instead of the one defined in the map.

A String can be created elsewhere within in trigger using the various string related actions available. The only limitation is that it must be a SELECT statement and the result of the statement must match the output columns of the SELECT statement used to create the map.

So if the original select is SELECT A,B,C from MYTABLE, the alternate query can use any valid SQL SELECT modifiers like WHERE, JOIN, etc, or reference a different table, as long as the resulting columns returned are A,B,C.

Database null value handling

It is possible that a database column used in a Freeform Select operation contains a null value. This will cause the transport map to fail if a null is returned.

To enter this problem, type a value in the Default Value column.

This Default Value column is used in operations to handle null values from the database. 

The value entered must be compatible with the database type. For example do not enter a string for a numeric data type.
When the Select operation executes and a null is encountered, the default value will be substituted for the null, thereby allowing the transaction to complete successfully.

The default value format for a database column of type date and time is MM/dd/yy HH:mm:ss.000 where hours are zero through 23.

Since this date format has an abbreviated year pattern ("yy"), the abbreviated year pattern must be interpreted relative to some century.  This is done by adjusting dates to be within 80 years before and 20 years after the current date.
For example, using a pattern of "MM/dd/yy" and a current date of January 1, 1997, the string "01/11/12" would be interpreted as January 11, 2012 while the string "05/04/64" would be interpreted as May 4, 1964.

For the following enterprise gateway platforms you have to specify the default value of the timestamp as a long value that represents milliseconds since 1/1/1970 00:00:00.000

• Mistubishi MESInterface IT
• Enterprise Gateway for Siemens-ERPC