Generic Database Query

[Tehila1]

I create a search query to find particular packages.  
I want the query to consider Oracle databases in addition to simple Access (.eap) repositories.

However, the #wc# used as a generic wild-card character, does not work for me.
  
When I execute the following query I get the expected packages.

Code: [Select]

SELECT t_package.Package_ID FROM t_package
WHERE t_package.PackageFlags LIKE '*VCCFG=*CheckedOutTo=*'  
  
But this generic query gives no results at all:  

Code: [Select]

SELECT t_package.Package_ID FROM t_package
WHERE t_package.PackageFlags LIKE '#WC#VCCFG=#WC#CheckedOutTo=#WC#'  
  
Any ideas?

[Helmut Ortmann]

Hi,

that should work with all supported database.

But keep in mind: You have to define a Search and not just change the tab from Search Builder to SQL. The SQL have to be in the "Search tab".

The EA standard Search 'My Checked Out Packages' shows it.

The easieast way is to copy an SQL Search like the above and change the SQL string.

Helmut

[Tehila1]

Hi,

that should work with all supported database.

But keep in mind: You have to define a Search and not just change the tab from Search Builder to SQL. The SQL have to be in the "Search tab".

The EA standard Search 'My Checked Out Packages' shows it.

The easieast way is to copy an SQL Search like the above and change the SQL string.

Helmut

Why is it necessary to define a search?
I execute these queries programmatically, does it matter?

[Helmut Ortmann]

Hi,

ask SPARX why it is this way.

Macros like #WC# only works in EA Searches. Quick search via SQL tab or via API only works with poor SQL for the database you work against.

If you choose API you may change the SQL string dependent the current repository you are working with.

A good introduction to SQL-Query for EA you find in Thomas Kilians eBook InsideEA.  

Helmut

[qwerty]

You can also run such a user defined query from within the API. Still off-site, so you need to check the help yourself. It's either in Repository or in ProjectInterface.

You'd need to raise a feature request if you want to run such a #-query on the fly from automation.

Figuring out the #WC# is not too difficult after all. Just check the ConnectionString and have a table according to the found RDBMS.

q.

[Tehila1]

Hi,

ask SPARX why it is this way.

Macros like #WC# only works in EA Searches. Quick search via SQL tab or via API only works with poor SQL for the database you work against.

If you choose API you may change the SQL string dependent the current repository you are working with.

A good introduction to SQL-Query for EA you find in Thomas Kilians eBook InsideEA.  

Helmut

So there is no way to execute a generic query via add-in?

[qwerty]

See above :-)

q.

[Aaron B]

Figuring out the #WC# is not too difficult after all. Just check the ConnectionString and have a table according to the found RDBMS.

Can also use Repository.RepositoryType() to more easily get the type of database you are currently connected to.  According to the release notes this was introduced in EA 10.

[qwerty]

It looks like I should once again go through the API to find out what it new (to me).

q.

[Tehila1]

Figuring out the #WC# is not too difficult after all. Just check the ConnectionString and have a table according to the found RDBMS.

Can also use Repository.RepositoryType() to more easily get the type of database you are currently connected to.  According to the release notes this was introduced in EA 10.

Thanks.
I thought a local EAP file is Access based, but using  Repository.RepositoryType() returns JET.

Is it incorrect?
How can it help me to figure if it is an Access or Oracle DBMS?

[Helmut Ortmann]

Hi,

as far as I remember is the connection string good for non JET databases. The problems are *.eap shortcuts.

As far as I remember you don't have this problem with repository.RepositoryType().

Helmut

[Aaron B]

JET == Access
See: http://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine

If RepositoryType() returns "JET", it's a pretty safe bet you are working with an EAP file.

[Stefan Bolleininger]

Hi Helmut,

you are right. If you are using shortcuts, the connection string won't help.

Best regards

Stefan

[qwerty]

Hi,

as far as I remember is the connection string good for non JET databases. The problems are *.eap shortcuts.

As far as I remember you don't have this problem with repository.RepositoryType().

Helmut

You are right. Now it comes to my mind that before introducing the RepositoryType one needed to look at the file size if it was an EAP and if smaller than 1M assuming it was a shortcut. Then you could read the contents and find the real connection string.

q.

[Helmut Ortmann]

Hi,

that was the way I did until I got aware of repository.RepositoryType().

Helmut