Eclipse Community Forums: BIRT » Accessing any dataset from a scripted datasource

Home » Archived » BIRT » Accessing any dataset from a scripted datasource

Accessing any dataset from a scripted datasource [message #884987]

Tue, 12 June 2012 04:22

Eclipse User

I have two datasets. One JDBC and a scripted one.

From the scripted dataset I what to access the jdbc dataset. Do some changes (query string). Then execute it. At the end I will use result value in my scripted dataset.

All this should be done different times in a for loop.

But how can I access the dataset?

Help will be appreciated.

Re: Accessing any dataset from a scripted datasource [message #885169 is a reply to message #884987]

Tue, 12 June 2012 10:53

Eclipse User

Juergen,

Can you give some more details. By default no dataset is executed
unless it is used in the report. You could use the data engine api to
call another dataset from script but it will add complexity to your
report. Attached is an example of using the data engine api.

Jason

On 6/12/2012 4:22 AM, Juergen Leeb wrote:
> I have two datasets. One JDBC and a scripted one.
>
> From the scripted dataset I what to access the jdbc dataset. Do some
> changes (query string). Then execute it. At the end I will use result
> value in my scripted dataset.
>
> All this should be done different times in a for loop.
>
> But how can I access the dataset?
>
> Help will be appreciated.

Attachment: dataengineexample.zip
(Size: 2.94KB, Downloaded 690 times)

Re: Accessing any dataset from a scripted datasource [message #885173 is a reply to message #884987]

Tue, 12 June 2012 10:53

Eclipse User

Attachment: dataengineexample.zip
(Size: 2.94KB, Downloaded 352 times)

Re: Accessing any dataset from a scripted datasource [message #885176 is a reply to message #884987]

Tue, 12 June 2012 10:53

Eclipse User

Attachment: dataengineexample.zip
(Size: 2.94KB, Downloaded 343 times)

Re: Accessing any dataset from a scripted datasource [message #885180 is a reply to message #884987]

Tue, 12 June 2012 10:53

Eclipse User

Attachment: dataengineexample.zip
(Size: 2.94KB, Downloaded 554 times)

Re: Accessing any dataset from a scripted datasource [message #886547 is a reply to message #885180]

Fri, 15 June 2012 02:44

Eclipse User

Hi Jason,

thanks für your answer. Is example was exactly what I' am looking for Smile

.
Thanks a lot for that.

Here is the background for my question:
We have 3 production lines. Each of them monitors its consumption of natural gas. Therefore a table in a data base exists. With every major change the new consumption value is added to the old one. This new value is written in the table (also the timestamp and the variable's name). This means the values grow and grow. Compare with a water meter.
To get the consumption in a period of time, let say from 06/04/12 12:00 am till 06/10/12 12:00 am, I have to query two values from the database. The one entry direct before 06/04/12 12:00 and the one entry direct before 06/10/12 12:00. The value I what to know is difference between the two values. This can only be done by java script.

In our production are 3 Shifts. I have to generate a report which shows for every day and shift the value of consumption for every production line.

To get out the values of the data base I use an ordenary jdbc data set. Parameter are the name of the variable, timestamp from and timestamp till.
As a middle step I have scripted data set. The table looks like that:
Variable Name, Production day, Shift, value;
With a crosstable I' ll do the presenation.

But within the script code (open event scripted ds) I have to execute the jdbc ds multiple times. For every day, shift and variable.

With your example I will have the change to generate the report.

Once again. Thank you!

regards
Juergen

Re: Accessing any dataset from a scripted datasource [message #886831 is a reply to message #886547]

Fri, 15 June 2012 13:48

Eclipse User

Thanks for the update.

Jason

On 6/15/2012 2:44 AM, Juergen Leeb wrote:
> Hi Jason,
>
> thanks für your answer. Is example was exactly what I' am looking for :) .
> Thanks a lot for that.
>
>
> Here is the background for my question:
> We have 3 production lines. Each of them monitors its consumption of
> natural gas. Therefore a table in a data base exists. With every major
> change the new consumption value is added to the old one. This new value
> is written in the table (also the timestamp and the variable's name).
> This means the values grow and grow. Compare with a water meter. To get
> the consumption in a period of time, let say from 06/04/12 12:00 am till
> 06/10/12 12:00 am, I have to query two values from the database. The one
> entry direct before 06/04/12 12:00 and the one entry direct before
> 06/10/12 12:00. The value I what to know is difference between the two
> values. This can only be done by java script.
>
> In our production are 3 Shifts. I have to generate a report which shows
> for every day and shift the value of consumption for every production line.
>
> To get out the values of the data base I use an ordenary jdbc data set.
> Parameter are the name of the variable, timestamp from and timestamp
> till. As a middle step I have scripted data set. The table looks like that:
> Variable Name, Production day, Shift, value;
> With a crosstable I' ll do the presenation.
>
> But within the script code (open event scripted ds) I have to execute
> the jdbc ds multiple times. For every day, shift and variable.
>
>
> With your example I will have the change to generate the report.
>
> Once again. Thank you!
>
>
> regards Juergen

Re: Accessing any dataset from a scripted datasource [message #892682 is a reply to message #886831]

Fri, 29 June 2012 03:58

Eclipse User

I' m not able to get it work.

In the function:

function berechneWert (_var, _von, _bis) {
	//logToDebugWindow("_var: " +_var +" _von: " +_von +" bis: " +_bis);
	
	//Aus BirtDate einen String dd.mm.yyyy HH24:Mi:ss machen
	//var sdf = new SimpleDateFormat("dd.MM.yyyy HH:mm:ss", reportContext.getLocale());
	//var von = sdf.format( _von );
	//var bis = sdf.format( _bis );
	
	
  
  	var myconfig = reportContext.getReportRunnable().getReportEngine().getConfig();
	var des = DataRequestSession.newSession(myconfig, new DataSessionContext(3));
	var dsrc = reportContext.getDesignHandle().findDataSource("GefasoftDataSource");
	var dset = reportContext.getDesignHandle().findDataSet("DSet01_Detail");
	des.defineDataSource(des.getModelAdaptor().adaptDataSource(dsrc));
	des.defineDataSet(des.getModelAdaptor().adaptDataSet(dset));
	
		
	var paramBinding1 = new InputParameterBinding( "para_var",new ScriptExpression("\"" +_var +"\"" ) );
	var paramBinding2 = new InputParameterBinding( "para_von", new ScriptExpression(_von));
	var paramBinding3 = new InputParameterBinding( "para_bis", new ScriptExpression(_bis));
	
	//logToDebugWindow("para_var: " +"\"" +_var+"\"" );
	//logToDebugWindow("para_von: " +"\"\'" +von +"\'\""  );
	//logToDebugWindow("para_bis: " +"\'" +bis+ "\'" );
	
	
	queryDefinition = new QueryDefinition( );
	queryDefinition.setDataSetName( "DSet01_Detail" );
	queryDefinition.addInputParamBinding( paramBinding1 );
	queryDefinition.addInputParamBinding( paramBinding2 );
	queryDefinition.addInputParamBinding( paramBinding3 );
	queryDefinition.setAutoBinding(true);
	var pq = des.prepare( queryDefinition );
	var qr = pq.execute( null );
	rowcount=0; 
	var ri = qr.getResultIterator( ); 
	while ( ri.next( ) )
	{ 
		//logToDebugWindow(ri.getString("VALUE_SYMBOL")); //geht nicht Fehler suchen
		//logToDebugWindow(ri.getInteger("Wert"));
		rowcount++
	}
	ri.close( );
	qr.close( );
	des.shutdown( );
  
  return 1;
}

I get the error:

Error evaluating Javascript expression. Script engine error: missing ;

I' m sure the error has to do with the date vars.

Any ideas what' s wrong?

the report file ist attached.

Attachment: 4_DLFinishVerluste.rptdesign
(Size: 31.23KB, Downloaded 461 times)

Re: Accessing any dataset from a scripted datasource [message #892804 is a reply to message #892682]

Fri, 29 June 2012 14:27

Eclipse User

Do you get a line number on the error? I would definitely be looking
the the Script expressions. They should have a format like:

"'Trains'" The internal quotes are because this is an evaled js
expression. If you script expression is going to be a variable the
report context or somewhere else and not a constant you would want
something like:

"reportContext.getGlobalVariable('mytest');" as the expression.

Jason

On 6/29/2012 3:58 AM, Juergen Leeb wrote:
> I' m not able to get it work.
>
>
> In the function:
> function berechneWert (_var, _von, _bis) {
> //logToDebugWindow("_var: " +_var +" _von: " +_von +" bis: " +_bis);
>
> //Aus BirtDate einen String dd.mm.yyyy HH24:Mi:ss machen
> //var sdf = new SimpleDateFormat("dd.MM.yyyy HH:mm:ss", reportContext.getLocale());
> //var von = sdf.format( _von );
> //var bis = sdf.format( _bis );
>
>
>
> var myconfig = reportContext.getReportRunnable().getReportEngine().getConfig();
> var des = DataRequestSession.newSession(myconfig, new DataSessionContext(3));
> var dsrc = reportContext.getDesignHandle().findDataSource("GefasoftDataSource");
> var dset = reportContext.getDesignHandle().findDataSet("DSet01_Detail");
> des.defineDataSource(des.getModelAdaptor().adaptDataSource(dsrc));
> des.defineDataSet(des.getModelAdaptor().adaptDataSet(dset));
>
>
> var paramBinding1 = new InputParameterBinding( "para_var",new ScriptExpression("\"" +_var +"\"" ) );
> var paramBinding2 = new InputParameterBinding( "para_von", new ScriptExpression(_von));
> var paramBinding3 = new InputParameterBinding( "para_bis", new ScriptExpression(_bis));
>
> //logToDebugWindow("para_var: " +"\"" +_var+"\"" );
> //logToDebugWindow("para_von: " +"\"\'" +von +"\'\"" );
> //logToDebugWindow("para_bis: " +"\'" +bis+ "\'" );
>
>
> queryDefinition = new QueryDefinition( );
> queryDefinition.setDataSetName( "DSet01_Detail" );
> queryDefinition.addInputParamBinding( paramBinding1 );
> queryDefinition.addInputParamBinding( paramBinding2 );
> queryDefinition.addInputParamBinding( paramBinding3 );
> queryDefinition.setAutoBinding(true);
> var pq = des.prepare( queryDefinition );
> var qr = pq.execute( null );
> rowcount=0;
> var ri = qr.getResultIterator( );
> while ( ri.next( ) )
> {
> //logToDebugWindow(ri.getString("VALUE_SYMBOL")); //geht nicht Fehler suchen
> //logToDebugWindow(ri.getInteger("Wert"));
> rowcount++
> }
> ri.close( );
> qr.close( );
> des.shutdown( );
>
> return 1;
> }
>
>
> I get the error:
>
> Error evaluating Javascript expression. Script engine error: missing ;
>
> I' m sure the error has to do with the date vars.
>
> Any ideas what' s wrong?
>
>
> the report file ist attached.
>

Re: Accessing any dataset from a scripted datasource [message #895004 is a reply to message #892804]

Wed, 11 July 2012 07:34

Eclipse User

Sorry for my late response. I had to do some other work. Now I will try to get on with this report.

I have changed the two lines:

var paramBinding2 = new InputParameterBinding( "para_von", new ScriptExpression("\"'" +_von + "'\"" ));
var paramBinding3 = new InputParameterBinding( "para_bis", new ScriptExpression("\"'" +_bis + "'\""));

But now I get the error:

Caused by: org.eclipse.birt.data.engine.core.DataException: A BIRT exception occurred: Error evaluating Javascript expression. Script engine error: Wrapped org.eclipse.birt.data.engine.core.DataException: Failed to prepare the query execution for the data set: DSet01_Detail
Cannot convert the parameter value 'Thu Jun 21 05:00:00 CEST 2012' to type class java.sql.Timestamp.
Can not convert the value of 'Thu Jun 21 05:00:00 CEST 2012' to Date type. (#96)
Script source: , line: 0, text:

The datatyp of the variables _von and _bis are BirtDateTime.
I think I have to convert it into java.sql.Timestamp.
I have no idea to do this. Any hint?

Thanks a lot!

Re: Accessing any dataset from a scripted datasource [message #895152 is a reply to message #895004]

Wed, 11 July 2012 16:05

Eclipse User

If it is a date why not use:

new InputParameterBinding( "para_von", new ScriptExpression(_von ));

Jason

On 7/11/12 7:34 AM, Juergen Leeb wrote:
> Sorry for my late response. I had to do some other work. Now I will try
> to get on with this report.
>
> I have changed the two lines: var paramBinding2 = new
> InputParameterBinding( "para_von", new ScriptExpression("\"'" +_von +
> "'\"" ));
> var paramBinding3 = new InputParameterBinding( "para_bis", new
> ScriptExpression("\"'" +_bis + "'\""));
>
> But now I get the error:
>
> Caused by: org.eclipse.birt.data.engine.core.DataException: A BIRT
> exception occurred: Error evaluating Javascript expression. Script
> engine error: Wrapped org.eclipse.birt.data.engine.core.DataException:
> Failed to prepare the query execution for the data set: DSet01_Detail
> Cannot convert the parameter value 'Thu Jun 21 05:00:00 CEST 2012' to
> type class java.sql.Timestamp.
> Can not convert the value of 'Thu Jun 21 05:00:00 CEST 2012' to Date
> type. (#96)
> Script source: , line: 0, text:
>
>
>
> The datatyp of the variables _von and _bis are BirtDateTime.
> I think I have to convert it into java.sql.Timestamp.
> I have no idea to do this. Any hint?
>
> Thanks a lot!

Re: Accessing any dataset from a scripted datasource [message #895226 is a reply to message #895152]

Thu, 12 July 2012 04:07

Eclipse User

This was the first thing I tried. With it I get the error:

Caused by: org.eclipse.birt.core.exception.CoreException: Error evaluating Javascript expression. Script engine error: missing ; before statement
Script source: , line: 0, text:
__bm_OPEN()

Re: Accessing any dataset from a scripted datasource [message #895396 is a reply to message #895226]

Thu, 12 July 2012 13:06

Eclipse User

Can you reproduce the issue with a report using the sample db and post
the report?

Jason

On 7/12/12 4:07 AM, Juergen Leeb wrote:
> This was the first thing I tried. With it I get the error:
>
> Caused by: org.eclipse.birt.core.exception.CoreException: Error
> evaluating Javascript expression. Script engine error: missing ; before
> statement
> Script source: , line: 0, text:
> __bm_OPEN()

Re: Accessing any dataset from a scripted datasource [message #897533 is a reply to message #895396]

Tue, 24 July 2012 08:35

Eclipse User

I Think this isn' t necessary anymore. I got this part runing. Now I create the whole query string in the script. But one next question is how to get values from the query?

With ri.getValue("VALUE_SYMBOL") I got the error message "VALUE_SYMBOL" is not defined. Even though the binding is done and the row also exists.

Re: Accessing any dataset from a scripted datasource [message #897630 is a reply to message #897533]

Tue, 24 July 2012 16:06

Eclipse User

Are you positive you have the column name correct? You can examine the
result set meta data using:

var rsmd = ri.getResultMetaData();
ie var cc = ri.getResultMetaData().getColumnCount();
ie ri.getValue(rsmd.getColumnName(1)).toString();

Jason

On 7/24/2012 8:35 AM, Juergen Leeb wrote:
> I Think this isn' t necessary anymore. I got this part runing. Now I
> create the whole query string in the script. But one next question is
> how to get values from the query?
>
> With ri.getValue("VALUE_SYMBOL") I got the error message "VALUE_SYMBOL"
> is not defined. Even though the binding is done and the row also exists.

Previous Topic:	upgrate birt 2.3.2 to birt 4.2
Next Topic:	How to hide crosstab columns at runtime (in Excel)

Goto Forum:

-=] Back to Top [=-

Current Time: Sat Jul 12 15:00:16 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter