MySQL stored procedure

2

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??

jmolina's picture
21
Joined: Jun 9 2009 - 2:03pm
Last seen: 10 years 1 month ago

2 Answers:

0

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.

dnvsrikanth's picture
Joined: Jul 22 2007 - 1:29pm
Last seen: 8 months 1 day ago
1

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

mdahlman's picture
8940
Joined: Mar 13 2007 - 2:43am
Last seen: 4 years 8 months ago
Feedback
randomness