By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,964 Members | 1,318 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,964 IT Pros & Developers. It's quick & easy.

Keeping cursors across page reloads/changes

P: 21
Hello,

I'm currently working on an order management system that uses PHP and PostgreSQL. In my situation, I'd like to be able to use a cursor in order to do pagination.

My PHP script checks (by cookie status) if a cursor has or hasn't been set, or has to be changed. If one hasn't been set, or it needs to change the SELECT statement, it does so. Here's the SQL code:

Expand|Select|Wrap|Line Numbers
  1. BEGIN WORK;
  2. DECLARE cat_curs SCROLL CURSOR WITH HOLD FOR
  3. SELECT
  4. itemcode, desc1, desc2, base_price, brk1_qty, brk2_qty, rowserno 
  5. FROM shrtitmmas
  6. WHERE itemcat1
  7. ...
  8. ;COMMIT WORK;
When this executes, no errors come back and the PHP executes this (this is the same code that gets executed if nothing needs to be changed):

Expand|Select|Wrap|Line Numbers
  1. BEGIN WORK;FETCH FORWARD 5 FROM cat_curs;COMMIT WORK;
This works fine, as long as the cursor was declared RIGHT AFTER (in the same PHP script), and rows 1-5 get outputted.

The problem is that when I reload the page, the cookie stays the same so just the FETCH code is run. This is whats supposed to happen, but instead of the query fetching the next 5 rows (6-10), it gives this error: "ERROR: cursor "cat_curs" does not exist".

I need the cursor to stay where it's at in the database ACROSS page reloads/changes in order for this to work properly.

Thank You,
Nate Kay
Feb 27 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.