Script for export to Excel get different SQL result?

[Hurra]

Hello!

I did a SQL query in the query builder and when I got my desired result I plugged it in my Excel-export script.

For some reason the Excel output is not the same as in the query builder output. It's the third column, 'conveyed'.

Query builder:

Code: [Select]

SELECT DISTINCT source.name AS 'source', target.Name AS 'target', COALESCE(STRING_AGG(conveyedObject.Name, ', '), 'NO DATA') AS 'conveyed'
FROM t_connector AS actualLinks
INNER JOIN t_diagramlinks AS diagramLinks ON actualLinks.Connector_ID = diagramLinks.ConnectorID
INNER JOIN t_object AS source ON source.Object_ID = actualLinks.Start_Object_ID
INNER JOIN t_object AS target ON target.Object_ID = actualLinks.End_Object_ID
FULL JOIN t_xref AS x ON x.Client = actualLinks.ea_guid AND x.Behavior = 'abstraction'
FULL JOIN t_connector AS infoLinks ON x.Description LIKE '#WC#'+infoLinks.ea_guid+'#WC#'
FULL JOIN t_xref AS x2 ON x2.Client = infoLinks.ea_guid AND x2.Behavior = 'conveyed'
FULL JOIN t_object AS conveyedObject ON conveyedObject.ea_guid = x2.Description

WHERE diagramLinks.DiagramID = 2576
GROUP BY source.name, target.Name

Script:

Code: [Select]

var queryString = "SELECT DISTINCT source.name AS source, target.Name AS target, COALESCE(STRING_AGG(conveyedObject.Name, ', '), 'NO DATA') AS conveyed " +
"FROM t_connector AS actualLinks " +
"INNER JOIN t_diagramlinks AS diagramLinks ON actualLinks.Connector_ID = diagramLinks.ConnectorID " +
"INNER JOIN t_object AS source ON source.Object_ID = actualLinks.Start_Object_ID " +
"INNER JOIN t_object AS target ON target.Object_ID = actualLinks.End_Object_ID " +
"FULL JOIN t_xref AS x ON x.Client = actualLinks.ea_guid AND x.Behavior = 'abstraction' " +
"FULL JOIN t_connector AS infoLinks ON x.Description LIKE '#WC#'+infoLinks.ea_guid+'#WC#' " +
"FULL JOIN t_xref AS x2 ON x2.Client = infoLinks.ea_guid AND x2.Behavior = 'conveyed' " +
"FULL JOIN t_object AS conveyedObject ON conveyedObject.ea_guid = x2.Description " +
"WHERE diagramLinks.DiagramID = " + theDiagram.DiagramID + " " +
"GROUP BY source.name, target.Name";

// Parse query result
var queryResult as EA.Collection;
queryResult = Repository.SQLQuery(queryString);
var DOMDoc = XMLParseXML(queryResult);

// Setup arrays for export
var sourceNames = XMLGetNodeTextArray( DOMDoc, "//Row/source" );
var targetNames = XMLGetNodeTextArray( DOMDoc, "//Row/target" );
var conveyedNames = XMLGetNodeTextArray( DOMDoc, "//Row/conveyed" );

// Create headers
var header = new Array();
header = ["Source", "Target", "Information"];

// Add headers to Excel
for ( i = 0; i < header.length; i++)
{
excelSheet.ActiveSheet.Cells(1, i+1).Value = header[i];
}
Session.Output("Headers generated...");

// Add arrays to Excel
Session.Output("Exporting " + sourceNames.length + " rows...");
for ( i = 0; i < sourceNames.length; i++ )
{
excelSheet.ActiveSheet.Cells(i+2, 1).Value = sourceNames[i];
excelSheet.ActiveSheet.Cells(i+2, 2).Value = targetNames[i];
excelSheet.ActiveSheet.Cells(i+2, 3).Value = conveyedNames[i];
}

In the Excel conveyedNames is just NO DATA. But in the query builder there is data.

What's going on?

[Geert Bellekens]

'#WC#' only works in EA.

If you want to use that in a script, you have to provide the actual wildcard '%' (or '*' if you are on .eap)

Careful though, there is more then one way conveyed objects can be modelled.
Depends on whether you are using an informationflow connector or not.

