demasi@paisano.UUCP (Michael C. De Masi) (07/07/87)
Hello people!
If any of you have seen these questions before, please bear with
me since I've been off the net for a while while the machine I was
on was going through some mental strain.
Basically, what I was wondering if I had missed something regarding
the cursor concept in Informix esql/c. The first question involves
the use of the 'fetch' statement to advance the cursor through a
queried list of rows from a given declare statement. Although fetch
does a fine job in the forward direction, there seems to be no way to
do what I would imagine would best be called an 'unfetch'. Ie, is it
possible to back the cursor up a row (or more) to traverse already
fetched data? It would certainly seem to be a nice thing to be able to do,
and in fact is quite possible from the vanilla Informix generated screens.
Is this just the way the database is designed, or am I missing something
obvious?
The second question involves active lists queried down for update
purposes. Again, one might query the set and advance through it
with fetch statements, but it seems that the first non-readonly activity
performed after the query (ie an insert, deletion or update) somehow
breaks the cursor making it impossible to use for any purpose.
Now maybe I can buy the fact that these cursors were meant to be
uni-directional, but am I to understand that they were designed to
be as fragile as I've stated? Why bother to make it possible to
query multiple records for update when making one change to one
record makes the whole query inoperative?
Again, it is entirely possible that I'm missing something wildly
obvious, and would greatly appreciate having my errors pointed out
to me in any format you'd like. Also, if you've had the same problems,
please write and we can comiserate.
Great to be back on the net!
(By the way, the address is now paisan'o' instead of paisan'a'.
And no, she didn't have a sex change, it's her brother, you pervert!)
--
Michael C. De Masi - AT&T Communications (For whom I work and not speak)
3702 Pender Drive, Fairfax, Virginia 22030 Phone: 703-246-9555
UUCP: seismo!decuac!grebyn!paisano!demasi
"Life. Don't tell me about life." - Marvin, the paranoid androidbh@ptsfa.UUCP (Brian Holliday) (07/10/87)
demasi@paisano.UUCP (Michael C. De Masi) in <147@paisano.UUCP>, writes: >Basically, what I was wondering if I had missed something regarding >the cursor concept in Informix esql/c. The first question involves >the use of the 'fetch' statement to advance the cursor through a >queried list of rows from a given declare statement. Although fetch >does a fine job in the forward direction, there seems to be no way to >do what I would imagine would best be called an 'unfetch'. Ie, is it >possible to back the cursor up a row (or more) to traverse already >fetched data? It would certainly seem to be a nice thing to be able to do, >and in fact is quite possible from the vanilla Informix generated screens. >The second question involves active lists queried down for update >purposes. Again, one might query the set and advance through it >with fetch statements, but it seems that the first non-readonly activity >performed after the query (ie an insert, deletion or update) somehow >breaks the cursor making it impossible to use for any purpose. >Now maybe I can buy the fact that these cursors were meant to be >uni-directional, but am I to understand that they were designed to >be as fragile as I've stated? Why bother to make it possible to >query multiple records for update when making one change to one >record makes the whole query inoperative? I have not used Informix ESQL/C -- but I am a current user of Informix 4GL. Informix 4GL programs are compiled down into ESQL/C, so if a 4GL solution is satisfactory, you should be able to do the same thing in ESQL/C. (You will just need to write more code.) You are correct -- cursors take you forward through your database selections, and there is no way to step backwards, and ask for a previously fetched selection. I used vanilla Informix 3.3 before I used Informix 4GL, so this apparent brain damage affected me in the same manner it has affected you ([insert favorite 4-letter word here]!). However, now that I know 4GL much better, I immensely prefer it over the vanilla Informix. Since 4GL programming is so much easier and more productive than vanilla Informix, I accept the work-around. Declare two cursors -- one that goes forward (let's call it CURSORF), and one that goes backwards (let's call it CURSORB). Both cursors use the data from the current cursor, and find the next data (there's a ">" selection) or previous data (a "<" selection). To go forward: OPEN CURSORF, FETCH CURSORF, and CLOSE CURSORF. To go backwards: OPEN CURSORB, FETCH CURSORB, and CLOSE CURSORB. Since the cursor has been closed, your second question/problem has also been solved. You may update/delete/whatever, and then open up the cursor for the next selection. As 4GL technology improves, these goofy things will be ironed out. I'm looking forward to better 4GLs, and the upcoming 5GLs in the 1990's. <<std disclaimer -- I don't work for Informix, my company is not responsible for my big mouth, blah blah blah>> Brian Holliday (...!ptsfa!bh)
ssl@ptsfa.UUCP (Sam Lok) (07/10/87)
In article <3219@ptsfa.UUCP> bh@ptsfa.UUCP (Brian Holliday) writes: > >Declare two cursors -- one that goes forward (let's call it CURSORF), >and one that goes backwards (let's call it CURSORB). Both cursors use the >data from the current cursor, and find the next data (there's a ">" >selection) or previous data (a "<" selection). > >To go forward: OPEN CURSORF, FETCH CURSORF, and CLOSE CURSORF. >To go backwards: OPEN CURSORB, FETCH CURSORB, and CLOSE CURSORB. > >Since the cursor has been closed, your second question/problem has also been >solved. You may update/delete/whatever, and then open up the cursor >for the next selection. Perhaps you might want to enlighten us a bit more on this subject? 1. How can you associate CURSORF and CURSORB with the 'current cursor' you mentioned. Is 'current cursor' a third cursor that's required? I always thought cursors associate with a particular set of select statement. 2. How can you adjust CURSORF/B to work like perform's next and previous? You can't really touch cursor, can you? 3. The 'ESQL/C' manual says "The CLOSE statement puts the cursor in the closed state and leaves its active set undefined. To me, it means your next open and fetch will give you the same row you just read. Of course, you can put in a while loop so you can go pass your last record and read the next one in line, but think about how many reads one have to do in order to go thru a long list of rows makes me wonder if this is all worth the trouble. Of course, it's would be a different matter if I misunderstand the manual! And now back to the original poster question about update trashing cursor. Although I never had a need to update multiple rows in a shot, I do always use: declare cursor for update, open, fetch, update where current of sequence. The manuals says "If you use the CURRENT OF option in the WHERE clause, ESQL/C updates the current row of the active set and leaves the cursor on the same row. You may UPDATE only those columns that you previously listed in the FOR UPDATE clause of the DECLARE statement that defined cursor-name." And if the cursor really got trashed after the update, I think it's a bug for RDS, oops, Informix, Inc. Also, as I was told by Informix, you'll have to use the 'declare .. for update' syntax if you want to lock the rows. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Sam Lok San Ramon {ihnp4,pyramid,qantel}!ptsfa!ssl || To err is human, to really foul things I disclaim my disclaimer! || up requires super-user privilege!
bh@ptsfa.UUCP (Brian Holliday) (07/10/87)
In article <3227@ptsfa.UUCP> ssl@ptsfa.UUCP (Sam Lok) writes >In article <3219@ptsfa.UUCP> bh@ptsfa.UUCP (Brian Holliday) writes: >> >>Declare two cursors -- one that goes forward (let's call it CURSORF), >>and one that goes backwards (let's call it CURSORB). Both cursors use the >>data from the current cursor, and find the next data (there's a ">" >>selection) or previous data (a "<" selection). >> >>To go forward: OPEN CURSORF, FETCH CURSORF, and CLOSE CURSORF. >>To go backwards: OPEN CURSORB, FETCH CURSORB, and CLOSE CURSORB. >> >>Since the cursor has been closed, your second question/problem has also been >>solved. You may update/delete/whatever, and then open up the cursor >>for the next selection. > >Perhaps you might want to enlighten us a bit more on this subject? > >1. How can you associate CURSORF and CURSORB with the 'current cursor' > you mentioned. Is 'current cursor' a third cursor that's required? > I always thought cursors associate with a particular set of select > statement. When CURSORF or CURSORB is FETCHed, the data from the database is extracted, and put into your program's variables. Use one of these program variables to determine what is to be selected. There is no third cursor. I meant the data from the cursor (CURSORF or CURSORB) that had just been FETCHed. (Before the first FETCH, you need to set the program variable to something reasonable, of course.) >2. How can you adjust CURSORF/B to work like perform's next and previous? > You can't really touch cursor, can you? You can use the data brought in from a cursor -- for another cursor. The FETCH extracts data from the database and puts it in your program's variables. You can pick up one of these variables, and use it for another FETCH. >3. The 'ESQL/C' manual says "The CLOSE statement puts the cursor in the closed > state and leaves its active set undefined. To me, it means your next > open and fetch will give you the same row you just read. Of course, > you can put in a while loop so you can go pass your last record and read > the next one in line, but think about how many reads one have to do in > order to go thru a long list of rows makes me wonder if this is all > worth the trouble. Of course, it's would be a different matter if I > misunderstand the manual! From page 7-138 of the Informix 4GL Reference Manual (Volume 2): The OPEN statement examines the content of the program variables and, using these values for the parameters in the SELECT statement, determines the set of rows that satisfies the WHERE clause. This set of rows is called the *active set*. So the OPEN will create a new active set, because the content of the program variable has changed. (Don't confuse this concept of "active set" with the program variables. An "active set" row is copied into the program variables each time a FETCH is done.) Good enough? Brian Holliday {ihnp4,pyramid,qantel}!ptsfa!bh
allbery@ncoast.UUCP (07/15/87)
As quoted from <3219@ptsfa.UUCP> by bh@ptsfa.UUCP (Brian Holliday): +--------------- | >queried list of rows from a given declare statement. Although fetch | >does a fine job in the forward direction, there seems to be no way to | >do what I would imagine would best be called an 'unfetch'. Ie, is it | >possible to back the cursor up a row (or more) to traverse already | >fetched data? It would certainly seem to be a nice thing to be able to do, | >and in fact is quite possible from the vanilla Informix generated screens. | | You are correct -- cursors take you forward through your database | selections, and there is no way to step backwards, and ask for a previously | fetched selection. I used vanilla Informix 3.3 before I used Informix 4GL, | so this apparent brain damage affected me in the same manner it has affected | you ([insert favorite 4-letter word here]!). However, now that I know 4GL | much better, I immensely prefer it over the vanilla Informix. Since 4GL | programming is so much easier and more productive than vanilla Informix, I | accept the work-around. +--------------- I've used Informix 3.3 and Informix-4GL, as has my boss at my new job; we both prefer Informix 3.3, and he's to the point of actually considering switching to UNIFY to keep the C interface: which, for him, is almost blasphemy. The next (soon, maybe now?) version of Informix-4GL is supposed to have bidirectional cursors. My solution finessed both of the original poster's problems: I do a "foreach cursor into c_rowid call saveit(c_rowid)" loop, where saveit() is a C routine which writes the rowid into a binary file in /tmp. Other routines exist to append and delete entries in the temp file and to step forward and backward; I use it with some shell scripts which write 4GL "Perform"-like programs. (My own opinion of the deficiency? Considering that the "read" routines are called "frstrec", "nextrec", "prevrec", "lastrec", they should be obvious to the initiate.) I consider Informix-4GL and the whole embedded SQL concept a major mistake on the part of Informix Software. I only hope they see the light before they feel the flames. -- [Copyright 1987 Brandon S. Allbery, all rights reserved] \ ncoast 216 781 6201 [Redistributable only if redistribution is subsequently permitted.] \ 2400 bd. Brandon S. Allbery, moderator of comp.sources.misc and comp.binaries.ibm.pc {{ames,harvard,mit-eddie}!necntc,{well,ihnp4}!hoptoad,cbosgd}!ncoast!allbery <<The opinions herein are those of my cat, therefore they must be correct!>>
scott@cdp.UUCP (07/17/87)
Get the new version 2.10 EQSL/C . You can move the cursor forward, backward, first, last and various flavors of random accessing. -scott hplabs!cdp!scott
rbp@investor.UUCP (Bob Peirce) (07/24/87)
> I've used Informix 3.3 and Informix-4GL, as has my boss at my new job; we > both prefer Informix 3.3, and he's to the point of actually considering > switching to UNIFY to keep the C interface: which, for him, is almost > blasphemy. When we went from an Altos 68000 to an Altos 3068 we had no choice. Standard Informix wasn't available. Fortunately, a version of the ALL library for Informix SQL became available and we were able to get our main data entry programs running in short order. Frankly, from the experience we are getting from 4GL, I am looking forward to rewriting these programs in that language. I like the idea of being able to combine fairly straightforward database routines with C functions where required rather than the other way around. ALL has always been a major pain for me. When we began the conversion there were three possibilities: convert directly with ALL; rewrite the C programs to use ESQL/C instead of ALL; and begin with 4GL. We have the whole Informix package, but the idea of using ESQL/C never excited me. It looked like a lot of work to end up with something not much different from what we had. In the interests of speed we used ALL with the idea of converting to 4GL later. This still seems like a good plan. -- Bob Peirce, Pittsburgh, PA 412-471-5320 uucp: ...!{allegra, bellcore, cadre, idis, psuvax1}!pitt!investor!rbp NOTE: Mail must be < 30K bytes/message