Report Developed using Stored function with parameters in postgresql database is not working in jasper server professinal edition

[kcsekhar25]

Hi,

i hav egenerated a report in ireport professional 5.1.0 with query as below

select * from stored_function($P{parameter1},$P{parameter2},$P{parameter3});

stored function is written in postgresql database.

well the report works very fine in ireport pro but not in jasper server professinal 5.1.0,throwing fill data error

corrected procedure but now throwing 6632 error and tried all possible solutions but not helping me to resolve the issue. anything wrong with stored function usgae in jasper server and anything needs to be confugured in jasper server. changed security-config and validation properties files but not getting closer to solve the issue. i am getting dried up with this,as i am finding it awkward because jasper server should be supportable in promoting the reportis working in ireport. Please let me know your thoughts on this.

attached the report for your reference and the screenshot of the error.

Thanks much

[kcsekhar25]

Hi all,

my issue got resolved and the solution is below.

 

use jndi datasource when working with reports using stored functions in jasper server.

create a jndi datasource in jasper server with the following configuaration files needs to be chnaged according to below.

before adding these in configuaration files please make sure your jasper server stopped.once changes are done restart the jasper server and create a the jndi data source in jasper server and the datasource will be created without any problems.

i am providing jndi configuarations for postgresql database.

1) add the following in jasperreports-server-5.1/apache-tomcat/conf/Catalina/localhost/jasperserver-pro.xml file.

<Resource  name="jdbc/contract_port_api_dev22" auth="Container" type="javax.sql.DataSource"
        maxactive="100" maxidle="30" maxwait="10000"
        username="postgres" password="root"
        driverclassname="org.postgresql.Driver"
        validationquery="SELECT 1"
        testonborrow="true"  
        url="jdbc:postgresql://192.168.1.53:5432/contract_port_api_dev22?defaultReadOnly=false&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&autoReconnectForPools=true"/>

2) add following in the file jasperreports-server-5.1/apache-tomcat/webapps/jasperserver-pro/META-INF/context.xml

 <Resource  name="jdbc/contract_port_api_dev22" auth="Container" type="javax.sql.DataSource"
        maxactive="100" maxidle="30" maxwait="10000"
        username="postgres" password="root"
        driverclassname="org.postgresql.Driver"
        validationquery="SELECT 1"
        testonborrow="true"  
        url="jdbc:postgresql://192.168.1.53:5432/contract_port_api_dev22?defaultReadOnly=false&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&autoReconnectForPools=true"/>
 

 

3) add following in the file jasperreports-server-5.1/apache-tomcat/webapps/jasperserver-pro/WEB-INF/web.xml

 

<resource-ref>
    <description>Postgres database</description>
    <res-ref-name>jdbc/contract_port_api_dev22</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>

 

 

once done with above changes,restart the jasper server and createjndi datasource in the server using the service name mentioned in the web.xml that is in my case  jdbc/contract_port_api_dev22.