cancel
Showing results for 
Search instead for 
Did you mean: 

Executing SQL in a Script - how to reference an Extension table?

Former Member
0 Kudos

Hello all,

I have been successful in executing a SQL statement within a script - but I hardcoded an Extension table's name as part of my testing just to get it working. Now, I'd like to grab the table name dynamically, in case it is different between environments. Any suggestions on how to do so?

Example of what I have currently:


dbHandle = session.getDbHandle();
dbHandle.beginTransaction();
mySQL = "SELECT T1.DISPLAY_NAME, " +
               "T2.EXTRA_DATA_FIELD " +
               "FROM " +
               "FCI_SERV_LINEITEM T1, " +
               "FCI_DYN_$2147483145 T2 " +
               "WHERE " +
               "T1.OBJECTID = T2.PARENT_OBJECT_ID"
dbHandle.executeQuery(mySQL);

What class & methods can I use to grab "FCI_DYN_$2147483145" dynamically to insert it into my SQL string?

Thanks,

Matt

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Matt -

There is not a simple way to do this, but here are a couple of ideas:

1. You could actually write another sql select statement to get the table name from the database. The extension table names are stored in: FCI_SYS_DYN_CLASSES (T2) which joins to the extension definition table: FCI_DOC_EXT_DEFN (T1) using something like: T1.EXT_OBJECT_CLASSID = T2.ASSOC_CLASSID AND T1.BUS_UNIT_CTXT_OBJECT_ID = T2.ASSOC_CONTEXT_OBJECT_ID.

While technically possible, I am not sure I would go with this solution, however.

2. Another approach requires a few more steps, but I like it a bit more: you could store the table name as a system property and "lock" that property in each system. That way, you can have different values in each of your systems. The first step is to create the "system property metadata" - this is done via a CSV import file with contents like:

APP_ID,PROP_ID,PROP_TYPE,DATA_TYPE,EDIT_MODE,DESCRIPTION

custom,extension.XXX.tablename,-1,-1,READ_WRITE,Name of the XXX extension defintion table

This property metadata then makes it possible for you to set the value of your custom system property via the System Properties area in setup (Setup > System Setup > Configuration > System Properties). You will create a "new" property, select the property id just created, and set the value to the extension table name.

The third component is to use this system property in your script. That is done easily in the IAPIs using:

tableName = IapiSystemUtilities.getSystemProperty(session, "custom", "extension.XXX.tablename", "not_found");

It might be best to have an error generated if the returned value equals "not_found" - then you know the property was not set correctly.

Good luck...let us know what you decide.

Rob

Former Member
0 Kudos

Hi Rob,

This was a useful tip.

I could however not find information on what values are allowed for PROP_TYPE and DATA_TYPE.

Could you help with that?

Thanks & Regards,

Subhasini

Answers (2)

Answers (2)

Former Member
0 Kudos

Thanks Rob - that makes sense. I'm going to attempt your 2nd suggestion and will let you know if I have any follow-up questions.

Former Member
0 Kudos

Hi Matt,

In regular reports, we use a token to obtain the extension fields.

Maybe you could try to use the same in the script-run SQL.

Here is the sample from the reporting guide:

<%EXT_TABLE(projects.projects)%> T3 OR

<%EXT_TABLE(projects.projects, CollectionName)%> T3

Regards,

Reshma