Path: utzoo!mnetor!uunet!lll-winken!lll-lcc!pyramid!infmx!aland From: aland@infmx.UUCP (Dr. Scump) Newsgroups: comp.databases Subject: Re: Informix SQL Questions Message-ID: <158@infmx.UUCP> Date: 23 Apr 88 02:06:59 GMT References: Organization: Informix Software Inc., Menlo Park, CA. Lines: 88 Keywords: Updating active set in memoery Summary: answers In article , 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 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) { ... $ 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.