nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Custom ODA Driver ResultSetMetaData

Paul A

Hi, 
 
I am hoping this is the correct forum for this query. I cannot see any ODA on these forums. 
 
We have created an ODA Driver based on the CSV and Hibernate examples in the Integrating and Extending BIRT Second Edition. Everything is working fine at present. We are using Eclipse SDK Indigo. 
 
However I want to be able to return a string or number in my ResultSet and apply a format if it is a number. For example, I have the following underlying data: 
 
 
Row Column 1 Column 2 
1 100.123 200.232 
2 1000.789 100000.12345 
3 Missing 5.3452 
4 Missing Missing 
 
In the above example, I want to apply the number format of ###,##0.000 to Column 1 and 2. Missing is stored as a string so no format would be applied. The desired output in the report would be: 
 
 
Row Column 1 Column 2 
1 100.123 200.232 
2 1,000.789 100,000.123 
3 Missing 5.345 
4 Missing Missing 
 
 
Scenario - Designing the Report 
 
When I create a new DataSet in the BIRT designer, it retrieves all of my column names correctly. However, I return JAVA_OBJECT from getColumnType. 
 
In the BIRT designer Output Columns dialog for the data set, the column types are all listed as String. When you click on Edit, the available types are: 
 
String, Decimal, Integer, Boolean, Date, Date Time, Float, and Time. 
 
In the report designer I have a Table with the data set columns assigned. You can right-click on the data set column and select Change Data Column... item. This shows the Select Data Binding dialog. In this dialog, you can select the column and click on Edit... button. This shows the Edit Data Binding dialog. In the Data Type dropdown, you can change the type to be one of the following: 
 
Blob, Boolean, Date, Date Time, Decimal, Float, Integer, Java Object, String, and Time 
 
Our system has a concept of Missing values. I am handling this in the ODA Driver Query class which will have a string value of Missing. Valid values will be of type Double. 
 
Issue 
 
My issue is I want to return a string or a double from the column. If it is a number then I want to be able to customise the number format of the column in the designer. ResultSet#getString(int) is always being called to retrieve the value for the report. 
 
I was hoping that I could use the getColumnType to return JAVA_OBJECT would do this. 
 
Any help/pointers greatly appreciated. 
 
Andez

Find more posts tagged with

Comments

JasonW

Andez,

Can you try putting an onCreate script like the following on your data item:
importPackage(Packages.java.text);
importPackage(Packages.java.util);

scanner = new Scanner(this.getValue());
if (scanner.hasNextDouble()) {
d = scanner.nextDouble();
formatter = new DecimalFormat("#0.00");
this.setDisplayValue(formatter.format(d));
}

or put the expression in for the column like:

importPackage(Packages.java.text);
importPackage(Packages.java.util);

scanner = new Scanner(dataSetRow["col"]);
if (scanner.hasNextDouble()) {
d = scanner.nextDouble();
formatter = new DecimalFormat("#0.00");
formatter.format(d);
}else{
dataSetRow["col"];
}

Jason

Paul A

Hi Jason, 
 
Thanks for the reply. I am newish to BIRT so just coming to terms with the scripting. I have had a quick look on <a class='bbc_url' href='http://eclipse.org/birt/phoenix/deploy/reportScripting.php'>Report Scripting</a>. 
 
I am not too sure what you mean when you say data item. Is it DataSet? If so there is noCreate event available in the drop down in the scripts when you select the data set in the Outline view. I have tried adding the script to other events but it comes up with errors in the report - denoted in red text. 
 
I can add the following script ona Table->Detail->Row->Cell->Data(Temperature) onCreate: 
 
importPackage(Packages.java.text); 
importPackage(Packages.java.util); 
 
if( this.getValue().equals( "Missing" ) ) 
{ 
this.getStyle().backgroundColor="red";//This will only change the row instance 
} 
else 
{ 
try 
{ 
formatter = new DecimalFormat("#0.00"); 
d = formatter.parse( this.getValue() ); 
this.setDisplayValue(formatter.format( d ) ); 
} 
catch( ex ) 
{ 
// it has errorred 
this.setDisplayValue( "1.234" ); 
} 
} 
 
 
It's a shame BIRT won't let you define a column as JAVA_OBJECT then call the ResultSet#getObject in your DataSource. For that reason I think we have to change. 
 
The fact that our underlying data uses constants starting from -999999999.999999 to -999999999.998999 which have an equivalent string, for example "Missing", we will change the meta data to return a decimal for now. So say -999999999.998999 which is "Missing" and we return missing in our ResultSet as -999999999.998999, the report designer will have to create some script to deal with this. This unfortunate as we can have lots of columns - say up to 500!!!. 
 
So the big picture would be (columns data on the report would be related in some shape or form): 
 
Date Time,Column 1, Column 2, Column 3,...,Column 20 
1/1/2000 00:00,11,22,33,...,2020 
1/1/2000 00:10,11,22,33,...,-999999999.998999 
... 
1/1/2000 23:50,11,22,33,...,-999999999.998999 
 
 
Cheers, 
 
Andez 
 
 
 
 
 
 
 
<blockquote class='ipsBlockquote' data-author="'JasonW'" data-cid="80403" data-time="1311274523" data-date="21 July 2011 - 11:55 AM">
Andez, 
 
Can you try putting an onCreate script like the following on your data item: 
importPackage(Packages.java.text); 
importPackage(Packages.java.util); 
 
 
scanner = new Scanner(this.getValue()); 
if (scanner.hasNextDouble()) { 
d = scanner.nextDouble(); 
formatter = new DecimalFormat("#0.00"); 
this.setDisplayValue(formatter.format(d)); 
} 
 
