cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

We are happy to announce the new Windchill Customization board! Learn more.

QueryResult doesn't return any rows. QuerySpec has a Column Access Control Item Builder

NTailor
4-Participant

QueryResult doesn't return any rows. QuerySpec has a Column Access Control Item Builder

 

 So I have the following code which is a query that should return me a result of WTDocuments with an identical iba value to an iba value in part.

 

But I’m not getting any results thought the sql query that the statementspec calls returns a result. Can you help me understand what I am doing wrong and also what is a columnaccesscontrolitembuilder and how do I remove it.

 

SQL Query from StatementSpec : (yellow I think is the culprit but not sure what that is)

 

SELECT A0.*,wt.pds.ColumnAccessControlItemBuilder@7e17cd6

FROM wt.doc.WTDocument A0,wt.iba.value.StringValue A1,wt.iba.definition.StringDefinition A2,com.ptc.core.lwc.server.LWCIBAAttDefinition A3

WHERE (A3.idA3A7 = A2.idA2A2) AND (A1.idA3A6 = A2.idA2A2) AND (A1.idA3A4 = A0.idA2A2) AND (A3.name = 'ICS_NUMBER') AND (A1.value IN '1234567') AND (A0.branchIditerationInfo IN (SELECT MAX(A0.branchIditerationInfo) FROM WTDocument A0 WHERE ((A0.idA2A2 = A0.idA2A2))))

 

SQL query :

 

SELECT A0.*

FROM WTDocument A0,StringValue A1,StringDefinition A2,LWCIBAAttDefinition A3

WHERE (A3.idA3A7 = A2.idA2A2) AND (A1.idA3A6 = A2.idA2A2) AND (A1.idA3A4 = A0.idA2A2) AND (A3.name = 'ICS_NUMBER') AND (A1.value IN '1234567') AND (A0.branchIditerationInfo IN

(SELECT MAX(A0.branchIditerationInfo) FROM WTDocument A0 WHERE ((A0.idA2A2 = A0.idA2A2))))

 

 

CODE :

 

 

package ext.itt.audit.configaudit;

 

import java.io.IOException;

import java.util.ArrayList;

import java.util.Iterator;

import java.util.List;

 

import com.ptc.core.lwc.server.LWCIBAAttDefinition;

import com.ptc.core.lwc.server.LWCNormalizedObject;

 

import ext.itt.commonutil.CommonUtils;

import ext.itt.manager.ITTExceptionHandler;

import wt.audit.configaudit.AuditEventRecorder;

import wt.doc.WTDocument;

import wt.epm.EPMDocument;

import wt.events.KeyedEvent;

import wt.fc.ObjectReference;

import wt.fc.Persistable;

import wt.fc.PersistenceHelper;

import wt.fc.QueryResult;

import wt.fc.WTObject;

import wt.fc.collections.WTArrayList;

import wt.fc.collections.WTCollection;

import wt.iba.definition.StringDefinition;

import wt.iba.value.StringValue;

import wt.part.WTPart;

import wt.pds.StatementSpec;

import wt.query.ClassAttribute;

import wt.query.QueryException;

import wt.query.QuerySpec;

import wt.query.SearchCondition;

import wt.query.SubSelectExpression;

import wt.util.WTException;

import wt.util.WTPropertyVetoException;

 

public class SecurityLabelPropagationAuditEventListener implements AuditEventRecorder {

      

       @Override

