Skip to main content

Generate components in bulk

If you'd otherwise end up with dozens of near-identical templates (one per currency, one per view, one per region), use a dynamic query instead. One template, a SQL query that returns rows, one component per row.

The shape​

Add a <DynamicQuery> element to any template. Its SQL returns one row per component to generate. The SELECT aliases become ${…} placeholders you can use anywhere else in the template:

<DataComponent Category="currencies" Name="${filename}" DeleteEnabled="true">
  <Table Id="1" Name="CURRENCY">
    <Column Name="CCY_CODE" PrimaryKey="true"/>
    <Filters>
      <Value Column="CCY_CODE" Value="${name}"/>
    </Filters>
  </Table>

  <DynamicQuery><![CDATA[
    SELECT LOWER(REPLACE(CCY_CODE, ' ', '_')) AS '${filename}',
           CCY_CODE                           AS '${name}'
    FROM CURRENCY
    WHERE CCY_NAME NOT LIKE '%DO NOT USE%'
  ]]></DynamicQuery>

  <Deployment Weight="500"/>
</DataComponent>

For each row the query returns, DataStar generates one component:

  • The filename comes from ${filename}: sanitised currency code.
  • The filter value comes from ${name}: the real CCY_CODE.
  • The MERGE is scoped to just that currency's rows.

100 currencies in? 100 components out, each deploying one currency's rows.

Writing the query​

A few rules:

  • Each aliased column becomes a placeholder. AS '${filename}' → usable anywhere as ${filename}.
  • Use CDATA so you don't have to XML-escape SQL operators.
  • Every row must produce a unique filename. Duplicate filenames overwrite each other; add disambiguating columns to the SELECT if needed.
  • Keep it read-only. The query runs at every workspace refresh; side effects would run repeatedly.
  • Make it fast. A 5-second query becomes a noticeable lag every time the workspace reloads templates.

Real examples​

Every view in the database​

<ObjectComponent Category="views" Name="${filename}" Schema="${schema}">
  <SchemaObject Name="${name}" Type="View"/>
  <DynamicQuery><![CDATA[
    SELECT TABLE_SCHEMA AS '${schema}',
           '[' + LOWER(TABLE_SCHEMA) + '].[' + LOWER(TABLE_NAME) + ']'
                         AS '${filename}',
           TABLE_NAME    AS '${name}'
    FROM INFORMATION_SCHEMA.VIEWS
  ]]></DynamicQuery>
  <Deployment Weight="300"/>
</ObjectComponent>

Every function that isn't a system function​

<DynamicQuery><![CDATA[
  SELECT OBJECT_SCHEMA_NAME(id) AS '${schema}',
         '[' + LOWER(OBJECT_SCHEMA_NAME(id)) + '].[' + name + ']'
                               AS '${filename}',
         name                  AS '${name}'
  FROM sysobjects
  WHERE type IN (N'FN', N'IF', N'TF')
    AND name NOT IN ('fn_diagramobjects')
]]></DynamicQuery>

One component per tenant​

<DataComponent Category="tenant-config" Name="${filename}" DeleteEnabled="true">
  <Table Id="1" Name="CONFIG">
    <Column Name="TENANT_ID" PrimaryKey="true"/>
    <Filters>
      <Value Column="TENANT_ID" Value="${tenantId}"/>
    </Filters>
  </Table>

  <DynamicQuery><![CDATA[
    SELECT TENANT_ID     AS '${tenantId}',
           CONCAT('tenant-', LOWER(TENANT_CODE)) AS '${filename}'
    FROM TENANTS
    WHERE IS_ACTIVE = 1
  ]]></DynamicQuery>
</DataComponent>

When not to use dynamic queries​

  • You only have a handful of objects. Two or three explicit templates are clearer than one dynamic one.
  • Each object needs bespoke options. A dynamic query produces uniformly-configured components; if the view in position 3 needs Permissions="True" but nothing else does, split it out.
  • The query needs access the user might not have. Dynamic queries run as the workspace user; if they need sysadmin, workstations without those rights will silently produce empty lists.

Filtering out unwanted rows​

Filter in the query itself, not after the fact:

WHERE CCY_NAME NOT LIKE '%DO NOT USE%'
  AND IS_ACTIVE = 1
  AND TENANT_ID NOT IN ('test', 'demo')

Anything excluded here won't generate a component. Add comments explaining why; the next person maintaining the template will thank you.

Debugging​

If the query produces fewer (or more) components than expected:

  • Run the SELECT directly in your database client and compare the result count with the components DataStar generated.
  • Check every aliased column is being used somewhere in the template. Unused aliases are allowed but usually indicate a mistake.
  • Look for NULL in critical placeholders; ${filename} as NULL will produce an unusable component. Add WHERE … IS NOT NULL or COALESCE(...) to guarantee a value.

Handling duplicates​

When a dynamic query returns two or more rows that produce the same filename, DataStar treats them as duplicates. By default, duplicates are flagged as errors: the row is highlighted red, the error indicator shows, and a warning bar appears at the top of the tab.

Sometimes duplicates are expected. A query that joins across schemas might legitimately return the same object name twice, and you only want the first match. Rather than complicating the query with ROW_NUMBER() or DISTINCT, you can tell DataStar to quietly drop the extras:

<DataComponent Category="reference-data" Name="${filename}"
               ExcludeDuplicates="true">
  <!-- ... -->
</DataComponent>

With ExcludeDuplicates="true":

  • Duplicate components that don't exist on the file system are hidden from the grid entirely — no error, no row.
  • Duplicate components that already exist on the file system (because they were pulled before the query started returning duplicates) still appear, but both the Workspace and Database columns show Excluded. You can't pull or extract them while they're in this state.
  • Every excluded duplicate is logged at warning level, so you still have visibility in the log output.

The same attribute works on <ObjectComponent>:

<ObjectComponent Category="views" Name="${filename}"
                 ExcludeDuplicates="true">
  <!-- ... -->
</ObjectComponent>
tip

Prefer fixing the query over using ExcludeDuplicates. The attribute is a safety net for cases where the source data genuinely produces unavoidable collisions, not a substitute for a well-written SELECT.

See also​

Last updated on