Author Topic: SQL search for conveyed items and source/target names  (Read 3391 times)

Ramirez-UK

  • EA Novice
  • *
  • Posts: 6
  • Karma: +0/-0
    • View Profile
SQL search for conveyed items and source/target names
« on: June 19, 2017, 08:00:48 pm »
I've been working with conveyed information and trying bits of script found on the forums.  I've managed to get the following SQL to list all items conveyed, and can return the Start_Object_ID and End_Object_ID:

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 '*'+t_object.ea_guid+'*'

What I'd really like is to return the names of the start and end object elements, not just the IDs.  Is there a bit of script that can do this?

I'm afraid I'm not a SQL expert and even less of a guru when it comes to finding info buried in Sparx's database!

qwerty

  • EA Guru
  • *****
  • Posts: 11472
  • Karma: +305/-263
  • I'm no guru at all
    • View Profile
Re: SQL search for conveyed items and source/target names
« Reply #1 on: June 19, 2017, 08:27:28 pm »
Without having tested, the following should give you the result you need:

Code: [Select]
select Connector_ID, Direction, Connector_Type, t_object.Name, so.name, eo.name
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

q.

Ramirez-UK

  • EA Novice
  • *
  • Posts: 6
  • Karma: +0/-0
    • View Profile
Re: SQL search for conveyed items and source/target names
« Reply #2 on: June 20, 2017, 08:01:46 pm »
Many thanks, that gives exactly the result I need and it's quick and simple too!

Ramirez-UK

  • EA Novice
  • *
  • Posts: 6
  • Karma: +0/-0
    • View Profile
Re: SQL search for conveyed items and source/target names
« Reply #3 on: July 19, 2017, 02:05:53 am »
OK, an update on this topic - I've been busy building up some models using conveyed items, but storing this all in a local eap file.  I've then transferred this into my main SQL Server repository via an XMI import. Having done this, the custom search I created above now no longer works.  As far as I can see the search isn't timing out - it's taking longer but seems to complete - but comes up with no results at all.

I don't understand how a simple search can work OK in a local file and not on a shared repository.  To be clear, it's exactly the same search, held under 'My Searches'.

Any ideas?

qwerty

  • EA Guru
  • *****
  • Posts: 11472
  • Karma: +305/-263
  • I'm no guru at all
    • View Profile
Re: SQL search for conveyed items and source/target names
« Reply #4 on: July 19, 2017, 07:08:07 am »
Instead of * you need a %.

q.

KP

  • EA Administrator
  • EA Expert
  • *****
  • Posts: 2768
  • Karma: +47/-3
    • View Profile
Re: SQL search for conveyed items and source/target names
« Reply #5 on: July 19, 2017, 09:10:17 am »
Instead of * you need a %.

q.

Or use '#WC#' which will substitute the appropriate wildcard character for the database you're running the search on.
The Sparx Team
support@sparxsystems.com

Ramirez-UK

  • EA Novice
  • *
  • Posts: 6
  • Karma: +0/-0
    • View Profile
Re: SQL search for conveyed items and source/target names
« Reply #6 on: July 19, 2017, 07:17:09 pm »
Thanks both.  I hadn't realised that the search syntax was slightly different depending on whether you were in an eap file or a shared server.

The wildcards now work but I've run into another issue.   To confirm the search query I'm using is:
Code: [Select]
select Connector_ID, Direction, Connector_Type, t_object.Name, so.name, eo.name
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 '#WC#'+t_object.ea_guid+'#WC#' and so.object_id = Start_Object_ID and eo.object_id = End_Object_ID and t_object.name LIKE '<Search Term>'

In the eap file the search returns the following columns:

Connector_ID | Direction | Connector_Type | t_object.name | so.name | eo.name

and the search results come up fine, listing the information conveyed, the start element and end element.  On the server, the same query comes back as:

Connector_ID | Direction | Connector_Type | Name | name | name

The first and third name columns are populated with the end element; the start element and the name of the information conveyed doesn't show up at all.  Maybe there's another syntax issue I need to correct in the query?

qwerty

  • EA Guru
  • *****
  • Posts: 11472
  • Karma: +305/-263
  • I'm no guru at all
    • View Profile
Re: SQL search for conveyed items and source/target names
« Reply #7 on: July 19, 2017, 10:21:55 pm »
Also without testing: replace the first line like this (I think you get the idea):
Code: [Select]
select Connector_ID, Direction, Connector_Type, t_object.Name as "Obj-Name", so.name as "Start-Name", eo.name as "End-Name"
That should result in a unique display.

q.

Ramirez-UK

  • EA Novice
  • *
  • Posts: 6
  • Karma: +0/-0
    • View Profile
Re: SQL search for conveyed items and source/target names
« Reply #8 on: July 20, 2017, 12:14:46 am »
Thank you that's done the trick.