model search (SQL) and project constants

[Happy]

Hi,

is there a way to access the project constants' values within a SQL model search?

Thx,
Happy

[Happy]

As documentation of those features is lacking extremely (my point of view), I started today to "browse" through the eap tables with visual studio database access.

Finally, I found the place where the Project Constants, alias RTF_PROJECT_FIELDS, alias ProjectOpts are stored.

t_rtf (Type-field = "ProjectOpts", Template-field contains string with "Name=Value"-pairs, separated by semicolons).

Actually I don't think that these values are accessible within a EA-search.

As Export of those fields creates a simple structured xml-like file, I thought the data would be stored structured as in the xml file.

Is there a limit of tables inside the eap?

[Geert Bellekens]

Happy,

Is there a reason you you think you can't access these fields from the EA search?
AFAIK you can return anything from the database using an EA SQL search.

Geert

[Happy]

Hi Geert,

well may it because I'm not very used to SQL queries. Especielly when confronted with EAs SQL-Search interface.

Actually I think retrieving the string contained in the t_rtf Template field which belongs to the Type field ProjectOpts, should be possible, the following statement should do the work.

Code: [Select]

SELECT rtf.Template
FROM t_rtf rtf
WHERE rtf.Type = 'ProjectOpts'
But then, I don't know how to easily break up the String into the project constants.

Maybe breaking the work up into a cascade of SQL-searches and scripts?

[Geert Bellekens]

A series of "substring" type functions should work there to split up the complete string into the relevant portions.
Depends a bit on the type of database you're working on, these type of functions are usually vendor specific.

Geert

[Happy]

Actually I'm using the database model which is builtin to EA. We never changed it. Most of our modelling data is stored in xml and separately version controlled.

Our eap only contains the EA-specific hull as scripts, templates, project constants, the links to the root model xmls aso.

You say a series of substring type functions? Are there any accessible within a model search of EA?

[Geert Bellekens]

eap file == MS Access database.
That is actually where EA stores the data, the xmi is only used for version control, not for storage of data.
So in your SQL searches you'll need to use the Access syntax and functions to split up the key=value;key2=value2 string.
If you find that easier you can open the eap file in Access, write (and test) your query there, and copy it to EA later.

Geert

[Happy]

When you say Access syntax and function to split up the string I have two questions :

1. Is it possible to do this with EAs Model Search Builder?

2. Where can I find more info about available Access syntax for EA?

Cheers
Happy

[Geert Bellekens]

1. I don't know what you mean by Model Search Builder, but if you choose SQL search you can type any SQL you want, including Access string functions.

2. microsoft.com or Access help. EA will basically send the SQL string to the database without touching it. (except for replacing <Search Term> by whatever was typed in the search box.

Geert

[JocaRamiro]

Ah, I have a follow up question here.

I have pulled the model content into Access, and created several queries.  Now I would like to save them within EA so that I don't have to keep dumping the model into Access as it changes.

Can you tell me how to name and store a query within the EA environment?

Thanks in advance,

Mead Walker

[Geert Bellekens]

Hmm, first of all, what you do mean by "dumping the model into access"? Isn't it already in Access? (unless you are using another DBMS, but then why the hell would you want to use Access iso a "real" database :-/)

So save queries in EA you go to "Edit/Model Search" (Ctrl-Alt-A)
Select "Options/Manage Searches" and clickt he "new" icon.
That will bring up a dialog where you can choose SQL search as the type of search and give the thing a name.
Once there, copy your query and save it.

To make the searches usable in EA you need to return the ea_guid and object_type. On the community site there are some examples of SQL searches.

Geert

[Happy]

To make the searches usable in EA you need to return the ea_guid and object_type.

Hi Geert,

Can you describe the requirements what to return? Where can I find more information regarding this point?

Happy

[Geert Bellekens]

The topic "Create Search Definitions" in the help file, or the examples I cited in the previous response should help you.

Geert

[Happy]

Hi Geerts,

thanks a lot. Unfortunately the help only gives examples but, doesn't give detailed informations about the underlying technique.

Do you know, for example, what to select for packages from t_package?

Cheers,
Happy

[Geert Bellekens]

Try

Code: [Select]

select p.ea_guid as CLASSGUID,'Package' as CLASSTYPE,p.name as Name
from t_package p
where p.name like '<Search Term>'
Geert