VBScript retrieve ONLE value from SQLQuery

[m2i7c9k]

Hi Guy,

pleas HELP I'd like to retrieve from SQLQuery only ONE value <DestID>.

Sub Main

   Repository.EnsureOutputVisible "Script"
   Repository.ClearOutput "Script"
   
   dim currentDiagram as EA.Diagram
   set currentDiagram = Repository.GetCurrentDiagram()
   
   for each currentDiagram in currentDiagram.SelectedObjects
      
      dim theElement as EA.Element
      set theElement = Repository.GetElementByID( currentDiagram.ElementID )   
      
      Dim sqlCmd
      sqlCmd = "SELECT " &  _
         "   Object_1.Object_ID AS DestID " &  _
         "FROM  " & _
         "   t_object  "  & _
         "   INNER JOIN t_connector INNER JOIN t_object AS Object_1 ON
t_connector.End_Object_ID = Object_1.Object_ID "  & _
         "   ON t_object.Object_ID = t_connector.Start_Object_ID "  & _
         "WHERE " &  _
         "    t_object.Object_ID = " & theElement.ElementID
      
      Dim queryResult
      queryResult = Repository.SQLQuery(sqlCmd)
      'LOGInfo (queryResult)
      
      getDiagramOccurences  (queryResult)

                The result id:
                2021-8-7 14:27:24 [INFO]: <?xml version="1.0"?>
               <EADATA version="1.0" exporter="Enterprise Architect">
          <Dataset_0><Data><Row><DestID>2</DestID></Row></Data></Dataset_0></EADATA>
                   
                But I want to <DestID> value
End Sub

Thanx a lot

Mick

[m2i7c9k]

So, I did it. Like this: good enough - for me

option explicit

!INC Local Scripts.EAConstants-VBScript
!INC EAScriptLib.VBScript-Logging

Sub Main

   Repository.EnsureOutputVisible "Script"
   Repository.ClearOutput "Script"
   
   dim currentDiagram as EA.Diagram
   set currentDiagram = Repository.GetCurrentDiagram()
   
   for each currentDiagram in currentDiagram.SelectedObjects
      
      dim theElement as EA.Element
      set theElement = Repository.GetElementByID( currentDiagram.ElementID )   
      
      Dim sqlCmd
      Dim queryResult
      
      sqlCmd = "SELECT " &  _
         "   Object_1.Object_ID AS DestID " &  _
         "FROM  " & _
         "   t_object  "  & _
         "   INNER JOIN t_connector INNER JOIN t_object AS Object_1 ON t_connector.End_Object_ID = Object_1.Object_ID "  & _
         "   ON t_object.Object_ID = t_connector.Start_Object_ID "  & _
         "WHERE " &  _
         "    t_object.Object_ID = " & theElement.ElementID
         
      queryResult = Repository.SQLQuery( sqlCmd )
      'Session.Output ( queryResult )
      Session.Output ( getDestObjectID ( queryResult )  )

   next

End Sub

Function getDestObjectID ( queryResult )
'   FUnkcia vráti ID asociované (<<use>>) objektu
      Dim str1,  str2, retVal
      str1 = InStr ( queryResult, "<DestID" )
      str2 = len(queryResult)
      retVal = MID( queryResult, str1, str2 )
      retVal = replace(retVal, "</Row></Data></Dataset_0></EADATA>", "")
      retVal = replace(retVal, "<DestID>", "")
      retVal = replace(retVal, "</DestID>", "")
      'Session.Output ( retVal )
      getDestObjectID = retVal
End Function

Main

[Geert Bellekens]

Wow, that is horrible code.

What you need is to parse the xml output using an xml library and then use those functions to retrieve the actual value.

Something like this:

Code: [Select]

'converts the query results from Repository.SQLQuery from xml format to a two dimensional array of strings
Public Function convertQueryResultToArray(xmlQueryResult)
    Dim arrayCreated
    Dim i
    i = 0
    Dim j
    j = 0
    Dim result()
    Dim xDoc
    Set xDoc = CreateObject( "MSXML2.DOMDocument" )
    'load the resultset in the xml document
    If xDoc.LoadXML(xmlQueryResult) Then       
