write table rows to disk

I have several queries that return rows. The rows may appear in other queries.

I need to list the results for all queries uniquely and sorted in order.

Is there a way to grab the results from several queries and report on them.

Perhaps write the queries to disk (CSV) then read them into another query?

How do you write the query result rows to disk?

TIA

Find more posts tagged with

Comments

johnw

In the initialize statement of your report, create a HashMap that stores an array or an ArrayList, using the key of your row as the Map key. So in the initialize event: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
var myMap = new Packages.java.util.HashMap();
reportContext.setGlobalVariable("myMap", myMap);
</pre>
 
Then, in the onFetch event for all of your datasets, you'd do something like: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
var myMap = reportContext.getGlobalVariable("myMap");
var myArray = new Packages.java.util.ArrayList();

myArray.add(row["col1"]);
myArray.add(row["col2"]);

myMap.put(row["id"], myArray);
reportContext.setGlobalVariable("myMap", myMap); //this usually isn't necessary, but to be safe.
</pre>
 
In your report, you would have all the datasets in Tables with the visibility expression set to false. This will build your unique entries. You could also use a set as well. 
 
Then, you have a Scripted Data Source that will do something like: 
 
Open on Data Set: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
count = 0;
localMap = reportContext.getGlobalVariable("myMap");
localKeys = localMap.keySet();
</pre>
 
Fetch: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if (count < localKeys.size())
{
var currentKey = localKeys.get(count);

var currentRow = localMap.get(currentKey);

row["id"] = currentKey;
row["col1"] = currentRow.get(0);
row["col2"] = currentRow.get(1);
...

count++;
return (true);
}

return (false);
</pre>
 
Then, you drop the scripted data set into your report. You can sort it at the table level.

Calder

Thanks for your quick response.

I am working with a UML Data Source, not a SQL data source, so there are no events on the data sets.

Any suggestions?

johnw

That would be weird. the onFetch and onOpen are defined in the parent class for data sets. But if there really aren't any events in the data set, you can do it on the Tables in the onCreate events.

Calder

<blockquote class='ipsBlockquote' data-author="'johnw'" data-cid="97021" data-time="1330529838" data-date="29 February 2012 - 08:37 AM">
That would be weird. the onFetch and onOpen are defined in the parent class for data sets. But if there really aren't any events in the data set, you can do it on the Tables in the onCreate events. </blockquote>
 
you were right - I was looking at the Data Sets group, rather than the individual data sets. 
 
I am trying to apply your javascript to a classicModels example, to get it working. I get an error message: 
 
The following items have errors: 
 
 
ReportDesign (id = 1): 
+ There are errors evaluating script "if (count < localKeys.size()) 
{ 
var currentKey = localKeys.get(count); 
 
var currentRow = localMap.get(currentKey); 
 
row["id"] = currentKey; 
row["col1"] = currentRow.get(0); 
row["col2"] = currentRow.get(1); 
 
 
count++; 
return (true); 
} 
 
return (false);": 
{1}. 
Error.ScriptEvaluationError ( 1 time(s) ) 
detail : org.eclipse.birt.report.engine.api.EngineException: There are errors evaluating script "if (count < localKeys.size()) 
{ 
var currentKey = localKeys.get(count); 
 
var currentRow = localMap.get(currentKey); 
 
row["id"] = currentKey; 
row["col1"] = currentRow.get(0); 
row["col2"] = currentRow.get(1); 
 
 
count++; 
return (true); 
} 
 
