Can you use transactions in mysql query with Jasper?

[systemsam]

 I'm trying to run a report that uses the following query:

start transaction;
SET @variable = select statement;
Select stuff, including @variable as variable_name;
commit;

I'm 100% sure my queries are right - I've tested them plenty of times. It seems that Jaser isn't doing well with the multiple semi-colons or something. I get an error saying I have an error at line - and its the next line of the query. 

Can iReports/Jasper server run transactions? I need top be able to commit the top query once (for performance reasons) and then just return that value. I know I could make a sub report and return it that way - just seemed like this should work. 

Any thoughts? 

 

Sam

[mdahlman]

You can only have a single SQL statement as the SQL query. It gets executed as a single prepared statement.

What DBMS are you using? What does it mean to commit the query? Typically inserts and updates are committed as part of a transaction. Similarly what would it mean to rollback after a select?

As you suggested, perhaps there are other ways to achieve what you want.

Regards,
Matt

[jmurray]

Can you use transactions in mysql query with Jasper?  Yes you can, but as matt points out ,they are only useful if you are updating data.

JasperReports can only execute a single SQL statement in each report or subreport element.  So to execute a series of updates you would simply call a stored function if your dB supports them.

eg. SELECT myFunction() as myResult FROM DUAL;

You would perform this sort of thing if you were running a report where you need to source data from a temporary table or if you wanted to perform some sort of housekeeping or totalisation before filling the report.  In these instances you would call the function from a subreport in the header of the main report because it will be evaluated first.  The return value can be used to indicate sucess or failure, the number of rows updated or created, etc. and can therefore influence what is displayed on the main report.

Your function should handle all errors gracefully and return an appropriate value should an error occur.