Hello,
We are currently porting to Postgres from SQL Server and are experiencing problems with ADO and returning refcursors. We currently use the parameterized ADO methodology to return our data from procedures in the database. After researching and converting over our database to postgres, we are trying to re-use as much of our logic as we can. As currently configured, we return a refcursor as an out parameter from the procedure, and try to return the data into an ADO resultset. From further research, we found methodology to call our procedure and return the cursor, and then fetch the results into a resultset. Here is the current code:
Procedure:
CREATE OR REPLACE FUNCTION usp_commhost_get(IN sdomain "varchar", OUT swv_refcur refcursor) AS
$BODY$
BEGIN
-- GET SUBDOMAIN COMMUNITY DETAILS
open swv_RefCur for SELECT C.ccPublicCommID FROM tbl_Comm C WHERE(LOWER(C.ccDomain) = LOWER(sdomain) OR C.ccDomain = 'change_me') AND(C.ccStatusID = 1);
RETURN;
END; $BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION usp_commhost_get(IN sdomain "varchar", OUT swv_refcur refcursor) OWNER TO postgres;
ASP code
<!--#include virtual="/global/constants.html"-->
<html>
<head></head>
<body>
<%
dim conn, rst, cmd
set conn = server.createobject("ADOdb.connection")
conn.open "Data Source=pssam1;location=Connect3_0;UserID=username;password=password;"
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "select usp_commhost_get('refcur', 'localhost'); FETCH ALL IN refcur;"
set rst = server.createobject("ADOdb.recordset")
Set rst= cmd.Execute
do until rst.eof
response.write Server.HTMLEncode(rst.fields(0))&"<BR>"
rst.movenext
loop
rst.Close
conn.Close
%>
</body>
</html>
We would like to convert this into a parameterized call to the procedure, and not have to stipulate the cursor name to retrieve the data. Is this possible? And if so, what is the proper format? Thank you for any help in advance.
Sam Thukral
Centercode