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

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

pgsql-odbc by date

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

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