CallableStatement in JasperReports

[2004 IR Help]

By: Adeniran Fajemisin - afajem

CallableStatement in JasperReports

2005-05-03 09:00

Hi all ---

 

I know there's been a lot of questions concerning using Stored Procedure, but after searching the group for almost a full day, I still can find any definitive way of accomplishing this.

 

I'm not sure if anyone has ever used stored procedures with JasperReports before but when I dove into the code, I realized that in the JRBaseFiller and ultimately the JRQueryExecuter classes a PreparedStatement is being used to execute the queries.

 

I know if you want to execute a stored procedure, one needs to use a CallableStatement. Somehow, I'm not sure of how this will ever work without a change to the core of the JasperReport framework.

 

I am using Oracle Database 9i, the application is running within Struts and the web application resides in Websphere.

 

Any light that can be shed on this issue will be deeply appreciated. I'm sure there are lots of people out there that are struggling with the same issue.

 

Thanks all.

 

Niran

 

 

 

 

 

By: Juan Reyes - jreymol

RE: CallableStatement in JasperReports

2005-05-03 09:58

I think this will help you:

 

=========================================

String prm1 = "102%";

String prm2 = "Ser%";

 

CallableStatement cs = conexion.prepareCall ("{Call prcStoredProcedureTest(?,?)}",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);

cs.setString(1,prm1);

cs.setString(2,prm2);

ResultSet rs=cs.executeQuery();

 

JRDataSource dataSource = new JRResultSetDataSource(rs);

 

File reportFile = new File(application.getRealPath("reportlocation/myreport.jasper"));

 

Map parameters = new HashMap();

parameters.put("Parametro1", "valor");

 

 

byte[] bytes = JasperRunManager.runReportToPdf(reportFile.getPath(), parameters, dataSource);

 

=========================================

 

Just call stored procedure, get the recordset, transform it to JRDataSource and then JasperRunManager

 

 

 

 

By: Adeniran Fajemisin - afajem

RE: CallableStatement in JasperReports

2005-05-03 11:30

Thank you so much for your prompt response.

 

Just so that I understand, based on your reply, I will have to first execute the stored procedure and then pass the data source (with the prepared result set) as parameters to the Jasper Engine.

 

In otherword, there's no way of simply issuing a statement: {call myProcedure(?,?)...} which calls a stored procedure directly in the <queryString> tag of the JasperReports xml file?

 

Thanks,

Niran

 

 

 

 

By: Juan Reyes - jreymol

RE: CallableStatement in JasperReports

2005-05-03 11:38

I don't know if there is another way. I just succeeded this morning in calling a stored procedure this way and I wanted to share with you my happiness :-).

 

If you succeed the other way, let me know

 

 

 

 

By: Teodor Danciu - teodord

RE: CallableStatement in JasperReports

2005-05-04 23:57

 

Hi,

 

You can put the stored procedure call inside the

<queryString> only if that store procedure does not have

OUT parameters and returns a ResultSet.

That means it is a stored procedure that can be called

using a PreparedStatement and not necessarily

a CallableStatement.

 

I hope this helps.

Teodor

[prasad_psl]

Hi,

I am also want to call the stored procedure .. but i dont know where i should create the callable statment for ireport.

You mentioned above how to write the callable statment .But where i can write this statement.

It will be good if you please explain in the step by step ,that how one can use the stored procedure in ireport..

 

Thanks

Prasad

[dnvsrikanth]

prasad_psl
Wrote:

Hi, I am also want to call the stored procedure .. but i dont know where i should create the callable statment for ireport. You mentioned above how to write the callable statment .But where i can write this statement. It will be good if you please explain in the step by step ,that how one can use the stored procedure in ireport..   Thanks Prasad

Hi

 

If you want to call stored procedures and use Callable Statements you need to use it in the Java generator class where in the jrxml file is being compiled, filled and displayed onto the screen or printed.

 

DNV Srikanth.

[prasad_psl]

Hi,

Thanks for the help.. Because of your reply i find the way how these procedures will be call.I am still exploring the things but you show the direction where to search.

Thanks and Regards,

Prasad

[dnvsrikanth]

Hi,

 

Just pass the parameters to the SP in the same order as how they are declared in SP. Otherwise you will end up with lot of exceptions.

 

DNV Srikanth

[Teodor Danciu]

Hi,

 

When trying to use stored procedures with a report you find yourself in one of the following cases:

1. The stored procedure can be executed with PreparedStatement from JDBC. This means it does not have OUT parameters and returns a ResultSet.

In such case, there is nothing to do. It works simply by calling the stored procedure in the <queryString>. Calling stored procedure syntax is different depending on the server. You might need to write "exec mySP" or "call mySP" or simply write the name of the stored procedure in the query. Check the DB documentation.

 

2. The stored procedure has OUT parameters. This means it can only be called using CallableStatement from JDBC. In this case, because dealing with OUT parameters is something only YOU could do, knowing your stored procedure, you would have to write a custom query executer to execute those procedures and produce a JRDataSource for the report.

There are many report executer implementations in JR today, and you can look into the source code and see how you could create one. i suggest you look into the one that is for executing SQL.

 

If you are talking about Oracle stored procedures, there is a separate project here on the forge, that helps with that:

http://jasperforge.org/projects/oraclestoredprocedures

 

I hope this helps.

Teodor