SQL Search macro #BranchGUIDorID= xyz#

[PeterHeintz]

Searching recursively children of a parent is not really simple in SQL. EA already provides the #Branch# macro which simplifies to find all children of a selected package.

However I have several use cases where this feature would be very useful if I could define the package without selecting it.

Example: a Kanban related to a higher level package could be updated by sql selecting elements under the higher level package.
Due to the fact that the feature is in principle already there, the implementation seems to me to be easy.

[Paolo F Cantoni]

+1

Paolo

[Helmut Ortmann]

Hi,

If I understand you right:

Select a Diagram, Element,.. and use #Branch# in SQL than you get the comma seperated list of Package IDs which you can use in SQL.

If you have this in mind you can get help by using hoTools and the SQL features. It provides this.
See:
http://community.sparxsystems.com/community-resources/977-hotools-sql-with-tabbed-editing-macros-templates or
https://github.com/Helmut-Ortmann/EnterpriseArchitect_hoTools/wiki/hoTools

I you have something else in mind please explain it.

Regards,

Helmut

[qwerty]

No, he wants it explicitly, using a parameter rather than setting a manual focus.

q.

[PeterHeintz]

Yes, I confirm I want an explicit parameter rather than selection something in the browser.
This would allow filtering things under a package without selection it.

[Paolo F Cantoni]

Yes, I confirm I want an explicit parameter rather than selection something in the browser.
This would allow filtering things under a package without selection it.

This is important for us because we have a standard repository structure where the same package in various snapshots and sub-set repositories has the same GUID.  These are hard-wired into our SQL queries and it would be good to be able to do the same in our searches.

Paolo

[Helmut Ortmann]

Hi,

thanks, I got the picture.

If you need such a feature in hoTools as a SQL macro just drop me a line. It's no big deal and it's a simple extension of the existing #Branch# macro / concept.

Regards,

Helmut

[PeterHeintz]

Helmut,
where I get this hoTools from?
Is it open source?

[qwerty]

ho = Helmut Ortmann :-) It's his origin and he made it open source on github.

q.

[Helmut Ortmann]

Hi PeterHeintz,

where pkg.package_ID in (#Branch={0E702249-BFFA-43cd-86C9-41D3324A7617}#) is now part of hoTools.

There is also a Context Menu to insert this with the current selected package.

You find it at (2.03):
https://github.com/Helmut-Ortmann/EnterpriseArchitect_hoTools/releases

Possible Macros / WiKi:
https://github.com/Helmut-Ortmann/EnterpriseArchitect_hoTools/wiki/EaSqlMacros

Regards,

Helmut

[PeterHeintz]

Hello Helmut,
that is super!
I will try it today.
Thank you!

[McMannus]

Hi all, cool thing! I didn't know about the macros yet.

However, the simple query

Code: [Select]

SELECT * FROM t_object WHERE Package_ID IN (#Branch={D2B412CB-E1C6-4d59-A6B2-ECF22F7C8418}#)
fails for me with the following DAO error

Code: [Select]

DAO.Database [3075]

Syntax error in date in query expression 'Package_ID IN (#Branch={D2B412CB-E1C6-4d59-A6B2-ECF22F7C8418}#)'.

Context:
SQL: SELECT * FROM t_object WHERE Package_ID IN (#Branch={D2B412CB-E1C6-4d59-A6B2-ECF22F7C8418}#)
The package exists for sure with this GUID and the macro is exactly how Helmut described it.
Any ideas?

[PeterHeintz]

Did you install the hoTools on you EA installation?

[McMannus]

Yeah, I have it installed, but I thought the macros are processed by EA as well? It seems to me as EA simply passes the query as is to the database and doesn't resolve the macro, because in Access ## indicates date literals.

[Helmut Ortmann]

Hi,

you have to install hoTools and you have to run the SQL from the hoTools Addin in the AddinWindow, Tab 'SQL'.

The Macros are converted before it is sent to EA and then by EA to the Database / Repository. As benefit: If you get SQL errors hoTools automatically reads the EA error file and outputs it together with the error message.

To install it you have to have the rights to install a Windows dll (local administrator).

Kind regards,

Helmut