Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » BeforeOpen Script Error(Script Execution problem)
BeforeOpen Script Error [message #1783012] Tue, 06 March 2018 13:45 Go to next message
JP Redro is currently offline JP RedroFriend
Messages: 181
Registered: July 2009
Location: India
Senior Member

Experts,

I have developed a report in BIRT 4.3.1 and as per my requirement i am using script to pass parameters.
Here is my queryText
queryText = "SELECT rsh.receipt_num";
queryText = queryText + " ,rsh.creation_date Receipt_date";
queryText = queryText + " ,ood.organization_code Inv_Org";
queryText = queryText + " ,rsl.to_subinventory Subinventory";
queryText = queryText + " ,ph.segment1 PO_NUM";
queryText = queryText + " ,msi.segment1 item_name";
queryText = queryText + " ,msi.description item_des";
queryText = queryText + " ,pdl.quantity_ordered Qty_Expected";
queryText = queryText + " ,NVL(rsl.quantity_received,0) Qty_Received";
queryText = queryText + " ,(pdl.quantity_ordered - rsl.quantity_received) bal_qty";
queryText = queryText + " ,rsl.shipment_line_status_code receiving_status";
queryText = queryText + "FROM po_headers_all ph";
queryText = queryText + " ,po_lines_all pll";
queryText = queryText + " ,po_distributions_all pdl";
queryText = queryText + " ,mtl_system_items_b msi";
queryText = queryText + " ,org_organization_definitions ood";
queryText = queryText + " ,rcv_shipment_lines rsl";
queryText = queryText + " ,rcv_shipment_headers rsh";
queryText = queryText + "WHERE ph.po_header_id = pll.po_header_id";
queryText = queryText + "AND pll.po_line_id = pdl.po_line_id";
queryText = queryText + "AND pll.item_id = msi.inventory_item_id";
queryText = queryText + "AND msi.organization_id = pdl.destination_organization_id";
queryText = queryText + "AND msi.organization_id = ood.organization_id";
queryText = queryText + "AND pll.po_line_id = rsl.po_line_id(+)";
queryText = queryText + "AND rsl.shipment_header_id = rsh.shipment_header_id(+)";


if (params["P_RCT_MUM"].value != null && params["P_RCT_NUM"].value.length > 0) {
queryText = queryText + " AND rsh.receipt_num = '" + params["P_RCT_NUM"].value + "'";
}
if (params["P_PO_MUM"].value != null && params["P_PO_NUM"].value.length > 0) {
queryText = queryText + " AND ph.segment1 = '" + params["P_PO_NUM"].value + "'";
}

But i am getting error while running the report.

Fail to execute script in function __bm_beforeOpen(). Source:
------
" + queryText = "SELECT rsh.receipt_num";
queryText = queryText + " ,rsh.creation_date Receipt_date";
queryText = queryText + " ,ood.organization_code Inv_Org";
queryText = queryText + " ,rsl.to_subinventory Subinventory";
queryText = queryText + " ,ph.segment1 PO_NUM";
queryText = queryText + " ,msi.segment1 item_name";
queryText = queryText + " ,msi.description item_des";
queryText = queryText + " ,pdl.quantity_ordered Qty_Expected";
queryText = queryText + " ,NVL(rsl.quantity_received,0) Qty_Received";
queryText = queryText + " ,(pdl.quantity_ordered - rsl.quantity_received) bal_qty";
queryText = queryText + " ,rsl.shipment_line_status_code receiving_status";
queryText = queryText + "FROM po_headers_all ph";
queryText = queryText + " ,po_lines_all pll";
queryText = queryText + " ,po_distributions_all pdl";
queryText = queryText + " ,mtl_system_items_b msi";
queryText = queryText + " ,org_organization_definitions ood";
queryText = queryText + " ,rcv_shipment_lines rsl";
queryText = queryText + " ,rcv_shipment_headers rsh";
queryText = queryText + "WHERE ph.po_header_id = pll.po_header_id";
queryText = queryText + "AND pll.po_line_id = pdl.po_line_id";
queryText = queryText + "AND pll.item_id = msi.inventory_item_id";
queryText = queryText + "AND msi.organization_id = pdl.destination_organization_id";
queryText = queryText + "AND msi.organization_id = ood.organization_id";
queryText = queryText + "AND pll.po_line_id = rsl.po_line_id(+)";
queryText = queryText + "AND rsl.shipment_header_id = rsh.shipment_header_id(+)";


if (params["P_RCT_MUM"].value != null && params["P_RCT_NUM"].value.length > 0) {
queryText = queryText + " AND rsh.receipt_num = '" + params["P_RCT_NUM"].value + "'";
}
if (params["P_PO_MUM"].value != null && params["P_PO_NUM"].value.length > 0) {
queryText = queryText + " AND ph.segment1 = '" + params["P_PO_NUM"].value + "'";
} + "
-----
A BIRT exception occurred. See next exception for more information.
Report parameter "P_RCT_MUM" does not exist..
Error.ScriptEvaluationError ( 1 time(s) )
detail : org.eclipse.birt.report.engine.api.EngineException: There are errors evaluating script "queryText = "SELECT rsh.receipt_num";
queryText = queryText + " ,rsh.creation_date Receipt_date";
queryText = queryText + " ,ood.organization_code Inv_Org";
queryText = queryText + " ,rsl.to_subinventory Subinventory";
queryText = queryText + " ,ph.segment1 PO_NUM";
queryText = queryText + " ,msi.segment1 item_name";
queryText = queryText + " ,msi.description item_des";
queryText = queryText + " ,pdl.quantity_ordered Qty_Expected";
queryText = queryText + " ,NVL(rsl.quantity_received,0) Qty_Received";
queryText = queryText + " ,(pdl.quantity_ordered - rsl.quantity_received) bal_qty";
queryText = queryText + " ,rsl.shipment_line_status_code receiving_status";
queryText = queryText + "FROM po_headers_all ph";
queryText = queryText + " ,po_lines_all pll";
queryText = queryText + " ,po_distributions_all pdl";
queryText = queryText + " ,mtl_system_items_b msi";
queryText = queryText + " ,org_organization_definitions ood";
queryText = queryText + " ,rcv_shipment_lines rsl";
queryText = queryText + " ,rcv_shipment_headers rsh";
queryText = queryText + "WHERE ph.po_header_id = pll.po_header_id";
queryText = queryText + "AND pll.po_line_id = pdl.po_line_id";
queryText = queryText + "AND pll.item_id = msi.inventory_item_id";
queryText = queryText + "AND msi.organization_id = pdl.destination_organization_id";
queryText = queryText + "AND msi.organization_id = ood.organization_id";
queryText = queryText + "AND pll.po_line_id = rsl.po_line_id(+)";
queryText = queryText + "AND rsl.shipment_header_id = rsh.shipment_header_id(+)";


if (params["P_RCT_MUM"].value != null && params["P_RCT_NUM"].value.length > 0) {
queryText = queryText + " AND rsh.receipt_num = '" + params["P_RCT_NUM"].value + "'";
}
if (params["P_PO_MUM"].value != null && params["P_PO_NUM"].value.length > 0) {
queryText = queryText + " AND ph.segment1 = '" + params["P_PO_NUM"].value + "'";
}":
Fail to execute script in function __bm_beforeOpen(). Source:
------
" + queryText = "SELECT rsh.receipt_num";
queryText = queryText + " ,rsh.creation_date Receipt_date";
queryText = queryText + " ,ood.organization_code Inv_Org";
queryText = queryText + " ,rsl.to_subinventory Subinventory";
queryText = queryText + " ,ph.segment1 PO_NUM";
queryText = queryText + " ,msi.segment1 item_name";
queryText = queryText + " ,msi.description item_des";
queryText = queryText + " ,pdl.quantity_ordered Qty_Expected";
queryText = queryText + " ,NVL(rsl.quantity_received,0) Qty_Received";
queryText = queryText + " ,(pdl.quantity_ordered - rsl.quantity_received) bal_qty";
queryText = queryText + " ,rsl.shipment_line_status_code receiving_status";
queryText = queryText + "FROM po_headers_all ph";
queryText = queryText + " ,po_lines_all pll";
queryText = queryText + " ,po_distributions_all pdl";
queryText = queryText + " ,mtl_system_items_b msi";
queryText = queryText + " ,org_organization_definitions ood";
queryText = queryText + " ,rcv_shipment_lines rsl";
queryText = queryText + " ,rcv_shipment_headers rsh";
queryText = queryText + "WHERE ph.po_header_id = pll.po_header_id";
queryText = queryText + "AND pll.po_line_id = pdl.po_line_id";
queryText = queryText + "AND pll.item_id = msi.inventory_item_id";
queryText = queryText + "AND msi.organization_id = pdl.destination_organization_id";
queryText = queryText + "AND msi.organization_id = ood.organization_id";
queryText = queryText + "AND pll.po_line_id = rsl.po_line_id(+)";
queryText = queryText + "AND rsl.shipment_header_id = rsh.shipment_header_id(+)";


if (params["P_RCT_MUM"].value != null && params["P_RCT_NUM"].value.length > 0) {
queryText = queryText + " AND rsh.receipt_num = '" + params["P_RCT_NUM"].value + "'";
}
if (params["P_PO_MUM"].value != null && params["P_PO_NUM"].value.length > 0) {
queryText = queryText + " AND ph.segment1 = '" + params["P_PO_NUM"].value + "'";
} + "
-----
A BIRT exception occurred. See next exception for more information.
Report parameter "P_RCT_MUM" does not exist..
at org.eclipse.birt.report.engine.script.internal.DtEScriptExecutor.handleJS(DtEScriptExecutor.java:99)
at org.eclipse.birt.report.engine.script.internal.DataSetScriptExecutor.handleJS(DataSetScriptExecutor.java:256)
at org.eclipse.birt.report.engine.script.internal.DataSetScriptExecutor.handleBeforeOpen(DataSetScriptExecutor.java:107)
at org.eclipse.birt.data.engine.impl.DataSetRuntime.beforeOpen(DataSetRuntime.java:567)
at org.eclipse.birt.data.engine.impl.QueryExecutor.dataSetBeforeOpen(QueryExecutor.java:214)
at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:326)
at org.eclipse.birt.data.engine.impl.PreparedQuery.doPrepare(PreparedQuery.java:463)
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.produceQueryResults(PreparedDataSourceQuery.java:190)
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.execute(PreparedDataSourceQuery.java:178)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery.execute(PreparedOdaDSQuery.java:178)
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:637)
at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(DteDataEngine.java:152)
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:275)
at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1947)
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:80)
at org.eclipse.birt.report.engine.executor.TableItemExecutor.execute(TableItemExecutor.java:62)
at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplicateItemExecutor.execute(SuppressDuplicateItemExecutor.java:43)
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:46)
at org.eclipse.birt.report.engine.internal.executor.l18n.LocalizedReportItemExecutor.execute(LocalizedReportItemExecutor.java:34)
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:65)
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:92)
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:100)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:181)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:77)
at org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(ReportEngineService.java:937)
at org.eclipse.birt.report.service.BirtViewerReportService.runAndRenderReport(BirtViewerReportService.java:973)
at org.eclipse.birt.report.service.actionhandler.BirtRunAndRenderActionHandler.__execute(BirtRunAndRenderActionHandler.java:76)
at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:90)
at org.eclipse.birt.report.presentation.aggregation.layout.EngineFragment.doService(EngineFragment.java:318)
at org.eclipse.birt.report.presentation.aggregation.AbstractBaseFragment.service(AbstractBaseFragment.java:76)
at org.eclipse.birt.report.servlet.BirtEngineServlet.__doGet(BirtEngineServlet.java:116)
at org.eclipse.birt.report.servlet.BaseReportEngineServlet.doGet(BaseReportEngineServlet.java:185)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:735)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:848)
at org.eclipse.birt.report.servlet.BaseReportEngineServlet.service(BaseReportEngineServlet.java:116)
at org.eclipse.equinox.http.registry.internal.ServletManager$ServletWrapper.service(ServletManager.java:180)
at org.eclipse.equinox.http.servlet.internal.ServletRegistration.service(ServletRegistration.java:61)
at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:128)
at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:60)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:848)
at org.eclipse.equinox.http.jetty.internal.HttpServerManager$InternalHttpServiceServlet.service(HttpServerManager.java:386)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:669)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:457)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:229)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1075)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:384)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:193)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1009)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:135)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:116)
at org.eclipse.jetty.server.Server.handle(Server.java:368)
at org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:489)
at org.eclipse.jetty.server.AbstractHttpConnection.headerComplete(AbstractHttpConnection.java:942)
at org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.headerComplete(AbstractHttpConnection.java:1004)
at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:640)
at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:235)
at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:82)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:628)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:52)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:608)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:543)
at java.lang.Thread.run(Thread.java:662)
Caused by: org.eclipse.birt.data.engine.core.DataException: Fail to execute script in function __bm_beforeOpen(). Source:
------
" + queryText = "SELECT rsh.receipt_num";
queryText = queryText + " ,rsh.creation_date Receipt_date";
queryText = queryText + " ,ood.organization_code Inv_Org";
queryText = queryText + " ,rsl.to_subinventory Subinventory";
queryText = queryText + " ,ph.segment1 PO_NUM";
queryText = queryText + " ,msi.segment1 item_name";
queryText = queryText + " ,msi.description item_des";
queryText = queryText + " ,pdl.quantity_ordered Qty_Expected";
queryText = queryText + " ,NVL(rsl.quantity_received,0) Qty_Received";
queryText = queryText + " ,(pdl.quantity_ordered - rsl.quantity_received) bal_qty";
queryText = queryText + " ,rsl.shipment_line_status_code receiving_status";
queryText = queryText + "FROM po_headers_all ph";
queryText = queryText + " ,po_lines_all pll";
queryText = queryText + " ,po_distributions_all pdl";
queryText = queryText + " ,mtl_system_items_b msi";
queryText = queryText + " ,org_organization_definitions ood";
queryText = queryText + " ,rcv_shipment_lines rsl";
queryText = queryText + " ,rcv_shipment_headers rsh";
queryText = queryText + "WHERE ph.po_header_id = pll.po_header_id";
queryText = queryText + "AND pll.po_line_id = pdl.po_line_id";
queryText = queryText + "AND pll.item_id = msi.inventory_item_id";
queryText = queryText + "AND msi.organization_id = pdl.destination_organization_id";
queryText = queryText + "AND msi.organization_id = ood.organization_id";
queryText = queryText + "AND pll.po_line_id = rsl.po_line_id(+)";
queryText = queryText + "AND rsl.shipment_header_id = rsh.shipment_header_id(+)";


