I wouldn't bother trying to execute the search, but instead just copy the SQL code in your script.
You can make a diagram script that you can execute from the context menu of the diagram.
Use Repository.SQLQuery to exectue the query.
This will return an xml string that you can parse and process into a CSV file.
Here's an example of a similar type of script:
option explicit
!INC Local Scripts.EAConstants-VBScript
!INC Wrappers.Include
'
' Script Name: Export ValidationRules
' Author: Geert Bellekens
' Purpose: Export the ValidationRules to a csv file to be imported by ODS
' Date: 2020-09-22
'
const outputName = "Export ValidationRules"
const rulesPackageGUID = "{190B02C3-7A82-4df5-90DB-171243B3818F}"
sub main
'create output tab
Repository.CreateOutputTab outPutName
Repository.ClearOutput outPutName
Repository.EnsureOutputVisible outPutName
'inform user
Repository.WriteOutput outPutName, now() & " Starting Export Validation Rules", 0
'start the actual work
exportValidationRules
'inform user
Repository.WriteOutput outPutName, now() & " Finished Export Validation Rules", 0
end sub
function exportValidationRules
Repository.WriteOutput outPutName, now() & " Getting data", 0
dim rulesPackage as EA.Package
set rulesPackage = Repository.GetPackageByGuid(rulesPackageGUID)
dim rulesPackageTreeIDs
rulesPackageTreeIDs = getPackageTreeIDString(rulesPackage)
'get data in memory
dim sqlGetData
sqlGetData = "select o.name, o.Note, tvr.value as RuleID, tvb.Value as BIVP " & vbNewLine & _
" , tvc.Value as CorrectiveAction, tvp.Value as SanityPriority " & vbNewLine & _
" , tvs.Value as SuggestedAction " & vbNewLine & _
" from t_object o " & vbNewLine & _
" left join t_objectproperties tvr on tvr.Object_ID = o.Object_ID " & vbNewLine & _
" and tvr.Property = 'RuleID' " & vbNewLine & _
" left join t_objectproperties tvb on tvb.Object_ID = o.Object_ID " & vbNewLine & _
" and tvb.Property = 'BIVP' " & vbNewLine & _
" left join t_objectproperties tvc on tvc.Object_ID = o.Object_ID " & vbNewLine & _
" and tvc.Property = 'CorrectiveAction' " & vbNewLine & _
" left join t_objectproperties tvp on tvp.Object_ID = o.Object_ID " & vbNewLine & _
" and tvp.Property = 'SanityPriority' " & vbNewLine & _
" left join t_objectproperties tvs on tvs.Object_ID = o.Object_ID " & vbNewLine & _
" and tvs.Property = 'SuggestedAction' " & vbNewLine & _
" where o.Stereotype = 'DQM_ValidationRule' " & vbNewLine & _
" and o.Package_ID in (" & rulesPackageTreeIDs & ") " & vbNewLine & _
" order by cast(isnull(tvr.Value,999) as int), o.name "
dim data
set data = getArrayListFromQuery(sqlGetData)
'format the notes
formaNotes data, 1
'write data to file
Repository.WriteOutput outPutName, now() & " Exporting data to file", 0
dim csvFile
set csvFile = new CSVFile
csvFile.Contents = data
'ask user if we need this for production
dim response
response = msgbox("Export for production?" , vbYesNo+vbQuestion, "Production or Test")
if response = vbYes then
csvFile.FullPath = "\\Project\PRD_EA\ValidationRulesOutput.csv"
csvFile.Save
csvFile.FullPath = "\\Project\GA Evaluatie_EA\ValidationRulesOutput.csv"
csvFile.Save
else
'let the user select a file for himself
dim selectedFolder
set selectedFolder = new FileSystemFolder
set selectedFolder = selectedFolder.getUserSelectedFolder("")
csvFile.FullPath = selectedFolder.FullPath & "\" & "ValidationRulesOutput.csv"
csvFile.Save
end if
end function
function formaNotes(data, columnIndex)
dim row
for each row in data
row(columnIndex) = Repository.GetFormatFromField("TXT",row(columnIndex))
next
end function
main
the code for the CSVfile class:
'[path=\Framework\Utils]
'[group=Utils]
'Name: CSVFile
'Author: Geert Bellekens
'Purpose: Wrapper script class for CSV files
'Date: 2017-03-20
!INC Utils.Include
Class CSVFile
'private variables
Private m_TextFile
Private Sub Class_Initialize
set m_TextFile = new TextFile
End Sub
' FileName property.
Public Property Get FileName
FileName = m_TextFile.FileName
End Property
Public Property Let FileName(value)
m_TextFile.FileName = value
End Property
' Contents property. An ArrayList ArrayLists of strings
Public Property Get Contents
set Contents = getDataFromCSVString(m_TextFile.Contents)
End Property
Public Property Let Contents(value)
'create CSV string
m_TextFile.Contents = getCSVString(value)
End Property
' FullPath property.
Public Property Get FullPath
FullPath = m_TextFile.FullPath
End Property
public Property Let FullPath(value)
m_TextFile.FullPath = value
m_TextFile.LoadContents
End Property
'save
sub Save
m_TextFile.Save
Dim objStream
Set objStream = CreateObject("ADODB.Stream")
objStream.CharSet = "utf-8"
objStream.Open
objStream.WriteText m_TextFile.Contents
objStream.SaveToFile me.FullPath, adSaveCreateOverWrite
end sub
private function getDataFromCSVString(csvstring)
dim data
set data = CreateObject("System.Collections.ArrayList")
dim lines
lines = split(csvstring, vbNewLine)
dim line
for each line in lines
dim row
set row = CreateObject("System.Collections.ArrayList")
data.Add row
dim parts
parts = split(line, ",")
dim part
for each part in parts
part = replace(part, """", "") 'remove all double quotes from the text
row.Add part
next
next
set getDataFromCSVString = data
end function
private function getCSVString(arrayList)
dim csvString
csvString = ""
dim row 'also an arrayList
for each row in arrayList
if len(csvString) > 0 then
csvString = csvString & vbNewLine
end if
dim rowString
'add double quotes for any value that isn't numerical
dim value
dim i
for i = 0 to row.Count -1
value = row(i)
if not isNumeric(value) then
row(i) = """" & value & """"
end if
next
rowString = Join(row.ToArray(), ";")
csvString = csvString & rowString
next
'return
getCSVString = csvString
end function
end ClassAnd the TextFile
'[path=\Framework\Utils]
'[group=Utils]
'Author: Geert Bellekens
'Date: 2015-12-07
!INC Utils.Include
Class TextFile
Private m_FullPath
Private m_Contents
Private m_Folder
Private m_FileName
Private Sub Class_Initialize
set m_Folder = Nothing
m_FileName = ""
m_Contents = ""
End Sub
' FullPath property.
Public Property Get FullPath
FullPath = me.Folder.FullPath & "\" & me.FileName
End Property
public Property Let FullPath(value)
dim startBackslash
startBackslash = InstrRev(value, "\", -1, 1)
dim folderPath
folderPath = left(value, startBackslash -1) 'get everything before the last "\"
if ucase(folderPath) <> ucase(me.Folder.FullPath) then
'make new folder object to avoid side effects on the folder object
me.Folder = New FileSystemFolder
me.Folder.FullPath = left(value, startBackslash -1) 'get everything before the last "\"
end if
me.FileName = mid(value, startBackslash + 1) 'get everything after the last "."
end Property
'FolderPath property
public property Get FolderPath
FolderPath = left(me.FullPath, len(me.FullPath) - len(me.FileName))
end property
'FullPathWithoutExtension property
public property get FullPathWithoutExtension
FullPathWithoutExtension = folderPath & FileNameWithoutExtension
end property
' Contents property.
Public Property Get Contents
Contents = m_Contents
End Property
Public Property Let Contents(value)
m_Contents = value
End Property
' FileName property.
Public Property Get FileName
FileName = m_FileName
End Property
Public Property Let FileName(value)
m_FileName = value
End Property
' FileNameWithoutExtension property.
Public Property Get FileNameWithoutExtension
dim startExtension
startExtension = InstrRev(me.FileName, ".", -1, 1)
FileNameWithoutExtension = left(me.FileName, startExtension -1) 'get everything before the last "."
End Property
' Extension property.
Public Property Get Extension
dim startExtension
startExtension = InstrRev(me.FileName, ".", -1, 1)
Extension = mid(me.FileName, startExtension + 1) 'get everything after the last "."
End Property
' Folder property.
Public Property Get Folder
if m_Folder is nothing then
set m_Folder = new FileSystemFolder
end if
set Folder = m_Folder
End Property
Public Property Let Folder(value)
set m_Folder = value
End Property
'save the file
sub Save
Dim fso, MyFile
Set fso = CreateObject("Scripting.FileSystemObject")
'first make sure the directory exists
me.Folder.Save
'then create file
Set MyFile = fso.CreateTextFile(me.FullPath, True, False) 'second true for unicode
MyFile.Write(Contents)
MyFile.close
end sub
'delete the file
sub Delete
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
if fso.FileExists(me.FullPath) then
fso.DeleteFile me.FullPath
end if
end sub
'let the user select a file from the file system
public function UserSelect(initialDir,filter)
dim selectedFileName
selectedFileName = ChooseFile(initialDir,filter)
'check if anything was selected
if len(selectedFileName) > 0 then
me.FullPath = selectedFileName
UserSelect = true
me.LoadContents
else
UserSelect = false
end if
end function
'load the contents of the file from the file system
public function loadContents()
Dim fso
dim fsoFile
dim ts
Set fso = CreateObject("Scripting.FileSystemObject")
if fso.FileExists(me.FullPath) then
set fsoFile = fso.GetFile(me.FullPath)
set ts = fsoFile.OpenAsTextStream(ForReading, TristateUseDefault)
If Not ts.AtEndOfStream Then
me.Contents = ts.ReadAll
else
me.Contents = ""
end if
end if
end function
end class
function getTempTextFile(subFolderName)
dim tempFolder
set tempFolder = getSpecialFolder(TemporaryFolder)
dim tempFilePath
dim fso
set fso = CreateObject("Scripting.FileSystemObject")
tempFilePath = tempFolder.fullPath & "\" & subFolderName & "\" & fso.GetTempName()
dim tempFile
set tempFile = new TextFile
tempFile.FullPath = tempFilePath
'return
set getTempTextFile = tempFile
end function
Geert
