Sparx Systems Forum

Enterprise Architect => Automation Interface, Add-Ins and Tools => Topic started by: SevEd on May 17, 2023, 06:14:14 pm

Title: SQL "like" operator not working in script
Post by: SevEd on May 17, 2023, 06:14:14 pm

Hello everyone,

I'm having issues when running a SQL query in one of my scripts. The issue is when using the "LIKE" operator. See below a snippet of the code (VBScript):

Code: [Select]

    looksimilar = eaElement.Name & "#WC#"

    query = "SELECT t_object.ea_guid " & _
            "FROM t_object " & _
            "WHERE " & _
                    "t_object.Object_Type = " & Chr(34) & "UseCase" & Chr(34) & " AND " & _
                    "t_object.Stereotype = " & Chr(34) & "Dynamic Architecture" & Chr(34) & " AND " & _
                    "t_object.Name LIKE " & Chr(34) & looksimilar & Chr(34)
       
    dbgPrint("query")
    dbgPrint(query)

    xmlResult = Repository.SQLQuery(query)
    dbgPrint("xmlResult")
    dbgPrint(xmlResult)
Do you know if there is a bug in the SQL API? or is this operator not supported in the scripts?

using as example:

Code: [Select]

looksimilar = "APPSWC_Voltage_Monitoring#WC#"
The output query is:

Code: [Select]

SELECT t_object.ea_guid FROM t_object WHERE t_object.Object_Type = "UseCase" AND t_object.Stereotype = "Dynamic Architecture" AND t_object.Name Like "APPSWC_Voltage_Monitoring#WC#"

In the script the xml output is empty. But if I run the query in the Query Builder, the output is as expected. Do you know what I am missing here? Any recommendation?

Thanks for your help!


Best regards,
Edgar Sevilla

Title: Re: SQL "like" operator not working in script
Post by: Geert Bellekens on May 17, 2023, 06:36:14 pm

Hi Edga,

Macros's such as #WC# are only evaluated when going through the GUI, not via the API.

So you'll have to replicate them yourself. If you are using a .eap file that's "*", else it's "%"

on another note, the text indicator in SQL is a single quote, so you can write something like

Code: [Select]

"t_object.Stereotype = 'Dynamic Architecture' AND " & _instead of

Code: [Select]

"t_object.Stereotype = " & Chr(34) & "Dynamic Architecture" & Chr(34) & " AND " & _
Much easier to read and write I think.
If you are dealing with a lot of queries in VBScript, you might appreciate this little tool I made: https://github.com/GeertBellekens/TextHelper/releases (https://github.com/GeertBellekens/TextHelper/releases) (it's also included in EA-Matic)

It will transform queries like this:

Code: [Select]

select o.Object_ID from t_object o
where o.Name is not null
and o.Package_ID in (" & packageTreeIDs & ")
and not exists (
select * from t_diagramobjects do
where do.Object_ID = o.Object_ID
and do.Diagram_ID = " & diagram.DiagramID & "
)
Into

Code: [Select]

"select o.Object_ID from t_object o                 " & vbNewLine & _
" where o.Name is not null                          " & vbNewLine & _
" and o.Package_ID in (" & packageTreeIDs & ")      " & vbNewLine & _
" and not exists (                                  " & vbNewLine & _
" select * from t_diagramobjects do               " & vbNewLine & _
" where do.Object_ID = o.Object_ID                " & vbNewLine & _
" and do.Diagram_ID = " & diagram.DiagramID & "   " & vbNewLine & _
" )                                               "
And vice versa

Geert

Title: Re: SQL "like" operator not working in script
Post by: SevEd on May 17, 2023, 07:02:19 pm

Hi Geert,

Thanks for you quick response. It's working correct after the fix using "*" instead of "#WC#"
Also, I also find your suggestion really useful for improving readability!

Thanks again for your support


Best regards
Edgar Sevilla