Messages

1

Automation Interface, Add-Ins and Tools / Re: SQL query of connectors and usage in Python

« on: February 08, 2024, 09:45:28 pm »

Thanks for the hint.
The problem is that I get the error in EA. I think that EA cannot process the "code".
I only get the error from line 29 with the "call" of "AND o_LO.Name = 'Logical plant object (LO)'"

André

2

Automation Interface, Add-Ins and Tools / Re: SQL query of connectors and usage in Python

« on: February 08, 2024, 07:46:34 pm »

Hello Geert

Thanks for the solution. It works perfectly.

Now I have another problem with the SQL query.
I have created a new SQL query in EA (search with SQL builder). When I run the following "code" in EA, I get the expected result.
When I try to run the "code" from Pyhton or with your Excel plugin (eaexcelexporter), I get the following error in EA:
"Error processing XML document: invalid or illegal XML character"

The problem is or starts with the following line:
'o_LO.Name = 'Logical attachment object (LO)'

Do you have a solution for this problem?

Code: [Select]

eaRep = win32com.client.Dispatch("EA.App").Repository # Verbindung zum Enterprise Architect-Modell herstellen
SearchTerm = "{09D06C03-1882-4226-A7E9-6D97DE70F2EC}"

### SQL Abfrage an EA
sqlQueryConnector = """
SELECT 
o.Object_Type AS CLASSTYPE,
o.ea_guid AS GUID,
p_package5.Name AS p_package5,
p_package4.Name AS p_package4,
p_package3.Name AS p_package3,
p_package2.Name AS p_package2,
p_package1.Name AS p_package1,
o.Name,
o.Stereotype,
(
SELECT
COALESCE(GROUP_CONCAT(a_SBB.Name, ''), '')
FROM t_connector c_SBB
LEFT JOIN t_object o_SBB ON c_SBB.End_Object_ID = o_SBB.Object_ID
LEFT Join t_attribute a_SBB ON o_SBB.Object_ID = a_SBB.Object_ID AND o_SBB.Name = 'SBB-CFF-FFS'
WHERE c_SBB.Start_Object_ID = o.Object_ID AND c_SBB.Connector_Type = 'Association' AND o_SBB.Name = 'SBB-CFF-FFS'
) AS 'SBB-CFF-FFS',
(
SELECT
COALESCE(GROUP_CONCAT(a_LO.Name, ''), '')
FROM t_connector c_LO
LEFT JOIN t_object o_LO ON c_LO.End_Object_ID = o_LO.Object_ID
LEFT Join t_attribute a_LO ON o_LO.Object_ID = a_LO.Object_ID AND o_LO.Name = 'Logisches Anlagenobjekt (LO)'
WHERE c_LO.Start_Object_ID = o.Object_ID AND c_LO.Connector_Type = 'Association' AND o_LO.Name = 'Logisches Anlagenobjekt (LO)'
) AS 'Logisches_Anlagenobjekt_(LO)',
(
SELECT
COALESCE(GROUP_CONCAT(a_PO.Name, ''), '')
FROM t_connector c_PO
LEFT JOIN t_object o_PO ON c_PO.End_Object_ID = o_PO.Object_ID
LEFT Join t_attribute a_PO ON o_PO.Object_ID = a_PO.Object_ID AND o_PO.Name = 'Physisches Anlagenobjekt'
WHERE c_PO.Start_Object_ID = o.Object_ID AND c_PO.Connector_Type = 'Association' AND o_PO.Name = 'Physisches Anlagenobjekt'
) AS ['Physisches Anlagenobjekt'],
(
SELECT
COALESCE(GROUP_CONCAT(a_SPEZ.Name, ''), '')
FROM t_connector c_SPEZ
LEFT JOIN t_object o_SPEZ ON c_SPEZ.End_Object_ID = o_SPEZ.Object_ID
LEFT Join t_attribute a_SPEZ ON o_SPEZ.Object_ID = a_SPEZ.Object_ID AND o_SPEZ.Name = 'Spezifikation'
WHERE c_SPEZ.Start_Object_ID = o.Object_ID AND c_SPEZ.Connector_Type = 'Association' AND o_SPEZ.Name = 'Spezifikation'
) AS ['Spezifikation'],
(
SELECT
COALESCE(GROUP_CONCAT(a_OBJ_LO.Name, ''), '')
FROM t_connector c_OBJ_LO
LEFT JOIN t_object o_OBJ_LO ON c_OBJ_LO.End_Object_ID = o_OBJ_LO.Object_ID
LEFT Join t_attribute a_OBJ_LO ON o_OBJ_LO.Object_ID = a_OBJ_LO.Object_ID AND o_OBJ_LO.Name LIKE '%LO'
WHERE c_OBJ_LO.Start_Object_ID = o.Object_ID AND c_OBJ_LO.Connector_Type = 'Association' AND o_OBJ_LO.Name LIKE '%LO'
) AS ['OBJ_LO'],
(
SELECT
COALESCE(GROUP_CONCAT(a_OBJ_PO.Name, ''), '')
FROM t_connector c_OBJ_PO
LEFT JOIN t_object o_OBJ_PO ON c_OBJ_PO.End_Object_ID = o_OBJ_PO.Object_ID
LEFT Join t_attribute a_OBJ_PO ON o_OBJ_PO.Object_ID = a_OBJ_PO.Object_ID AND o_OBJ_PO.Name LIKE '%PO'
WHERE c_OBJ_PO.Start_Object_ID = o.Object_ID AND c_OBJ_PO.Connector_Type = 'Association' AND o_OBJ_PO.Name LIKE '%PO'
) AS ['OBJ_PO'],
(
SELECT
COALESCE(GROUP_CONCAT(a_OBJ_SPEZ.Name, ''), '')
FROM t_connector c_OBJ_SPEZ
LEFT JOIN t_object o_OBJ_SPEZ ON c_OBJ_SPEZ.End_Object_ID = o_OBJ_SPEZ.Object_ID
LEFT Join t_attribute a_OBJ_SPEZ ON o_OBJ_SPEZ.Object_ID = a_OBJ_SPEZ.Object_ID AND o_OBJ_SPEZ.Name LIKE '%SPEZ'
WHERE c_OBJ_SPEZ.Start_Object_ID = o.Object_ID AND c_OBJ_SPEZ.Connector_Type = 'Association' AND o_OBJ_SPEZ.Name LIKE '%SPEZ'
) AS ['OBJ_SPEZ']
FROM t_object o
Inner JOIN t_package p_package1 ON (o.Package_ID = p_package1.Package_ID)
Inner JOIN t_package p_package2 ON (p_package2.package_id = p_package1.Parent_ID)
Inner JOIN t_package p_package3 ON (p_package3.package_id = p_package2.Parent_ID)
Inner Join t_package p_package4 ON (p_package4.package_id = p_package3.Parent_ID)
Inner Join t_package p_package5 ON (p_package5.package_id = p_package4.Parent_ID)
WHERE p_package2.ea_GUID = '""" + SearchTerm + "' AND o.Stereotype = 'SBB_BIM_Data::fdkObjektdatentyp'"