return (false);": 
{1}. 
at org.eclipse.birt.report.engine.script.internal.DtEScriptExecutor.handleJS(Unknown Source) 
at org.eclipse.birt.report.engine.script.internal.DataSetScriptExecutor.handleJS(Unknown Source) 
at org.eclipse.birt.report.engine.script.internal.ScriptDataSetScriptExecutor.handleFetch(Unknown Source) 
at org.eclipse.birt.data.engine.impl.ScriptDataSetRuntime.fetch(Unknown Source) 
at org.eclipse.birt.data.engine.impl.PreparedScriptDSQuery$ScriptDSQueryExecutor$CustomDataSet.fetch(Unknown Source) 
at org.eclipse.birt.data.engine.executor.cache.OdiAdapter.fetch(Unknown Source) 
at org.eclipse.birt.data.engine.executor.cache.RowResultSet.next(Unknown Source) 
at org.eclipse.birt.data.engine.executor.cache.ExpandableRowResultSet.next(Unknown Source) 
at org.eclipse.birt.data.engine.executor.cache.SmartCacheHelper.populateData(Unknown Source) 
at org.eclipse.birt.data.engine.executor.cache.SmartCacheHelper.initInstance(Unknown Source) 
at org.eclipse.birt.data.engine.executor.cache.SmartCacheHelper.getResultSetCache(Unknown Source) 
at org.eclipse.birt.data.engine.executor.cache.SmartCache.<init>(Unknown Source) 
at org.eclipse.birt.data.engine.executor.transform.pass.PassUtil.populateOdiResultSet(Unknown Source) 
at org.eclipse.birt.data.engine.executor.transform.pass.PassUtil.pass(Unknown Source) 
at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.doSinglePass(Unknown Source) 
at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.prepareDataSetResultSet(Unknown Source) 
at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.pass(Unknown Source) 
at org.eclipse.birt.data.engine.executor.transform.pass.PassManager.populateResultSet(Unknown Source) 
at org.eclipse.birt.data.engine.executor.transform.ResultSetPopulator.populateResultSet(Unknown Source) 
at org.eclipse.birt.data.engine.executor.transform.CachedResultSet.<init>(Unknown Source) 
at org.eclipse.birt.data.engine.executor.CandidateQuery.execute(Unknown Source) 
at org.eclipse.birt.data.engine.impl.PreparedScriptDSQuery$ScriptDSQueryExecutor.executeOdiQuery(Unknown Source) 
at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(Unknown Source) 
at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(Unknown Source) 
at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(Unknown Source) 
at org.eclipse.birt.report.engine.data.dte.QueryResultSet.<init>(Unknown Source) 
at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(Unknown Source) 
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(Unknown Source) 
at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(Unknown Source) 
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(Unknown Source) 
at org.eclipse.birt.report.engine.executor.TableItemExecutor.execute(Unknown Source) 
at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplicateItemExecutor.execute(Unknown Source) 
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(Unknown Source) 
at org.eclipse.birt.report.engine.internal.executor.l18n.LocalizedReportItemExecutor.execute(Unknown Source) 
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(Unknown Source) 
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(Unknown Source) 
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(Unknown Source) 
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(Unknown Source) 
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(Unknown Source) 
at org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(Unknown Source) 
at org.eclipse.birt.report.service.BirtViewerReportService.runAndRenderReport(Unknown Source) 
at org.eclipse.birt.report.service.actionhandler.BirtGetPageAllActionHandler.__execute(Unknown Source) 
at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(Unknown Source) 
at org.eclipse.birt.report.soapengine.processor.AbstractBaseDocumentProcessor.__executeAction(Unknown Source) 
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.executeAction(Unknown Source) 
at org.eclipse.birt.report.soapengine.processor.BirtDocumentProcessor.handleGetPageAll(Unknown Source) 
at sun.reflect.GeneratedMethodAccessor211.invoke(Unknown Source) 
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) 
at java.lang.reflect.Method.invoke(Unknown Source) 
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.process(Unknown Source) 
at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingImpl.getUpdatedObjects(Unknown Source) 
at sun.reflect.GeneratedMethodAccessor194.invoke(Unknown Source) 
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) 
at java.lang.reflect.Method.invoke(Unknown Source) 
at org.apache.axis.providers.java.RPCProvider.invokeMethod(Unknown Source) 
at org.apache.axis.providers.java.RPCProvider.processMessage(Unknown Source) 
at org.apache.axis.providers.java.JavaProvider.invoke(Unknown Source) 
at org.apache.axis.strategies.InvocationStrategy.visit(Unknown Source) 
at org.apache.axis.SimpleChain.doVisiting(Unknown Source) 
at org.apache.axis.SimpleChain.invoke(Unknown Source) 
at org.apache.axis.handlers.soap.SOAPService.invoke(Unknown Source) 
at org.apache.axis.server.AxisServer.invoke(Unknown Source) 
at org.apache.axis.transport.http.AxisServlet.doPost(Unknown Source) 
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doPost(Unknown Source) 
at javax.servlet.http.HttpServlet.service(Unknown Source) 
at org.apache.axis.transport.http.AxisServletBase.service(Unknown Source) 
at javax.servlet.http.HttpServlet.service(Unknown Source) 
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(Unknown Source) 
at org.eclipse.equinox.http.registry.internal.ServletManager$ServletWrapper.service(Unknown Source) 
at org.eclipse.equinox.http.servlet.internal.ServletRegistration.service(Unknown Source) 
at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(Unknown Source) 
at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(Unknown Source) 
at javax.servlet.http.HttpServlet.service(Unknown Source) 
at org.eclipse.equinox.http.jetty.internal.HttpServerManager$InternalHttpServiceServlet.service(Unknown Source) 
at org.mortbay.jetty.servlet.ServletHolder.handle(Unknown Source) 
at org.mortbay.jetty.servlet.ServletHandler.handle(Unknown Source) 
at org.mortbay.jetty.servlet.SessionHandler.handle(Unknown Source) 
at org.mortbay.jetty.handler.ContextHandler.handle(Unknown Source) 
at org.mortbay.jetty.handler.HandlerWrapper.handle(Unknown Source) 
at org.mortbay.jetty.Server.handle(Unknown Source) 
at org.mortbay.jetty.HttpConnection.handleRequest(Unknown Source) 
at org.mortbay.jetty.HttpConnection$RequestHandler.content(Unknown Source) 
at org.mortbay.jetty.HttpParser.parseNext(Unknown Source) 
at org.mortbay.jetty.HttpParser.parseAvailable(Unknown Source) 
at org.mortbay.jetty.HttpConnection.handle(Unknown Source) 
at org.mortbay.io.nio.SelectChannelEndPoint.run(Unknown Source) 
at org.mortbay.thread.QueuedThreadPool$PoolThread.run(Unknown Source) 
Caused by: org.eclipse.birt.data.engine.core.DataException: Fail to execute script in function __bm_FETCH(). Source:

