Author Topic: SQL in VBScript  (Read 3364 times)

Abett

  • EA Novice
  • *
  • Posts: 5
  • Karma: +0/-0
    • View Profile
SQL in VBScript
« on: September 05, 2018, 07:49:09 pm »
I have two different SQL queries that I'm using to obtained information about information items conveyed in a diagram. I wish to automatically retrieve that information using VBScript and then compare the information to determine if it is the same in both queries.

The SQL Queries I have obtain the relevant information but I can't get the VBScript to successfully run them. I've tried Repository.GetElementSet , Repository.GetElementsByQuery , Repository.RunModelSearch and Repository.SQLQuery however I usually receive error messages such as 'Item not found in this collection' which is returned by the script below.

sub main
   dim sql
   dim result
   sql = "SELECT CONNECTOR.Name AS Connector_Name, CONVEYED.Name AS Conveyed_Name, PRODUCER.Name AS Producer_Name, PRODUCER.object_ID AS Producer_ID, CONSUMER.Name AS Consumer_Name, CONSUMER.object_ID AS CONSUMER_ID FROM t_xref XCONVEYED, t_object CONVEYED, t_object PRODUCER, t_object CONSUMER, t_connector CONNECTOR WHERE XCONVEYED.Behavior=""conveyed"" AND XCONVEYED.Description LIKE ""*"" + CONVEYED.ea_guid + ""*"" AND CONNECTOR.Start_Object_ID=PRODUCER.Object_ID AND CONNECTOR.End_Object_ID=CONSUMER.Object_ID AND CONNECTOR.ea_guid = XCONVEYED.Client AND PRODUCER.ParentID = 4"
   set result = Repository.GetElementSet(sql, 2)
end sub

Am I going about extracting this information the wrong way or have I made a mistake within the script?
Thanks

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 11849
  • Karma: +460/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: SQL in VBScript
« Reply #1 on: September 05, 2018, 08:42:22 pm »
Repository.GetElementSet is suited if you are looking for a set of EA.Elements. Your query should return the Object_ID's of the elements you are after.

For everything else use Repository.SQLQuery

That will return a xml as string that you can then further process.

For some examples using these operations see https://github.com/GeertBellekens/Enterprise-Architect-VBScript-Library/blob/master/Framework/Utils/Util.vbs

Geert