Author Topic: Conveyed items & SQL  (Read 103 times)

Mats Gejnevall

  • EA User
  • **
  • Posts: 29
  • Karma: +0/-0
    • View Profile
Conveyed items & SQL
« on: March 17, 2017, 08:35:49 pm »
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

  • EA Guru
  • *****
  • Posts: 8395
  • Karma: +115/-106
  • I'm no guru at all
    • View Profile
Re: Conveyed items & SQL
« Reply #1 on: March 17, 2017, 08:52:39 pm »
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

  • EA Guru
  • *****
  • Posts: 7448
  • Karma: +137/-20
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Conveyed items & SQL
« Reply #2 on: March 18, 2017, 12:49:45 am »
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