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

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.

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

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 ExecutionThe 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.

8 comments:

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.

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.