search in t_xref.Description: SQL errors DAO.Database [3342] invalid memo/ole

[Shegit Brahm]

what I have:

a model with some signals, some statemachines and some triggers on state's transitions from type signal (=using existing signals as specification).

what I want:
search for a signal's name or ea_guid and find all triggers that are based on them (additionally their start state and end state)

what I did:
using advice in https://www.sparxsystems.com/forums/smf/index.php/topic,37989.msg236784.html to find corresponding states to a trigger (and their start state and end state), works well

What I observed:
in t_xref is also the link between a signal and its trigger:
in t_xref.Description there is "RefGUID={6BE433ED-92D8-4ff4-8CBF-BE947A2B5627};RefName=signaltrigger_to_stm3St2" and t_xref.Client contains the ea_guid of the trigger, e.g. "{AC0F2201-0378-46d7-B825-124F8220A686}"

So I thought:
I need to search for the signal's ea_guid in t_xref.Description and from there I will get the trigger via t_xref.Client

step one: create search column for signal (and use this advise: https://www.sparxsystems.com/forums/smf/index.php/topic,40370.msg247193.html):

Code: [Select]

select 'RefGUID=' + ob1ob.ea_guid  + ';RefName=' + ob1ob.name + ';'as something from
t_object ob2
inner join (select ob1.ea_guid, ob1.name from t_object ob1 where object_type = 'Signal') ob1ob on ob1ob.ea_guid = ob2.ea_guid
step two: search for that in t_xref.Description:

Code: [Select]

select * from t_xref where t_xref.Description in
    (select 'RefGUID=' + ob1ob.ea_guid  + ';RefName=' + ob1ob.name + ';'as something
        from t_object ob2
        inner join (select ob1.ea_guid, ob1.name from t_object ob1 where object_type = 'Signal') ob1ob
        on ob1ob.ea_guid = ob2.ea_guid
    )
throws the error:
DAO.Database [3342] invalid memo or OLE object in subquery ('t_xref.Description')

checking with fixed values - works fine:

Code: [Select]

select * from t_xref where t_xref.Description in
    ('RefGUID={77EB79E7-DA34-4532-B64B-8B0F26A82545};RefName=signaltrigger_to_stm3St3;',
    'RefGUID={6BE433ED-92D8-4ff4-8CBF-BE947A2B5627};RefName=signaltrigger_to_stm3St2;',
    'RefGUID={6BE433ED-92D8-4ff4-8CBF-BE947A2B5627};RefName=signaltrigger_to_stm3St2;')
Did I miss some general SQL knowledge here or is it EA specific and what can I do to solve it?

Thanks,
Shegit

[Shegit Brahm]

I saw that my join pattern was different in outer query, so I changed to:

Code: [Select]

select * from t_xref xr
inner join
(select 'RefGUID=' + ob1ob.ea_guid  + ';RefName=' + ob1ob.name + ';' as abc.something
from t_object ob2
inner join (select ob1.ea_guid, ob1.name from t_object ob1 where object_type = 'Signal') ob1ob
on ob1ob.ea_guid = ob2.ea_guid
) abc
on abc.something = xr.Descriptionresulting in new error code:
DAO.Database [3141]
the select statement includes a reserved word or argument name which has been entered wrong, wrong character set or is missing at all.

[Geert Bellekens]

as abc.something is invalid

An alias cannot contain a "."

Geert

[Shegit Brahm]

oh, yes, of course, thank you very much.

Well, now the error is changed to
DAO.Database [3118]
combination using a memo or ole object not possible
(xr.Description='RefGUID='+ob1ob.ea_guid +';RefName='+ob1ob.name+';')

even when I "wrap it" and put a (select abc.something) edf around to achieve a "not so visible concatenation".

Sounds like the string concat is not loved, while a given single string is accepted.

[Shegit Brahm]

Solved. (update: for postgres dbms only, see below)

this link https://blog.lieberlieber.com/2014/08/26/enterprise-architect-sql-issue-on-selecting-note-fields/
brought me to the attempt to try it in a dbms EA project - works like a charm.

So it is a eap file only issue [update:] depending on the database - see below.

[Geert Bellekens]

I think in .eap files you have to concatenate string with the "&" operator instead of the "+" operator

Geert

[Shegit Brahm]

Maybe, just not in my specific case.

As my first step with select 'RefGUID=' + ob1ob.ea_guid  + ';RefName=' + ob1ob.name + ';'as something from works fine within eap file,
and a replacement check with "&" works as well for step one and pops same error on step two - I just don't know if & which additional SearchSQL hack could make it fit within eap file, too.

[Geert Bellekens]

Debugging SQL syntax errors in EA can be challenging. I usually use another SQL client (AnySQL Maestro) to test out the SQL before using it in EA.


Geert

[Shegit Brahm]

thanks for that client option. I will have a look into it.

I have to narrow my "solved" for step two:
- it does not work on eap file
- it does not work on dbms based on mysql because step one returns 0.00 [for "+"]/ 0[for "&"] in the desired amount => step two returns entire t_xref / needs fixes
- it does work on dbms based on postgres

I copy'pasted the package from project browser via gui feature from ea project to ea project.
I think that is unproblematic.

Just the different implementation of memo fields seem the key factor here.

[Shegit Brahm]

Update as I think I'm done:

I rebuild my query to avoid subqueries and take (implicit) inner joins instead.
That way it fits regardless the database used - unless database specific features are used.

The EA translate most things, so #WC# works in EA, not in HeidiSQL or AnySQL Maestro.
And + als string concatenation works in EA, not in HeidiSQL or AnySQL Maestro. (where mysql needs concat(), postgresql supports || too)

so

Code: [Select]

select * from t_xref xr
inner join
(select 'RefGUID=' + ob1ob.ea_guid  + ';RefName=' + ob1ob.name + ';' as something
from t_object ob2
inner join (select ob1.ea_guid, ob1.name from t_object ob1 where object_type = 'Signal') ob1ob
on ob1ob.ea_guid = ob2.ea_guid
) abc
on abc.something = xr.Description
turned to

Code: [Select]

select
xr.client, 'RefGUID=' + ob1s.ea_guid  + ';RefName=' + ob1s.name + ';' as something
from t_object obs1, t_xref xr
where 'RefGUID=' + ob1s.ea_guid  + ';RefName=' + ob1s.name + ';' = xr.Description
and obs2.object_type = 'Signal'

As you may notice: the deepest join was obsolete.
In the end I also used this line:

Code: [Select]

where xr.Description like ('RefGUID=' + ob1s.ea_guid + '#WC#')

why?

Because I renamed a signal after connecting it to a trigger and it keeps in t_xref safed with it's old name ... so I never found it.
In dialogues and gui the naming is correct.