calling stored procedure that input parameter

[pochi]

I could get the stored procedure work with one input parameter ( number ) to work in IReport but not in Jasper server.

I created an input control and gave the default expression as ${param}.

 

 

Jasper server throws the error message that the SQL statement is wrong-

 

java.lang.Exception: 1 - net.sf.jasperreports.engine.JRException: Error executing SQL statement for : FirstReport_jrxml_1176494122131 at com.jaspersoft.jasperserver.irplugin.wsclient.WSClient.runReport(WSClient.java:382) at com.jaspersoft.jasperserver.irplugin.wsclient.WSClient.runReport(WSClient.java:299) at com.jaspersoft.jasperserver.irplugin.ReportRunner.run(ReportRunner.java:73) at java.lang.Thread.run(Unknown Source)

 

 

 

 

Any help is really appreciated.

[Teodor Danciu]

Hi,

 

I don't see what you mean by: "I created an input control and gave the default expression as ${param}."

but seeing the JRXML might help.

 

Thanks,

Teodor

[pochi]

I think the problem is something else. we are using jdbc driver to connect to Sybase database. When we run the reports thru Ireports, we don't have any issues with calling stored procedures that have input parameters. But when we run the same report from jasperserver with the same jdbc driver, jasper server automatically puts the connection in transaction mode and throws errors that DDL's can not be created in this mode.

 

How do we set the connection autocommit property to true ?

[lucianc]

You should first check your JDBC driver's documentation to see whether it can create connections having autocommit set by default.

 

Current JS code does not set/unset the autocommit flag for connections created for JDBC data sources. If you need this, you can either post it as a feature request or implement it yourself by customizing the jdbcDataSourceServiceFactory bean found in applicationContext.xml.

 

One more alternative would be to define your JDBC data source as a container resource (for which you would specify defaultAutoCommit="false") and access it in JS via a JNDI data source.

 

HTH,

Lucian

[avu]

Hello,

 

Has anyone been successful in executing a report in JS/JI that calls a MySQL Store Procedure passing it parameters?

 

My report works for iReports but does not work in JS. I have created input controls that has the same name/datatype as the parameters expected in the store procedure.

 

I was able to setup input controls to pass into other SQL queries; however, not been able to pass parameters to a store procedure. Am I missing something here OR is this a BUG?

 

I have been tackling this issue for days, so if anyone has been success in passing PARAMETERS from into a store procedure in the Jasper Intelligence environment please let me know.

 

Many thanks in advance for your response/input.

 

Ann

[Teodor Danciu]

Hi,

 

If you say it worked in iReport, what is the error you get in JasperServer?

 

Thanks,

Teodor

[avu]

Thanks for responding :)

 

Please see error from jasperserver.log in the attachment --- basically the error only indicate that the dataset is empty and that it did not find the field it needs in the report.

 

Below is the Report Query in my report:

 

call reporting.sp_balance($P{SiteId}, $P{StartDate}, $P{EndDate});

 

NOTE: This works in iReport.

 

In addition, instead of passing the parameters into the store procedure I actually passed in values into the store procedure (i.e. call reporting.sp_balance(1, '2007-01-01', '2007-03-31'); ) this works. Which implies that Jasper Server can execute MySQL store procedures; however, did not allow parameters to be pass into a store procedure.

 

Hopefully this information is helpful and can help lead us to a solution. Many thanks in advance your you attention to this issue. Please let me know if you need any additonal information.

 

Ann

[Teodor Danciu]

Hi,

 

There's no attachment to your post.

 

Thanks,

Teodor

[avu]

oops -- let me try the attachment again.

[avu]

let me try it again ... it did not allow me to send a .log file??? [file name=storeProcError.txt size=4878]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/storeProcError.txt[/file]

[pochi]

Actually we were able to do it with Sybase ASE. The problem was not with the stored procedure call itself but with the way a connection was obtained. Somehow jdbc driver we used put the connection in a transaction mode ( autocommit=false). As our stored procedures create temp tables in temporary db heavily, this didnt fly.

Using jndi resource instead, we were able to set the autocommit to true and everything was ok. btw, we used procedures having parameters and Jasper server was fine with it as well.