Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

 

 

 

 

pgsql-odbc by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group