rick@jpusa1.UUCP (Rick Mills) (10/11/89)
A few weeks ago I posted a note asking about How to use the "Next-Previous" fetch function of a cursor and do selective updates on the fly. Since there was a general intrest in the answer, I'm posting the replies I've received. I also was digging around my comp.databases archives and found a note from Dr. Scump (at Informix) on pretty much the same question, so I'll throw that in on the end. What I ended up doing in my particular application is the last one(From Dr Scump). Actually, most replies were similar to that one, which is, basically, keep track of where you're at in the open cursor, then after you do an update, reopen it and (by some means or another) position yourself to the same place you were before. Using this method, you will probably have to lock the table for the duration of the cursor session. Thanks again for all the support! ------------------------------------------------------------------------------------ From gargoyle!clout!gargoyle!cs.cornell.edu!batcomputer!roger Wed Oct 4 13:51:55 1989 Date: Wed, 4 Oct 89 12:03:26 EDT From: gargoyle!cs.cornell.edu!batcomputer!roger (Roger Boissonnas) To: oddjob!gargoyle!clout!jpusa1!rick Subject: Re: Informix Question (Again) Organization: Cornell Theory Center, Cornell University, Ithaca NY In article <1178@jpusa1.UUCP> you write: > > I would like to declare a scrolling cursor to select >a list of rows for a screen form. I want the ability to do a >Next-Previous with these rows, and update selectively as I go. > Is this possible without declaring a second cursor to >keep track of where I'm at in the list? Shouldn't this be easy? Yeah, right. I've just wrestled with this question myself, and I didn't like they way I had to solve it. You're right: you can't DECLARE a SCROLL CURSOR FOR UPDATE. What's more, if you try to update a row with a separate UPDATE command, and the UPDATE fails, your cursor will be closed on you. So: I was stuck with reopening my cursor automatically after an UPDATE. Of course, the record you just changed may no longer fit the query conditions you used to get it in the first place, which means you can either restart the query, perhaps losing the record, or keep the record, losing the rest of the query. I asked my users, and they'd prefer to restart the query, since they figured they wouldn't necessarily need a record once they were done updating it, but would probably want the rest of their list. So my solution is a non-solution, because I can't reopen a cursor and obtain the same row reliably. Sorry I can't help you any more than to say how I ended up compromising. Please, please, let me know if you find a better solution. Roger Boissonnas roger@tcgould.tn.cornell.edu Cornell Theory Center (607) 255-8306 ------------------------------------------------------------------------------------ Date: Wed, 4 Oct 89 13:51:25 PDT From: gargoyle!uunet.uu.net!infmx!morris (Morris Bisted) To: oddjob!gargoyle!clout!jpusa1!rick Subject: Re: Informix Question (Again) Newsgroups: comp.databases Organization: Informix, Menlo Park, Ca. U.S.A. You have discovered the only way (I know of) to do an update with a scrolling cursor. Realize however that your updates will show up in the database, but not in the working set of your cursor. To have it show up in your existing working set you will need to create your own logic and display either the contents of an array that has the values of the tuple that you have updated or the contents of temporary table tuple that has the contents of the updated row. Bottom line, once you have a working set, you have it until you re-open the cursor. If you don't have technical support, you may want to look into that because they have a lot of good information regarding this type of functionality. Morris ------------------------------------------------------------------------------- From: >From: emuleomo@yes.rutgers.edu (Emuleomo) Subject: Re: Informix Question (Again) Date: 6 Oct 89 17:15:19 GMT Organization: Rutgers Univ., New Brunswick, N.J. In article <1178@jpusa1.UUCP>, rick@jpusa1.UUCP (Rick Mills) writes: > > I have Informix 4GL 1.10 Rev A and a question... I had this same problem some time ago trying to port a Foxbase application to Informix. The trick I used was to declare 2 cursors. The 1st one a SCROLL cursor that will be used to retrieve the **SERIAL NUMBERS** of the records you need. The 2nd cursor will be used to select ONLY ONE record; i.e. the record that matches the current serial number!! Furthermore, this 2nd cursor will be declared *for update*. Now you can do NEXT, PREVIOUS, LAST etc.. on the SCROLL cursor and whenever you need to update the current record, all you need to do is fire off the *update* cursor!! This worked quite well and in the application I wrote. Watch out for records in the *active set* of the scroll cursor that may have been deleted. My approach was to inform the user that the record has JUST been deleted!! Hpoe this helps. --Emuleomo O.O. (emuleomo@yes.rutgers.edu) -- ** Writing error-free code MUST be magic! Why else is it sooo difficult to do? ---------------------------------------------------------------------------------------- From gargoyle!clout!gargoyle!uunet.uu.net!viusys!dpocs2!tomb Sat Oct 7 22:54:43 1989 Subject: Re: Informix Question (Again) To: oddjob!gargoyle!clout!jpusa1!rick Date: Fri, 6 Oct 89 16:57:53 EDT Organization: Unisys Corp, McLean, VA 22102 Hi, The only way I know of to do what you are asking is to: 1. Keep track of the relative position of the row you are diplaying. 2. Do a "fetch relative" to get back to the original position in the previous active set after the cursor has be reopened. This has drawbacks, such as, if somebody else has inserted/deleted a row in the meantime. I'd be interested in what other responses you get. Tom (..!uunet!viusys!dpocs2!tomb) ------------------------------------------------------------------------------------ From: Path: jpusa1!gargoyle!tank!ncar!gatech!rebel!hisata!walt >From: walt@hisata.UUCP (Walt Hultgren) Subject: Re: Informix Question (Again) Date: 6 Oct 89 15:49:47 GMT Organization: Hultgren Information Systems, Atlanta, GA In article <1178@jpusa1.UUCP> rick@jpusa1.UUCP (Rick Mills) writes: > > I have Informix 4GL 1.10 Rev A and a question... Here's the best solution I've found so far. Use three cursors for each table (or logical group of tables) that you want to query and update. The first cursor is the one that is prepared and declared "on the fly" based on the where text obtained from a construct statement. For this cursor, don't select all of the fields you want from the row, but only enough to uniquely identify one row. As an example, suppose you have a table of invoices called "inv". Each row can be uniquely identified by an invoice number "inv.numb". In this first cursor, don't say "select * from inv", say "select numb from inv". Define a variable in your I4GL program to hold the "current" invoice number; e. g., "cur_inv_numb". To navigate the rows returned by the user's query, fetch the prepared cursor into cur_inv_numb. The two other cursors are used to fetch the entire row from inv for the current invoice number and are declared in the program source. One is used for inspecting rows and the other is used to delete or update rows. These declarations might look like: declare inv_nolock cursor for select * from inv where inv.numb = cur_inv_numb declare inv_lock cursor for select * from inv where inv.numb = cur_inv_numb for update For menu selections like Next, Previous, etc., do the appropriate fetch with the query cursor, followed immediately by: fetch inv_nolock into pr_inv.* where pr_inv is the "program record" corresponding to a row of inv. This will show the user the current contents of the row, not just the contents of the row at the time the scroll cursor was opened. For a Delete or Update, first do a fetch inv_lock into pr_inv.* then display the row. This shows the user the guaranteed current contents of the row and locks it. You can then update or delete the row using a "where current of inv_lock" clause. Close inv_lock immediately after the update or delete. An alternate way of doing things is to use ROWID instead of a unique data value. Whether or not this is appropriate will depend on your application. This method will not automatically include added rows to the current user selection like Informix 3.3 did. I wish I knew a clever way to do this. Everything I've thought of so far involves a lot of overhead. Any ideas, anyone? Walt. Walt Hultgren Hultgren Information Systems, P. O. Box 386, Tucker, Georgia 30085 USA Voice: +1 404 564 4707 UUCP: ...!{most_backbones}!gatech!hisata!walt ------------------------------------------------------------------------------------ From: >From: bob@sequoia.UUCP (Bob Kieras) Subject: Re: Informix Question (Again) Date: 6 Oct 89 21:22:36 GMT Organization: Execucom Systems Corp., Austin, Texas In article <1178@jpusa1.UUCP> rick@jpusa1.UUCP (Rick Mills) writes: > > I have Informix 4GL 1.10 Rev A and a question... Rick, I too wanted this sort of update as you go. This is how I solved the problem. When the user picks a record for update, the 4gl program first stores the rowid for that record. The record is then updated with an update sql statement like " Update xtabel set xxx= yyyy where rowid = stored_rowid". Then the cursor is closed and reopened. A while loop is used to position the cursor back to the record that was updated again by matching the rowid with the stored rowid. Then the updated record is displayed for review. This solution is brute force. It would grind too much if the cursor returned too many records and the record updated was the 500th one. In my case, the records to be scanned for update were already limited to a small number by the query the cursor was using. Hope this helps, Bob Kieras (usual disclaimer stuff) From gargoyle!att-ih!pacbell!lll-tis!mordor!lll-lcc!pyramid!infmx!aland Tue Apr 26 07:56:33 CDT 1988 >From: aland@infmx.UUCP (Dr. Scump) Subject: Re: Informix SQL Questions Keywords: Updating active set in memoery Date: 23 Apr 88 02:06:59 GMT Organization: Informix Software Inc., Menlo Park, CA. 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. ------------------------------------------------------------------------------------ Thanks again.... -Rick Mills -- Rick Mills {gargoyle.uchicago.edu,uunet.uu.net,spl1,ddsw1}!jpusa1.uucp!rick