       public void recordMultiObjectEvent(KeyedEvent keyedevent, WTCollection wtcollection) throws WTException {

             

              System.out.println("KeyedEvent: " + keyedevent.getEventType() + " | " + keyedevent.getEventKey());

             

              System.out.println("WTCollection.size() : " + wtcollection.size());

             

             

              Iterator wtcollection_iterator = wtcollection.iterator();

              WTArrayList arrList = new WTArrayList();

              while (wtcollection_iterator.hasNext()) {

                     Persistable p = (Persistable) ((ObjectReference) wtcollection_iterator.next()).getObject();

                     System.out.println("Persistable Info : " + p.getPersistInfo().getObjectIdentifier());

                     if (p instanceof WTPart) {

                           // eligible for pdf stamping

                           arrList.add((WTPart) p);

                     } else {

                           continue;

                     }

              }

             

              if (!arrList.isEmpty()) {

                     try {

                           propogateSecurityLabels(arrList);

                     } catch (WTPropertyVetoException e) {

                           // TODO Auto-generated catch block

                           e.printStackTrace();

                     } catch (IOException e) {

                           // TODO Auto-generated catch block

                           e.printStackTrace();

                     } catch (ITTExceptionHandler e) {

                           // TODO Auto-generated catch block

                           e.printStackTrace();

                     }

              } else {

                     System.out.println("No persistable found from where can propogate security labels");

              }

             

       }

 

       @Override

       public void recordEvent(KeyedEvent keyedevent) throws WTException {

              System.out.println("KeyedEvent: " + keyedevent.getEventType() + " | " + keyedevent.getEventKey());

       }

      

    public void propogateSecurityLabels(WTArrayList docList) throws WTException, WTPropertyVetoException, IOException, ITTExceptionHandler {

        //logger.debug("Entering ********propogateSecurityLabels********");

        System.out.println("Entering propogateSecurityLabels");

        

        CommonUtils cu = new CommonUtils();

        if (docList.size()!=0) {

           Iterator iterator = docList.iterator();

           

           while(iterator.hasNext()) {

              Persistable perst;

              Object object = iterator.next();

              if (object instanceof ObjectReference) {

                 ObjectReference obj_ref = (ObjectReference)object;

                 perst = obj_ref.getObject();

                 

                 WTPart part = (WTPart) perst;

                 LWCNormalizedObject obj = new LWCNormalizedObject(perst, null, java.util.Locale.US, newcom.ptc.core.meta.common.DisplayOperationIdentifier());

 

                 /* Get value of IBAName soft attribute */

                 obj.load("ICS_NUMBER");

                 String ibaValue = (java.lang.String) obj.get("ICS_NUMBER");

                 //String ibaValue = cu.getIbaValue("ICS_NUMBER", part);

                 System.out.println("ibaValue : " + ibaValue);

                 

                 // get all WTDocuments with an iba of ics and the value that wtpart has

                 List<WTObject> docs = fetchAllDocumentsWithIBAValue(WTDocument.class, "ICS_NUMBER", ibaValue, false );

                 

                 System.out.println("doc size: " + docs.size());

                 // update their security labels

                 for (WTObject doc : docs) {

                    new CommonUtils().setSecurityLabels((WTDocument) doc, cu.getSecurityLabels(part) );

                 }

                 

                 // get all WTDocuments with an iba of ics and the value that wtpart has

                 List<WTObject> epmDocs = fetchAllDocumentsWithIBAValue(EPMDocument.class, "ICS_NUMBER", ibaValue, false);

                 

                 System.out.println("epmdoc size: " + epmDocs.size());

                 // update their security labels

                 for (WTObject doc : docs) {

                    new CommonUtils().setSecurityLabels((EPMDocument) doc, cu.getSecurityLabels(part) );

                 }

                 

              }

           }

        }

        System.out.println("Exiting propogateSecurityLabels");

        //logger.debug("Leaving propogateSecurityLabels");

     }

    

    

    /*

     * Query Database

     * Find all documents with an ICS Number that is linked to WTPart

     * SQL Query :

     * Select doc.ida2a2, sv.value, lwc.name

              FROM WTDocument doc, StringValue sv, StringDefinition sd, LWCIBAAttDefinition lwc 

              WHERE

              doc.latestiterationinfo=1

              and

              lwc.ida3a7 = sd.ida2a2

              and

              sv.ida3a6 = sd.ida2a2

              and

              sv.ida3a4 = doc.ida2a2

              and

              lwc.name='ICS_NUMBER'

              and

              sv.value='000466990'

              and

              doc.branchiditerationinfo in

              (select MAX(doc2.branchiditerationinfo) from wtdocument doc2 where doc.ida2a2 = doc2.ida2a2)

     */

    

