Path: utzoo!mnetor!uunet!mcvax!ukc!reading!onion!riddle!john From: john@riddle.UUCP (Jonathan Leffler) Newsgroups: comp.databases Subject: Re: Informix SQL Questions Message-ID: <595@riddle.UUCP> Date: 27 Apr 88 08:23:16 GMT References: Reply-To: john@sphinx.co.uk (Jonathan Leffler) Organization: Sphinx Ltd., Maidenhead, England Lines: 39 In article paone@topaz.rutgers.edu (Phil Paone) writes: >Following a query and >any update or delete operation, doing a FETCH NEXT followed by a FETCH >PRIOR retrieves the data from the unaltered active set. It's a nuisance, isn't it. What happens is that a scroll cursor is implemented as a temporary table which rows are seelected from using next, previous etc. This table is by definition not updatable, and any changes made to the database are not reflected in that table. There are two ways around the problem, neither satisfactory, but both work. a) After an update, close the scroll cursor, and then reopen it, and then reposition your cursor in the scroll cursor. This can be tricky if somoeone else is also updating the database. This certainly works if the tables are small -- it is unlikely to be satisfactory if the tables are large. b) Cut the scroll cursor down to a list of primary key values. Scroll through this, and when you want to display a row, use a second select (probably for update) to fetch the data which matches the primary key info from the scroll cursor. Be prepared for the fetch to fail -- it means you (or someone else) deleted that row. Fetch the next row. This is a good technique when all the data displayed comes from one table; I do not have a satisfactory solution to the general case where the data selected is from several tables, but one table is to be updated. The trouble is that the update cursor can only be selecting from a single table. Forget the number of rows processed unless you are executing batch mode statements such as DELETE FROM xyz WHERE ... UPDATE xyz SET ddfd= edfds WHERE ... INSERT INTO xyz(fhk) SELECT fddss FROM sdfhkjs WHERE ... ------------- Jonathan Leffler (john@sphinx.co.uk) (...!ukc!reading!riddle!john)