calling postgresql stored procedure gives error

[zishanali]

 Hi,

{call show_names($P{sptest})}

calling the above stored procedure in report query but getting the following error.

Error:SQL PROBLEMS: ERROR: Syntax error at or near "{"

dont know what is the problem. 

below is my stored procedure which i am calling

create or replace function show_names(ref refcursor) returns refcursor as $$

begin

 open ref for select id,name,address from oba_emp;

 return ref;

end;

$$ language plpgsql;

find attached the snip for help.

above stored procedure is working fine in pgadmin but not when I am calling using ireport. 

any help is appreciated.

 

zk

 

 

[zishanali]

 plz help any one

 

zk

[ankushchhabra]

Change the type of sptest parameter from String to java.sql.ResultSet and uncheck the Prompt property of sptest parameter.

 

Kindly Refer the following link

 

http://meezageekyside.blogspot.com/2012/04/jasper-reports-ireport-45-using-oracle.html

 

Regards,

Ankush

 

[zishanali]

Hi Ankush,

 sorry, i am using postgresql and ireport, I am new to functions as well as ireport. 

i will tell yu what I want to achieve, 

I created a function in postgresql by the name show_names(ref refcursor), here is the code

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

create or replace function show_names(ref refcursor) returns refcursor as $$

begin

 open ref for 

 select id,name from oba_emp;

 return ref;

end;

$$ language plpgsql;

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

now, i have to call this function in ireport - what I did to test the bare bones,

In a brand new ireport, I opened the Report Query and type below:

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

select show_names('you');

fetch all from you

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

when I am pressing the button Read Fields it's giving the error: Multiple Resultsets returned by the query. I dont know how to handle multiple resultset in ireport. or may be I am writing the query wrong. 

to sum up:

I have to call function's of postgresql in ireport and roaming in the dark.

reg.

zk