Insert Query

[dre-day]

I cannot get  IReport to commit after an insert query. Are insert queries possible in IReport?

INSERT INTO TEMP_TABLE (BILL_ACCT_NBR, ORDER)SELECT  ltrim('$P!{temp}','and bill_acct_nbr = '),order FROM ORDERS  WHERE 1=1  $P!{order};

COMMIT

 

[cbarlow3]

No, no, no, no, no.  Only SELECT.  It's intended to be a read-only product.

Carl

P.S.: I'm going to have nightmares tonight about our clients updating their database through report design/running.

[dre-day]

I'm aware that its meant as a read-only product. But I really need to insert a record when the report is ran. Is there another way this can be achived?

 

 

 

Post Edited by dre31186 at 03/07/2011 19:55

[Gaby38]

 I succeeded to run insert query in a Jasperreport by running this insert query inside a stored procedure/function.

For instance you define a stored function which runs your insert query and after returns a value (no matters : logicaj, integer,...) and you can get this value in a field even if you don't care but jasper is happy and won't give an error.

It was just a test and I never used that in production.

(In fact, I wanted to update dynamically the list of recipients for a scheduled job by inserting their id in the right jasperdatabase table. But perhaps is it a little crazy ..!!)

PS: just a precision my test was running under jasperserver and I didnot use a commit statement : but the inserted values were present inside the table.

Post Edited by Gaby38 at 03/08/2011 19:36

[dre-day]

I attempted to do the same thing by running a stored procedure, but Im usure on how to pass parameters from ireport to the procedure.

[raylee]

Parameters are used the same whether it's stored proc or a query:

stored_proc($P{parameter})

Post Edited by raylee at 02/24/2012 21:52

[beacon_bharg]

Hey... there is a surprise. /sites/all/libraries/ckeditor/plugins/smiley/images/heart.png

 

[beacon_bharg]

Hey... there is a surprise.

Yes you cannot use insert/update queries as functionality. but you can run insert/update queries easily. you need not to put "commit" after query. The jasper will through error at the time of execution, but the query will be executed for sure. This is how I did it.

1. I have added an addional dataset where the insert statement is written.
2. Inserted a list/table in report pointing to the newly created dataset.
3. Done.
4. The jrxml will complile without any error.
5. While execution you will get error as "Caused by: net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.JRException: Unable to get next record from result set." Dont worry.

PS. I have successfully done this for number of times.