Enterprise Architect version 15.0



My Profile

Social Media Channels

facebook  twitter  youtube   linkedin

Tuesday, 11 September 2012 09:23

Execute a SQL query and return the result in a dictionary of rows

Written by
Rate this item
(0 votes)

The function ExecuteSQL will run a SQL select statement with a variable list of arguments and return a dictionary with the resulting rows. Each row contains itself a dictionary of columns.

To test this script create a new 'Normal' script (type VBScript) and replace the contents with the script below. The scripting output window displays a list of classes and usecases in your project with names starting with 'SSS'.

option explicit

!INC Local Scripts.EAConstants-VBScript

sub main
    Dim row, dict
    Set dict = ExecuteSQL("select * from t_object where name like '{0}' and Object_Type in ({1})", _
    if not dict is nothing then
        for each row in dict.Items
            Session.output("Found: " & _
                " | Name="&row.item("NAME")&_
                " | Notes='"&row.item("NOTES")&"'")
    end if
end sub

 ' ======================================================================================
 ' Executes a SQL query and returns the result in a dictionary of rows
 ' IN:
 '    sql  - the sql SELECT statement to run with optional arguments. Arguments must be
 '          denoted as {#} where # is the argument number, a specific value say {0} may occur
 '         multiple times
 '    args - a -string- of argument values, separated by the pipe symbol('|').
 ' OUT:
 '    a dictionary object storing for each row in the result set a dictionary of columns
 '    (name/value pairs) where name is the name of a column in the SQL result in UPPERCASE
 '    - if an error occurs the call returns the value 'nothing'
 ' - All references to an argument {i} are replaced by its value
 ' - Always use dict.item(<columname>) to obtain column values: empty columns
 '   are -not- included in the XML returned by the call to Repository.SQLQuery so
 '   the number of columns may differ for each row in the dictionary.
 '   Use the vbscript isEmpty function to check for empty values.
 ' - the dictionary keys are -case sensitive-. To prevent obvious mistakes keys use
 '    -uppercase- column names
 ' ======================================================================================
 Public Function ExecuteSQL(SQL, argList)
    set ExecuteSQL = Nothing

    Dim dict : set dict = CreateObject("Scripting.Dictionary")
    SQL = StrRep(SQL, argList)
    Session.output("INFO: executeSQL:  executing " + SQL)
    Dim xml : xml = SQLQuery(SQL)

    ' Parse the query result in a DOM tree
    Dim doc : set doc = CreateObject("MSXML2.DOMDocument")
    doc.validateOnParse = False
    doc.async = False

    ' Populate the dictionary
    Dim rowNum : rowNum = 0
    Dim row, rowSet : Set rowSet = doc.selectNodes("//EADATA//Dataset_0//Data//Row")
    For Each row In rowSet
        Dim rowDict : set rowDict = CreateObject("Scripting.Dictionary")
        Dim col, colSet : set colSet = row.childNodes
        For Each col In colSet
            rowDict.add UCase(col.nodeName), col.Text
        dict.add rowNum, rowDict
        rowNum = rowNum + 1

    If (dict.count = 1) Then
        Session.output("INFO: executeSQL: found 1 record")
        Session.output("INFO: executeSQL: found " + CStr(dict.count) + " records")
    End If
    set ExecuteSQL = dict
 End Function

Function StrRep(str, argList)
    StrRep = ""

    Dim apl: apl = "{0}"
    If argList <> "" Then
        Dim args : args = Split(argList,"|")
        Dim i
        For i = 0 To UBound(args)
            apl = "{" + CStr(i) + "}"
            If InStr(str, apl) = 0 Then
                Session.output("ERROR: missing argument " + apl + " in '" + str + "'")
                Exit Function
            End If
            str = Replace(str, apl, args(i))
        apl = "{" + CStr(UBound(args) + 1) + "}"
    End If

    ' Check for unassigned parameters
    If InStr(str, apl) <> 0 Then
        Session.output("ERROR: Value for argument " + apl + " not supplied ('" + str + "')")
        StrRep = str
    End If
 End Function

Read 13527 times


  • Comment Link paulusma Saturday, 06 September 2014 19:46 posted by paulusma

    Hi Bruce,

    Regarding the 'Run script' button, it may be disabled depending on the edition of EA you are using. Check the comparison matrix at http://www.sparxsystems.com/downloads/pdf/editions.pdf. If that's not the problem i am at a loss. I suggest you try the excellent EA forum.

    As for the precise SQL statement, a tip: EA repositories are aaccess databases. By renaming the extension from .eap to .accdb you can open it in M$ Access and examine the repository table structure.
    You need to query t_object, which contains a column 'Classifier' in case the object is an instance of another t_object (for instance an Object from a Class, or Action from Activity). I believe the table t_objectproperties contains the tag name/value pairs you need.

    Good luck!


  • Comment Link Bruce Singleton Tuesday, 27 August 2013 10:49 posted by Bruce Singleton

    Sparx EA Script Editor and Run Script icon

    With my background in VBScript and Javascript, it should not be a problem for me to write my own script to extract inherited tagged values from diagram elements, but I am having difficulty in understanding the Sparx EA Script Editor.

    My plan is to
    1. create a Javascript script named 'JS_Inherited_Tags'
    2. catch output from that script in the RTF Fragment between custom> Scripts: double-click on local script name. I should be able to run the script and see the output in the Script tab of the System Output window.

    Regardless of which local script name I select, the 'Run Script' icon (at the top of the script edit window) is not enabled (greyed out), so I cannot run any script to see the output in the Script tab of the System Output window.

  • Comment Link Bruce Singleton Tuesday, 27 August 2013 08:03 posted by Bruce Singleton

    Please can you help me with inherited tagged values in an RTF Report? I have a diagram that contains elements with realization connections to a Logical_Node, which has its own tagged values '01-Runtime Env' and '02-OS'. The realization causes the Logical_Node tagged values ('01-Runtime Env' and '02-OS') to be displayed in the diagram inside the realizing node (e.g. 'x86 VM'). This display is useful. I also want to include these inherited tagged values to appear in an exported RTF table, wherein each row specifies the node (e.g. 'x86 VM') and the tagged values inherited from the connected (realization) Logical_Node. I have successfully built a table that displays the following 4 columns for each element with stereotype 'x86 VM'.

    In the same table row, I also want to show inherited tagged values from the realization-connected Logical_Node as suggested in the following pseudocode
    {Element.valueOf(realized.01-Runtime Env)}

    I have ben told by Sparx Support that this is not possible using function valueOf(). Apparently I need to construct a SQL query in order to populate my table with tagged values 01-Runtime Env and 02-OS. I have extensive VB scipting and SQL scripting experience but none within Sparx EA. Please could you provide a sample custom script for obtaining tagged values 01-Runtime Env and 02-OS.

Login to post comments