" + if (count < localKeys.size()) 
{ 
var currentKey = localKeys.get(count); 
 
var currentRow = localMap.get(currentKey); 
 
row["id"] = currentKey; 
row["col1"] = currentRow.get(0); 
row["col2"] = currentRow.get(1); 
 
 
count++; 
return (true); 
} 
 
return (false); + "

A BIRT exception occurred. See next exception for more information. 
Error evaluating Javascript expression. Script engine error: TypeError: Cannot find function get in object [0.0]. 
Script source: /report/data-sets/script-data-set[@id="10"]/method[@name="fetch"], line: 0, text: 
__bm_FETCH() 
at org.eclipse.birt.data.engine.script.JSMethodRunner.runScript(Unknown Source) 
... 87 more 
Caused by: org.eclipse.birt.data.engine.core.DataException: A BIRT exception occurred. See next exception for more information. 
Error evaluating Javascript expression. Script engine error: TypeError: Cannot find function get in object [0.0]. 
Script source: /report/data-sets/script-data-set[@id="10"]/method[@name="fetch"], line: 0, text: 
__bm_FETCH() 
at org.eclipse.birt.data.engine.core.DataException.wrap(Unknown Source) 
at org.eclipse.birt.data.engine.script.ScriptEvalUtil.evaluateJSAsExpr(Unknown Source) 
... 88 more 
Caused by: org.eclipse.birt.core.exception.CoreException: Error evaluating Javascript expression. Script engine error: TypeError: Cannot find function get in object [0.0]. 
Script source: /report/data-sets/script-data-set[@id="10"]/method[@name="fetch"], line: 0, text: 
__bm_FETCH() 
at org.eclipse.birt.core.script.JavascriptEvalUtil.wrapRhinoException(Unknown Source) 
at org.eclipse.birt.core.script.JavascriptEvalUtil.evaluateRawScript(Unknown Source) 
at org.eclipse.birt.core.script.JavascriptEvalUtil.evaluateScript(Unknown Source) 
... 89 more 
Caused by: org.mozilla.javascript.EcmaError: TypeError: Cannot find function get in object [0.0]. 
at org.mozilla.javascript.ScriptRuntime.constructError(Unknown Source) 
at org.mozilla.javascript.ScriptRuntime.constructError(Unknown Source) 
at org.mozilla.javascript.ScriptRuntime.typeError(Unknown Source) 
at org.mozilla.javascript.ScriptRuntime.typeError2(Unknown Source) 
at org.mozilla.javascript.ScriptRuntime.notFunctionError(Unknown Source) 
at org.mozilla.javascript.ScriptRuntime.getPropFunctionAndThisHelper(Unknown Source) 
at org.mozilla.javascript.ScriptRuntime.getPropFunctionAndThis(Unknown Source) 
at org.mozilla.javascript.gen.c715._c1(Unknown Source) 
at org.mozilla.javascript.gen.c715.call(Unknown Source) 
at org.mozilla.javascript.optimizer.OptRuntime.callName0(Unknown Source) 
at org.mozilla.javascript.gen.c549._c0(Unknown Source) 
at org.mozilla.javascript.gen.c549.call(Unknown Source) 
at org.mozilla.javascript.ContextFactory.doTopCall(Unknown Source) 
at org.mozilla.javascript.ScriptRuntime.doTopCall(Unknown Source) 
at org.mozilla.javascript.gen.c549.call(Unknown Source) 
at org.mozilla.javascript.gen.c549.exec(Unknown Source) 
... 91 more 
 
+ Data Set script method "Fetch" returned null; expected a Boolean value. 
I have attached my report design if you could have a look at it and tell me what I am doing wrong. 
 
tia

johnw

There is a logic error in the non-scripted Data Sets Fetch method. You are doing the following: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>var myMap = reportContext.getGlobalVariable("myMap");
var myArray = new Packages.java.util.ArrayList();
count=0;
myArray.add(row["CUSTOMERNAME"]);
myArray.add(row["CONTACTLASTNAME"]);

myMap.put(count, myArray);
reportContext.setGlobalVariable("myMap", myMap);
count++;</pre>
 
This will always be setting Map[0, myArray] since count is continually being reset to 0. Set that in a global variable called count. So change it to look something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>var myMap = reportContext.getGlobalVariable("myMap");
var myArray = new Packages.java.util.ArrayList();
var myCount = reportContext.getGlobalVariable("count");

if (myCount == null)
myCount=0;

myArray.add(row["CUSTOMERNAME"]);
myArray.add(row["CONTACTLASTNAME"]);

myMap.put(count, myArray);
reportContext.setGlobalVariable("myMap", myMap);

myCount++;
reportContext.setGlobalVariable("count", myCount);
</pre>

Calder

Just to clarify, Data Sets do have onFetch, but join data sets have no methods.

I tried to put the javascript on the create method of the table, but it only grabs the first record and ignores the rest.

johnw

Use onPrepare then. I might have the order of execution backwards in the Event Model