Geert

[Richard Freggi]

I see you are using *.eap Access repository.





I too like to live dangerously. 

[Paolo F Cantoni]

I see you are using *.eap Access repository.


I, too, like to live dangerously. 

I've used MS Access since v1.0.  In the right hands, it is a wonder to behold.   
We've set up an external MS Access helper that allows us to manage our repositories with the same queries and macros regardless of whether they are .EAPX or SQL Server repositories.

We'll be looking at adding QEAP repositories to the list.

You can take calculated risks with great benefits if you know what you are doing. 

Paolo

[Hurra]

'#WC#' only works in EA.

If you want to use that in a script, you have to provide the actual wildcard '%' (or '*' if you are on .eap)

Careful though, there is more then one way conveyed objects can be modelled.
Depends on whether you are using an informationflow connector or not.

Geert

Thank you!

Regarding your comment, could you elaborate? What different techniques are there, and how does it impact conveyed objects?

I was just about to create a new thread about InformationFlows, since it might be another topic. I'll try here, if it's to big of a follow-up question, let me know and I create a new thread.

I use EAs UAFP.

I create OperationalExchange (InformationFlow) connectors between to OperationalPerformers in an Operational-Connectivity (BD) diagram, and convey InformationElements (one per connector).

I use these OperationalPeformers in context of a OperationalArchitecture, represented as OperationalRoles (whole/part). The OperationalArchitecture has an IBD, Operational-Structure, where I create OperationalConnectors between the OperationalRoles.

On the OperationalConnectors I use 'Information Flows Realized' and select the InformationElements I'm intrested in for this particular context (OperationalArchitecture).

I have a question though.

If I create an OperationalExchange from OperationalPerformerA to OperationalPerformerB,
and in the IBD I create an OperationalConnector from OperatinoalRoleB to OperationalRoleA,
and realize the InformationFlow (OperationalExchange),
it looks like the conveyed object goes from OperationalRoleB to OperatinoalRole A,
although the actual OperationalExchange (InformationFlow) goes from OperationalPerformerA to OperationalPerformerB.

So in conclusion the questions are:

Does the InformationFlow, or OperationalExchange, just indicates that something moves between the two objects, and not really the direction?
Is it the direction of the realized informationflow that states the direction?
Or is it important that the direction of the InformationFlow (OperationalExchange) and the realization (OperationalConnector) has the same direction?

One problem that arises is that when an object flow both ways, I create two OperationalExchange (InformationFlow) connectors, instead of trying a bi-directional direction. Therefore, when I realize the InformationFlow, the object are represented two times. But there are no indication of which direction each object is representing. So it seems like it is the direction of the OperationalConnector (realized informationflow) and not the OperationalExchange (InformationFlow) that stipulates the direction of the realized flow.

Why I'm asking is because we have several occasions where the same object flows back and forth, what I'm after is:

Is it enough to create one OperationalExchange (InformationFlow) one way, and create two OperationalConnectors (realizing InformationFlows), both realizing the same OperationalExchange (InformationFlow)?

Edit:
I just tested some stuff. It seems like what is important is the END-object of the InformationFlow (OperatinoalExchange). Check thess images:
https://imgur.com/a/y8n6XRW

I see you are using *.eap Access repository.

I too like to live dangerously. 

I'm not really sure what you mean. The repositories are centralized and individual projects just request a repository to work in.

Is there some change I should suggest which we could do better?

[Geert Bellekens]

I think an informationflow uni-directional.

So if you have the same conveyed element flow back and forth, you would need two different informationflows.

The "different" ways I talked about is the difference between a regular information flow, and another type of relation (such as an association) that has conveyed elements.
In the latter case there is a (generally invisible) realizying information flow that actually has the conveyed elements.

Geert

[Richard Freggi]

Hi Hurra
regarding my joke, search the forum for criticism of Access repository as just about the worst option in terms of reliability, lack of standardization (not ANSI compliant) and easy of query.  Access is itself regarded as a joke in database circles and I don't know if Microsoft is planning to continue support for it. 

Sparx is moving to SQLite as the default repository which is something I requested on this forum and emailed to them a few years back.  I've had a few bad experiences myself with Access and I use Firebird *.feap for all my projects until V16 is out.