ADO Ref cursor return

This is a multi-part message in MIME format.

------=_NextPart_000_0080_01C65D88.B0687910
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

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











<%

dim conn, rst, cmd



set conn = server.createobject("ADOdb.connection")

conn.open "Data
Source=pssam1;location=Connect3_0;UserID=username;password=p assword;"

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))&"
"

rst.movenext

loop

rst.Close

conn.Close



%>







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










------=_NextPart_000_0080_01C65D88.B0687910
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:st1=3D"urn:schemas-microsoft-com:office:smarttags" =
xmlns=3D"http://www.w3.org/TR/REC-html40">


charset=3Dus-ascii">

namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"
name=3D"place" downloadurl=3D"http://www.5iantlavalamp.com/"/>
namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags"
name=3D"City" =
downloadurl=3D"http://www.5iamas-microsoft-com:office:smartt ags"/>