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; } } }

dataset value in beforeFactory method

Birtsnew

Hi,

How can i get the dataset value in scripts before factory method for dataset value comparison?

is anyway to acheive this?

thanks
sam.

Find more posts tagged with

Comments

JasonW

Sam,

Currently the only way to do this is to call a java class/js that connects to your datasource or use the Data Engine API directly. The Data Engine API may be subject to change in the future. I have attached an example that shows it being called in the beforeFactory.

Jason

actuser9

Hi Everyone, 
 
I am using the report provided by Jason and modified the beforefactory code to get the conditional watermark based on the data set value using the beforefactory code in the report. It works perfectly fine using ClassicModels sample db. 
 
I am trying to implement the same on my report and the beforefactory code does not seem to work. I am using Oracle DB. The error code that I am getting is as below. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Wrapped org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot execute the statement.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:[ActuateDD][Oracle JDBC Driver]Invalid parameter binding(s).
;
java.sql.SQLException: [ActuateDD][Oracle JDBC Driver]Invalid parameter binding(s).
</pre>
 
The code is also in "OracleDB_bfcode.txt", any ideas what I am missing?

actuser9

Hi,

Any suggestions?

Thanks
UY

Tubal

Are you able to connect to your oracle db with the Data Source that is set up in your report? The report you updloaded shows it still being connected to classicmodels.

What that beforeFactory script is doing is looking at 'Data Source' and copying all of your connection parameters from it, and then creating a new datasource using those same parameters. So if 'Data Source' doesn't have your oracle connection parameters in it, it won't work.

So that would be the first step, is to make sure 'Data Source' is connecting to the database you want.

actuser9

Thanks Tubal. I have attached the report that is using classicModels. 
 
But I have a data source that is pointing to Oracle db. You indicated it right. I am using the below code to get the oracle connection parameters. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>

var odaDataSource = new OdaDataSourceDesign( "Test Data Source" );
odaDataSource.setExtensionID( "org.eclipse.birt.report.data.oda.jdbc" );
odaDataSource.addPublicProperty( "odaURL", dsrc.getProperty("odaURL").toString() );
odaDataSource.addPublicProperty( "odaDriverClass", dsrc.getProperty("odaDriverClass").toString());
odaDataSource.addPublicProperty( "odaUser", dsrc.getProperty("odaUser").toString() );
odaDataSource.addPublicProperty( "odaPassword", dsrc.getProperty("odaPassword").toString() );

var odaDataSet = new OdaDataSetDesign( "Test Data Set" );
odaDataSet.setDataSource( odaDataSource.getName( ) );
odaDataSet.setExtensionID( "org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" );
odaDataSet.setQueryText( dset.getQueryText() );</pre>
 
I am not able to see what might be wrong with this. I guess I am not using the right extension ID. Can you please throw some light on this? 
 
Thanks 
UY

Tubal

I'm thinking it may be a problem with your query/dataset. Try doing a simple query on a table you know will get results. 
 
Replace: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>odaDataSet.setQueryText( dset.getQueryText() );</pre>
 
With 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>strSql = "SELECT count(*) FROM someTable"
odaDataSet.setQueryText( strSQL );</pre>
 
Replacing 'someTable' with a table you know has data, and see if you still get the error. 
 
If you don't, it's probably something to do with your report parameters. 
 
I've attached a beforeFactory I use to build a table dynamically based on data I get in the beforeFactory event. The data fetching looks almost identical to the one you posted. I connect to a PostgreSQL backend.

actuser9

Tubal,

I will test both the options and would post my results.

Thank you
UY

actuser9

Tubal, 
 
You were right. See the error message below. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
SQL error #1:[ActuateDD][Oracle JDBC Driver]Invalid parameter binding(s).
;
java.sql.SQLException: [ActuateDD][Oracle JDBC Driver]Invalid parameter binding(s).
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:52)
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:108)
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:84)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:586)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:927)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:416)
at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1154)
at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:232)
at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:177)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:161)
... 24 more
</pre>
 
I am using the below code to add the two parameters I am having as part of the report. Still getting the error. 
<pre class='_prettyXprint _lang-auto _linenums:0'>
var paramBinding = new InputParameterBinding( "param_1",new ScriptExpression( params['Pkg'].value ) );
var paramBinding1 = new InputParameterBinding( "param_2",new ScriptExpression( params['RefDate'].value ) );

//strSql = "SELECT count(*) FROM nsr_pkg"
//odaDataSet.setQueryText( strSQL );

odaDataSet.setQueryText( dset.getQueryText() );
odaDataSet.addParameter(paramBinding);
odaDataSet.addParameter(paramBinding1);
</pre>
 
Thanks 
UY

Tubal

Can you run the query from the strSql variable you create? It wasn't clear if that solved the error. 
 
If so, can you just add the parameters as part of that strSql rather than using parameter bindings? 
 
If you look at code I posted, I'm adding the parameters into my sql statement without binding them. 
 
like... 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>strSql = "SELECT count(*) from someTable WHERE someColumn = " + params["myParam"]</pre>

actuser9

Hi Tubal, 
 
I reran the code using the strSQL and getting the below error. I have attached the code I have used in the report.

Tubal

<pre class='_prettyXprint _lang-auto _linenums:0'>ReferenceError: "strSQL" is not defined.</pre>
 
variables are case sensitive. 
 
You are declaring "strSql" and then you are calling "strSQL". 
 
