SQL search throws a warning by use it from the plu

[Tobias Funk]

SQL search throws a warning by use it from the plugin:

I have generate a SQL search which give me back all requirements and their sub requirements (recursivly) from the selected package:

SELECT t_object.ea_guid AS CLASSGUID, t_object.Object_ID , t_object.Name
FROM  t_object
INNER JOIN t_package ON t_object.Package_ID = t_package.Package_ID
WHERE t_package.Name='<Search Term>'
AND t_object.Object_Type='Requirement'

when i run this sql in the EA it works fine!

When i call it from a plugin like this:
EA.Collection coll = this.repository.GetElementsByQuery("MySqlSearchName", package.Name.ToString());

the EA throws a Warning:

DAO.QueryDef[3219]
Invalid Operation.

Why is this so? I think my SQL is correct and the call from the plugin is fine... i don't know the answer, can anybody help me?

Greez Tobi
                

[Tobias Funk]

It doesn't matter me this Warning it only doesn't looks good by the user. I can't catch this Warning in a try/catch block

perhaps can it be that too much requirements will be givn back by the search (3929 requirements are the results)

[Geert Bellekens]

Does DBError.txt contain anything?

Geert

[Tobias Funk]

yes this is the content but i think its only the resulktobjects

07.12.2010 13:00:05
DAO.QueryDef [3219]

Invalid operation.

Context:
      SQL: select t_xref.* from t_xref, t_object where t_object.Object_ID in(-2121869252,-2114493212,-2088557936,-2066293754,-2043865412,-2023380112,-1922371490,-1915031898,-1822163560,-1815219018,-1689088138,-1619918968,-1616901042,-1588462406,-1575394174,-1569507924,-1555132476,-1505667326,-1501669196,-1454858660,-1444888686,-1219125284,-1209568890,-1101119822,-1046121410,-1031843862,-980434648,-904768926,-871092394,-859477482,-802793182,-748390042,-741661612,-725565668,-700280352,-611576066,-583167618,-568870162,-523482220,-520399364,-497932242,-460312320,-422071608,-295483182,-249662324,-244804564,-203183054,-1253263,-1253262,-1253261,-1253259,-1253258,-1253257,-1253255,-1253254,-1253253,-1253252,-1253251,-1253250,-1253249,-1253248,-1253247,-1253246,-1253245,-1253243,-1253242,-1253241,-1253240,-1251647,-1251643,-1251641,-1251628,-1251627,-1251626,-1251625,-1251621,-1251620,-1251617,-1251616,-1251614,-1251613,-1251607,-1251605,-1251604,-1251603,-1251601,-1251600,-1251599,-1251598,-1251567,-1251559,-1251558,-1251552,-1251551,-1251550,-1251549,-1251547,-1251546,-1251545,-1251544,-1251542,-1251541,-1251540,-1251539,-1251537,-1251536,-1251535,-1251534,-1251533,-1251532,-1251531,-1251530,-1251529,-1251528,-1251527,-1251526,-1251525,-1251524,-1251523,-1251521,-1251520,-1251519,-1251518,-1251517,-1251484,-1251475,-1251474,-1251473,-1251469,-1251464,-1251458,-1251457,-1251451,-1251450,-1251449,-1251448,-1251447,-1251446,-1251445,-1251444,-1251443,-1251442,-1251440,-1251439,-1251438,-1251437,-1251436,-1251429,-1251410,-1251409,-1251408,-1251407,-1251406,-1251405,-1251403,-1251402,-1251401,-1251400,-1251399,-1251398,-1251397,-1251396,-1251395,-1251390,-1251389,-1251386,-1251385,-1251342,-1251329,-1251327,-1251325,-1251308,-1251285,-1251282,-1251264,-1251258,-1251257,-1251256,-1251255,-1251254,-1251248,-1251247,-1251246,-1251245,-1251244,-1251225,-1251224,-1251223,-1251222,-1251199,-1251193,-1251192,-1251182,-1251175,-1251172,-1251149,-1251139,-1251135,-1251128,-1251120,-1251118,-1251117,-1251116,-1251115,-1251113,-1251111,-1251110,-1251106,-1251104,-1251090,-1251089,-1251088,-1251087,-1251086,-1251085,-1251056,-1251051,-1251050,-1251049,-1251048,-1251047,-1251041,-1251040,-1251023,-1251020,-1251004,-1250999,-1250996,-1250995,-1250993,-1250992,-1250991,-1250968,-1250967,-1250965,-1250964,-1250962,-1250961,-1250940,-1250939,-1250938,-1250936,-1250935,-1250929,-1250928,-1250927,-1250926,-1250925,-1250924,-1250923,-1250922,-1250921,-1250920,-1250919,-1250918,-1250917,-1250916,-1250915,-1250914,-1250913,-1250912,-1250911,-1250910,-1250909,-1250908,-1250907,-1250906,-1250905,-1250904,-1250903,-1250902,-1250900,-1250899,-1250898,-1250897,-1250896,-1250895,-1250894,-1250893,-1250892,-1250891,-1250890,-1250889,-1250888,-1250887,-1250886,-1250885,-1250884,-1250883,-1250882,-1250881,-1250880,-1250879,-1250878,-1250877,-1250876,-1250875,-1250874,-1250873,-1250872,-1250871,-1250870,-1250869,-1250868,-1250867,-1250866,-1250865,-1250864,-1250863,-1250862,-1250861,-1250860,-1250859,-1250858,-1250857,-1250856,-1250855,-1250854,-1250853,-1250852,-1250851,-1250850,-1250849,-1250848,-1250847,-1250846,-1250845,-1250844,-1250843,-1250842,-1250841,-1250840,-1250839,-1250838,-1250837,-1250836,-1250835,-1250834,-1250833,-1250832,-1250831,-1250830,-1250829,-1250828,-1250827,-1250826,-1250825,-1250824,-1250823,-1250822,-1250821,-1250820,-1250819,-1250818,-1250817,-1250816,-1250815,-1250814,-1250813,-1250812,-1250811,-1250810,-1250809,-1250808,-1250807,-1250806,-1250805,-1250804,-1250803,-1250802,-1250801,-1250800,-1250799,-1250798,-1250797,-1250796,-1250795,-1250794,-1250793,-1250792,-1250791,-1250790,-1250789,-1250788,-1250787,-1250786,-1250785,-1250784,-1250783,-1250782,-1250781,-1250780,-1250779,-1250778,-1250777,-1250776,-1250775,-1250774,-1250773,-1250772,-1250771,-1250770,-1250769,-1250768,-1250767,-1250766,-1250765,-1250764,-1250763,-1250762,-1250761,-1250760,-1250759,-1250758,-1250757,-1250756,-1250755,-1250754,-1250753,-1250752,-1250751,-1250750,-1250749,-1250748,-1250747,-1250746,-1250745,-1250744,-1250743,-1250742,-1250741,-1250740,-1250739,-1250738,-1250737,-1250736,-1250735,-1250734,-1250733,-1250732,-1250731,-1250730,-1250729,-1250728,-1250727,-1250726,-1250725,-1250724,-1250723,-1250722,-1250721,-1250720,-1250719,-1250718,-1250717,-1250716,-1250715,-1250714,-1250713,-1250712,-1250711,-1250710,-1250709,-1250708,-1250707,-1250706,-1250705,-1250704,-1250703,-1250702,-1250701,-1250700,-1250699,-1250698,-1250697,-1250696,-1250695,-1250694,-1250693,-1250692,-1250691,-1250690,-1250689,-1250688,-1250687,-1250686,-1250685,-1250684,-1250683,-1250682,-1250681,-1250680,-1250679,-1250678,-1250677,-1250676,-1250675,-1250674,-1250673,-1250672,-1250671,-1250670,-1250669,-1250668,-1250667,-1250666,-1250665,-1250664,-1250663,-1250662,-1250661,-1250660,-1250659,-1250658,-1250657,-1250656,-1250655,-1250654,-12

[Tobias Funk]

and many more entries

this is the end of the DBError file

-1159435,-1159567,-1159566,-1159565) and t_object.Object_ID = t_operation.Object_ID and t_operationparams.OperationID = t_operation.OperationID and t_operationparams.ea_guid=t_xref.Client

when i call

string str = this.repository.SQLQuery(
                    "SELECT t_object.ea_guid AS CLASSGUID, t_object.Object_ID , t_object.Name "
                  + "FROM  t_object INNER JOIN t_package ON t_object.Package_ID = t_package.Package_ID "
                  + "WHERE t_package.Name='"+packageA2l.Name.ToString()
                  + "' AND t_object.Object_Type='Requirement'");
                System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
                doc.Load(new StringReader(str));
                System.Xml.XmlElement document = doc.DocumentElement;
                System.Xml.XmlNodeList nodeList = doc.GetElementsByTagName("Object_ID");

