on 03-05-2010 12:07 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.