Path: utzoo!attcan!uunet!lll-winken!lll-tis!helios.ee.lbl.gov!nosc!ucsd!ames!amdahl!rtech!davek From: davek@rtech.rtech.com (Dave Kellogg) Newsgroups: comp.databases Subject: Re: INGRES/EQC and SQL/C ...and general notes... Message-ID: <2407@rtech.rtech.com> Date: 30 Aug 88 21:57:05 GMT References: <24546@bu-cs.BU.EDU> <2401@rtech.rtech.com> <24585@bu-cs.BU.EDU> Reply-To: davek@rtech.UUCP (Dave Kellogg) Organization: Relational Technology Inc, Alameda CA Lines: 94 In article [...] berlin@buita.bu.edu (David Fickes Einstein Project) writes: [quoting himself from an earlier posting] >> >> One other question. What's so hard about supporting NESTED EQUEL >> statements in the EQUEL/C processor??? This really isn't the pre-processor's choice. It's giving you a warning when you nest a statement within a retrieve loop only because it was told to. And it was told to simply because retrieve loops weren't designed to support that construct. What, then, are they designed to do? The answer is that the EQUEL retrieve loop was designed (and this is more of a DBMS issue than an pre-processor issue) as a high-speed "portal" through which you can extract multiple rows from the database. The "high-speed" part is true because retrieve loops are implemented to return lots of rows in user-tuneable "pipeblocks" from the back-end to the front-end. In most cases, even if your front-end code isn't ready to accept another row, the back-end (or DBMS) will be finding rows, and filling up pipeblocks full of data to send to the front-end. Thus, the back-end won't be looking for (or expecting) another DBMS statement until you either get all the rows, or issue a ## endretrieve to stop the processing. It *can* (architecturally, no flames please) get data back faster than other approaches (discussed below) because it's job is to get data and send it back to the front-end. Thus, given it's fairly narrow duty, it does it pretty fast. All this springs from the issue that QUEL and SQL are 'set-oriented' languages. QUEL and SQL commands generally affect sets of tuples. RETRIEVE/SELECT statements can return 0, 1, or a whole bunch of rows. Similarly, a single UPDATE statement can change 0, 1 or a whole bunch of rows. Programming languages, on the other hand, tend to be more 'record- oriented.' Thus the problem becomes interfacing a record-oriented language with a set-oriented one. Two approaches are: 1. RETRIEVE-loops 2. CURSORs Retrieve loops were invented in QUEL and a code fragment best shows the idea. ## retrieve ( name = emp.name ) ## { printf("Name is now: %s\n", name); ## } That is, once for every row that comes back from the DBMS, the code between the ## { and the ## } will be executed. Cursors came with SQL and the idea is different. With cursors you 'declare' a named cursor which will run a query. You then 'open' the cursor which conceptually sets the cursor to be pointing right before the first row to be returned. Finally, you issue [multiple] 'fetch'es from the cursor and after each fetch a row is returned and the conceptual pointer is moved forward to the next qualifying row. Since you can have multiple cursors open at one time, and you can update and delete the current row of a cursor, you get the nesting effect that David desired. In pseudo ESQL (because this is getting long) EXEC SQL DECLARE FRED CURSOR FOR [.. select statement ..] EXEC SQL OPEN FRED while ( rows_keep_coming_back ) { EXEC SQL FETCH [.. next row from the cursor ..] } Thus, *depending on the implementation* (no flames please), retrieve- loops can get data faster than cursors because retrieve-loops send back 'chunks' of data and not rows. Cursors, *conceptually*, send back rows, and if implemented "literally" will cause more inter-process communication for DBMS's (like most) where the front-end and back-end are separate processes. In any case, with INGRES 6 QUEL users will get the best of SQL (cursors will be supported in QUEL) and SQL users will get best of QUEL because select-loops will be supported in SQL. In version 5, each construct is found only in its native habitat (or language). Sorry this was so long. David Kellogg Relational Technology (INGRES) New York City davek@rtech.rtech.com "Opinions above are mine and not necessarily those of my employer"