Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have a function(stored procedure) which returns refcursor from postgresql function

I want to fill classic asp recordset from the result of refcursor.

PGSQL - Function
SQL
CREATE OR REPLACE FUNCTION getlisttest(IN i_list text, retcur refcursor)
  RETURNS refcursor AS
$BODY$
BEGIN
	
    IF i_list = 'pli' THEN

	open retcur for SELECT
            provider_name AS name, provider_id AS id
            FROM qf.rec_provider
            ORDER BY provider_name;

           return retcur; 
    
    ELSE
        RAISE USING ERRCODE := '20001', MESSAGE := CONCAT_WS('', 'Listname is invalid:', i_list);
    END IF;
 
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;



If I execute above SP using PGAdmin console it gives me actual result which I need.

select getlisttest('pli');
fetch all in "<unnamed portal 1>";



But when I try to access it from classic asp code it is not returning any rows.

Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open "Driver=**************;"
    set cmd = Server.CreateObject("ADODB.Command")
    set cmd.ActiveConnection = Conn
	'cmd.CommandType = adCmdText
	cmd.CommandType = adCmdStoredProc
	Dim SQL
	
	SQL = "fida.getlisttest('pli','refcur');"
	'SQL = "select fida.getlisttest('pli'); 
	Response.write SQL
	cmd.CommandText = SQL

	Set rsGetHireID = Server.CreateObject("ADODB.RecordSet")
	set rsGetHireID = cmd.execute
	
	do until rsGetHireID.eof
		response.write ("<br/>"&rsGetHireID.fields(0).Name &"--"&rsGetHireID(0).value)
		rsGetHireID.movenext
	loop

	rsGetHireID.Close


Help will be much appreciated...!!!

What I have tried:

I already googled many websites but no luck..:)
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900