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
main
sub main
Dim row, dict
Set dict = ExecuteSQL("select * from t_object where name like '{0}' and Object_Type in ({1})", _
"SSS*|'Class','Usecase'")
if not dict is nothing then
for each row in dict.Items
Session.output("Found: " & _
" | Name="&row.item("NAME")&_
" | Notes='"&row.item("NOTES")&"'")
next
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'
'
' NOTES:
' - 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
doc.loadXML(xml)
' 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
Next
dict.add rowNum, rowDict
rowNum = rowNum + 1
Next
If (dict.count = 1) Then
Session.output("INFO: executeSQL: found 1 record")
Else
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))
Next
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 + "')")
Else
StrRep = str
End If
End Function
