Fun with Jython and FDMEE – Getting ResultSet Column Names

When working with the fdmAPI call the documentation indicates that return is a ResultSet.

But what is in the ResultSet?

Sometimes the documentation indicates the table that ResultSet is based on.

Well you can open your favorite SQL Tool and query the FDMEE repository tables if you know what to look for.

In the case of the getLocationDetails call; it queries from the TPOVPARTITION table.

 

But can you get that information without a SQL Tool?  How can you do it within Jython? 

After a bit of searching the Python/Jython coding sites I found a method on the ResultSet that provided the information I needed.  The method ResultSet.getMetaData() provides the structure to get information needed.  The .getMetaData() coupled with metadataCollection.getColumnCount() and metadataCollection.getColumnName(##).

So, after a bit of trial and error, I came up with the following.

This produces a list.

 

So having these column names and if you look back on my first post on Fun with FDMEE and Jython where I used the method ResultSet.getString("ColumnName"), I can get the value of any field of the ResultSet without using SQL Tool and I can get creative with my Custom Scripts.

Until Next time...Happy Coding!


Update 6/3/2016:
------------------------
I just got to reading the 11.1.2.4.200 FDMEE Admin Guide published April 2016 and noticed the nice little block of code at the end of the description for getLocationDetails(BigDecimal pPartitionKey) within the Using the JAVA API section. 

 

 

Fun with Jython and FDMEE

The past few months I have had the opportunity to assist in FDM to FDMEE migrations and during that time I have been able to explore Jython scripting.
 
I have loved programming since I was in high school and wrote my first program on an Apple 2.  Yeah, a very long time ago.  No matter where my career path has taken me I seem to find a way back into some form of programming.
 
As part of the migration efforts I needed to convert the FDM VBScript to Jython.  While some of the Event Scripts could have been kept in VBScript it was cleaner to convert to Jython for 2 reasons...VBScript is not strategic for Oracle with FDMEE and I like a puzzle.
 
One of the migrations I had was from FDM Classic with the Essbase Source Adapter to FDMEE 11.1.2.4.  This particular client had stored email server and email addresses in the integration settings to be used to send an email upon failure during any part of the data loading done by FDMEE.
 

 
The one thing I did not want to do was to embed this information in my each one of my Event Scripts - AftImport, AftValidate, AftExportToDat, AftLoad, AftCheck...
 

 

 
So where could I store this information and yet make it accessible to easily modify once.  Why not the Integration Options for a Location?
  

 
If I look at the TPOVPARTION table which stores location information is the 4 Integration Option values.

A simple SQL statement

So how do I get these easily without having to write SQL in my Jython?

Enter the FDMEE API and particularly the 

 

     getLocationDetails(BigDecimal pPartitionKey)

 

 

So what does this look like in Jython in my Eclipse editor?

 

And the Output...

 

So putting it into my FDMEE Event Scripts...

Now the neat thing about this approach is the if there are different people responsible for different locations they could get individual emails when there is an issue with their location by switching out the hardcoded fdmLocID to the fdmContext["LOCKEY"].

Well that was a fun diversion from BI and since I am having fun with Jython and FDMEE I may find some other neat tidbits to share in the future.