How to call postgreSQL function in jasper Report

[sandhiya]

Hi All,

Is it possible for me to call a postgreSQL function in my  jrxml file instead of directly passing the SQL query??
I want to do is to run a Postgresql  function in my jrxml file and the function returns a cursor back.

I’m using Ireport version 4.0.2 with postgreSQL 8.2. what would the syntax be to run the query from jasper?

When I call the query like this in Report Query

---------------------------------------

SELECT my_own_postgresql_function  (list of parameters)

create or replace my_own_postgresql_function  RETURNS refcursor AS '

DECLARE

// my function goes here

END ;'

LANGUAGE 'plpgsql';

It throws error

1.Error filling print... Error executing SQL statement for : my_own_postgresql_function 
2.Caused by: org.postgresql.util.PSQLException: Multiple ResultSets were returned by the query

-------------------------------------------------------------------------------

Error filling print... Error executing SQL statement for : my_own_postgresql_function 
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : TestReprotUsingFunctionCall      at com.jaspersoft.jrx.query.PlSqlQueryExecuter.createDatasource(PlSqlQueryExecuter.java:143)      at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:684)      at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:605)      at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1281)      at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:901)      at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:845)      at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:58)      at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:417)      at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:247)      at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:878)      at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572)      at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997)  Caused by: org.postgresql.util.PSQLException: Multiple ResultSets were returned by the query.      at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:259)      at com.jaspersoft.jrx.query.PlSqlQueryExecuter.createDatasource(PlSqlQueryExecuter.java:136)      ... 11 more 
Print not filled. Try to use an EmptyDataSource...
 ---------------------------------------------------------------------

 

[zishanali]

 Hi,

did you solved it, I am getting the same error.

let me know please.

regards

zk