if (params["P_RCT_MUM"].value != null && params["P_RCT_NUM"].value.length > 0) {
queryText = queryText + " AND rsh.receipt_num = '" + params["P_RCT_NUM"].value + "'";
}
if (params["P_PO_MUM"].value != null && params["P_PO_NUM"].value.length > 0) {
queryText = queryText + " AND ph.segment1 = '" + params["P_PO_NUM"].value + "'";
} + "
-----
A BIRT exception occurred. See next exception for more information.
Report parameter "P_RCT_MUM" does not exist.
at org.eclipse.birt.data.engine.script.JSMethodRunner.runScript(JSMethodRunner.java:147)
at org.eclipse.birt.report.engine.script.internal.DtEScriptExecutor.handleJS(DtEScriptExecutor.java:90)
... 62 more
Caused by: org.eclipse.birt.data.engine.core.DataException: A BIRT exception occurred. See next exception for more information.
Report parameter "P_RCT_MUM" does not exist.
at org.eclipse.birt.data.engine.core.DataException.wrap(DataException.java:123)
at org.eclipse.birt.data.engine.script.ScriptEvalUtil.evaluateJSAsExpr(ScriptEvalUtil.java:997)
at org.eclipse.birt.data.engine.script.JSMethodRunner.runScript(JSMethodRunner.java:138)
... 63 more
Caused by: org.eclipse.birt.core.exception.CoreException: Report parameter "P_RCT_MUM" does not exist.
at org.eclipse.birt.core.script.JavascriptEvalUtil.wrapRhinoException(JavascriptEvalUtil.java:307)
at org.eclipse.birt.core.script.JavascriptEvalUtil.evaluateRawScript(JavascriptEvalUtil.java:106)
at org.eclipse.birt.core.script.JavascriptEvalUtil.evaluateScript(JavascriptEvalUtil.java:138)
at org.eclipse.birt.data.engine.script.ScriptEvalUtil.evaluateJSAsExpr(ScriptEvalUtil.java:993)
... 64 more
Caused by: org.mozilla.javascript.JavaScriptException: Report parameter "P_RCT_MUM" does not exist.
at org.eclipse.birt.core.script.ScriptableParameters.get(ScriptableParameters.java:41)
at org.mozilla.javascript.ScriptableObject.getProperty(ScriptableObject.java:1617)
at org.mozilla.javascript.ScriptRuntime.getObjectElem(ScriptRuntime.java:1390)
at org.mozilla.javascript.ScriptRuntime.getObjectElem(ScriptRuntime.java:1372)
at org.mozilla.javascript.gen.c122._c1(/report/data-sets/oda-data-set[@id="2792"]/method[@name="beforeOpen"]:28)
at org.mozilla.javascript.gen.c122.call(/report/data-sets/oda-data-set[@id="2792"]/method[@name="beforeOpen"])
at org.mozilla.javascript.optimizer.OptRuntime.callName0(OptRuntime.java:108)
at org.mozilla.javascript.gen.c7._c0(/report/data-sets/oda-data-set[@id="2792"]/method[@name="beforeOpen"]:0)
at org.mozilla.javascript.gen.c7.call(/report/data-sets/oda-data-set[@id="2792"]/method[@name="beforeOpen"])
at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:398)
at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3065)
at org.mozilla.javascript.gen.c7.call(/report/data-sets/oda-data-set[@id="2792"]/method[@name="beforeOpen"])
at org.mozilla.javascript.gen.c7.exec(/report/data-sets/oda-data-set[@id="2792"]/method[@name="beforeOpen"])
at org.eclipse.birt.core.script.JavascriptEvalUtil.evaluateRawScript(JavascriptEvalUtil.java:99)
... 66 more



Table (id = 2794):
+ Cannot get the result set metadata.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:ORA-00923: FROM keyword not found where expected

Please suggest me.
Thanks & Regards.


Thanks,
JP
Re: BeforeOpen Script Error [message #1784530 is a reply to message #1783012] Thu, 29 March 2018 03:34 Go to previous message
Ali Raza is currently offline Ali RazaFriend
Messages: 5
Registered: March 2018
Junior Member
Have you found any solution to this issue? I am interested to know as well please
Previous Topic:scripted datasource fetch metod exception
Next Topic:Color chart series based on another series value
Goto Forum:
  


Current Time: Mon Jun 25 02:49:06 GMT 2018

Powered by FUDForum. Page generated in 0.01753 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top