Repository.SQLQuery(string) returns an empty string

[MartinHe]

Hello together,

I have the problem that the method Repository.SQLQuery(string) returns an empty string instead of a string with XML content. This happens sporadically with Enterprise Architect 13.5.1354. The EAP file (JET 3) is quite large, about 160 MB after compact. I also tried to repair the database and to update the indices. Replication is used. The SQL query contains an inner SQL query, the rest seems to be standard for me.

I could not figure out when this happens. It happens sporadically on some computers. If it happens, executing the same query three times in a row produces always the empty string as result. But once the file is reopened, the problem is usually gone and a xml string is returned?

Can anybody please explain me when this happens and if a workaround exist?

Thank you very much!

Best regards!

[qwerty]

Well, you had the solution: close and reopen EA. That's the usual Windoze way. Sorry for not having a smart explanation.

I have it that querying EA from an (external) script without EA being open will make me have restart Windoze since from that point on I only get a dummy object instead of an EA object. Makes me not even sigh anymore.

q.

[MartinHe]

Yes, restarting Enterprise Architect helps. But it is unsatisfying and nothing I can expect from end users of the add-in I'm developing. I just figured out that it also happens with a really simple query:

SELECT PropertyID, ea_guid, Object_ID, Property, Value, Notes FROM t_objectproperties

The database was freshly compacted and repaired. Executing the query via Repository.SQLQuery(string) results in an empty string. But when I executed the query via the UI of Enterprise Architect, the records were shown, 99292 in total. I had to restart EA in order to get the results via Repository.SQLQuery(string).

I really would like to understand the problem in order to adjust my add-in to avoid it. Is the JET 3 database corrupt? Would JET 4 avoid the problem? But why does Enterprise Architect show results in the UI? Is there a defect in Enterprise Architect? But why does this happen only sporadically. But so often that end users got annoyed and asked me to fix it. Would upgrading to a newer Version of Enterprise Architect help?

PS: I gave up working with EA objects for performance reasons.

[qwerty]

Jet4 is definitely to be preferred as it contains lots of improvements. You might also consider to switch to Firebase. I think anything is better than Mickeysoft Access.

Since you are on V13.5 there's not much point in sending a bug report. Even if you get through to development to confirm a bug (which is not that easy) you will not get any fix for 13.5. And even for V15 (or 16) only God knows when there will come any fix.

And yes, working with the API objects is not very performant. As long as only a handful are involved it's okay. But once you have to deal with hundreds there's probably no way around direct database operations.

q.

[Geert Bellekens]

Yes, restarting Enterprise Architect helps. But it is unsatisfying and nothing I can expect from end users of the add-in I'm developing. I just figured out that it also happens with a really simple query:

SELECT PropertyID, ea_guid, Object_ID, Property, Value, Notes FROM t_objectproperties

The database was freshly compacted and repaired. Executing the query via Repository.SQLQuery(string) results in an empty string. But when I executed the query via the UI of Enterprise Architect, the records were shown, 99292 in total. I had to restart EA in order to get the results via Repository.SQLQuery(string).

I really would like to understand the problem in order to adjust my add-in to avoid it. Is the JET 3 database corrupt? Would JET 4 avoid the problem? But why does Enterprise Architect show results in the UI? Is there a defect in Enterprise Architect? But why does this happen only sporadically. But so often that end users got annoyed and asked me to fix it. Would upgrading to a newer Version of Enterprise Architect help?

PS: I gave up working with EA objects for performance reasons.

Why would you want to get all element tagged values? Feels like that is simply too much, and you run into some kind of timeout/data overload.
Maybe you should rethink your code and get only the tagged values you need (use the WHERE clause)

PS.

You don't have to give up working with EA objects, but you have to do it smart.
Try to avoid iterating EA collections and avoid instantiating objects you don't need.
If you have to choice but to iterate a collection (such as the tagged values) make sure you iterate the collection only once. If you need to loop over the same collection more than once make sure to store the objects in a plain collection such as a .Net List or ArrayList and loop that collection.

Use Repository.SQLQuery to find the id's of the things you need, and then use the Repository.Get<something>ByID to directly get the objects you need.

Geert

[MartinHe]

Yes, that is a really good advice. And we do that already.

We did not query all tagged values in the beginning, because for the task at hand not all tagged values are needed. But almost. So we filtered the tagged values in a WHERE clause first. The problem is that at some point the SQL query becomes to complex to be executed by JET. So multiple SQL queries were needed. The performance was simply too bad. Even with one SQL query, it is much faster to filter the results in memory instead of asking the JET database to do it for you.

I have the impression, that the size of the database causes the problem. In the past it was half the size and everything worked well. How big are your JET databases? For me 150 MB after a compact feels impressive for Enterprise Architect models.

Yes, you cannot give up working with EA objects completely. Sometimes you need them when changes should be recognized by Enterprise Architect to update the user interface efficiently. But sadly, Repository.GetElementByID(int) does not scale. The response time depends on the number of children of the requested element.

[Geert Bellekens]

Martin,

That is not my experience.
In general limiting the results with a where clause is lighting fast.
But it really depends on how you write your SQL query. There are really slow ways to write queries.
Just recently I reworked a slow query (on SQL Server) and it went from 30 minutes to 5 seconds.

I don't really work a lot .eap files as all serious clients use SQL server, but the times when I tested on .eap files it really felt about the same.
The .eap file I worked on where 200 to 400 MB.

I'm not sure what your use case is, but I've written a bunch of add-ins and thousands of scripts, and in almost all of them I use the EA Objects, and GetElementByID
There are a few exceptions where I didn't initialize EA objects, and those were cases where I only needed their ID.

Geert