Insert in a Report via Pentaho Report Designer

[malkorgan]

Dear Pentaho Community,
I'm assessing the Pentaho Report Designer for three weeks,
and i wonder if it is possible with the Pentaho Report Designer to create a Report to exploit the following behaviour:

1)Perform a SQL insert query:
insert into "TABLE_A" VALUES (1,'TEXT')
2)Consequently perform a select * SQL query in the same report:
Select * from "TABLE_A"


I'm using an Oracle DATASET with a User allowed to perform insert in the Oracle DB;
In my Database i've got a TABLE_A with consistent dataformat to the 1) insert instruction;
At the moment i'm able to create a report with only one between the 1) or the 2) query listed above;
If i use query 1) (i'm using filtering parameters too, in order to input the data) when i click "View Report" button, the tuple is inserted in my DB but no data are displayed.
If i use the query 2) i've got all the tuple displayed but no possibility to prior insert a tuple, within the same Report execution...

Is there any way to perform the insert prior the select in a Report? (e.g.: using a plsql script with both the istructions?).
I've also tried to use a subreport but without any effect.

Thanks in advance if any of you want to help me.
Best regards

[Taqua]

If you are using the Report designer to execute the "Insert" query, I would have expected it to fail. We use a part of the JDBC API that is limited to SELECT (and other read-only) queries. In theory, your JDBC driver should have blocked your attempt to make an INSERT. (But then again, you are using Oracle, so I don't expect them to follow any sort of standard.)

When we do write-back in a Web-App Scenario, we usually do it like this:

(1) insert sort of JavaScript into the report (html::append-raw attribute) that has a form or button that fires a HTTP-request to the server. This then inserts the record in the database.
(2) When successful, the script triggers a refresh of the current page / report so that the server produces the new report that now should show the updated values.


Within a single run, the reporting engine queries your data first and then assumes that the data does not change. In fact, if your data would change this could mess up the layout (as potentially there are now more or less pages to display) and that will result in hard errors.


You can always call stored-procedures (SP) on the server. As long as your SP returns a result-set, you should be fine. What you do before you return that result-set is up to you and can involve any operation your database allows. Inserting values should be possible - after all, it is common to build temp-tables and similar structures to speed up a query.