Saturday, May 18, 2013

Teamcenter POM Query


POM Query is one of important ITK  module in teamcenter from perspective on Data extraction and performance. Teamcenter POM query is API layer provided to query database through API rather then  direct query in database, as Teamcenter doesn't officially expose the underline database design. Developer often prefer to use POM Query rather then going for sets of ITK api to get the desired object from Teamcenter because of performance factor as well using one set of call for getting desired object. Once you understand POM Query mechanism it is very easy to implement complex query cases through it rather then going through lengthy set of ITK API calls . In this blog I will give basis of POM query. With this basic understanding you can build complex query through it. I am assuming the reader will have basic understanding of Teamcenter Data Model. If not please refer my previous blog on Teamcenter Data Model

Introduction 
POM query is noting but sql query which wrapped around ITK program for extraction of data from teamcenter database. I will explain  POM query through simple SQL example which we will convert to POM query. Let assume we want to extract some item based on specific item id and item type . If we want to do it through SQL query, the sql statement look like this
Select puid from item where itemid = “1234”  and object_type = “Item”;
So there are three main constituent of any sql query.
  •   Select (attributes)
  • From (table)
  • Where (condition)
    • And /OR

SQL statement  is a function, constituting above three aspect. If you want to covert the above statement in to POM query, all the above aspect formS the building block for POM query.
Following are basic characteristic of POM Query.

  • POM Query has unique identification.
  • POM query will have select attribute from  POM Classes
  • POM Query has expression which specified where condition
  • All expressions are binding through POM query API with logical clauses
  • POM query required to be executed to get the results
Steps for building POM Query

  1. Create the query by unique name
  2. Add select attribute on POM query by defining attribute and corresponding POM class
  3. Build the  query with all specified expression\condition against query identification.
  4. Bind the expression through logical value .
  5. Execute the query and get the results.
  6. Delete Query
Let see how the sample sql statement can be converted to POM query

Create Query
Unique identification for query
POM_enquiry_create (“get_itemid”)
Teamcenter identify  query through unique string name in a given session. Hence it is good practice to clear the query after it is used.

Select attributes
const char * select_attr_list[] = {"puid"};
POM_enquiry_add_select_attrs(“get_itemid”, “Item”,1,” select_attr_list)
The above api set the select attribute agaist POM Class (It item in this case). You can have multiple attribute defined for select attributes in array and specified that in api. We defined 1 as we have only select attribute in our case.
Build Expression
const char * itemid[] = {"1234"};
POM_enquiry_set_attr_expr(“get_itemid”, "ExprId1", "Item", "item_id", POM_enquiry_equal, “valueId1”)
POM_enquiry_set_string_value (“get_itemid”, "valueId1", 1, itemid, POM_enquiry_bind_value )

The above set condition expression of the query. This is equal to item_id= ‘1234’. The expression is identified  by unique string  identification  which in this case is ExprId1. The value required to be binding through unique identified because of different data type binding. The value identifier valueId1 is then binding by value through proper API call based on attribute type to which it is binding. In our case binding is with string attribute, hence we call set_string_value api. If you have any other data type for attribute  then you have to  call appropriate API. Following data type are supported for POM Query.

Int  : POM_enquiry_set_int_value
Double : POM_enquiry_set_double_value
Char : POM_enquiry_set_string_value
String : POM_enquiry_set_string_value
Logical POM_enquiry_set_logical_value
Date : POM_enquiry_set_date_value
Tag : POM_enquiry_set_tag_value

This expression is binded by query by providing query identification which ‘get_itemid’ in our case. Similar expression will be for other condition of object type
 const char * itemtype[] = {"Item"};
POM_enquiry_set_attr_expr(“get_itemid”, "ExprId2", "Item", "object_type", POM_enquiry_equal, “valueId2”)
POM_enquiry_set_string_value (“get_itemid”, "valueId2", 1, itemtype, POM_enquiry_bind_value )

Expression Binding
Now the two expression should be combined for where clauses. The logical binding between expression is done through api call
POM_enquiry_set_expr(“get_itemid”, "ExprId3", "ExprId1", POM_enquiry_and, "ExprId2")
The above api will bind ExprId1 and ExprId2 with and clause. This is equal to
itemid = “1234”  and object_type = “Item”;
To identify the binding a new expression id is created. This expression id can be used now to develop complex binding if there are more then two condition clauses.
Expression can be binded by and, or and not condition. This is similar to sql condition binding.
Once the expression binding is completed, then we required to put as where clause in expression. This is done by calling API
POM_enquiry_set_where_expr(“get_itemid”, "ExprId3")
This set the where clause against expression ExprId3 which in binding expression for ExpId1 and ExpId2.

