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

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.