Another error using SQL

[Ian Mitchell]

Trying to use the the SQL:
" Select   T_OBJECT.name, Object_Type, Stereotype, COUNT(*) from T_OBJECT  "
 group by T_OBJECT.name, Object_Type, Stereotype "
HAVING COUNT(*) > 1"
(which finds duplicate elements in a model)
-

• Works fine in the model search with EAP file
• Works fine called via Repository.SQLQuery to EAP file
• Works fine in a model search with mySQL
• Doesn't work with Repository.SQLQuery in mySQL

..and the error is the one so often mentioned in this forum:
Error: Code = 0x0 Source Line: 0; Char : 0, Error Description = (null).

This is an especially nasty error because (a) it's very unhelpful and (b) keeps repeating until the use crashes EA.
Other forum items suggest changing the SQL a bit, but this really is what I need the query to look like. About the only change possible is to change the select(*) to a select(Name), but that doesn't work either.
Does anyone have an ideas either (a) how to get around this or (b) how to get more helpful error message from EA ?

[Ian Mitchell]

Update: Just tried using SQLServer, and that also works OK in a Search, but throws the same error when called via the Repository interface.

[Geert Bellekens]

Ian,

What does dbError.txt contain?
Often that is more helpful then the error message in EA.

Geert

[Helmut Ortmann]

Hi Ian,

have you looked into dberror.txt in %APPDATA%...

Next I would try to slightly change the SQL. I'm not sure if EA pre processes the SQL before launching it. Try e.g:
from t_object o  and all usages with o. (lower case)

I would also try to enter the query with another query tool (sqlServer Management platform is pretty good and free)

Ok, I admit a lot of try and error.

Kind regards,

Helmut

[Ian Mitchell]

Honestly, I had no idea that DBError.txt existed. 12 wasted years using EA.
But anyway, it just contains an error from earlier today, just a simple SQL syntax error - nothing to do with this, so no help there.
So I'll try Helmut's suggestions- thanks.

[qwerty]

I just copy/pasted the above query and it worked with no issue (EAP, rather recent V12).

q.

[Geert Bellekens]

The problem is the unnamed column "count(*)".

If you add AS someName it will work.

This script fragment works without a problem.

Code: [Select]

option explicit

!INC Local Scripts.EAConstants-VBScript


sub main
dim SQLGet
SQLGet = " Select o.name, o.Object_Type, o.Stereotype, COUNT(*) AS count_name from t_object o  " & _
" where o.name is not null " & _
" group by o.name, o.Object_Type, o.Stereotype " & _
" HAVING COUNT(*) > 1"
dim result
result = Repository.SQLQuery(SQLGet)
Session.Output result
end sub
Geert

[Ian Mitchell]

Pure genius - it works now!
Thanks Geert

[Eve]

Error: Code = 0x0 Source Line: 0; Char : 0, Error Description = (null).

Interpretation:
The file position of zero zero hints towards this error coming from reading an empty/missing file/string/buffer.

This error structure in EA corresponds to an XML error.

As for differences you saw:
Repository.SQLQuery serializes the response to XML for consumption by the automation client, while a SQL search uses the results directly.
Every different database/ODBC client behaves slightly differently in what it returns. Apparently MySQL returns a column name (or lack of) that prevents the results being serialized to XML.

[Ian Mitchell]

Ok - Geert's suggestion fixed that last one - of course - but it has uncovered others.
I've done some more research on this, and I think it may be to do with the size of the results set.
Queries which return large result sets (a 16Mb (string size) definitely throws the error) seem to throw this error - smaller ones don't.
Is there a restriction ?