It's telling you that "strSQL" has not been declared. 
 
Change <pre class='_prettyXprint _lang-auto _linenums:0'>odaDataSet.setQueryText( strSQL );</pre>
 
To <pre class='_prettyXprint _lang-auto _linenums:0'>odaDataSet.setQueryText( strSql );</pre>

actuser9

Hi Tubal,

That worked. I was sure I was doing some thing wrong. Its been a week going on with this issue. Finally you got me out of this. Thanks for all your patience and support.

I have a 150line select statement in one of the data set. I donot want to use that code as part of the "strSQL" instead would be nice to use the dataset directly. Any idea how I could get the SQL and bind the report parameters to the dataset?

Again thanks for all the time you have taken to help me.

Regards
UY

actuser9

Any one interested in getting the report function based on the data set values can use the link below. Jason modified the code to use the data set values based on report parameters in this post.

http://www.birt-exchange.org/org/devshare/designing-birt-reports/1542-data-engine-api-to-check-data-set-values/

wirzbicki

Greetings,

A question; does executing the dataset call in the beforeFactory event causes it to fetch the data twice if it is also bind to the table?

I thought I read somewhere in the birt documentation if the dataset is called multiple times it caches the results and on the 2nd-n call to it it does not execute the select again.

Thank-you in advance!

Mike W.

Forty2

<blockquote class='ipsBlockquote' data-author="'wirzbicki'" data-cid="114684" data-time="1362080253" data-date="28 February 2013 - 12:37 PM">
A question; does executing the dataset call in the beforeFactory event causes it to fetch the data twice if it is also bind to the table? 
 
I thought I read somewhere in the birt documentation if the dataset is called multiple times it caches the results and on the 2nd-n call to it it does not execute the select again. </blockquote>
 
I've been wondering the same thing and did some testing with a fairly slow dataset which takes about 45s to execute. When I ran the report it took about 1min 35s to render. So there you have your answer

. 
 
You'd have to directly access the data engine instance that the report engine uses internally to execute data sets and cache them.

wirzbicki

Thanks for confirming what I had guessed was 2 instances of the dataset being executed. So these coding examples just duplicates the existing dataset in the report. 
 
So is there any examples of getting the direct access to the data engine instance so I can execute the dataset once instead of multiple time? My dataset is call out to a sql procedure which can take up to 20 minutes to generate the data needed by the report. 
 
I certaintly don't want to execute it twice as you proven this approach will do. 
 
Thanks again to all!! 
 
Mike W. <a class='bbc_url' href='http://www.birt-exchange.org/org/forum/public/style_emoticons/'>http://www.birt-exchange.org/org/forum/public/style_emoticons/</a><#EMO_DIR#>/tongue.gif

actuser9

Hi wirzbicki, 
 
Sorry for the delayed reply. Here you go. 
<blockquote class='ipsBlockquote' >
A question; does executing the dataset call in the beforeFactory event causes it to fetch the data twice if it is also bind to the table? </blockquote>
 
Yes. The data set will be executing multiple times based on the number of elements(table, chart etc) binded to the data set. 
 
What type of data source do you use in your report? 
 
Thanks, 
UY

wirzbicki

Hi, 
 
I am using an Oracle 11g data base connection. I was able to create my own connection using the previous examples above and was able to execute a simple sql "select count(*) from mytable" and get a successfullyreturn. 
 
In my beforeFactory event the code I used in this thread creates another connection from my existing Data Source and Data Set. Forty2 stated that I should use the same "data engine instance that the report engine uses" so I can take advantage of the data cache that is going on so that I don't duplicate executing the same data set call twice (the behavoir I want to achieve). 
 
I was looking to see if there was any code examples showing the syntax that provided that. 
 
Thanks again! 
Mike W. 
 
 
<blockquote class='ipsBlockquote' data-author="'actuser9'" data-cid="114726" data-time="1362157173" data-date="01 March 2013 - 09:59 AM">
Hi wirzbicki, 
 
Sorry for the delayed reply. Here you go. 
 
 
Yes. The data set will be executing multiple times based on the number of elements(table, chart etc) binded to the data set. 
 
What type of data source do you use in your report? 
 
Thanks, 
UY </blockquote>

actuser9

Hi wirzbicki,

This is the sample code that I built with forums help, with few issues.

1. Not all the row data are being populated.
2. Need to see how many times the scripted data set is being called.

But to start with, it might help. Also, let me know if you come with a more sophisticated code.

Thanks,
UY

Forty2

<blockquote class='ipsBlockquote' data-author="'wirzbicki'" data-cid="114730" data-time="1362161115" data-date="01 March 2013 - 11:05 AM">
Hi, 
 
I am using an Oracle 11g data base connection. I was able to create my own connection using the previous examples above and was able to execute a simple sql "select count(*) from mytable" and get a successfullyreturn. 
 
In my beforeFactory event the code I used in this thread creates another connection from my existing Data Source and Data Set. Forty2 stated that I should use the same "data engine instance that the report engine uses" so I can take advantage of the data cache that is going on so that I don't duplicate executing the same data set call twice (the behavoir I want to achieve). 
 
I was looking to see if there was any code examples showing the syntax that provided that. 
 
Thanks again! 
Mike W. </blockquote>
 
The workaround I suggested is not as easy to implement as using a scripted data set to cache the data yourself. 
In order to get access to the data engine BIRT uses you either have to use quite a bit of java reflection or checkout the source code yourself and make the required changes.