enterprise architect - How to programatically get the value of a field from a sql query?

Question

I'm writing a script to query the t_object and t_objectproperties tables and iterate on the result elements. This actually fairly simple. I'm using this:

function find_model_doc() {
var query = "select o.Object_ID, o.Name, op.Value from t_objectproperties op inner join (select * from t_object where stereotype = 'model document') o on o.Object_ID = op.Object_ID where op.Property = 'RTFTemplate'"
var all_modeldocs = Repository.GetElementSet(query, 2);
for (var o = 0; o < all_modeldocs.Count; o++) {
    elem_name = all_modeldocs.GetAt(o).Name;
    elem_value = all_modeldocs.GetAt(o).Value;
    Session.Output("name|value: " + elem_name + " | " + elem_value)
}}

If I use this query in the 'SQL scratch pad' I get:

However, with the script I can't seem to get the strings from the 'Value' field. Any suggestions on how I can do this?

Answer 1

The method EA.Repository.GetElementSet(query, 2) returns an EA.Collection with items of type EA.Element. It does so based upon the Object_ID returned by your query. All other returned values are ignored.

These objects don't have a Value property.

What you seem to be looking for, is value of the tagged values with name "RTFTemplate" defined on the element.

You can do that in two ways

Iterate the tagged values of the elements returned by GetElementSet

Something like this (vbscript)

for each element in all_modeldocs
    elem_name = element.Name
    for each tag in element.TaggedValues
        if tag.Name = "RTFTemplate" then
            elem_value : tag.Value
        end if
    next
next

Use Repository.SQLQuery and parse the resulting xml string

Something like this (vbscript)

function getArrayListFromQuery(sqlQuery)
    dim xmlResult
    xmlResult = Repository.SQLQuery(sqlQuery)
    set getArrayListFromQuery = convertQueryResultToArrayList(xmlResult)
end function

Function convertQueryResultToArrayList(xmlQueryResult)
    Dim result
    set result = CreateObject("System.Collections.ArrayList")
    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
        'loop rows and find fields
        For Each rowNode In rowList
            dim rowArrayList
            set rowArrayList = CreateObject("System.Collections.ArrayList")
            'loop the field nodes
            For Each fieldNode In rowNode.ChildNodes
                'add the contents
                rowArrayList.Add fieldNode.Text
            Next
            'add the row the the general list
            result.Add rowArrayList
        Next
    end if
    set convertQueryResultToArrayList = result
end function