matt@hpcmmb.HP.COM (Matt Ledna) (12/29/87)
>>How does everybody feel about using a non-procedural "language" >>to write, maintain and report a database? I am currently using an SQL based database for a development project and have found it to be a great productivity tool. This is especially evident in reporting/retrieving from the database. Embedding SQL in a procedural language (i.e. C) has shown very few limitations. >I have been using a database w/ a procedureal language for 10 years >and, if I could, I would go back to it in a second. There are so MANY >things that simply cannot be done with INFORMIX that I am used to >doing, that I find I waste A LOT of time trying to kludge up something >(usually I end up running into a bug). Just curious...what types of limitations have you found? (I assume that these would be generic to any relational package, not just INFORMIX). I, for one, hope to never see a "procedural" (hierarchical, network) database again. Matt Ledna
lukas@ihlpf.ATT.COM (00704a-Lukas) (12/31/87)
In article <3890001@hpcmmb.HP.COM> matt@hpcmmb.HP.COM (Matt Ledna) writes: >Just curious...what types of limitations have you found? (I assume that >these would be generic to any relational package, not just INFORMIX). When first using INFORMIX I kept a list of bugs and deficiencies, some minor, some not. Most were (I think) unnecessary in that they were NOT a manifistation of the relational algebra. Some examples that occur to me: 1) Inability to restrict options on forms (if I dont want, say, to allow an ADD for a given scree). 2) The control blocks of perform cover < 1/2 of the possibilities (say you want to test a query BEFORE the database is searched). 3) Inability to join to a table in another database (say, the personell department keeps an up-to-date table of employees, which your application needs to access). 4) Difficulty (outside of perform) of doing that most basic of database applications: that of cycling through the rows, adding, deleting, modifying as you go. 5) Inability to have two LOOKUP tables for one column. I could go on (I collected over 50 before I quit). I realize that some of these deficiencies could be overcome with gobs of c code, but one of the things that I want from a datbase is functionality without the need to use a host language (I understand from a previous discussion in comp.databases that #4 is tough even with c). I am used to a database envirionment that has a great user interface and programming language. As a matter of fact, if I had my wish, it would be that the two different databases would magically merge, keeping the relational algebra (join and whatnot) and perform (you can take ace out and shoot it). If wishes were horses . . . BTW. I offered my list of (some, no doubt, bogus) deficiencies to INFORMIX. Thats when I found out how deep their concern for customers is. Happy New Year! -- John Lukas ihnp4!ihlpf!lukas 312-510-6290
chris@AUSTIN.LOCKHEED.COM (Chris Wood) (01/04/88)
In article <3216@ihlpf.ATT.COM>, lukas@ihlpf.ATT.COM (00704a-Lukas) writes: > 4) Difficulty (outside of perform) of doing that most basic of > database applications: that of cycling through the rows, adding, > deleting, modifying as you go. I would like to know how to go about this also. I am using ORACLE and would like to do this. I used to do something like the following in MODEL 204: 1. Find customers where qty-ordered > 1000 2. For each record in 1 2.1 If customer.area = "NORTHEAST" then 2.1.1 update region set good-customers = good-customers + 1 2.2 find orders where orders.custno = customers.number 2.3 for each record in 2.2 2.3.1 ... add up the total orders for that customer and store them back into that customers record... . . . Is there some underlying mathematical "relational purist" reason why such procedural constructs are lacking in relational implementations? The above looping code is very easy to develop and understand (at least for me) compared to the shenanigans I have to go through to do something like this in a SQL based system. How about something like: Select * from customer where customer.qty-ordered > 1000 into junk for each record in junk DO . . . . End DO (WOW Shades of FORTRAN) Why Can't relational systems do this? Or better yet, is there a SQL/Relational system out there that does?
truett@cup.portal.com (01/06/88)
Chris@AUSTIN.LOCKHEED.COM (Chris Wood) asks if there is a general way to
sequentially process the rows of a table, with each row processed resulting
in an insertion, deletion, update, or more complex operation on the table
being processed.
The obvious problem is that the rows may be retrieved in an arbitrary order
and, even if they are retrieved in a sorted form, the insertions and deletions
may change the ordering while processing is done. Another problem is that
rows which are inserted may be inadvertently retrieved later in the process as
if they had been originally in the table.
Perhaps I am suggesting too simple an answer, but in SQL terms the following
should generally do it:
1. Create a view comprised of the table to be processed with a column added to
indicate if a row has been "processed".
2. Perform the processing on successively fetched rows from the view. The
WHERE clause includes a condition that the "processed" column be NULL.
3. A deletion from either the original table or the created view will
remove that row from any further procesing or existence.
4. An addition or update, however, can be made via either the original table
or the view, allowing explicit control of whether or not the modified or
new row will appear later for processing.R
5. When no more rows in the view have a NULL "processed" flag, the view can
be dropped. The modified version of the original table can now be
committed.
I admit that the above would fail if the relational system in use does not
preserve "view concurrency", i.e., if a view does not immediately reflect
changes in its underlying entities.
Would appreciate knowing if I have missed something fundamental.
truett@cup.portal.com (Truett Smith, Sunnyvale, CA)chapman@ihuxy.ATT.COM (Ralph Chapman) (01/07/88)
In article <60@coot.AUSTIN.LOCKHEED.COM> Chris Wood writes: > In article <3216@ihlpf.ATT.COM>, lukas@ihlpf.ATT.COM (00704a-Lukas) writes: > > 4) Difficulty (outside of perform) of doing that most basic of > > database applications: that of cycling through the rows, adding, > > deleting, modifying as you go. > > I would like to know how to go about this also. I am using ORACLE > and would like to do this. ... > > Is there some underlying mathematical "relational purist" reason why such > procedural constructs are lacking in relational implementations? ... > > Why Can't relational systems do this? Or better yet, is there a > SQL/Relational system out there that does? SQL is set-oriented-- There is no implicit ordering in a plain vanilla "set". SQL insists on operating on an entire set all at once (conceptually, anyway), not an element at a time. This is an instance of "non-procedural" programming style-- It's supposed to SAVE you the TROUBLE of spelling out a procedure to do what you want. :-) (Not surprisingly, it's easier for non-programmers to learn this style than for programmers (of 3rd and previous generation languages) to learn it.) So you have to either: (1) formulate an "UPDATE ... WHERE" statement that selects the appropriate records a priori and updates just that subset (don't forget that you can nest SQL statements, in case the subset can't be specified by a boolean expression in a WHERE clause, e.g.: UPDATE ... WHERE field IN ( SELECT ... and so on ) (2) (in Oracle) make yourself an IAF application; that will let you go through the records one at a time. Oracle has a command that will make a quick-and-dirty IAF application for a single table. I can't figure out why SQL DBMS vendors don't supply a browser program that will loop through a SELECTed set, as you described. (Maybe I'll write one someday.) --Ralph Chapman ihnp4!ihuxy!chapman
chris@AUSTIN.LOCKHEED.COM (Chris Wood) (01/07/88)
In article <2318@cup.portal.com>, truett@cup.portal.com writes: > 1. Create a view comprised of the table to be processed with a column added to > indicate if a row has been "processed". > 2. Perform the processing on successively fetched rows from the view. The > > WHERE clause includes a condition that the "processed" column be NULL. > Please give me a sample of SQL syntax to perform the above, especially the second step. Thanks, Chris Wood
UH2@PSUVM.BITNET (Lee Sailer) (01/08/88)
In article <2327@ihuxy.ATT.COM>, chapman@ihuxy.ATT.COM (Ralph Chapman) says: > >In article <60@coot.AUSTIN.LOCKHEED.COM> Chris Wood writes: >> In article <3216@ihlpf.ATT.COM>, lukas@ihlpf.ATT.COM (00704a-Lukas) writes: >> > 4) Difficulty (outside of perform) of doing that most basic of >> > database applications: that of cycling through the rows, adding, >> > deleting, modifying as you go. >> >> I would like to know how to go about this also. I am using ORACLE >> and would like to do this. ... >> How about a little toy example of what it is that you are saying that it is hard to do in SQL? lee
UH2@PSUVM.BITNET (Lee Sailer) (01/08/88)
In article <67@coot.AUSTIN.LOCKHEED.COM>, chris@AUSTIN.LOCKHEED.COM (Chris Wood) says: > >In article <2318@cup.portal.com>, truett@cup.portal.com writes: >> 1. Create a view comprised of the table to be processed with a column added to >> indicate if a row has been "processed". >> 2. Perform the processing on successively fetched rows from the view. The >> >> WHERE clause includes a condition that the "processed" column be NULL. > >> >Please give me a sample of SQL syntax to perform the above, especially >the second step. > >Thanks, >Chris Wood I think it would be helpful if you offer some context. What kind of transactions are you trying to model here? Maybe your approach is wrong. There is a general rule in declarative programming that if it is hard to do, then you are likely taking the wrong approach. But since we don't know what you are trying to do, (only HOW you plan to accomplish it) we cannot help. lee
tn@hpldola.HP.COM (Ted Neff) (01/08/88)
>> 4) Difficulty (outside of perform) of doing that most basic of >> database applications: that of cycling through the rows, adding, >> deleting, modifying as you go. > I would like to know how to go about this also. I am using ORACLE > and would like to do this. > . . . > Why Can't relational systems do this? Or better yet, is there a > SQL/Relational system out there that does? I don't know about Oracle, but the SQL standard defines a facility to do exactly what you describe. Here is what C. J. Date has to say on page 54 of "A Guide to the SQL Standard" (from Addison-Wesley). "A cursor consists essentially of a kind of "pointer" that can be used to run through an ordered collection of rows, pointing to each of the rows in that collection in turn and thus providing addressability to those rows one at a time. If cursor C is pointing to row R, it is said to be "positioned on" row R. Row R can then be updated or deleted via the "positioned" form of the UPDATE and DELETE operations (UPDATE/DELETE ... WHERE CURRENT OF C)." The following excerpt is from Chapter 8, "Sequential Table Processing" in the "HPSQL C Application Programming Guide" (from Hewlett-Packard), "Sequential table processing is the programming technique you use to operate on a "multiple-row query result, one row at a time". The query result is referred to as an "active set". You use a pointer called a "cursor" to move through the active set, retrieving a row at a time into host variables and optionally updating or deleting the row." Note that the host language in which SQL is embedded (e.g., C, 4GL, or as in Date's book -- PL/1), provides the procedural constructs (such as while loops) to control the sequencing through the selected data. Such constructs are not specified in the SQL standard. (If you don't have such constructs, it doesn't make much sense to have cursors). The standard SQL cursor operations are: DECLARE CURSOR, OPEN, FETCH, UPDATE ... WHERE CURRENT, DELETE ... WHERE CURRENT, and CLOSE. All of these operations are present in the two embedded SQL implementations that I've used -- Informix-4GL and HP Allbase. Informix-4GL even has extensions to standard SQL cursors (the most useful being a scroll-cursor to provide random access to the active set). A typical use of the cursor operations would be similar to the following. DECLARE c CURSOR FOR select-statement OPEN c while (condition) FETCH c INTO host_language_variables if (no more records found) break; ... UPDATE table SET table.qty = table.qty + 100 WHERE CURRENT OF c ... DELETE FROM table WHERE CURRENT OF c ... end CLOSE c
UH2@PSUVM.BITNET (Lee Sailer) (01/08/88)
In article <60@coot.AUSTIN.LOCKHEED.COM>, chris@AUSTIN.LOCKHEED.COM (Chris Wood) says: > >1. Find customers where qty-ordered > 1000 >2. For each record in 1 >2.1 If customer.area = "NORTHEAST" then >2.1.1 update region > set good-customers = good-customers + 1 >2.2 find orders where orders.custno = customers.number >2.3 for each record in 2.2 >2.3.1 ... add up the total orders for that customer and store them back > into that customers record... >. I've never used any SQL product, but supposedly know some of the underlying ideas. How about something like good customers = Select count(id#) FROM customers WHERE area = NORTHEAST and qty.ordered > 1000 > For the total orders problem, try something like SELECT number, sum(amount) FROM customers, orders WHERE custno = number GROUP BY number and define customers.totalamt = SELECT tamt FROM t WHERE customers.number=t.number
eric@rtech.UUCP (Eric Lundblad) (01/09/88)
From article <60@coot.AUSTIN.LOCKHEED.COM>, by chris@AUSTIN.LOCKHEED.COM (Chris Wood): > In article <3216@ihlpf.ATT.COM>, lukas@ihlpf.ATT.COM (00704a-Lukas) writes: >> 4) Difficulty (outside of perform) of doing that most basic of >> database applications: that of cycling through the rows, adding, >> deleting, modifying as you go. > > I would like to know how to go about this also. I am using ORACLE > and would like to do this. I used to do something like the following > in MODEL 204: > > 1. Find customers where qty-ordered > 1000 > 2. For each record in 1 > 2.1 If customer.area = "NORTHEAST" then > 2.1.1 update region > set good-customers = good-customers + 1 > 2.2 find orders where orders.custno = customers.number > 2.3 for each record in 2.2 > 2.3.1 ... add up the total orders for that customer and store them back > into that customers record... > . > . > . > > Is there some underlying mathematical "relational purist" reason why such > procedural constructs are lacking in relational implementations? The above > looping code is very easy to develop and understand (at least for me) compared > to the shenanigans I have to go through to do something like this in a > SQL based system. > > How about something like: > > Select * from customer where customer.qty-ordered > 1000 > into junk > for each record in junk > DO > . > . > . > . > End DO > > (WOW Shades of FORTRAN) > > Why Can't relational systems do this? Or better yet, is there a > SQL/Relational system out there that does? Standard SQL (ANSI or DB2, take your choice) requires you to define a cursor and a loop that fetchs the tuples. This is somewhat more involved than what you describe, but it can be done. There are implementations of SQL that allow you to do the following: exec sql select * from customer where qty-ordered > 1000 exec sql begin <program code that plays with the data here> exec sql end There is also QUEL, a language similar to SQL (only better, ref. C J Date) that allows you to do: ## retrieve (customer.all) where customer.qty-ordered > 1000 ## begin <program code that plays with the data here> ## end In both cases, the program code is executed for each tuple that the select or retrieve returns. eric As always, these opinions are mine and no one elses. -- Eric Lundblad ucbvax!mtxinu!rtech!eric
chris@AUSTIN.LOCKHEED.COM (Chris Wood) (01/12/88)
In article <29265UH2@PSUVM>, UH2@PSUVM.BITNET (Lee Sailer) writes: > I've never used any SQL product, but supposedly know some of the underlying > ideas. How about something like > > good customers = Select count(id#) > FROM customers > WHERE area = NORTHEAST and qty.ordered > 1000 > > > > For the total orders problem, try something like > > SELECT number, sum(amount) > FROM customers, orders > WHERE custno = number > GROUP BY number > > and define customers.totalamt = SELECT tamt FROM t > WHERE customers.number=t.number > All of this is fairly close, but the point is that I could do it in 1 query with M204, but a single query in SQL can only do a single thing. This means that if I want to do several things to the same set of records, I must re-retrieve those records for each function. I guess this kind of grates against the "traditional" programmer in me that still yearns for a modicum of efficiency. It is obviously simpler to do one thing at a time, but it bothers me to repeat work. Once I have located and retrieved a set of data, I really don't want to have to do it all over again just because the query language forces me into it! I am now convinced that this is not a problem with "relational", it is a problem with SQL. Question: I am (embarassingly) unfamiliar with CURSORS. Will they solve some of this problem? Chris Wood
truett@cup.portal.com (01/12/88)
It turns out that in order to solve the problem, a single cursor is needed. The cursor is defined for a SELECT . . . FOR UPDATE OF . . . and then the UPDATE . . . is performed using the qualifier CURRENT OF "cursorname". The looping action within which the FETCH is performed sems to differ according to the particular implementation of SQL involved. Of course, it may be that using the FOR UPDATE OF and CURRENT OF constructs with a cursor ties the two operations together so that everything works without an explicit looping type of syntax. That would seem to be more in the spirit of "nonprocedural" languages. (I will defer the diatribe about how sleazy the term "non- procedural" is. :-)) Truett Lee Smith, Sunnyvale, CA UUCP: truett@cup.portal.com
UH2@PSUVM.BITNET (Lee Sailer) (01/13/88)
In article <71@coot.AUSTIN.LOCKHEED.COM>, chris@AUSTIN.LOCKHEED.COM (Chris Wood) says: > >In article <29265UH2@PSUVM>, UH2@PSUVM.BITNET (Lee Sailer) writes: >> I've never used any SQL product, but supposedly know some of the underlying >> ideas. How about something like >> >> good customers = Select count(id#) >> <code deleted> > >All of this is fairly close, but the point is that I could do it in >1 query with M204, but a single query in SQL can only do a single thing. >This means that if I want to do several things to the same set of >records, I must re-retrieve those records for each function. I guess this As the technology improves, you will see optimizers that recognize when two queries could be more efficiently executed in parallel, and will automagically arrange this for the user. Currently, disk cacheing schemes can do nearly the same thing. The only hope for optimizers in the future is to limt the set of allowable constructs today. >Question: I am (embarassingly) unfamiliar with CURSORS. Will they >solve some of this problem? > As others have described, cursors are the mechanism that lets SQL be embedded in languages such as C, PL/1, and COBOL. Indeed, they solve many problems. However, they also allow the data base security and integrity schemes to be subverted, and so must be used by knowledgable programmers, and not by the average end-user.