Path: utzoo!attcan!utgpu!jarvis.csri.toronto.edu!mailrus!tut.cis.ohio-state.edu!rutgers!cs.utexas.edu!uunet!portal!cup.portal.com!Bron From: Bron@cup.portal.com (George Bron Faison) Newsgroups: comp.databases Subject: Re: Oracle: Previous Record Message-ID: <20645@cup.portal.com> Date: 21 Jul 89 02:56:01 GMT References: Organization: The Portal System (TM) Lines: 94 Re: Oracle: Previous Record On 7/17/89 11:22 mao@eden.uucp (Mike Olson ) writes: >in article <20532@cup.portal.com>, Bron@cup.portal.com (G.B. Faison) writes >>In an article written 7/7/89 17:48 miker@tdpvax.UUCP writes: >> >>> I am using Oracle on SCO386 Xenix and I wish to be able to be >>> able to retrieve a previous record. >> >> ... you should think *sets* not record-at-a-time! >> Previous record has no valid meaning in a truly relational system >> where by definition physical ordering is irrelevant. > >a valid point, but virtually all commercial implementations of SQL provide >pretty hefty non-relational extensions. in a real relational system, for >example, duplicate tuples are forbidden. it turns out, though, that the >pentagon and the phone company care more about solving their own problems >than preserving the semantics of relational calculus and set theory. the >database systems with which i'm familiar at least offer the option of ... For those unfamiliar with Oracle, let me clarify. Oracle also provides many extensions to SQL, allows duplicate rows to exist in a table, and all the rest. It certainly has what must be called a very robust and complete SQL implementation. Oracle's SQL*Forms product which provides the screen/form interface that is what most "users" interact with does provide the most complete "next/previous record" type capability you could possibly want. You can browse the records in any order you wish, optionally clear records from the que as you do so, and more. However, all access to the database is (and properly so in my opinion) through SQL, and SQL defines no "previous row" and shouldn't. It's just inappropriate. Using PRO*C (C with embedded SQL cursors, fetches, etc.) you can achieve just about anything you want, including implementing a next/previous record/row. My point was, that, as a consultant, I am constantly called in to straighten out the mess that results when one jumps to using procedural, row-at-a-time logic instead of taking advantage of all of the power and functionality provided by the relational system at hand. I see people spend days writing convoluted C code to do things that can be accomplished in 1/2 hour using SQL directly with no procedural statements necessary. They use SQL but they use it like it was CoBol! Sure there are times when nothing but good old procedural code will do, but those times should be few and far between, not routine, or else why are you implementing a "relational" system? I added the caution, because it was obvious from the question that this person might profit by it. Of course, he, you, and I are all free to do these things as we chose - and that's as it should be. On 7/18/89 06:42 bg0l+@andrew.cmu.edu (Bruce E. Golightly) writes: >There are indeed times when you can't think in terms of the relational >model. Sometimes the users insist that they want what they want, and you >can't do it without breaking the model. We had to write an application >to do this kind of thing a while back. I hope you argued like hell against it! >We don't use Oracle here, we use Ingres. The problem is basically the same >using either, though. The suggestion that you use the ROWID (TID in Ingres) >has some merit. Relational Technology (Ingres) has repeated pointed out >that use of the tuple ID in user specified operations is not a supported >feature and that relying on characteristics of the TID may result in a >broken application some time. I suspect that Oracle would echo this. Well, they document it (and that's saying something for Oracle!) and I think it would be safe to use as an "address" as in this case, but who knows, I agree that another method might be preferable. >Another method might be used, however. In Ingres I have occassionally set >up a tablefield that the user can't see on the screen and used it to store >the set of possible matches. The idea of PreviousRecord may then be >implemented by scrolling up in this table field and doing a simple SELECT >based on the result to fill in the visible portions of the screen. The >problem with this appraoch is that it requires lots more code. As discussed above, this isn't necessary in Oracle's SQL*Forms, as it supports very sophisticated access methods for the "forms" type applications, including all manner of next/previous row functions. Ok, I'll shutup! :-) ----------------------------------------------------------------------------- George "Bron" Faison Email: Bron@cup.portal.com USMAIL: Modern Office Systems Technology, Inc. "Be Still!" 6455 Penrith Drive Mechanicsville, VA 23111 Oracle & UNIX Phone: (804) 730-1467 (voice) -----------------------------------------------------------------------------