We installed Postgres 7.3.1 from the source RPMS downloaded from
postgres.org (postgresql-7.3.1-1PGDG.src.rpm). We also installed the
psqlODBC driver (psqlodbc-7.2.5.tar.gz) downloaded from GBorg. Everthing
compiled and installed cleanly. Test machine is x86 running a newly
installed RedHat 8.0.
The problem is that when our web server (Win2k IIS 5.0 with PostgreSQL ODBC
driver 7.02.00.05 11/29/2002) connects there seems to be an ODBC error when
returning record sets from a cursor called within transaction. We get this
ASP error:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/caltrans/inspjrnl/journalv2loadjlist.asp, line 57
Simple select from table queries run OK, as do the new table functions.
We're thinking cursors are broken in the Win32 ODBC driver or nearby.
We've been trouble shooting this for 4 or 5 days now and need help.
If any one cares to attempt to reproduce this on their machines, here is our
test code:
--the function
create or replace function sp_test(refcursor) returns refcursor as '
declare
cur alias for $1;
begin
open cur for select datname from pg_database;
return cur;
end;
' language plpgsql;
'asp
<html>
<head></head>
<body>
<%
Dim STRCONN
Dim sql
Dim rs
STRCONN = "PROVIDER=MSDASQL.1;Extended
Properties=DRIVER={PostgreSQL};DATABASE=sdb08;SERVER=10.0.0.10;PORT=5432;UID
=sdb08;PWD=sdb08"
sql = "begin; select sp_test('rcursor'); fetch all in rcursor; commit;"
set rs = server.createobject("adodb.recordset")
rs.open sql, STRCONN
While Not rs.EOF
response.write("<br>"&rs(0))
rs.MoveNext
Wend
rs.Close
%>
</body>
</html
Craig Addlman DBA
ShareChive LLC
San Francisco
(415) 284-9662