'select the rows
Dim rowList
Set rowList = xDoc.SelectNodes("//Row")

Dim rowNode
Dim fieldNode
arrayCreated = False
'loop rows and find fields
For Each rowNode In rowList
j = 0
If (rowNode.HasChildNodes) Then
'redim array (only once)
If Not arrayCreated Then
ReDim result(rowList.Length, rowNode.ChildNodes.Length)
arrayCreated = True
End If
For Each fieldNode In rowNode.ChildNodes
'write f
result(i, j) = fieldNode.Text
j = j + 1
Next
End If
i = i + 1
Next
'make sure the array has a dimension even is we don't have any results
if not arrayCreated then
ReDim result(0, 0)
end if
end if
    convertQueryResultToArray = result
End Function
Geert

[m2i7c9k]

Thank you, Geert!

Well, it was horrible code, indeed
I rewrite it without SQL.

Your code will help me in other ideas, thank you again.

Mick

Sub main
   
   On Error Resume Next
   Err.Clear
   
   Repository.EnsureOutputVisible "Script"
   Repository.ClearOutput "Script"
   
   dim msg, appName
   appName = "A&D VBScript"
   
   if Err.Number <> 0 Then
      Session.Output( "NOK" )
      msg=MsgBox( "Unknown Error.", vbOKOnly  + vbExclamation , appName )
      Exit Sub
   else
      dim currentDiagram as EA.Diagram
      set currentDiagram = Repository.GetCurrentDiagram()
      
      'Selected element type=Component check.
      if currentDiagram.SelectedObjects.Count = 0 then
         msg=MsgBox("Select 'Component' element.", vbOKOnly  + vbExclamation , appName)
         Exit Sub
      else
         for each currentDiagram in currentDiagram.SelectedObjects
         
            dim theElement as EA.Element
            set theElement = Repository.GetElementByID(currentDiagram.ElementID)   
            
            if theElement.Stereotype <> "Component" then
               msg=MsgBox("Select 'Component' element.", vbOKOnly  + vbExclamation , appName)
               Exit Sub
            else
               'Check the element has connector.
               dim con as EA.Element
               set con = theElement.Connectors.GetAt(0)
               
               dim client as EA.Element
               set client = Repository.GetElementByID (con.SupplierID )
               
               if client.Name = "" then
                  msg=MsgBox("The connector is missing.", vbOKOnly  + vbExclamation , appName)
                  Exit Sub
               else
                  'Check connector type = Usage.
                  if con.Type <> "Usage" then
                     msg=MsgBox("The connector type must be a 'Usage.'", vbOKOnly  + vbExclamation , appName)
                     Exit Sub
                  else
                     set theElement = Repository.GetElementByID(client.ElementID)
                     
                     dim theComponent as EA.Element
                     set theComponent = Repository.GetElementByID(currentDiagram.ElementID)
                     
                     dim i
                     dim attributes as EA.Collection
                     set attributes = theElement.EmbeddedElements

                     for i = 0 to attributes.Count - 1
                        dim currentAttribute as EA.Element
                        set currentAttribute = attributes.GetAt( i )
                        
                        'pondelok, 09 augusta 2021, 15:19:32
                        'check service name.
                        dim serviceName
                        serviceName = currentAttribute.name
                        if instr(1,serviceName,"(") <> 0 then
                           serviceName = mid(serviceName, 1, instr(1,serviceName,"(") - 1)
                        else
                           serviceName = serviceName
                        end if
                        'Session.Output("serviceName: " & serviceName)
                        'copy webpage interface to the component element
                        Call AddOperation (currentDiagram.ElementID, serviceName, currentAttribute.Notes)
                     next
                  end if
               end if
            end if
         next
      end if
   end if
End Sub