SQL Finding uses of Signals - How to find a GUID within the t_xref.description

[dcocks]

Signals carried on a connector of a SysML IBD are apparently stored using a t_xref where the .Client attribute is the GUID of the connector and the .description is a concatenation of GUIDs of all signals carried on that signal. I am trying to write SQL to find all the connectors that carry a selected signal. When the connector only has one signal on it then code like the following will work:

Code: [Select]

select Connector_ID, Direction, Connector_Type, t_object.Name, so.object_id, eo.object_id
from t_connector, t_xref, t_object, t_object so, t_object eo
where t_xref.Client = t_connector.ea_guid and t_xref.description LIKE t_object.ea_guid and so.object_id = Start_Object_ID and eo.object_id = End_Object_ID and t_object.name LIKE '<Signal Name>'

However, if there are more than one signals on the connector, then the "t_xref.description LIKE t_object.ea_guid" fails and that connector is not returned.

So, is there a way to effectively say "t_xref.description LIKE '%' +  t_object.ea_guid + '%' so that it would find the GUID even if it is buried in a long list of GUIDs?

[KP]

That should do it unless you're using an EAP file as your DBMS in which case you need to use '*' as your wildcard instead of '%'.

[qwerty]

If you're writing it for the search menu use %WC% as replacement for the wild card.

q.

[dcocks]

Actually, I did try the '%' +  t_object.ea_guid + '%' notation and it does not seem to work. I know very little about SQL, but the training I read always used "%" around ASCII text (e.g., Address.State like '%Carolina' to find both North Carolina and South Carolina). I test that and it works fine. Here, though, I am using "%" around a data column (ea_guid) and it is not working for me. Can someone who knows SQL better than me confirm that wildcards bookending data columns should work? I've tried with "+" and without and I have tried removing the spaces around the "+" characters.

Better yet, can someone provide a successful "where" statement that I can copy?

FWIW, I am using EA 14.01 connecting to SQL Server.

[Geert Bellekens]

In SQL Server the statement

where t_xref.description LIKE '%' + t_object.ea_guid  + '%'
will work. I've used it on numerous occasions.

If you are using this in an SQL search you can replace that with

where t_xref.description LIKE '#WC#' + t_object.ea_guid  + '#WC#'
as EA will then fill in the appropriate wildcard character depending on the database.

Geert

[Eve]

I can return the information flow details

Code: [Select]

select Connector_ID, Direction, Connector_Type, t_object.Name, Start_Object_ID, End_Object_ID
from t_connector, t_xref, t_object
where t_xref.Client = t_connector.ea_guid and t_xref.description LIKE '#WC#' + t_object.ea_guid + '#WC#' and t_object.name LIKE '<Signal Name>'
If you wanted the details of where that flow was realized I get

Code: [Select]

select Connector_ID, Direction, Connector_Type, t_object.Name, Start_Object_ID, End_Object_ID
from t_connector, t_xref, t_xref xref2, t_object
where xref2.Client = t_connector.ea_guid and xref2.description like '#WC#' + t_xref.Client + '#WC#' and t_xref.description LIKE '#WC#' + t_object.ea_guid + '#WC#' and t_object.name LIKE '<Signal Name>'

[ikraft]

I've gotten this to work using this query:

Code: [Select]

select Connector_ID, Direction, Connector_Type, t_object.Name, t_object.ea_guid, Start_Object_ID, End_Object_ID, eo.Object_ID
from t_connector, t_xref, t_object, t_object so, t_object eo
where
t_xref.Client = t_connector.ea_guid
and so.object_id = Start_Object_ID
and eo.object_id = End_Object_ID
and t_xref.description LIKE '#WC#' + t_object.ea_guid + '#WC#'
and t_object.name LIKE '<Search Term>'
I toggle between eo.Object_ID and so.Object_ID for last column based on if I want to be able to use "Find In Project Browser" for the start/source object or the end/target object.

The question I have is: How do you return the Property Type for a port object? The objects are proxy ports that are typed to InterfaceBlocks. I would like to return a column to see the name of that InterfaceBlock. I haven't been able to figure out how the Property Type for a port object is stored in the tables. Is it a tagged value? I see the proxy port type in the Tagged Values for the proxy port as "from [InterfaceBlock] isEncapsulated (blank drop down)." I've dug around in t_objectproperties and searched that table for the Object_IDs of the ports that return from the above query, but there is no record for that Object_ID.

I am able to select the returned record and view the Element Properties to see the Property Type, but it is very round-about. Returning the Property Type name (the InterfaceBlock name) would be helpful.

Any help is appreciated!

[KP]

It appears that t_object.PDATA1 for a port contains the GUID of the port's property type.

[ikraft]

That's it! Thank you so much