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
- Create the query by unique name
- Add select attribute on POM query by defining attribute and corresponding POM class
- Build the query with all specified expression\condition against query identification.
- Bind the expression through logical value .
- Execute the query and get the results.
- 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.