print (sqlQueryConnector)


xmlResult = eaRep.SQLQuery(sqlQueryConnector)
from_xmlResult = XET.fromstring(xmlResult)


3

Automation Interface, Add-Ins and Tools / SQL query of connectors and usage in Python

« on: December 21, 2023, 07:53:01 pm »

Hey everyone
I am an absolute noob with SQL queries.
I want to query the connectors between classes and then use them in Python for further operations.
My problem is that the list that arrives in Python is empty.
If I do the same with an object query, I get the list as it is displayed in the EA also in Python.

Code: [Select]

SELECT
c.Connector_Type AS CLASSTYPE,
c.ea_GUID AS CLASSGUID,
pp_p.Name as Parent_Parent_Package_Name,
c.ea_GUID as Connector_GUID,
c.connector_ID as Connector_ID,
c.connector_Type as Connector_Type,
o2.Name AS Source_Name,
c.Direction AS Direction,
o.Name AS Target_Name
FROM t_connector c
INNER JOIN t_object o ON (c.End_Object_ID = o.Object_ID)
INNER JOIN t_object o2 ON (c.Start_Object_ID = o2.Object_ID)
JOIN t_package p_p ON (o.package_ID = p_p.Package_ID)
JOIN t_package pp_p ON (pp_p.Package_ID = p_p.parent_id)
WHERE pp_p.ea_GUID = '<Search Term>' AND (o.Stereotype = 'Objektdatentyp') AND (c.Connector_Type = 'Realisation')