Interpolate Query
Description
You can use the Interpolate Query part to auto-fill (interpolate) data object values into a SQL-oriented string. This part has more SQL features than the Interpolate String part.
Use
This part is not directly accessible from the Designer. However, you can add a process node, select any other part from the Designer menu, and go to the Code tab. Change the _name value to interpolate_query and save your changes.
The interpolation searches for SQL binding parameters (:param, :param:, @param, @param@, ?param, ?param?, #param, #param#) or the MS SQL Server Management Studio templates <PARAM, data type, default>. They system extracts PARAM and drops the data type and default value.
The system then performs a lookup for each param found and replaces it with the found path if you have specified the Column mapping object. Additionally, the part obtains the corresponding data type from the Column data type object if you have specified it. The data type determines if the system adds quotation marks to the object value. If the system does not find the actual object value, it inserts/interpolates NULL. If the value of a text object is 'NuLLeD' the system also inserts/interpolates NULL.
Editor Fields
| Field Name | Description/Use | Type/Options | Optional/Mandatory |
| Path | This is the path to the object into which the system will save the resulting SQL string. | Data Object | Mandatory |
| SQL query | This is the source query that will be interpolated. | Text | Data Object | Mandatory |
| Column mapping | This is the key-value collection that contains the mapping between the column names and the object paths. The column name is the key, the object path is the value in the collection. The column names are detected when specified in the parameter template or as parameter name. If not specified, the system will perform plain interpolation. | Data Object | Optional |
| Column data types | This is a key-value collection that contains the mapping between the column names and the object data types. The column name is the key and the object data type is the path in the collection. Supported data types:
|
Data Object | Optional |
| Interpolation scope | This is the root container from which the system interpolates the data object paths. Leave this empty or set to local to use the normal context (ctx), set to global to use the global context (gctx) for the situation when running in a sub-scoped call-node, or set it to a specific data object to be used (implicitly expected as an object within the local context scope or falling back to the global context scope). | Data Object | Optional |
Example
Download and import the following example project in your workspace. TBA.