Query Execution
The above steps completes POM query which is now equivalent to SQL query. Now query required to be executed. Which is done by calling API
POM_enquiry_execute(“get_itemid”, &rows,&cols,&results)
Where row, col and report are output. 
rows : number of results.
cols : Number of column for each result
results : result of query in two dimension array. This is array of void pointer
The above binding can be better understand by below diagram.


Once query is executed and results are stored in array, they required to extracted and type cast for specific type based on select attributes provided for POM Query. For example is above case we extracted puid which is nothing but object tag. So we required to convert our output to tag pointer. Below psedo code shows how to extract and store it in tag array.
if(rows > 0 )
      {
            int reportrow = 0 ;
            tag_t *objs = Null Tag
            (objs) = (tag_t *)MEM_alloc( (objs), ( rows) *sizeof(tag_t)));
            for ( int i= 0; i< rows; i++)
            {
                  (objs)[i] = (*(tag_t *)( results [i][0]));
                 
            }
           
      }
Once results are stored after type cast then this object can be used as a any tag object id in teamcenter.

Delete Query
After executing the query and storing the result in appropriate object type we required to delete the query. Remember the each query is unique and identified through its string name. If we don’t delete the query, then query will remain in given state in a session and again if it hit same code it will trough a error as query with the given name is already registered in a session.

POM_enquiry_delete ( “get_itemid” )

That’s all for introduction POM query. Once you understand basic of POM query, you can implement various complex query by joining two tables and having multiple expression hierarchy. Most of the SQL statement can be converted to POM query. I suggest for complex query better to first visualize in term of SQL statement and then design POM query.

