SQL Query Metadata
SQL query metadata is metadata extracted from a result of an SQL query. It is an improved concept of Dynamic metadata, introduced in CloverDX 5.3.0. This metadata is backward-compatible with dynamic metadata from previous versions - by saving a graph in version 5.3.0 or newer, dynamic metadata is converted to an SQL query metadata.
The SQL query metadata structure is generated dynamically during runtime using the result of an SQL query. The structure is generated only during the initialization phase of the graph and does not change during the run of the graph, i.e. the structure of the metadata can change between executions of a graph, but not during a single execution.
SQL Query Metadata in CTL
SQL query metadata can be used as a type for variables in CTL code.
If a direct reference to a field is made, the Transform editor shows this warning:
Field [field_name] may not exist in record with SQL query metadata [record_name]
SQL query metadata allows you to create reusable graphs thanks to parameterization of the connection and query used, allowing you to use the same graph with multiple database tables or databases.
SQL query metadata is useful in cases where the exact fields of metadata are irrelevant, such as when dumping a DB table to a file. When columns are added to a table, it breaks metadata extracted from DB as the field counts no longer match. So if you use SQL query metadata, you no longer have to go back and manually add fields. If you want to enforce exact metadata structure, you should use metadata extracted from a database.
In most aspects, the metadata behaves similarly to a non-SQL query metadata; however, there are some limitations:
an SQL query metadata cannot be exported or externalized;
it cannot be merged with a statically typed metadata;
SQL query metadata cannot be used if the database or the table does not exist during graph initialization, i.e. if creating the DB/Table is part of a jobflow;
the type of an SQL query metadata is always delimited.