    public static List<WTObject> fetchAllDocumentsWithIBAValue(Class documentClass, String ibaName, StringvalueToSearchFor, boolean grabLatest) throws WTException {

       QuerySpec qs = new QuerySpec(documentClass);

       int idxDocument = 0;

       int idxStringValue = qs.addClassList(StringValue.class, false);

       int idxStringDefinition = qs.addClassList(StringDefinition.class, false);

       int idxLWCIBAAttDefinition = qs.addClassList(LWCIBAAttDefinition.class, false);

      

       if (grabLatest) {

              qs.appendWhere(new SearchCondition(documentClass, "iterationInfo.latest", SearchCondition.IS_TRUE), newint[] {idxDocument});

      

              qs.appendAnd();

       }

      

       qs.appendWhere(new SearchCondition(LWCIBAAttDefinition.class, "ibaReference.key.id", StringDefinition.class,"thePersistInfo.theObjectIdentifier.id"), new int[] {idxLWCIBAAttDefinition, idxStringDefinition});

      

       qs.appendAnd();

      

       qs.appendWhere(new SearchCondition(StringValue.class, "definitionReference.key.id"  , StringDefinition.class, "thePersistInfo.theObjectIdentifier.id" ), new int[] {idxStringValue, idxStringDefinition});

      

       qs.appendAnd();

      

       qs.appendWhere(new SearchCondition(StringValue.class, "theIBAHolderReference.key.id", documentClass,"thePersistInfo.theObjectIdentifier.id"), new int[] {idxStringValue, idxDocument});

      

       qs.appendAnd();

      

       qs.appendWhere(new SearchCondition(LWCIBAAttDefinition.class, "name", SearchCondition.EQUAL, ibaName), new int[] {idxLWCIBAAttDefinition});

      

       qs.appendAnd();

      

       qs.appendWhere(new SearchCondition(StringValue.class, "value", SearchCondition.IN, valueToSearchFor), new int[] {idxStringValue});

      

       qs.appendAnd();

      

       qs.appendWhere(new SearchCondition(new ClassAttribute(documentClass, "iterationInfo.branchId"), SearchCondition.IN, 

                    new SubSelectExpression(getMAXBranchID(documentClass, idxDocument))), new int[] {idxDocument});

      

       qs.setAdvancedQueryEnabled(true);

       QueryResult qr = PersistenceHelper.manager.find((StatementSpec) qs);

       System.out.println("qs:" + qs.toString());

       List<WTObject> documents = new ArrayList<WTObject>();

       while (qr.hasMoreElements()) {

              Object nextElement = qr.nextElement();

             

              if (nextElement instanceof WTObject) {

                    documents.add((WTObject) nextElement);

              }

             

       }

      

       return documents;

      

    }

    

    

    // (select MAX(doc2.branchiditerationinfo) from wtdocument doc2 where doc.ida2a2 = doc2.ida2a2)

    public static QuerySpec getMAXBranchID(Class documentClass, int indexForSelectDocClass) throws QueryException {

       QuerySpec qs2 = new QuerySpec();

       int idxDoc2 = qs2.appendClassList(documentClass, false);

      

       qs2.appendSelect(wt.query.SQLFunction.newSQLFunction(wt.query.SQLFunction.MAXIMUM, newClassAttribute(documentClass, "iterationInfo.branchId" )), new int[] {idxDoc2}, false);

      

       qs2.appendWhere(new SearchCondition(documentClass, "thePersistInfo.theObjectIdentifier.id", documentClass,"thePersistInfo.theObjectIdentifier.id"), new int[] {idxDoc2, indexForSelectDocClass});

      

       return qs2;

      

    }

 

}

 

2 REPLIES 2
NTailor
4-Participant
(To:NTailor)

So apprently you want to use 

QueryResult qr = PersistenceServerHelper.manager.query((StatementSpec) qs);

 

to remove the access restrictions and having a columnaccesscontrolitembuilder show up

dsolat1
6-Contributor
(To:NTailor)

How can we write a query if we need to check if iba1 = specificValue also if iba2 = specificValue.

Top Tags