Author Topic: SQL "like" operator not working in script  (Read 1523 times)

SevEd

  • EA Novice
  • *
  • Posts: 7
  • Karma: +0/-0
    • View Profile
SQL "like" operator not working in script
« 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


Geert Bellekens

  • EA Guru
  • *****
  • Posts: 13251
  • Karma: +554/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: SQL "like" operator not working in script
« Reply #1 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 (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

SevEd

  • EA Novice
  • *
  • Posts: 7
  • Karma: +0/-0
    • View Profile
Re: SQL "like" operator not working in script
« Reply #2 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