Schedule Reports directly from the database?

[gillett.bob]

Hi

I am using Jasper Server 4.7.1 with an Oracle backend both for Jasper and my data.

I read with interest a previous post on here that hinted it was possible to schedule reports directly from the database, this is something I would like to do...  That question was:-

http://community.jaspersoft.com/questions/530058/multiple-report-execution-distincts-params

In the answer is the intriguing statement:

"if each report is required on individual files, then probably i would just do an update to the scheduler table and insert 99 more schedule with different paremeter"

My system does an initial ETL operation then refreshes several mviews, the reports read the mviews to get their data, then each report must run many times each time changing a parameter.  It would suit me very well if at the end of the mview refresh the PL/SQL code could then somehow prompt the server to schedule and run the reports.  I have looked at the tables in the JS schema that I think are involved in this but the parameters are strangely held in a blob field?  And they seem to be wrapped with some kind of non-printable header. 

Has anyone worked with them?  Or know how to populate these tables?  Or a resource where I may find the answers?

On the other hand, has anyone had any experience of calling the web services from PL/SQL?  While I have played PL/SQL and SQL for many years I have no experience with web services so any example code would be greatfully received!

Cheers

Bob

[marianol]

I will recomend against tinkering with the scheduler tables directly. There is a API that allows you to access the scheduler and will be less of a pain when you upgrade your jasper Server. You can schedule reports using the JasperServer REST or SOAP API, check the web services guide http://community.jaspersoft.com/documentation Chapter 3 fro REST and Chapter 5 for SOAP you can trigger those report schedules from your ETL Job.

Take a look at this http://www.youtube.com/watch?v=G0HuGxSROI4 I think it is along the lines of what you are looking for.

 

[marianol]

The video does not use the scheduler. Just executes the report using the tJasperServerExec component.

[g.den.breejen]

Hi,

 

In Oracle 11g it is possible to call the API directly from Oracle using UTL_HTTP.REQUEST and we are using that way to schedule reports from the database.

 

First we created an Access Control List (ACL) to enable Oracle to access the JasperReports server.

 

Then we created a PL/SQL package that set up the url with the parameters needed for calling the API and then perform a UTL_HTTP.REQUEST. The response is saved using "into" so a API call from PL/SQL will look like this:

 

SELECT UTL_HTTP.REQUEST(v_serveraddress || 'jasperserver-pro/services/ReportScheduler=' || Parameter List) INTO l_response FROM dual;

 

The response is checked for any error.

 

I hope this will help to interact with Jasperreports from Oracle db. Insert directly a scheduled job into the JS tables did not work: the job was never processed.

 

If you need further information, I can post some details and examples.

 

Best regards,

Guus den Breejen