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
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..:)