or put the expression in for the column like: 
 
importPackage(Packages.java.text); 
importPackage(Packages.java.util); 
 
 
scanner = new Scanner(dataSetRow["col"]); 
if (scanner.hasNextDouble()) { 
d = scanner.nextDouble(); 
formatter = new DecimalFormat("#0.00"); 
formatter.format(d); 
}else{ 
dataSetRow["col"]; 
} 
 
Jason </blockquote>

JasonW

Andrew,

What version of BIRT are you using? 2.6.1 and greater should allow Java Object columns. I wrote a post on this a while back:
http://birtworld.blogspot.com/2010/10/birt-java-object-data-type.html

You put the script in the right place. By data item I meant data element in a table. You should have also been able to add a computed column directly to the dataset.

Jason

Paul A

Jason, 
 
I believe I am using version 3.7.0.v20110603- ... BIRT was downloaded alongside Eclipse Indigo SDK from <a class='bbc_url' href='http://download.eclipse.org/eclipse/downloads/drops/R-3.7-201106131736/index.php'>Download Eclipse 3.7 SDK</a> 
 
The version information for BIRT hrom the Help->About->BIRT is as follow (I have not included context help pluigin): 
 
Business Intelligence and Reporting Tools 
Version: 3.7.0.v20110603-BDF9E7AGTzuHfngz-8A08WvWDv8v 
Build id: v20110607-1526 
 
Eclipse Business Intelligence and Reporting Tools SDK 
Version: 3.7.0.v20110603-809u78FT0z0-TVCu7ujN7w 
Build id: v20110607-1526 
 
I hope this is the correct version to write ODA drivers for. Everything seemed to be fine. I can debug my drivers in the Eclipse environment. 
 
The <a class='bbc_url' href='http://1.bp.blogspot.com/_GN1FyT5H8Kg/TLiTlS-h4OI/AAAAAAAAAQw/i8w9WBEBmEQ/s1600/outercols.png'>Screenshot</a> in your blog has Java Object listed in the Type for Output Columns 
 
I am starting to wonder how much different the BIRT versions are from the SDK I have and the 2.6.2 download from <a class='bbc_url' href='http://www.birt-exchange.com/be/info/helios-birt-designer-windows/'>Download Actuate BIRT Designer Helios</a> 
 
According to the Helios download, the version information is as follows: 
 
Eclipse Business Intelligence and Reporting Tools SDK 
Version: 2.6.2.r262_v20101008-7m9f7bFS5-OfHSjPqH_1t2 
Build id: v20110219-1842 
 
BIRT Chart Framework 
Version: 2.6.2.r262_v20101008-7f9T7OFPWowQ1naKt1Wwu4 
Build id: v20110219-1842 
 
Business Intelligence and Reporting Tools 
Version: 2.6.2.r262_v20101008-9iF7A9JGHMBCRz-R8tbxRel8kQrI 
Build id: v20110219-1842 
 
NOTE: Eclipse 3.7 does not have the BIRT Chart Framework included. 
 
I have tested my ODA drivers in the Actuate Eclipse Helios 2.6.2 release and I get the same results as 3.7 with the Eclipse Indigo SDK download. 
 
Should I be seeing Java Object in the Data Type column in the Output Columns view when I have set my ResultSetMetaData#getColumnType to return java.sql.Types.JAVA_OBJECT? 
 
Cheers, 
 
Andez 
 
 
 
<blockquote class='ipsBlockquote' data-author="'JasonW'" data-cid="80469" data-time="1311351500" data-date="22 July 2011 - 09:18 AM">
Andrew, 
 
What version of BIRT are you using? 2.6.1 and greater should allow Java Object columns. I wrote a post on this a while back: 
<a class='bbc_url' href='http://birtworld.blogspot.com/2010/10/birt-java-object-data-type.html'>http://birtworld.blogspot.com/2010/10/birt-java-object-data-type.html</a> 
 
You put the script in the right place. By data item I meant data element in a table. You should have also been able to add a computed column directly to the dataset. 
 
Jason </blockquote>

JasonW

Andrew,

I just tried this with 3.7. Make sure you have added the mapping for a java object to your oda runtime plugin datatypes:

<dataTypeMapping
nativeDataTypeCode="2000"
odaScalarDataType="JavaObject"
nativeDataType="JavaObject">
</dataTypeMapping>

And in your code make sure your ResultSet class implements:

public Object getObject( int index ) throws OdaException
{
// TODO Auto-generated method stub
//throw new UnsupportedOperationException();
return "row" + getRow() + "_column" + index + " value";
}

/* (non-Javadoc)
* @see org.eclipse.datatools.connectivity.oda.IResultSet#getObject(java.lang.String)
*/
public Object getObject( String columnName ) throws OdaException
{
return getObject( findColumn( columnName ) );
}

and your ResultSetMetaData class should set the type like:

public int getColumnType( int index ) throws OdaException
{
// TODO replace with data source specific implementation

// hard-coded for demo purpose
if( index == 1 )
return java.sql.Types.INTEGER; // as defined in data set extension manifest
return java.sql.Types.JAVA_OBJECT; // as defined in data set extension manifest
}

If you do not do these things the type will come back as a string I believe.

Jason

Paul A

Excellent Jason thankyou.

I have literally just started a PlugIn project from scratch through the wizards and the dataTypeMapping configuration in plugin.xml was included for the JavaObject, however, in my initial project it was not (based on the CSV driver code).

I have now put the configuration in my initial project and it now returns both String and Number from my data column. The columns show up as Java Object in the Data Set properties.

Thanks for that.

Andez