GetElementSet query failure

[msdb]

Hi

I'm using GetElementSet to query some data in Sparx EA which generates the following error, seemingly when the amount of data becomes too large:

"DAO.QueryDef[3219] Invalid operation"

However, it works fine with smaller amounts of data.

How do I query my data without getting this error?

[Helmut Ortmann]

Hi,

have a look in EA sql error in %APPDATA%SPARXSYSTEM\EA\dberror.txt.

There you can find the last sql error reported from your DB. Often it's more meaningful then the message box.

I mostly use the EA API to execute sql.

In the open Navigator from Geert you find code and very helpful helper.

Regards,

Helmut

[Geert Bellekens]

I've never had that problem before (and I use GetElementSet quite often).

How exactly are you using it, and how many elements do you think makes it fail?
In any way, there's always an alternative. You can use Repository.SQLQuery() to get the ID's and then loop those and use Repository.GetElementByID on each of the ID's to get a hold of the EA.Element.

Geert

[msdb]

The DBError.txt doesn't provide much insight, unfortunately.

I'm writing a program that populates an .eap-file with elements via the API. The purpose is to add data from a seperate database that does not exist in the eap yet. The .eap-file I'm using already has almost 9000 elements in t_object, which is quickly exceeded upon first execution. If I launch the program again it causes EA to return the error when it reaches the query.

My query looks like this:

Code: [Select]

App eaApp = (App)System.Runtime.InteropServices.Marshal.GetActiveObject("EA.App");
Repository sparx_repository = eaApp.Repository;
sparx_repository.OpenFile("C:/Sparx/MDR legemodel (EA-model klon).eap");
string sql_query = "select * from t_object where Stereotype = 'ArchiMate_BusinessActor' AND (Alias <> 'NULL')";
Collection eap_data = sparx_repository.GetElementSet(sql_query, 2);;


[Geert Bellekens]

have you tried with "select Object_ID from t_object where..."

It might be the * that is causing problem because it fetches way too much data. You only need the Object_ID's for the GetElementSet

Geert

[msdb]

I still get the same error even when only selecting Object_ID.

[Geert Bellekens]

In that case you'll have to choice but to work with Repository.SQLQuery() I guess

Geert

PS. It looks like your query is not entirely correct either. Should it not be?

Code: [Select]

select Object_ID from t_object where Stereotype = 'ArchiMate_BusinessActor' AND Alias is not NULL

[msdb]

SQLQuery works like a charm. 

Many thanks to both of you.

[kjourdan]

I ran into the same issue today using GetElementSet. The query found more than 25000 elements and was generating the same error (3 times).  This error was only being generated when run on an eap file; running the query on the DBMS repository did not generate an error.

[qwerty]

It's likely some shortcoming of Mickeysoft's Access. Remember that's it's not designed for large scales. And obviously 25k rows are already large scale. Also remember: nobody will ever need more than 640k of main memory xD (to be honest: http://www.computerworld.com/article/2534312/operating-systems/the--640k--quote-won-t-go-away----but-did-gates-really-say-it-.html)

q.

[Paolo F Cantoni]

It's likely some shortcoming of Mickeysoft's Access. Remember that's it's not designed for large scales. And obviously 25k rows are already large scale. Also remember: nobody will ever need more than 640k of main memory xD (to be honest: http://www.computerworld.com/article/2534312/operating-systems/the--640k--quote-won-t-go-away----but-did-gates-really-say-it-.html)

q.

These problems are usually caused by slight differences in the SQL syntax for SQL Server vs MS Access.  It generally has NOTHING to do with the size of the database and the size of the datasets.

Over the years of using Access to query BOTH .EAP files and SQL Server, I can create queries that work in both (without change) once you get used to the "quirks" of the two slightly different syntaxes - and how dual use queries need to be phrased.

We often use the same queries on Project transfers of the SQL Server databases (to .EAP).  The size of the .EAP files exceeds 0.5Gb (after compaction).  Since the data is identical, it's not a siz erelated issue.

Paolo

[Geert Bellekens]

AND (Alias <> 'NULL')"

Should be
AND Alias is not NULL

The string value 'NULL' is actually never stored in the database. It just indicates that the field is not filled in.

Geert

[Paolo F Cantoni]

AND (Alias <> 'NULL')"

Should be
AND Alias is not NULL

The string value 'NULL' is actually never stored in the database. It just indicates that the field is not filled in.

Geert

And (to paraphrase Glassboy, I think)  heaven preserve us from NULLS!  I HATE NULLS!

Paolo