Query inside a script does not return the same result as in SQL scratch pad

[ja1234]

Hi everyone,

I am using an example from Gert (thanks, Gert, http://sparxsystems.com/forums/smf/index.php/topic,24996.0.html)slighly modified, to obtain all elements that have a certain tagged value.

When I run the query in the Find in Project using SQL Scratch pat, it returns the result, but when I run within the script, a DAO error  (Item not found in this collection  ), any ideas why?

tagged value name = objectid
tagged value value = any string

Code: [Select]


var sql = "select c.ea_guid as CLASSGUID,c.object_type as CLASSTYPE,c.name as Name, c.stereotype as Stereotype ,package.name as PackageName ,package_p1.name as PackageLevel1,package_p2.name as PackageLevel2,package_p3.name as PackageLevel3"+
" FROM  (((((t_object c  " +
" INNER JOIN t_objectproperties op on op.Object_ID = c.Object_ID)  " +
" INNER JOIN t_package as package on c.package_id = package.package_id)  " +
" LEFT JOIN t_package as package_p1 on package_p1.package_id = package.parent_id)  " +
" LEFT JOIN t_package as package_p2 on package_p2.package_id = package_p1.parent_id)  " +
" LEFT JOIN t_package as package_p3 on package_p3.package_id = package_p2.parent_id)  " +       
" WHERE op.Property ='objectid' AND op.Value like '"+ flag_objectid +"'";

var els as EA.Collection;
els = GetElementSet(sql, 2);


After I inspect the SQL query & copy paste it into the SQL Scratch pad, it works correctly but not in the JScript 

[Eve]

Because GetElementSet is looking for a column called Object_ID, which is not in your query.

[ja1234]

Because GetElementSet is looking for a column called Object_ID, which is not in your query.

Adding

Code: [Select]

var sql = "select c.Object_ID "+
in the query, solved the problem.

Thank you!

[kjourdan]

I have a slightly different issue but the subject seems appropriate.

For the following query in the SQL scratchpad, EA indicates "Search returned 94002 results"
select * from t_object where Object_Type <> 'Package'

Using this same query with GetElementSet returns 28466 elements.

   var sql = "select * from t_object where Object_Type <> 'Package'";
   var collection as EA.Collection;
   
   collection = Repository.GetElementSet(sql, 2);

Why the difference and which is right?

[Paolo F Cantoni]

I have a slightly different issue but the subject seems appropriate.

For the following query in the SQL scratchpad, EA indicates "Search returned 94002 results"
select * from t_object where Object_Type <> 'Package'

Using this same query with GetElementSet returns 28466 elements.

   var sql = "select * from t_object where Object_Type <> 'Package'";
   var collection as EA.Collection;
   
   collection = Repository.GetElementSet(sql, 2);

Why the difference and which is right?

Well, not every row in t_object is an element.  That could explain the difference.  We come across this discrepancy all the time.  Title Blocks, Hyperlinks, Notes, texts etc. etc.  Basically, anything that doesn't appear int he browser directly is not an element in EA terms.

I'd suggest that GetElementSet is returning only true elements.

Paolo

[kjourdan]

Hi Paolo,

I did not think about this.  Unfortunately, it appears the results returned by the SQL are not complete.  I created a model in an eap file and transfered it to a DBMS repository.  Running Repository.GetElementSet("select * from t_object where Object_Type <> 'Package'", 2) from the EAP file (JET type) generates 3 DAO.QueryDef[3219] errors and the collection only has 28,466 elements. Running the same operation on the DBMS repository (SQLSVR type), generates no errors but also only 28,466 elements. Breaking the query into 120 smaller queries and summing the results indicates that there were 93,736 elements (non packages) in the model. This is close to 94,002 results that I got from the SQL scratchpad.

Why only 28,466 elements were returned by the SQL and why no errors when the query was run on the DBMS repository? This is a bit concerning.

[Eve]

Are you sure that your smaller queries aren't returning duplicate results?

Try combining them using union (but you'll need to omit notes etc) I would just use Object_ID, Name, ea_guid.

[kjourdan]

Each smaller query was done on a specific object type (eg. class, interface, etc) and a specific stereotype.  For each element found, I streamed the results to the console, exported to Excel and sorted on Element ID as well as UUID and found no duplicates.

[Paolo F Cantoni]

Hi Paolo,

I did not think about this.  Unfortunately, it appears the results returned by the SQL are not complete.  I created a model in an eap file and transfered it to a DBMS repository.  Running Repository.GetElementSet("select * from t_object where Object_Type <> 'Package'", 2) from the EAP file (JET type) generates 3 DAO.QueryDef[3219] errors and the collection only has 28,466 elements. Running the same operation on the DBMS repository (SQLSVR type), generates no errors but also only 28,466 elements. Breaking the query into 120 smaller queries and summing the results indicates that there were 93,736 elements (non packages) in the model. This is close to 94,002 results that I got from the SQL scratchpad.

Why only 28,466 elements were returned by the SQL and why no errors when the query was run on the DBMS repository? This is a bit concerning.

The difference in the errors is probably explained by the fact that the Schemas are NOT equivalent in the two forms.  If you check on a table by table basis, you find quite interesting variations.

Our production schemas have been adjusted (both EAP and SQL Server) to ensure that all fields are treated as consistently as possible.  For example, EXCEPT where EA forces us to do otherwise (presumably because of historical inconsistency) ALL our Boolean columns are defined as X(1) allowed values 0,1⁽¹⁾;  All GUID fields are X(38) etc.  Makes it easier to have compatible SQL between the two forms.

HTH,
Paolo

⁽¹⁾When we did this initially (almost a decade ago) we found one table where Booleans were treated in 3 different ways in 5 different columns!

[kjourdan]

The issue is caused by the size limitation on a collection and with Collection.Count being a Short.  94002 is what is returned by the SQL scratchpad; truncation of 94002 (0x16F32) to a short yields 28466 (0x6F32).  In my case, my query is greater than 32767 so the count is no longer valid (either reported as negative value or a bogus positive value).