no errors occur
but it have to work over the EA Searches

[Geert Bellekens]

It looks like the GetElementsByQuery is doing something very strange, because the context of DBError doesn't seem to be related to the query you used.

I would report it to support, it might be a small bug they can easily fix.

Geert

[Tobias Funk]

i have no support account

i think it makes the wright but have a problem,
the query result is fine and correct, but when the resultlist has very much entries, this warning occurs but the result is ok

[Geert Bellekens]

Tobias,

Even without a support account you can still report a (possible) bug. It may take a little longer for Support to respond, but I'm pretty sure you'll have an answer within a couple of days.

I think I remember having a similar issue when dealing with large resultsets. That will probably be the reason why I abandoned the GetElementsByQuery approach and wrote my own operation based on Repository.SQLQuery.

Geert

[Tobias Funk]

Oh that sounds very good i also thought about writing my own method but with 3929 entries in the xml string the conversion to a e.g. element list takes too much time

Tobi

[Geert Bellekens]

Tobias,

I'm absolutely not sure that your own method will be slower then GetElementsByQuery, don't forget that iterating an EA.Collection is usually much slower then iterating a native collection.
Have you tried the operation GetElementSet? You can pass an SQL string to that one as well is you pass "2" as the second parameter.

Geert

[Tobias Funk]

i thinkl it to make it so but this takes too much time

            List<EA.Element> elemList = new List<Element>();
            // Festlegen Verlauf der ProgressBar
            string str = this.repository.SQLQuery(sqlQuery);
            str=str.Replace("<Object_ID>",",");
            int index = str.IndexOf(',');
            str = str.Substring(index+1, str.Length - (index+1));
            int i = 1;
            while (str.Contains("<") && str.IndexOf(',', str.IndexOf('<')) > 0)
            {
                index = str.IndexOf('<');

                int index1 = str.IndexOf(',',index);
                str = str.Remove(index, index1-index);
               // string[] s1=str.Split(new char[] { ',' }, 2);
               // str = s1[1];
               // elemList.Add(this.repository.GetElementByID(Convert.ToInt32(s1[0])));
            }
            index = str.IndexOf('<');
            str = str.Remove(index);
            string[] s = str.Split(new char[] { ',' });
            //EA.Element subelement = this.repository.GetElementByID(Convert.ToInt32(node.InnerText));
            return this.repository.GetElementSet(str, 1);

and also an error occur

[Geert Bellekens]

Auch, I can imagine it takes a long time when parsing xml manually.
I just checked, and whenever I need a large number of elements I use GetElementSet with an SQLQuery.
For other object types such as operations I use SQLQuery.
Here's a the content of my operation that gets Operations based on a given query

Code: [Select]

       internal List<UMLOperation> getOperationsFromQuery(string sqlQuery)
        {
            //get the nodes with the name "OperationID"
            XmlDocument xmloperationIDs = this.SQLQuery(sqlQuery);
            XmlNodeList operationIDNodes = xmloperationIDs.SelectNodes("//OperationID");
            List<UMLOperation> operations = new List<UMLOperation>();
            foreach (XmlNode operationIDNode in operationIDNodes)
            {
                string operationID = operationIDNode.InnerText;
                UMLOperation operation = this.getOperationByID(operationID);
                operations.Add(operation);
            }
            return operations;
        }
I think you'll find the xml handling a little bit more elegant

Geert

[Geert Bellekens]

I see that you also tried to use GetElementSet, but not directly with the SQL query as parameter.

I think this should work:

Code: [Select]

Repository.GetElementSet(""SELECT t_object.ea_guid AS CLASSGUID, t_object.Object_ID , t_object.Name "
                 + "FROM  t_object INNER JOIN t_package ON t_object.Package_ID = t_package.Package_ID "
                 + "WHERE t_package.Name='"+packageA2l.Name.ToString()
                 + "' AND t_object.Object_Type='Requirement'",2);
Geert

[Tobias Funk]

when i use GetElementSet then the same error occurs like the GetElementByQuery

[Geert Bellekens]

I'm running out of idea's here.
Have you tried running the same code on a repository on a different type of database?

Geert