ADO Ref cursor return

From: "Sam Thukral" <sam(at)centercode(dot)com>
To: <pgsql-odbc(at)postgresql(dot)org>
Subject: ADO Ref cursor return
Date: 2006-04-11 23:55:06
Message-ID: 443c4204.0d49a05d.69f8.ffff92bc@mx.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

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

Browse pgsql-odbc by date

  From Date Subject
Next Message setyawan 2006-04-12 00:15:55 unsubscribe
Previous Message Dave Page 2006-04-11 07:24:50 Re: test results Win ME with psqlodbc driver 7.02.262