Thread: Problem returning a cursor through ODBC to ASP layer from postgres
strconn="DSN=p_test;uid=postgres;pwd="
mySQL = "begin "&vbcrlf
mySQL = mySQL & "select getallusers('rs'); "
mySQL = mySQL & "fetch all in rs; "
mySQL = mySQL & "commit; "
%>
This does not seem to return anything.
Thanks
!-------------------------------------!
David Williams
Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com
<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=" "
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%>
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/read.asp, line 35
----- Original Message -----From: Janet BorschowaSent: Monday, September 16, 2002 1:05 PMTo: 'david williams'; pgsql-odbc@postgresql.orgSubject: RE: [ODBC] Problem returning a cursor through ODBC to ASP layer from postgresI 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@roguewave.com <mailto:borschow@roguewave.com>
http://www.roguewave.com <http://www.roguewave.com/>
-----Original Message-----
From: david williams [mailto:dw_remote@hotmail.com]
Sent: Friday, September 13, 2002 9:29 AM
To: pgsql-odbc@postgresql.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.com
getallusers |
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)'
%>
----- Original Message -----From: david williamsSent: Monday, September 16, 2002 1:17 PMTo: pgsql-odbc@postgresql.orgSubject: Re: [ODBC] Problem returning a cursor through ODBC to ASP layer from postgresThe 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=" "
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 35It seems that the object is closed already because it returns nothing.ThanksDave----- Original Message -----From: Janet BorschowaSent: Monday, September 16, 2002 1:05 PMTo: 'david williams'; pgsql-odbc@postgresql.orgSubject: RE: [ODBC] Problem returning a cursor through ODBC to ASP layer from postgresI 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@roguewave.com <mailto:borschow@roguewave.com>
http://www.roguewave.com <http://www.roguewave.com/>
-----Original Message-----
From: david williams [mailto:dw_remote@hotmail.com]
Sent: Friday, September 13, 2002 9:29 AM
To: pgsql-odbc@postgresql.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.com
Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com
From: david williams
Could you try the snapshot dll at http://w2422.nsk.ne.jp/~inoue/ ?
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'beginopen $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