Cancel
Showing results for 
Search instead for 
Did you mean: 

Decrypt mssql teamcenter data using queries

Builder
Builder

How can we decrypt TC data using mssql queries? For example, item id's having unique id in the database. If we run sql queries, it returns the encrypted value.

SELECT * FROM [PLMDB].[dbo].[PVALUE]

puid            pseq       pval_0

wkBAAAgUYL5QYB    1    Downwards
gQOAAAgYYL5QYB    0    Metric
gUHAAAgYYL5QYB    1    No
gYIAAAgYYL5QYB    1    No
gQOAAAgYYL5QYB    1    Non-Metric
wkFAAAgUYL5QYB    0    Plug
wkFAAAgUYL5QYB    1    Socket
wkBAAAgUYL5QYB    0    Upwards
gUHAAAgYYL5QYB    0    Yes
gYIAAAgYYL5QYB    0    Yes


Need to decrypt puid's.

Any suggestions..

5 REPLIES

Re: Decrypt mssql teamcenter data using queries

Solution Partner Phenom Solution Partner Phenom
Solution Partner Phenom
The PUID is a unique object identifier. It is not the Item ID. The Item ID is stored in table dbo.PITEMID and attribute PITEM_ID. You join data from differing tables using the PUID to ensure that you're talking about the same object.
I've never tried to pull it from the PVALUE table but you might want to try something like:
select i.PITEM_ID, v.PSEQ, v.PVAL_0 from dbo.PITEMID i, dbo.PVALUE v where i.PUID = v.PUID;
Note: the above SQL probably won't work and I have not tested it. Direct SQL statements are not supported and actively discouraged.

Randy Ellsworth, Teamcenter Architect, Applied CAx, LLC
NX 11.0.1.mp01 | SW 2016 | TcUA 11.2.3
Evaluating:AW 3.2

Re: Decrypt mssql teamcenter data using queries

Builder
Builder

Thank you. But if it supports direct sql queries,it would be good.

Re: Decrypt mssql teamcenter data using queries

Genius
Genius

Hi

Below is sample for you to query the Teamcenter database through an SQL query and get all the items and revision id.

Please note that it's not supported and can create severe problems to make direct changes to the database entries using SQL queries.

 

Use it only for getting information that can't be found through the tools provided by Siemens.

 

As example I use a query to find item revisions that have double dataset or have one dataset type but missing another.

that gives me a list and then I adjust thrhough the Rich Client

 

select 
      i.pitem_id+'/'+ir.pitem_revision_id "ItemRevision",i.pitem_id "Item ID",ir.pitem_revision_id "Revision ID"
from
      PITEM i,
      PITEMREVISION ir
where
      i.puid=ir.ritems_tagu

Re: Decrypt mssql teamcenter data using queries

Builder
Builder

@jormah,

 

You are right. Thanks. Let me try..

Re: Decrypt mssql teamcenter data using queries

Experimenter
Experimenter

I have the below query and would like to add the OWNER field to it. I added DRAWN but that is not always who made the drawing.

 

SELECT
ITEM.PITEM_ID,
ITEM_REV.PITEM_REVISION_ID,
SPXFORM.PMATERIAL,
SPXFORM.PDRAWN	
FROM SRC.TCPROD12_INFODBA_PITEM ITEM
INNER JOIN SRC.TCPROD12_INFODBA_PITEMREVISION ITEM_REV
  ON ITEM.PUID = ITEM_REV.RITEMS_TAGU
INNER JOIN SRC.TCPROD12_INFODBA_PIMANRELATION RELATION
  ON ITEM_REV.PUID = RELATION.RPRIMARY_OBJECTU  AND RELATION.RRELATION_TYPEU = 'gMx8K_gpFZNDTA'
INNER JOIN SRC.TCPROD12_INFODBA_PFORM FORM
  ON RELATION.RSECONDARY_OBJECTU = FORM.PUID
INNER JOIN SRC.TCPROD12_INFODBA_PSPX6 SPXFORM
  ON FORM.RDATA_FILEU = SPXFORM.PUID
WHERE --ITEM.PITEM_ID = '00080510-001' --INSERT PART NUMBER HERE
--AND ITEM_REV.PITEM_REVISION_ID = 'A'; --INSERT REVISION HERE

-Will