cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

How can I programatically determine the database column(s) associated with a custom standard attribute?

RandyJones
19-Tanzanite

How can I programatically determine the database column(s) associated with a custom standard attribute?

Windchill 11.0 F000-CPS04

I am wanting to programatically find the database column(s) associated with a custom standard attribute. This would be the columns created by using the AddColumns utility and then are used when creating a custom standard attribute. The Type and Attribute Manager shows it as shown in the screenshot. How does it find it?

database_columns.png

So starting with a given subtype and a given standard parameter internal name how would I find this same information that the Type and Attribute Manager is showing?

3 REPLIES 3

Randy,

I saw your post and thought I'd reply.

The column value you are looking for is LWCColumnAllocation.PHYSICALNAME

Note that the value of this column is stored as typeInfoWTPart.ptc_str_1

LWCColumnAllocation is a table that links the type to the attribute.

  • LWCColumnAllocation.IDA3A3 = LWCFlexAttDefinition.IDA2A2
  • LWCColumnAllocation.IDA3B3 = <object type's IDA2A2 (LWCTypeDefinition.IDA2A2)>

Below are both SQL and Java code to return PHYSICALNAME

SQL

select ca.physicalname from lwccolumnallocation ca, lwctypedefinition tdef, lwcflexattdefinition attdef

where ca.ida3a3 = attdef.ida2a2

and ca.ida3b3 = tdef.ida2a2

and tdef.ida2a2 = 144505

and attdef.name = 'CustomString1';

where lwcflexattdefinition.name is the attribute internal name

Java

In this code I used QuerySpecs to find the data.

   public static String getStandardAttributesDataBaseColumnName(LWCTypeDefinition type, String attInternalName) throws WTException {

        QuerySpec qs = new QuerySpec(LWCFlexAttDefinition.class);

        qs.appendWhere(new SearchCondition(LWCFlexAttDefinition.class, LWCFlexAttDefinition.CONTEXT_REFERENCE + ".key.id", SearchCondition.EQUAL, PersistenceHelper.getObjectIdentifier(type).getId()), null);

        qs.appendAnd();

        qs.appendWhere(new SearchCondition(LWCFlexAttDefinition.class, LWCFlexAttDefinition.NAME, SearchCondition.EQUAL, attInternalName, true), null);

        QueryResult qr = PersistenceHelper.manager.find((StatementSpec) qs);

        if (qr.size() == 1) {

            LWCFlexAttDefinition flexAttDef = (LWCFlexAttDefinition) qr.nextElement();

            qs = new QuerySpec(LWCColumnAllocation.class);

            qs.appendWhere(new SearchCondition(LWCColumnAllocation.class, LWCColumnAllocation.TYPE_DEF_REFERENCE + ".key.id", SearchCondition.EQUAL, PersistenceHelper.getObjectIdentifier(type).getId()), null);

            qs.appendAnd();

            qs.appendWhere(new SearchCondition(LWCColumnAllocation.class, LWCColumnAllocation.ATTRIBUTE_DEF_REFERENCE + ".key.id", SearchCondition.EQUAL, PersistenceHelper.getObjectIdentifier(flexAttDef).getId()), null);

            qr = PersistenceHelper.manager.find((StatementSpec) qs);

            if (qr.size() == 1) {

                LWCColumnAllocation colAll = (LWCColumnAllocation) qr.nextElement();

                String physicalName = colAll.getPhysicalName();

                if (physicalName != null && physicalName.contains(".")) {

                    int index = physicalName.indexOf(".");

                    String columnNameDisplayed = (physicalName.substring(index + 1) + physicalName.substring(0, index));

                    System.out.println("Results:\n");

                    System.out.println("Type:" + type.getName());

                    System.out.println("Internal Name:" + attInternalName);

                    System.out.println("Datatype: " + flexAttDef.getDatatype().getName());

                    System.out.println("Physical Name:" + physicalName);

                    System.out.println("Column Name Displayed:" + columnNameDisplayed);

                    System.out.println("Column Name:" + columnNameDisplayed.toUpperCase());

                    System.out.println();

                    return columnNameDisplayed;

                }

            }

        }

        return null;

    }

Hope this help,  David


RandyJones
19-Tanzanite
(To:d_graham)

David: Perfect!!! That is exactly what I was looking for. I have created a java class using your code as a basis and it works good. As soon as I get it cleaned up a little I will post my class here.

Thanks Randy

RandyJones wrote:

David: Perfect!!! That is exactly what I was looking for. I have created a java class using your code as a basis and it works good. As soon as I get it cleaned up a little I will post my class here.

Thanks Randy

I have attached my java class.

Announcements