Dynamic query program not working

hamsa

Hi,

I am getting this error in program. Pls help me


Table table1:
+ Can not load the report query: 407. Errors occurred when generating the report document for the report element with ID 407. (Element ID:407)
Error.ReportQueryLoadingError2 ( 1 time(s) )
detail : org.eclipse.birt.report.engine.api.EngineException: Can not load the report query: 407. Errors occurred when generating the report document for the report element with ID 407. (Element ID:407)
at org.eclipse.birt.report.engine.data.dte.DataPresentationEngine.doExecuteQuery(DataPresentationEngine.java:164)
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:267)
at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1939)
at org.eclipse.birt.report.engine.internal.document.v4.ReportItemExecutor.executeQuery(ReportItemExecutor.java:412)
at org.eclipse.birt.report.engine.internal.document.v4.TableItemExecutor.doExecute(TableItemExecutor.java:72)
at org.eclipse.birt.report.engine.internal.document.v4.ReportItemExecutor.execute(ReportItemExecutor.java:294)
at org.eclipse.birt.report.engine.internal.document.v4.ContainerExecutor.prepareChildExecutor(ContainerExecutor.java:226)
at org.eclipse.birt.report.engine.internal.document.v4.ContainerExecutor.hasNextChild(ContainerExecutor.java:110)
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.hasNextChild(WrappedReportItemExecutor.java:86)
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.hasNextChild(WrappedReportItemExecutor.java:86)
at org.eclipse.birt.report.engine.api.impl.RenderTask$ReportExecutorWrapper.hasNextChild(RenderTask.java:780)
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM$1.hasNextChild(HTMLPageLM.java:68)
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:62)
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.RenderTask$PageRangeRender.render(RenderTask.java:661)
at org.eclipse.birt.report.engine.api.impl.RenderTask.render(RenderTask.java:294)
at org.eclipse.birt.report.service.ReportEngineService.renderReport(ReportEngineService.java:1555)
at org.eclipse.birt.report.service.BirtViewerReportService.getPage(BirtViewerReportService.java:204)
at org.eclipse.birt.report.service.actionhandler.AbstractGetPageActionHandler.doExecution(AbstractGetPageActionHandler.java:238)
at org.eclipse.birt.report.service.actionhandler.AbstractGetPageActionHandler.__execute(AbstractGetPageActionHandler.java:105)
at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:90)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseDocumentProcessor.__executeAction(AbstractBaseDocumentProcessor.java:47)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.executeAction(AbstractBaseComponentProcessor.java:143)
at org.eclipse.birt.report.soapengine.processor.BirtDocumentProcessor.handleGetPage(BirtDocumentProcessor.java:87)
at sun.reflect.GeneratedMethodAccessor104.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.process(AbstractBaseComponentProcessor.java:112)
at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingImpl.getUpdatedObjects(BirtSoapBindingImpl.java:66)
at sun.reflect.GeneratedMethodAccessor103.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397)
at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186)
at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323)
at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)
at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)
at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)
at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454)
at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281)
at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doPost(BirtSoapMessageDispatcherServlet.java:265)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:122)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.eclipse.birt.report.filter.ViewerFilter.doFilter(ViewerFilter.java:68)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:987)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:579)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:309)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:662)

hamsa

This is my program. Pls help me if you find errors on it.



import java.io.IOException;
import java.util.ArrayList;

import org.eclipse.birt.core.framework.Platform;
import org.eclipse.birt.report.model.api.CellHandle;
import org.eclipse.birt.report.model.api.DataItemHandle;
import org.eclipse.birt.report.model.api.DataSourceHandle;
import org.eclipse.birt.report.model.api.DesignConfig;
import org.eclipse.birt.report.model.api.ElementFactory;
import org.eclipse.birt.report.model.api.IDesignEngine;
import org.eclipse.birt.report.model.api.IDesignEngineFactory;
import org.eclipse.birt.report.model.api.LabelHandle;
import org.eclipse.birt.report.model.api.OdaDataSetHandle;
import org.eclipse.birt.report.model.api.OdaDataSourceHandle;
import org.eclipse.birt.report.model.api.ScriptDataSetHandle;

import org.eclipse.birt.report.model.api.PropertyHandle;
import org.eclipse.birt.report.model.api.ReportDesignHandle;
import org.eclipse.birt.report.model.api.RowHandle;
import org.eclipse.birt.report.model.api.SessionHandle;
import org.eclipse.birt.report.model.api.StructureFactory;
import org.eclipse.birt.report.model.api.TableHandle;
import org.eclipse.birt.report.model.api.activity.SemanticException;
import org.eclipse.birt.report.model.api.elements.structures.ColumnHint;
import org.eclipse.birt.report.model.api.elements.structures.ComputedColumn;
import org.eclipse.birt.report.model.api.elements.structures.ResultSetColumn;
import org.eclipse.birt.report.model.elements.interfaces.IDataSetModel;

