Version

    SQL Query Metadata

    To create metadata from an SQL query, right click Metadata in the Outline pane and select New metadata  SQL query metadata. After that, the SQL query metadata editor opens.

    sql query metadata editor
    Figure 188. SQL query metadata editor

    In the left pane of this editor, you have to provide a connection to database and an SQL query. You can either create a new connection, or link to an existing one. The SQL Query can be validated - if the query is invalid an error message is shown.

    The result of the SQL query is automatically converted to Clover types using the same algorithm that is used when extracting metadata from a database. If there are JDBC types that cannot be converted to Clover types automatically, the metadata creation fails. To avoid this, you can use the Use String for unknown JDBC types option.

    Optimizing database queries

    Since database queries are expensive operations, it is important to optimize the query used in the SQL query metadata, so that it does not return any data. This can be done manually, or by using the Optimize Query option which wraps the query in a SELECT and appends WHERE 1=0.

    In the right pane, you can edit properties of the metadata extracted from the SQL query, and add/remove custom properties by clicking on +/- symbols.

    During design time, the structure of the SQL query metadata is automatically initialized, so that it is easier to work with (the user can see what metadata are propagated from it, what fields can be mapped in Transform editors, etc.). Changes to configuration of the SQL query metadata or related connections/parameters trigger a re-initialization of the metadata structure.

    If initialization fails during design time, the structure of metadata is unknown. In this case, Transform editor displays the cannot resolve SQL metadata message. Furthermore, the metadata in the Designer’s Outline pane displays an error decorator. Placing a cursor over it, displays details of the error.