Passing in query as a parameter

Is it possible to do a dynamic query in iReport so that the SQL is passed in at runtime instead of saving it in a jrxml file?  If so please demonstrate how.

So, I'm using iReport and trying to pass in a query dynamically at runtime as a parameter.
Rationale: The rationale in my case is that this query is used somewhere else in my java application so instead of hardcoding the same query I would rather just get it from java and pass it in as a parameter so I don't have to maintain two synced up copies.

However, even trying to pass in a simple query like "select dummy from dual" chokes.
JRXML looks like this

<parameter class="java.lang.String" name="dynamicQuery">
    <querystring language="SQL">
    <field class="java.lang.String" name="DUMMY"></field>

But when I do it I get an errror below.

Even when I do a simple test like select

Caused by: java.sql.SQLException: Invalid SQL type 
    at oracle.jdbc.driver.DatabaseError.throwSqlException(  
    at oracle.jdbc.driver.DatabaseError.throwSqlException(  
    at oracle.jdbc.driver.DatabaseError.throwSqlException(  
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(  
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(  
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(  
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(  
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(  
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(      ... 11 more 
	Print not filled. Try to use an EmptyDataSource...
markorocko's picture
Joined: Aug 16 2011 - 11:16am
Last seen: 9 years 11 months ago

3 Answers:

Removed language="SQL" attribute from the query string element and check it once..


rsuddhala's picture
Joined: Aug 16 2012 - 10:55pm
Last seen: 4 years 10 months ago


Try using the P!{} syntax rather than $P{}.  $P!{} allows you to enter dynamic SQL.  I just tested it for an entire query and it works for me.


kcollins's picture
Joined: Aug 30 2011 - 12:22pm
Last seen: 2 years 10 months ago

IS your problem solved. I am also getting same problem can you please post a answer.



srinuydlp87's picture
Joined: Aug 4 2014 - 12:26am
Last seen: 5 years 3 months ago