Thread: cursor "" does not exist
Hi, I'm querying a Postgres 8.0.3 database from Java via the 8.0-311 JDBC driver. It mostly works. I ran into the "cursor does not exist" problem, but was able to fix it with connection.setAutoCommit(false); Or so I thought. There are several JSP pages which still throw the 'cursor "<unnamed portal 1>" does not exist' error. Everything works well if I call the same Java fuction on the command line. I tried to observe the network packets and found an interesting thing: the "select *" queries and responses that works look like this: Request S_1 BEGIN select * from user_pkg.GetPerson_fcn($1) as result 571 Response BEGIN result <unnamed portal 1>C SELECT While those that fail look like this: Request select * from material_pkg.ListCautions_fcn($1,$2) as result B Response result <unnamed portal 1>C SELECT Note that the successful ones contain strings "S_1" and "BEGIN", and the failed ones do not. However, there also are successful queries without these strings, but they are not "select *" queries. Eg, Request SELECT typname FROM pg_catalog.pg_type WHERE oid=$1 1790 Response typname refcursorC SELECT When I do it successfully on the command line, the "S_1" and "BEGIN" lines are also present in the query. I have a feeling it's some idiosyncrasy that I'm not familiar with. Does "BEGIN" refer to the beginning of a trasaction? I will be grateful for any suggestions. Simon -- Simon (Vsevolod ILyushchenko) simonf@cshl.edu http://www.simonf.com Terrorism is a tactic and so to declare war on terrorism is equivalent to Roosevelt's declaring war on blitzkrieg. Zbigniew Brzezinski, U.S. national security advisor, 1977-81
On Wed, Jun 15, 2005 at 06:45:56PM -0400, Vsevolod (Simon) Ilyushchenko wrote: > While those that fail look like this: > > Request select * from material_pkg.ListCautions_fcn($1,$2) as result B > Response result <unnamed portal 1>C SELECT > > Note that the successful ones contain strings "S_1" and "BEGIN", and the > failed ones do not. However, there also are successful queries without > these strings, but they are not "select *" queries. Eg, > I have a feeling it's some idiosyncrasy that I'm not familiar with. Does > "BEGIN" refer to the beginning of a trasaction? Yes. A transaction looks like this: BEGIN; SQL1; SQL2; COMMIT; But it also looks like this: SQL3; because in PostgreSQL, everything is always automatically in a transaction, and a bald SQL statement is just a transaction one statement long. With autocommit off, I think what you get is no COMMIT, but you still get the bald transaction. What you really need is to make sure you're starting a multi-statement transaction every time. A -- Andrew Sullivan | ajs@crankycanuck.ca The plural of anecdote is not data. --Roger Brinner