How do I put SQL query results in my document?

[D L]

I have created a number of SQL queries that assemble information from my model. I need to insert the results of these queries in my report. How do I do this?

[eadocX Support]

There is an error in the Sparx API for SQL reports, so that whilst they allow us to run a report from the API, we can’t get the results back!
I’m encouraging Sparx to fix this, but no luck so far – perhaps you could help out by submitting a question to the Sparx forums (as I have) noting this ?
The API calls in question are
Project.RunModelSearch , which returns ‘void’
and
Repository.RunModelSearch(), which doesn’t say what it returns.
Hmmmm – this last bit makes me wonder if the second one really does return something – it’s just Sparx aren’t telling us: i’ll have a look

[D L]

In the installed help, I did a search on Repository Class. The RunModelSearch and SQL Query are documented.

RunModelSearch is passed in 4 arguments. The fourth argument is an XML format. The results of the model search are put in the XML format. If the fourth argument is an empty xml format, the results are populated in the xml format.

There are also JScript and VBasic script examples of the RunModelSearch. It looks like the example script only returns an element ID. My SQL queries return the names of the elements I want in my table. I am in the process of figuring out how to send the XML file results into an RTF file.

[eadocX Support]

Excellent!
I’ll look at that, and plan to include it in v2.3 – due later next month.
Thanks for the tip :-))

[Markus Krallinger]

I dont think that RunModelSearch (both in repository and project interface) can be used to populate a report with elements from a SQL query. The documentation says “Runs a search, displaying the results in Enterprise Architect’s Model Search window”, meaning that the elements are just shown in the search window but since the function does not have any return value, the elements cannot be accessed (Maybe there is a function accessing the search window, that I’m not aware of).

My workaround is, that I programmatically create a temporary diagram with the result of an SQL query, sort of a programmatical “ad-hoc” diagram. This diagram is then used as input to eaDocX (elements are formated in a table, so the layout does not matter).

[Vinicius Sobral]

Sorry to reply on this topic, but I have a question regarding these SQL results also.

I understand that this feature is still not working correctly, due to EA’s limitations, is that correct?

However, I didn’t find any documentation about how to produce this queries for eaDocX. In future versions of eaDocX, if this feature is fixed, what exactly should I return in my queries? I ask because the option of using SQL queries is located in the ‘source’ of an element report, so I didn’t know if I should return the packages/diagrams in which I would like to run the report, or the filtered elements already.

Neither do I know what properties to return (the ID, the Name or what).

What can you guys say?

[eadocX Support]

OK – I have tried this, and like you, I can’t figure out a way to get a predictable result which eaDocX can understand.
So, for the moment, we’ll have to say that SQL Searches are NOT supported by eaDocX. Maybe in some future version, but it does rather go against the whole idea of eaDocX, in that we don’t need our users to understand what happens inside EA.

[D L]

Do you know if this bug is fixed yet? We will not purchase the tool until we are able to insert queries into our reports.

[eadocX Support]

I’m looking at this at the moment, and I can’t EA to accept my SQL at the API, even the SQL in their supplied example. 🙁
Just so I understand your requirement fully, are you running SQL against the main EA tables (object, connector, package, attribute, method) as simple SQl (just attributes from one table) , or do you want to create more ad-hoc SQl, with joins etc, and columns from many tables ?
Have you explored getting the same data using just plain eaDocX ?

[Vinicius Sobral]

In my case I have tried both simple queries (only one table) and using joint also (this would probably be the query I would use in my reports, but the result is always columns from one table only)

The thing with EAdocX is:
Since I want to report the same element differently in, for instance, 2 sections in the same document (I have also posted a feature suggestion about this issue), I need an Element report section. Unfortunately, the elements selection in this report does not fit my needs. This would be solved by the SQL queries, but as already said, it does not work as expected.

Best regards.

[eadocX Support]

We’ve had a LONG think about this one…
We totally get that it would be great to allow ad-hoc SQL queries, but we’d need a whole new bit of eaDocX to define how the results should be formatted. Not impossible, but quite a bit of new user interface.
..but the whole reason we built eaDocX, (which was for us to use on our own projects and let non-EA experts create their own documents), was to keep people away from the complexities of EA. And certainly to keep them away from needing to understand the data model. We think that if a user understands the EA data model well enough to write SQL against it, they aren’t really the market we’re aiming to help,
..So, the result of all this thinking is that we MAY add this as a feature in a future version, but our real focus is in making EA more accessible to inexperienced users, by making it simpler.
Your other idea, of having different profiles for different sections, we think of in the same way: really powerful for the power user, but may make the documentation process more complicated for the average user.

Another thought, we came up in our discussion:
Not that it will solve your problem, but one trend our customers tell us about is that with eaDocX, because creating documents is easier, they create more, but smaller and simpler documents, for different audiences. Something to think about ?

[Author]

Posts