7 comments:

  1. Manoj , First of all appreciations for a well written article .

    I have been working in POM enquiries since last couple of years. However, there are a certain aspects of Pom Enquiries which have often troubled me :
    1. Need for a query which supports outer joins.
    2. Ordering of clauses - ie how TC arranges the brackets to group the clauses.

    May be you can throw some light on these.

    ReplyDelete
  2. The following is a sample that gets Item Ids that were exported to a particular site written as a POM enquiry routine. This was written in 1999 and I haven't looked at it since 2005.

    Posted in 3 parts due to the character count restriction :(


    Hopefully this will serve as a less than trivial example that folks will find useful.

    ACMECALL is a Macro that checks the result of the ITK call

    ReplyDelete
  3. ===== part 2 ======

    define ACME_ITEMS_SENT_ENQUIRY "ACME_getSentItems"

    int ACME_getItemIdsExportedToSite( const int iSite , const date_t tDate , int *piListCount , char ***pppszList )
    {
    int iFail = ITK_ok;
    int i = 0;
    int j = 0;
    void *** pppReport = NULL;
    int iRowCount = 0;
    int iColCount = 0;
    char *pszReturn = NULL;
    const char * ppszSelectItemId[] = { "item_id" };

    tag_t tagSite = NULLTAG;

    ACMECALL( SA_find_site_by_id( iSite , &tagSite ) );

    if( !tagSite )
    return POM_invalid_site_id;

    /* Create the query. */
    ACMECALL( POM_enquiry_create( ACME_ITEMS_SENT_ENQUIRY ) );

    /* set distinct */
    ACMECALL( POM_enquiry_set_distinct( ACME_ITEMS_SENT_ENQUIRY , TRUE ) );

    /* set the order */
    ACMECALL( POM_enquiry_add_order_attr ( ACME_ITEMS_SENT_ENQUIRY,
    "Item",
    "item_id",
    POM_enquiry_asc_order ) );

    /* Set the value for the date before. */
    ACMECALL( POM_enquiry_set_date_value( ACME_ITEMS_SENT_ENQUIRY,
    "modAfterDate",
    1,
    &tDate,
    POM_enquiry_bind_value ) );

    /* Add the select part (select item.pitem_id from pitem item). */
    ACMECALL( POM_enquiry_add_select_attrs( ACME_ITEMS_SENT_ENQUIRY,
    "Item",
    1,
    ppszSelectItemId ) );

    /* get the site tag */
    ACMECALL( POM_enquiry_set_tag_value( ACME_ITEMS_SENT_ENQUIRY ,
    "siteTag" ,
    1 ,
    &tagSite ,
    POM_enquiry_bind_value ) );

    /* Set the expression ( ixr.ritxr_target_siteu='wM6koGo3QqWloC' ) */
    ACMECALL( POM_enquiry_set_attr_expr( ACME_ITEMS_SENT_ENQUIRY,
    "siteMatch",
    "ImanExportRecord",
    "ixr_target_site",
    POM_enquiry_equal,
    "siteTag" ) );

    /* Set the expression (pao.plast_mod_date >= 'date') */
    ACMECALL( POM_enquiry_set_attr_expr( ACME_ITEMS_SENT_ENQUIRY,
    "modifiedDate",
    "POMApplicationObject",
    "last_mod_date",
    POM_enquiry_greater_than_or_eq,
    "modAfterDate" ) );

    ReplyDelete
  4. ===== part 3 ======

    /* Set the join expression (item.puid=ixr.ritxr_exported_objectu) */
    ACMECALL( POM_enquiry_set_join_expr( ACME_ITEMS_SENT_ENQUIRY,
    "joinExpression1",
    "ImanExportRecord",
    "ixr_exported_object",
    POM_enquiry_equal,
    "Item",
    "puid" ) );

    /* Set the second join expression (item.puid = pao.puid) */
    ACMECALL( POM_enquiry_set_join_expr( ACME_ITEMS_SENT_ENQUIRY,
    "joinExpression2",
    "POM_application_object",
    "puid",
    POM_enquiry_equal,
    "Item",
    "puid" ) );

    /* Set the third join expression (pao.plast_mod_date >= ixr.pixr_last_export_date) */
    ACMECALL( POM_enquiry_set_join_expr( ACME_ITEMS_SENT_ENQUIRY,
    "joinExpression3",
    "POM_application_object",
    "last_mod_date",
    POM_enquiry_greater_than_or_eq,
    "ImanExportRecord",
    "ixr_last_export_date" ) );


    /* Combine the three join expressions */
    /* (item.puid = pao.puid) AND (item.puid=ixr.ritxr_exported_objectu) */
    ACMECALL( POM_enquiry_set_expr( ACME_ITEMS_SENT_ENQUIRY,
    "combinedJoins1",
    "joinExpression1",
    POM_enquiry_and,
    "joinExpression2" ) );

    /* (item.puid = pao.puid) AND (item.puid=ixr.ritxr_exported_objectu) */
    ACMECALL( POM_enquiry_set_expr( ACME_ITEMS_SENT_ENQUIRY,
    "combinedJoins2",
    "combinedJoins1",
    POM_enquiry_and,
    "joinExpression3" ) );


    /* add the expression (ixr.rixr_target_siteu='wM6koGo3QqWloC') */
    ACMECALL( POM_enquiry_set_expr( ACME_ITEMS_SENT_ENQUIRY,
    "combinedExpr1",
    "siteMatch",
    POM_enquiry_and,
    "combinedJoins2" ) );

    ACMECALL( POM_enquiry_set_expr( ACME_ITEMS_SENT_ENQUIRY,
    "combinedExpr2",
    "combinedExpr1",
    POM_enquiry_and,
    "modifiedDate" ) );

    /* Add the where clause: WHERE combinedExpr2 */
    ACMECALL( POM_enquiry_set_where_expr( ACME_ITEMS_SENT_ENQUIRY, "combinedExpr2" ) );


    /* Execute. */
    ACMECALL( POM_enquiry_execute( ACME_ITEMS_SENT_ENQUIRY , &iRowCount , &iColCount , &pppReport ) );

    /* We should return one row for every instance. */
    for(i=0; i < iRowCount; i++ )
    {
    /* There should always be exactly one column, but we'll check to make sure. */
    if( iColCount > 0 )
    {
    /* Add the tag to the list. */
    pszReturn = (char *) pppReport[i][j];
    ACMECALL( ACME_addStringToList( pszReturn , TRUE , piListCount , pppszList ) );
    }
    }

    /* Delete. */
    ACMECALL( POM_enquiry_delete( ACME_ITEMS_SENT_ENQUIRY ) );

    /* Tidy up. */

    if(pppReport)
    MEM_free( pppReport );

    return ITK_ok;
    }

    ReplyDelete
  5. Hi Manoj!

    My name is Nikolya!
    I'm sorry to bother you, I wanted to ask you a question.
    I'm trying to extract using POM data from Teamcentr.
    I want to extract its parts, so as not to overload the memory Teamcentr

    Example:

    Select Item.itemID from Item order by Item.itemID Limit 10,5

    Result: 5 lines of the sorted data from the 10 line, inclusive.

    Question: I can not find POM_enquiry_limit - does it exist? - if not, than it can be replaced by?

    Thank you so much for the information!

    ReplyDelete
  6. Hi Manoj,
    How to extract BOM children(all levels) using POM queries??
    Thanks in advance..

    ReplyDelete
  7. Hi Manoj,
    I am a TCE user for an auto company. I have a issue and a possible solution. But I do not know if it is fesable.
    My issue is revising/updating/round triping/updating revision master propeties of assembly. I do not have issue if there are less parts but many times I need to do this boring take for a number of assemblies.
    I was wondering if it is possible to write a program and execute this. I will give the inputs like which items to be revised and with child item revisions needs to be pulled in the assembly and other details. The program should deliver me the round tripped assemblies. If programming this possible, please let me know how to go about it; which programming to use and brief details. Thanks in advance.

    ReplyDelete