Sparx Systems Forum

Enterprise Architect => Automation Interface, Add-Ins and Tools => Topic started by: mjm on November 12, 2015, 07:19:14 am

Title: Using custom SQL fragment in custom template
Post by: mjm on November 12, 2015, 07:19:14 am

I have been trying to create documentation that produces a report using a custom query.

I created a template fragment, and added the following query to that fragment by right-clicking in the fragment's body > File > Document Options > Custom Query > Custom SQL:

with relevant_packages as (
      select      package_id,
                  name,
                  parent_id,
                  convert(varchar(max), '') as parent_name
      from      t_package
      where      package_id = 16241
      union all
      select      p.package_id,
                  p.name,
                  p.parent_id,
                  convert(varchar(max), r.name) as parent_name
      from      t_package p
      join      relevant_packages r
      on            r.package_id = p.parent_id
)
select      r.package_id,
            r.name as package_name,
            r.parent_id as parent_package_id,
            r.parent_name as parent_package_name,
            o.object_id as element_id,
            o.name as element_name,
            o.stereotype,
            o.note,
            o.author,
            o.createddate
from      t_object o
join      relevant_packages r
on            o.package_id = r.package_id
and            o.object_type <> 'Package'
where      o.createddate >= convert(datetime, '2015-10-20')
order by o.createddate desc;

I have tested this query in SQL Server Express, and can confirm that it does return records.

I then created another template, added sections, and added the SQL fragment by right-clicking in sections on the template body (I have tried adding the fragment to sections package & element) as described in the linked article:

http://www.sparxsystems.com/enterprise_architect_user_guide/10/reporting/adding_fragments_to_a_template.html

I then save the template, and generate documentation using that template, output to RTF. The document generated is blank each time.

Can anyone tell me what I'm doing wrong?

Thanks in advance!

Title: Re: Using custom SQL fragment in custom template
Post by: Eve on November 12, 2015, 08:07:21 am

EA only accepts queries starting with select.

Simplify

Title: Re: Using custom SQL fragment in custom template
Post by: Geert Bellekens on November 12, 2015, 08:10:03 am

You'll have to create a more vanilla variant of your SQL query.
EA (or the underlying database connector) doesn't understand with clauses.

It looks like you are trying to do a recursive query to traverse the model.
In that case the macro #Branch# should help you out.

More info on the macros: http://sparxsystems.com/enterprise_architect_user_guide/12.1/building_models/creating_filters.html

Geert

Title: Re: Using custom SQL fragment in custom template
Post by: mjm on November 19, 2015, 01:14:31 am

Thank you for your help!