Conveyed items & SQL

[Mats Gejnevall]

Hi
This code works to get a list source and target applications and the information that flows between them (from the InformationFlow relation) and the mechanism used to communicate (integrate) the appplications (tag on InformationFlow). I have used it from excel and it works fine. But when I use it from SPARX it does not find the dbo.split function.
I do have the code for the dbo.split (from internet), but I do not know to include it in the SPARX SQL window. Anyone knows???

Code: [Select]

SELECT  source.name AS Source ,
        source.stereotype AS Source_Stereotype ,
        target.name AS Target ,
        target.stereotype AS Target_Stereotype ,
        ISNULL(ct.Value, '') AS IntegrationDescription
, o.name Information
FROM    ( SELECT    Behavior ,
                    Client ,
                    d.s Description
          FROM      t_xref x1
                    CROSS APPLY dbo.split(',', x1.description) d
          WHERE     x1.Behavior = 'Conveyed'
        ) x
JOIN t_object o ON x.Description=o.ea_guid
        JOIN dbo.t_connector c ON c.ea_guid=x.Client
JOIN dbo.t_object source  ON c.Start_Object_ID=source.Object_ID
JOIN dbo.t_object target ON c.End_Object_ID=target.Object_ID
        LEFT OUTER JOIN dbo.t_connectortag ct ON c.Connector_ID=ct.ElementID
WHERE   x.Behavior = 'conveyed'
        AND c.end_object_id = target.object_id
        AND source.stereotype != 'Process'
        AND target.stereotype != 'Process';

dbo.split from https://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql.html

Code: [Select]

[/
CREATE FUNCTION [dbo].[Split]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)
GO
code]

[qwerty]

You can't. The SQL processing inside EA has some limitations. You can use only SELECT (I think for good reasons). No idea if you can issue the CREATE outside. Try putting it in a Repository.Execute and see if the function is created permanently.

q.

[Geert Bellekens]

Here's the code I use to get the conveyed items from an informationflow.
I think it's possible to get the conveyed items using only SQL as well.
If I'm not mistaken you should even find an SQL implementation on this forum.

Code: [Select]

public HashSet<UML.Classes.Kernel.Classifier> conveyed
{
get
{
if (_conveyed == null)
{
string getXrefDescription = @"select x.Description from t_xref x
where x.Name = 'MOFProps'
and x.Behavior = 'conveyed'
and x.client = '" + this.guid + "'";
//xrefdescription contains the GUID's of the conveyed elements comma separated
var xrefDescription = this.model.SQLQuery(getXrefDescription).SelectSingleNode(this.model.formatXPath("//Description"));
if (xrefDescription != null)
{
foreach (string conveyedGUID in xrefDescription.InnerText.Split(','))
{
var conveyedElement = this.model.getElementWrapperByGUID(conveyedGUID) as UML.Classes.Kernel.Classifier;
if (conveyedElement != null)
{
//initialize if needed
if (_conveyed == null)
{
_conveyed = new HashSet<UML.Classes.Kernel.Classifier>();
}
//add the element
_conveyed.Add(conveyedElement);
}
}
}
}
//nothing found, return empty list.
if (_conveyed == null)
{
_conveyed = new HashSet<UML.Classes.Kernel.Classifier>();
}
return _conveyed;
}
set
{
throw new NotImplementedException();
}
}
Geert

[stephane_roux]

Old post but the query below could help someone (EA v8 with eap file)

I get the list of conveyed items by using de t_xref.description field. Note : I got an sql error by using LIKE operator into JOIN clause : "can't join on memo access". It is due to the TEXT type of description field. So I truncate the field with LEFT function. 800 chars go to 20 eap_id (20x40) but you can increase this limit.

Stéphane

Code: [Select]

SELECT t_Connector.ea_guid AS CLASSGUID, t_Connector.Connector_Type AS CLASSTYPE,  t_Connector.Connector_ID,
t_Connector.Name,  t_Connector.Top_Mid_Label,
startObject.Object_Type, startObject.Name,
endObject.Object_Type, endObject.Name,
x.Behavior, x.Description, conveyedObject.name
FROM (((t_Connector
INNER JOIN t_Object as startObject ON t_Connector.Start_Object_ID=startObject.Object_ID)
INNER JOIN t_Object as endObject ON t_Connector.End_Object_ID=endObject.Object_ID)
INNER JOIN t_xref as x ON t_connector.ea_guid=x.Client)
INNER JOIN t_object as conveyedObject ON (x.Behavior = 'conveyed' AND LEFT(x.description,800) LIKE '#WC#'+conveyedObject.ea_guid+'#WC#')
WHERE  t_Connector.Connector_Type = 'InformationFlow'