Wrapping a Report in a Database Transaction?

[richbl]

Hello All,

 

I have a question that relates to database transaction processing in JasperReports Server (5.6).

 

• I have a report that contains three SQL queries (in this case MS SQL through JDBC): one master query and two chart queries. 
  
• I want to make sure that all queries are operating against the same database instance. That is, if the database is getting updated at the moment that these three queries are getting made, I want to make sure there is consistency in the data across all queries. 
  

My current understanding is that all queries are automatically wrapped in a database transaction (e.g., BEGIN TRAN | TRANSACTION). However, this could mean that, in the example above, the database could get updated at any point between the three discrete report queries.

 

So, my question is this:

 

How can I "wrap" my report--at the report level--in a database transaction such that there is data integrity between ALL queries at the report level?

 

Thanks much,

 

Rich

 

[hozawa]

Just use one sql with union of 3 sql statements.

[richbl]

Thanks very much for your quick response.

 

I had considered a master UNION of multiple SQL queries but there are numerous consequences with this approach:

 

A. Subreports can contain [n] queries, so the approach to UNION is practically limited to only a very few aggregate queries. You can imagine if a user performed a UNION on 5+ queries, this could very quickly get burdensome (not to mention that a report that becomes a child of a parent report would have to be rewritten to account for this approach).

 

B. As you might imagine, the UNION of discrete queries can (likely) result in heterogeneous resultsets: while one resultset might return three columns, another might return five columns. This ragged resultset would mean selective null column padding.

 

C. Any chart or component dependent on the resulting query UNION must now filter for a specific resultset (e.g., select on specific rows/columns, work around null column padding, etc.).

 

D. The value of the "dataset object" in JS Studio beyond the primary (main) dataset is put into question.

 

E. At a more abstract level, performing a query UNION breaks the data encapsulation afforded dataset objects (see above).

 

While I fully appreciate and understand the value of your response, I wonder if such a solution is worth the cost to implement.

 

Can you confirm that JS reports don't natively provide a means for treating a report atomically as a single transaction, and that instead, transactions are managed only at the query level?

 

Or, is there something perhaps at a lower level (read: not readily obvious within JS) that can be done to mitigate this issue?

 

My concern at the highest level is that a JS user must assume at all times that the underlying database does not change when running a report containing >1 query.

 

Else, they face the very real possibility that query results are not correlated, resulting in what might be considered to be a faulty report (rather, a successfully run report, but predicated on bad data).

 

I'm quite sure I may be missing some big picture element.

 

Thanks again for your insight and help in this matter.