Re: Problem returning a cursor through ODBC to ASP layer from postgres

From: "david williams" <dw_remote(at)hotmail(dot)com>
To: <pgsql-odbc(at)postgresql(dot)org>
Subject: Re: Problem returning a cursor through ODBC to ASP layer from postgres
Date: 2002-09-16 17:50:32
Message-ID: DAV17E8GpHSBQAJNACc0000ccff@hotmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

If I make the following change so that only the select getallusers('rs'); executes then all I get is:

1|select getallusers('rs'); <------------ Debug code - 1 field and command executedgetallusers
rs

<%
strconn="DSN=p_cars4u;uid=postgres;pwd="
'mySQL="select * from users"
'mySQL = "begin; "&vbcrlf
mySQL = mySQL & "select getallusers('rs'); "
'mySQL = mySQL & "fetch all in rs; "
'mySQL = mySQL & "commit; "
'call query2table(mySQL,strconn)'
%>
So text is being returned rather than a pointer/cursor/whatever.

Thanks

Dave
----- Original Message -----
From: david williams
Sent: Monday, September 16, 2002 1:17 PM
To: pgsql-odbc(at)postgresql(dot)org
Subject: Re: [ODBC] Problem returning a cursor through ODBC to ASP layer from postgres

The entire ASP page (read.asp) is here:

<HTML><HEAD>
<TITLE>read.asp</TITLE>
<body bgcolor="#FFFFFF"></HEAD>
<%
strconn="DSN=p_cars4u;uid=postgres;pwd="
'mySQL="select * from users"
mySQL = "begin; "&vbcrlf
mySQL = mySQL & "select getallusers('rs'); "
mySQL = mySQL & "fetch all in rs; "
'mySQL = mySQL & "commit; "
'call query2table(mySQL,strconn)'
%>
</BODY>
</HTML>
<%
sub query2table(inputquery, inputDSN)
dim conntemp, rstemp
set conntemp=server.createobject("adodb.connection")
conntemp.open inputDSN
set rstemp=conntemp.execute(inputquery)
howmanyfields=rstemp.fields.count -1%>
<table border=1><tr>
<% 'Put Headings On The Table of Field Names
for i=0 to howmanyfields %>
<td><b><%=rstemp(i).name%></B></TD>
<% next %>
</tr>
<% ' Now lets grab all the records
do while not rstemp.eof %> <------------------ LINE 35
<tr>
<% for i = 0 to howmanyfields
thisvalue=rstemp(i)
If isnull(thisvalue) then
thisvalue="&nbsp;"
end if%>
<td valign=top><%=thisvalue%></td>
<% next %>
</tr>
<%rstemp.movenext
loop%>
</table>
<%
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing
end sub%>
I get this error:

Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/read.asp, line 35

It seems that the object is closed already because it returns nothing.

Thanks

Dave
----- Original Message -----
From: Janet Borschowa
Sent: Monday, September 16, 2002 1:05 PM
To: 'david williams'; pgsql-odbc(at)postgresql(dot)org
Subject: RE: [ODBC] Problem returning a cursor through ODBC to ASP layer from postgres

I believe that when you call commit the refcursor is closed.

========================
Janet Borschowa
Software Engineer, Database Products
Rogue Wave Software
(541) 753-1931 FAX: (541) 757-4630
mailto:borschow(at)roguewave(dot)com <mailto:borschow(at)roguewave(dot)com>
http://www.roguewave.com <http://www.roguewave.com/>

-----Original Message-----
From: david williams [mailto:dw_remote(at)hotmail(dot)com]
Sent: Friday, September 13, 2002 9:29 AM
To: pgsql-odbc(at)postgresql(dot)org
Subject: [ODBC] Problem returning a cursor through ODBC to ASP layer from
postgres

I have created a function in postgres called getallusers(var) that returns a
cursor. This works fine at the psql level but when I try to return it
through ODBC I get errors.

The function is as follows:

CREATE OR REPLACE FUNCTION getallusers(refcursor) RETURN refcursor AS'

begin
open $1 for select * from users;
return $1;
end;
' language 'plpgsql';

I call it like this:

<%
strconn="DSN=p_test;uid=postgres;pwd="
mySQL = "begin "&vbcrlf
mySQL = mySQL & "select getallusers('rs'); "
mySQL = mySQL & "fetch all in rs; "
mySQL = mySQL & "commit; "
call query2table(mySQL,strconn)
%>

This does not seem to return anything.

Thanks

!-------------------------------------!
David Williams

_____

Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com
<http://explorer.msn.com>

Get more from the Web. FREE MSN Explorer download : http://explorer.msn.comGet more from the Web. FREE MSN Explorer download : http://explorer.msn.com

Browse pgsql-odbc by date

  From Date Subject
Next Message Jose Avalis 2002-09-16 21:32:00 Re: Problem w/trigger
Previous Message david williams 2002-09-16 17:14:57 Re: Problem returning a cursor through ODBC to ASP layer from postgres