MySQL stored procedure

[jmolina]

HI everyone,

Using, iReport 3.0, Im trying to create a graph by calling a stored procedure from a mysql database and everything seems ok since the fields appear after I write the query but when I execute my graph, I get an error saying, "category series name is null"

when I call the stored procedure on the database side, it works fine but through iReport it seems fine but it is not.

First off, does iReport even support mysql stored procedures?? And if it does, then what am I doing wrong??

[dnvsrikanth]

hi jmolina,

 

First of all, JasperReports doesn't support SPs(Stored Procedures). But there are some alternatives that we can call stored procedures and generate reports.

 

And the error you are mentioning - "category series name is null" will occur when we doesn't get any data from the SP to fill the graph. Check the SP once.

 

DNV Srikanth.

[mdahlman]

Can I use a stored procedure with JasperReports (and JasperServer)?

Yes.

But it's more complicated than just "Yes". As noted in the JR Definitive Guide, "JasperReports uses a java.sql.PreparedStatement behind the scenes to execute that SQL query through JDBC and retrieve a java.sql.ResultSet object to use for report filling." Therefore a stored procedure can be used, but the following conditions follow from the previous statement:

- "The stored procedure must return a java.sql.ResultSet when called through JDBC."
- "The stored procedure cannot have OUT parameters."

Most stored procedures follow these conditions. So you can write your SQL query as a single stored procedure call, and everything will just work. The stored procedure can take arguments which you pass in using JR parameters. MySQL should be fine. MS SQL Server should be fine. Etc.

The famous exception is Oracle. Oracle stored procedures require an OUT parameter, so the standard JR syntax can't be used. In this case you need a custom query executor. Fortunately Barry Klawans already wrote a JRQueryExecuter for use with Oracle Stored Procedures. It's available in the project OracleStoredProcedures:

http://jasperforge.org/plugins/project/project_home.php?group_id=173

It's likely that your Stored Procedure is working well, but some detail is incorrect in the definition of the chart.

Regards,
Matt