import com.ibm.icu.util.ULocale;

/**
* Dynamic Table BIRT Design Engine API (DEAPI) demo.
*/

public class DynamicTableBirt
{
ReportDesignHandle designHandle = null;
ElementFactory designFactory = null;
StructureFactory structFactory = null;
String jobName;
String jobGroup;
public static void main( String[] args )
{
try
{
DynamicTableBirt de = new DynamicTableBirt();
ArrayList al = new ArrayList();
al.add("JOB_NAME");
al.add("JOB_GROUP");
de.buildReport(al, "select * from appim.sched.QRTZ_JOB_DETAILS" );
}
catch ( IOException e )
{
// TODO Auto-generated catch block
e.printStackTrace();
}
catch ( SemanticException e )
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}



void buildDataSource( ) throws SemanticException
{

OdaDataSourceHandle dsHandle = designFactory.newOdaDataSource(
"Data Source", "org.eclipse.birt.report.data.oda.jdbc" );
dsHandle.setProperty( "odaDriverClass",
"com.microsoft.sqlserver.jdbc.SQLServerDriver" );
dsHandle.setProperty( "odaURL", "jdbc:sqlserver://msakedev:1433" );
dsHandle.setProperty( "odaUser", "SYS_ADMIN" );
dsHandle.setProperty( "odaPassword", "getin" );

designHandle.getDataSources( ).add( dsHandle );

}

void buildDataSet(ArrayList cols, String fromClause ) throws SemanticException
{

OdaDataSetHandle dsHandle = designFactory.newOdaDataSet( "ds",
"org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" );
dsHandle.setDataSource( "Data Source" );
String qry = "Select ";
for( int i=0; i < cols.size(); i++){
qry += " " + cols.get(i);
if( i != (cols.size() -1) ){
qry += ",";
}

}
qry += " " + fromClause;

dsHandle.setQueryText( qry );

designHandle.getDataSets( ).add( dsHandle );





}
void buildReport(ArrayList cols, String fromClause ) throws IOException, SemanticException
{


//Configure the Engine and start the Platform
DesignConfig config = new DesignConfig( );

config.setProperty("BIRT_HOME", "C:/techstack/birt-runtime-4_2_0/birt-runtime-4_2_0/ReportEngine");

IDesignEngine engine = null;
try{


Platform.startup( config );
IDesignEngineFactory factory = (IDesignEngineFactory) Platform.createFactoryObject( IDesignEngineFactory.EXTENSION_DESIGN_ENGINE_FACTORY );
engine = factory.createDesignEngine( config );

}catch( Exception ex){
ex.printStackTrace();
}


SessionHandle session = engine.newSessionHandle( ULocale.ENGLISH ) ;



try{
//open a design or a template

designHandle = session.openDesign("C:/work/workspace/TestReport/test.rptdesign");

designFactory = designHandle.getElementFactory( );
//designHandle.
// buildDataSource();
// buildDataSet(cols, fromClause);


designFactory.newDataItem(fromClause);
//class ct = designFactory.getClass();
// designFactory.newTableColumn().add(fromClause, , 6);
TableHandle table = designFactory.newTableItem( "table", cols.size() );
table.setWidth( "100%" );
table.setDataSet( designHandle.findDataSet( "Data Set1" ) );


PropertyHandle computedSet = table.getColumnBindings( );
ComputedColumn cs1 = null;

for( int i=0; i < cols.size(); i++){
cs1 = StructureFactory.createComputedColumn();
cs1.setName((String)cols.get(i));
cs1.setExpression("dataSetRow[\"" + (String)cols.get(i) + "\"]");
computedSet.addItem(cs1);
}


// table header
RowHandle tableheader = (RowHandle) table.getHeader( ).get( 0 );


for( int i=0; i < cols.size(); i++){
LabelHandle label1 = designFactory.newLabel( (String)cols.get(i) );
label1.setText((String)cols.get(i));
CellHandle cell = (CellHandle) tableheader.getCells( ).get( i );
cell.getContent( ).add( label1 );
}

// table detail
RowHandle tabledetail = (RowHandle) table.getDetail( ).get( 0 );
for( int i=0; i < cols.size(); i++){
CellHandle cell = (CellHandle) tabledetail.getCells( ).get( i );
DataItemHandle data = designFactory.newDataItem( "data_"+(String)cols.get(i) );
data.setResultSetColumn( (String)cols.get(i));
cell.getContent( ).add( data );
}

designHandle.getBody( ).add( table );

// Save the design and close it.

designHandle.saveAs( "C:/work/workspace/TestReport/test1.rptdesign" ); //$NON-NLS-1$


designHandle.close( );
System.out.println("Finished");
}catch (Exception e){
e.printStackTrace();
}



}
}