Am trying to create a temp table and then selecting the data from this for my reports

[raghu176.dvg]

Hello Team,

 

Am trying to create a temp table and then selecting the data from this table for my reports - i have tried this SQL and it works as expected in Postregsql/Oracle DB but am unable to execute the same via "Dataset and Query Dialogue" editor 

for example :  

create temp table emp_test(
name varchar(100)
);  select * from emp_test;

Am getting error 

 

net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query:

create temp table emp_test(
name varchar(100)
);

select * from emp_test;

    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:545)
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:299)
    at com.jaspersoft.studio.data.jdbc.JDBCFieldsProvider.getFields(JDBCFieldsProvider.java:83)
    at com.jaspersoft.studio.data.jdbc.JDBCDataAdapterDescriptor.getFields(JDBCDataAdapterDescriptor.java:70)
    at com.jaspersoft.studio.property.dataset.dialog.DataQueryAdapters.doGetFields(DataQueryAdapters.java:525)
    at com.jaspersoft.studio.data.designer.AQueryDesignerContainer$1.run(AQueryDesignerContainer.java:49)
    at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:122)
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: SELECT : line: 5 in statement [create temp table emp_test(
name varchar(100)
);

select * from emp_test;
]
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
    at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:394)
    ... 6 more
Caused by: org.hsqldb.HsqlException: unexpected token: SELECT : line: 5
    at org.hsqldb.error.Error.parseError(Unknown Source)
    at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
    at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
    at org.hsqldb.Session.compileStatement(Unknown Source)
    at org.hsqldb.StatementManager.compile(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 9 more
 

IS this issue with Driver or Jasper doesn;t support or Config change is required or am i missing any required Drivers?

 

Any help is appreciated

 

Thanks

Raghavendra

[joseng62]

Hi raghu176.dvg
So I am not sure of your are able to run the sql create command on the jasperserver side. The jasperserver side runs sql protection , i.e. blocking the use of some sql keywords form being executed as it is a security concern. 

If you are able to run the create sql by updating the property files which on the jasperserver side handels the sql validation. Then you need to seperate the sql demand. You will need to use one of the components i.e. a list to link a dataset that executes the linked sql query. 
You will also need to ensure the component that executes  the create table is set in a band that only executes once. 

The other option you have is to create something like a store procedure which you create a temp table in the store procedure logic. 
Then from the temp table with another component 'subreport', 'table','list','crosstab' etc select from the temp. 

Possible just requires some correct sequence of execution of components. 





 

[raghu176.dvg]

Thank you..let me explore setting options you have mentioned...