Queries in JasperReports Other Than Select

[2006 IR Open Dicussion]

By: Tim Lawson - kruez

Queries in JasperReports Other Than Select

2006-01-24 17:35

Is it possible to use non-SELECT SQL statement in a JasperReport? I need to drop a temporary table if it exists, create a new temporary table, update that table, and then do a select on that updated temporary table. Is this maybe an enhancement down the line?

 

And let's say this ability did exist, could we then have multiple sql statements separated by semi-colons within a single report? Cuz I wouldn't mind that either.

[joelvm]

I'm having the same problem, i need to create a temp table and do a select on it, did you managed to do that!!

 

best regards

 

Joel Martins

[chill_work]

We needed to create a temp table as well. And what we end up doing was creating the temp table before we called the jasper report. We passed the same connection used for the temp table down to jasper. Then in the query of the report, this report would select against the temp table. I was able to do this in a report that had four subreports. Each of the subreport used the temp table. Hope this is helpful.

 

Calvin

[joelvm]

Thanks for your reply. That's an interesting solution, can you give me some details on who to pass a conection to jasper, because i've searched the docs and the net, but didn't find nothing?

 

Best Regards

[teodord]

Hi,

 

A more elegant solution would be to implement a custom query executer that would know it needs to play with temp tables before executing the actual query needed by the report.

Query executers are associated with query languages so you could even "invent" you own query language if you want to give your query executer more information about what it needs to do through the <queryString> content.

 

I hope this helps.

Teodor

[rowen]

Hi all,

Is there an elegant solution to handling temp tables in a queryString anywhere? Since I needed it I've writen a customized JRJdbcQueryExecuter which handles multiple SQL statements within the query string (parsed on semi-colon) which I can pass over if it would be useful. If there is an existing solution then I'd rather move to that instead though.

Neville

[lucianc]

There is no functionality built into JR to support this.  So, for now at least, you should continue using your custom solution.

Regards,

Lucian

[rowen]

Hi all,

In case it is useful to anyone else, my code is attached. Based on Teodor's comment earlier I guess there should be a way of integrating this properly into the distribution through the QueryExecuterFactory but I've not got time at the moment to investigate that....

Neville

 

[rowen]

Except I cannot work out how to see the attachment I put on so the appropriate method from JRJdbcQueryExecuter.java is in the code section below....

Neville

Code:

	public JRDataSource createDatasource() throws JRException {		JRDataSource dataSource = null;        try {    		String[] queryStrings = getQueryString().split(";");    		if (connection != null) {                for (int i=0; i<queryStrings.length; i++) {                    if (queryStrings[i] != null && queryStrings[i].trim().length() > 0) {                        statement = connection.prepareStatement(queryStrings[i]);                        int fetchSize = JRProperties.getIntegerProperty(dataset, JRJdbcQueryExecuterFactory.PROPERTY_JDBC_FETCH_SIZE, 0);                        if (fetchSize != 0) {                            statement.setFetchSize(fetchSize);                        }                        List parameterNames = getCollectedParameters();                        if (!parameterNames.isEmpty()) {                            for(int j=0, paramIdx = 1; j<parameterNames.size(); j++) {                                QueryParameter queryParameter = (QueryParameter) parameterNames.get(j);                                if (queryParameter.isMulti()) {                                    paramIdx += setStatementMultiParameters(paramIdx, queryParameter.getName());                                } else {                                    setStatementParameter(paramIdx, queryParameter.getName());                                    ++paramIdx;                                }                            }                        }                        if (statement != null) {                            Integer reportMaxCount = (Integer) getParameterValue(JRParameter.REPORT_MAX_COUNT);                            if (reportMaxCount != null) {                                statement.setMaxRows(reportMaxCount.intValue());                            }                            boolean results = statement.execute();                            int updateCount = -1;                            if (! results) {                                updateCount = statement.getUpdateCount();                            }                            boolean loopAround = results || (updateCount != -1);                            while (loopAround) {                                if (results) {                                    dataSource = new JRResultSetDataSource(statement.getResultSet());                                }                                results = statement.getMoreResults();                                if (! results) {                                    updateCount = statement.getUpdateCount();                                }                                loopAround = results || (updateCount != -1);                            }                        }                    }                }    		}        } catch (SQLException e) {            throw new JRException("Error executing SQL statement for : " + dataset.getName(), e);        }		return dataSource;	}

[chaddn]

I have this same need, to be able to pass multiple SQL statements for dropping/creating/modifying a temp table and then eventually selecting the data from the temp table for my JasperReport.  Can anyone tell me how I can implement the solution that Neville created?  I am using Jasper Reports 3.5.1..

Thanks,

Chadd

[TimInTX]

The secret appears to having SET NOCOUNT ON in the SQL Code.  You will get a little Red X next to this, but that does not matter.

SET NOCOUNT ON
SET ANSI_NULLS OFF
SET FMTONLY OFF

DECLARE @tblDomains Table
(
    DomainFK  int NOT NULL,
    Name  nvarchar(255) NULL,
    PRIMARY KEY (DomainFK)
)

INSERT INTO @tblDomains (DomainFK, Name)
SELECT OrgFK, Name FROM dimOrg
WHERE Code  = 'AAA' AND DomainInd = 1
--Main_Report_Query
SELECT * FROM @tblDomains

I am guessing that if this not on, the first SQL command that runs and returns something, i.e. number of rows impacted, Jaspersoft  sees that first response as the result of the query and thus responds with the “The Statement did not return a result set” because it did not, it just returned a count of impacted records.

What SET NOCOUNT ON means...
https://msdn.microsoft.com/en-us/library/ms189837.aspx

[antonio.crispin]

with SET NOCOUNT ON works for me, i use ireport 5.1.0

thanks TimInTX