#DB=<DBNAME># macro in SQL Search not works in EA 16.1

[Farkas]

Hi,

I tried to use the #DB=<DBNAME># type of macro in SQL searches in EA 16.1, but it doesn't seem to work. I got errors for these searches in .qea (SL3 = sqlite) and Oracle db repositories too.
Example query:

Code: [Select]

SELECT p1.Package_ID, p1.Parent_ID, o1.Stereotype
FROM T_PACKAGE p1
#DB=SL3# inner join t_object o1 on o1.PDATA1=p1.Package_ID #DB=SL3#
#DB=Oracle# inner join t_object o1 on o1.PDATA1=TO_CHAR(p1.Package_ID) #DB=Oracle#
WHERE p1.Package_ID = #Package#
However #WC# and #Package# macro works perfectly in all cases.
Examples:

Code: [Select]

SELECT p1.Package_ID, p1.Parent_ID, o1.Stereotype
FROM T_PACKAGE p1
inner join t_object o1 on o1.PDATA1=p1.Package_ID
WHERE p1.Package_ID = #Package#(like the one above, but only for SL3/.qea)

Code: [Select]

SELECT o1.*
FROM t_object o1
WHERE o1.Name like "Control#WC#"(this uses #WC# perfectly in .qea and in Oracle too)

Please correct me if I missed something, or please investigate this and fix it!

[qwerty]

Which error do you find?

q.

[Geert Bellekens]

The manual says to use #DB=ORACLE#, not #DB=Oracle#.

These macro's tend be be case sensitive.

Geert

[Farkas]

Which error do you find?

Sorry, this I forgot to add to the original post.
In .qea (SL3) I get this:

Code: [Select]

SQL API Open FAILED with error: near "#DB": syntax errorIn Oracle I get this:

Code: [Select]

SQL API Open FAILED with error: ORA-00911: invalid character(I would share screenshots, just I cannot figure out how  )

If I remove the #DB=<xx># macro part, and only the #Package# remains in the query, there's no error. (Same is true for #WC#, I mentioned in the original post.)

The manual says to use #DB=ORACLE#, not #DB=Oracle#.
These macro's tend be be case sensitive.

You're quite right, however I tried both ways, and it fails every time.

So, my test query with 'ORACLE' (upper case) is the following:

Code: [Select]

SELECT p1.Package_ID, p1.Parent_ID, o1.Stereotype
FROM T_PACKAGE p1
#DB=SL3# inner join t_object o1 on o1.PDATA1=p1.Package_ID #DB=SL3#
#DB=ORACLE# inner join t_object o1 on o1.PDATA1=TO_CHAR(p1.Package_ID) #DB=ORACLE#
WHERE p1.Package_ID = #Package#

[Geert Bellekens]

I just tried it in v16.1.1628 and it worked perfectly

Code: [Select]

SELECT p1.Package_ID, p1.Parent_ID, o1.Stereotype
FROM T_PACKAGE p1
#DB=SL3# inner join t_object o1 on o1.PDATA1=p1.Package_ID #DB=SL3#
#DB=ORACLE# inner join t_object o1 on o1.PDATA1=TO_CHAR(p1.Package_ID) #DB=ORACLE#
#DB=SQLSVR# inner join t_object o1 on o1.ea_guid = p1.ea_guid#DB=SQLSVR#
WHERE p1.Package_ID = #Package#
Did you by any chance test it in the scratch pad instead of the actual SQL Search?
Those are not the same. In the scratch pad I got the same error you mentioned.

Geert

PS. you can join t_object and t_package on the field ea_guid, which don't require any conversions.

[Farkas]

Hi Geert,

you're right, I was using the sql scratch pad, and saving the query really did make #DB=<xx># macro work!

It would be nice if the help page about sql queries (https://sparxsystems.com/enterprise_architect_user_guide/16.1/the_application_desktop/creating_filters.html) explicitly mentioned this bit of information.
Though, to be fair, it implies it starting the section that describes the macros with this sentence:

You can create SQL statements using the SQL Editor through the 'Query Builder' tab.

On the other hand, your tip is the final solution for my original problem (that brought me to the problem of not working macros):

PS. you can join t_object and t_package on the field ea_guid, which don't require any conversions.

(In a nutshell, I wanted to use a sql query in a document template for excluding packages, like in the manual: https://sparxsystems.com/enterprise_architect_user_guide/16.1/model_publishing/exclude_package_query_and_scri.html. I was hoping for a database-agnostic solution and yours is one, because of not needing conversions.)

Thank you!