paone@topaz.rutgers.edu (Phil Paone) (04/22/88)
Hi, I am having a problem with informix 2.10. 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. The only statement that seems to suggest that it might work is the CURRENT OF option, but that only works without a scroll cursor which, in turn means no FETCH options other then next. Does anyone have any suggestions? I also am having problems using the sqlerrd[2] which is supposed to return the number of rows processed. Following the select, this number is ALWAYS 0. In testing, this seemed to work only if the SELECT involved a single table. Any input on either of these would be appreciated. Thanks, Phil Paone -- Phil Paone paone@topaz.rutgers.edu ihnp4!moss!oac!ppaone "Admiral...There be whales here"
aland@infmx.UUCP (Dr. Scump) (04/23/88)
In article <Apr.22.07.49.02.1988.4988@topaz.rutgers.edu>, paone@topaz.rutgers.edu (Phil Paone) writes: > > Hi, I am having a problem with informix 2.10. 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. The only > statement that seems to suggest that it might work is the CURRENT OF > option, but that only works without a scroll cursor which, in turn > means no FETCH options other then next. Does anyone have any > suggestions? > > I also am having problems using the sqlerrd[2] which is supposed to > return the number of rows processed. Following the select, this > number is ALWAYS 0. In testing, this seemed to work only if the > SELECT involved a single table. Any input on either of these would > be appreciated. > > Thanks, > Phil Paone > -- I presume that you are using INFORMIX ESQL/C version 2.10.00. It *is* true that you cannot use the {UPDATE, DELETE} WHERE CURRENT OF <cursor> in conjunction with a scroll cursor. This can be simulated, however, by including the pseudo-column "rowid" in your select list. ROWID exists in every table; it is the physical record number within the file and can be used to uniquely identify any given row. You can then update/delete the current row by using that rowid value in your WHERE clause, e.g. $ long currowid, currpos; ... $ declare curs_name scroll cursor for select rowid, * from tblname; $ open curs_name; $ fetch curs_name into currowid, var1, var2, ... ; currpos = 1; while (sqlca.sqlcode != SQLNOTFOUND) { ... <when you want to update> $ update tblname set ... where rowid = $currowid; ... $ fetch curs_name into currowid, var1, var2, ... ; currpos++; } The only problem with this is that any updates made will no be reflected in the still-open scroll cursor, as you mentioned. However, re-opening the cursor will refresh your list. If you have kept track of how far into the cursor you were, you can then use FETCH ABSOLUTE to get back to where you were. In the example above, the SQL statements $ open curs_name; $ fetch absolute $currpos curs_name; will return you to your old location within the cursor. If you have been deleting rows from this cursor, be sure to account for them in your "absolute" counter. If other users are inserting/deleting rows in the table which can be qualified by your WHERE clause, you may need to step through the list on row at a time to be sure you resume at the same place, since the number of rows can potentially change (unless you lock the table in advance). (Note that variables used in FETCH RELATIVE or ABSOLUTE *must* be longs). Your suspicions regarding sqlerrd[2] ("number of rows processed") being zero for any SELECT statement are correct -- this value is set only for single-statement counts (e.g. UPDATE, DELETE), not for cursors. (Selects into temporary tables DO set this value, since no cursors are involved; sqlerrd[2]=the number of rows selected into the temp table.) To know how many rows were returned by the SELECT, simpy count the rows as you fetch them. If you need to know the count in advance, use "select count(*) from tblname where ..." using the desired WHERE clause. The documentation should mention the fact that this control field is not applicable to SELECT statements; it is being corrected. Use of the ROWID feature is covered on page 1-50 of the ESQL/C 2.10 manual. I hope this information proves helpful. -- Alan S. Denney | Informix Software, Inc. | {pyramid|uunet}!infmx!aland CAUTION: Objects on terminal are closer than they appear... Disclaimer: These opinions are mine alone. If I am caught or killed, the secretary will disavow any knowledge of my actions.
john@riddle.UUCP (Jonathan Leffler) (04/27/88)
In article <Apr.22.07.49.02.1988.4988@topaz.rutgers.edu> 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)