如何从返回游标的经典ASP调用函数 [英] How to call function from classic ASP which returns cursor

问题描述

我有一个从postgresql函数返回refcursor的函数（存储过程）



我想从refcursor的结果填充经典的asp记录集。



PGSQL - 功能

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;

如果我使用PGAdmin控制台执行SP，它会给出我需要的实际结果。

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>";

但是当我尝试从经典的asp代码访问它，它没有返回任何行。

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

推荐答案

BODY

BEGIN

IF i_list = ' pli' 那么

打开 retcur for SELECT
provider_name AS 名称，provider_id AS id
FROM qf.rec_provider
ORDER BY provider_name;

return retcur;

ELSE
RAISE 使用 ERRCODE：= ' 20001'，MESSAGE：= CONCAT_WS（' '，' 列表名无效：'，i_list ）;
END IF ;

END ;

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

这篇关于如何从返回游标的经典ASP调用函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！