nigel@cnw01.storesys.coles.oz.au (Nigel Harwood) (06/25/91)
Okay, useless may be a bit strong, but not much. At least if this is true at any rate. I am beginning with PRO*C and my first program needs to read the rows from a table and modify each with values from a flat file before writing them back to the table. My first step was to set up a loop to display a test table to see that I had the basics correct. I did this by declaring a cursor, opening it, using a loop with a fetch to read through the rows selected. This worked as expected. My next step was to modify the fields with the information from the flat file, do an update to the table, and then a commit to complete the transaction etc. This failed with a "fetch out of sequence" error on the second row. On asking Oracle they said that you cannot do a commit in the middle of fetching ? In other words in doing what I want I can only do one commit and that's right at the end. How can I do that if I don't know how many records I might have in the table ? I've got something horribly wrong, right ? This can't be real, can it ? If I am forced to accept that this is the way of things, how do all you other Oracle programmers deal with this everyday processing loop ? Any help appreciated. Regards -- <<<<<<<<<<<<<<<<<<<<<<<<< Nigel Harwood >>>>>>>>>>>>>>>>>>>>>>>>>>> << Post: Coles Myer Ltd, PO Box 2000 Tooronga 3146, Australia >> << Phone: +61 3 829 6090 E-mail: nigel@cnw01.storesys.coles.oz.au >> << FAX: +61 3 829 6886 >>
par@sirius.Aus.Sun.COM (Paul Riethmuller SE Brisbane) (06/26/91)
nigel@cnw01.storesys.coles.oz.au (Nigel Harwood) writes: > Okay, useless may be a bit strong, but not much. At least if > this is true at any rate. > > ... deleted > > On asking Oracle they said that you cannot do a commit in the middle > of fetching ? In other words in doing what I want I can only do one > commit and that's right at the end. How can I do that if I don't > know how many records I might have in the table ? > > I've got something horribly wrong, right ? > This can't be real, can it ? Well, it does pose a problem for the developer - but you can some consolation in knowing that this limitation isn't just Oracle's. Every commercial SQL DBMS I've ever seen closes all open cursors [SQL/DS, DB2, Ingres ...] when a commit is issued. (Although their forms languages may behave differently) In your case, you could drive the transaction from the ASCII update file and just issue UPDATE commands. BTW, doing multiple fetch,update,commit operations is intensive. The alternative would be to do ~100 fetch,update operations, record your position in the cursor scan (and possibly the file offset - for recovery purposes) then issue 1 COMMIT. Good Luck Paul Riethmuller Sun Microsystems, Brisbane. Disclaimer: these are the personal opinions of the author.
elaps@lut.ac.uk (Alan Schwarzenberger) (06/26/91)
I had a long running discussion with the Oracle help desk in the uk
about this a few months back. A commit definitely closes the cursor.
However, in version 6 of oracle, if you make a change to a column that
is in your cursor, you'll see it immediately. For example
If test_table has columns a, b, reject.
EXEC SQL DECLARE C1 CURSOR FOR
select a, b from test_table for update of reject;
EXEC SQL OPEN C1;
do {
EXEC SQL FETCH C1 INTO :a, :b;
/* other code */
if (condition)
EXEC SQL UPDATE test_table SET reject=1 WHERE CURRENT OF C1;
} while (more_rows);
EXEC SQL CLOSE C1;
EXEC SQL COMMIT;
In this example, changes you make to the column reject are visible in
the cursor C1 th next time you go round the do{}while loop. I use this
because I need to know what other rows of data have already been rejected
when I llok at the current row of data.
I use Oracle RDBMS v6.0.30.3.1 running on HP9000 under HPUX, ProC
v1.3.15.1.2
--
============================================================================
Alan Schwarzenberger tel +44 509 222849 A.P.Schwarzenberger@lut.ac.uk
International Electronics Reliability Institute, Loughborough University, UKjfr@locus.com (Jon Rosen) (06/27/91)
In article <1991Jun26.033043.22948@sunaus.oz> par@sirius.Aus.Sun.COM writes: >nigel@cnw01.storesys.coles.oz.au (Nigel Harwood) writes: >> ... deleted >> On asking Oracle they said that you cannot do a commit in the middle >> of fetching ? In other words in doing what I want I can only do one >> commit and that's right at the end. How can I do that if I don't >> know how many records I might have in the table ? >> I've got something horribly wrong, right ? >> This can't be real, can it ? >Well, it does pose a problem for the developer - but you can some consolation >in knowing that this limitation isn't just Oracle's. Every commercial SQL >DBMS I've ever seen closes all open cursors [SQL/DS, DB2, Ingres ...] when >a commit is issued. Tis true, tis true... If the SELECT is only ordered on an index and you are not doing anything else that requires prefetching the entire answer set (like aggregate selection (HAVING) or something), most DBMSs will not actually fetch the entire answer set but will in fact retrieve it row-by-row for you. In this case, after each commit of the update (if you need to do this), you can issue a new SELECT cursor request with a WHERE predicate that asks for the next sorted column(s) of the index greater than the one just updated. This actually works pretty well in DB2, particularly if you are using static SQL (dynamic SQL sucks on this problem for the most part since it has to reoptimize the query each time you open the cursor). DB2 Release 2.3 has actualy sort of fixed this with a COMMIT HOLD option which will commit but keep the cursor position available. However, I have no idea yet what kind of locks will be held (the purpose of COMMIT afterall is also to release locks and if this doesn't happen, the cursor approach will still suck). What happens depends on the DBMS and your mileage may vary. >(Although their forms languages may behave differently) If a DBMS's forms language (or for that matter any tool) behaves differently, than it is using something other than SQL as its access method (or at least is augmenting its SQL with some kind of under-the-covers scheme for locking and navigation). This is not only not good but it can be very dangerous, particularly if you mix applications that use the tool with regular SQL applications and the DBMS doesn't integrate the usage properly. Oracle was certainly guilty of this in early SQL*Forms... I have no idea it that is still true. It was supposed to have changed with Forms 4.0 but I am not sure whether that has ever been released. Jon Rosen
markj@informix.com (Mark Jeske(Chicago Consultan)t) (06/27/91)
In article <1991Jun26.033043.22948@sunaus.oz> par@sirius.Aus.Sun.COM writes: > >nigel@cnw01.storesys.coles.oz.au (Nigel Harwood) writes: > >Well, it does pose a problem for the developer - but you can some consolation >in knowing that this limitation isn't just Oracle's. Every commercial SQL >DBMS I've ever seen closes all open cursors [SQL/DS, DB2, Ingres ...] when >a commit is issued. > Not Informix. In our 4.0 product you can declare a cursor with hold and it won't be closed during a commit. mark
jfr@locus.com (Jon Rosen) (06/27/91)
In article <1991Jun26.100717.17267@lut.ac.uk> elaps@lut.ac.uk (Alan Schwarzenberger) writes: >I had a long running discussion with the Oracle help desk in the uk >about this a few months back. A commit definitely closes the cursor. >However, in version 6 of oracle, if you make a change to a column that >is in your cursor, you'll see it immediately. For example >If test_table has columns a, b, reject. > >EXEC SQL DECLARE C1 CURSOR FOR > select a, b from test_table for update of reject; >EXEC SQL OPEN C1; >do { > EXEC SQL FETCH C1 INTO :a, :b; > /* other code */ > if (condition) > EXEC SQL UPDATE test_table SET reject=1 WHERE CURRENT OF C1; >} while (more_rows); >EXEC SQL CLOSE C1; >EXEC SQL COMMIT; > >In this example, changes you make to the column reject are visible in >the cursor C1 th next time you go round the do{}while loop. I use this >because I need to know what other rows of data have already been rejected >when I llok at the current row of data. Be careful about generalizing this theory. It will only work when Oracle traverses the table through the cursor on a row-by-row basis as each FETCH is processed. This will in fact occur quite often, particularly when you have no ORDER BYs and no aggregates or GROUP BY expressions. In addition, evem when you have ORDER BYs, this may still happen because either an index is available on the ORDER BY column(s) and/or Oracle chooses to build and sort a list of ORDER BY columns only and then retrieve by ROWIDs that it keeps with the sorted list. This is fraught with peril, however, since you can not be sure this will always work. If an ORDER BY on an unindexed column is used and the entire SELECT list is retrieved, sorted and held as an answer set, the result will be completely different and any updates or deletes you do while the cursor is open will no work. In fact, you may block due to locks held on your rows (depending on the kind of cursor SELECT you are doing) and you might even find your update being cancelled for deadlock reasons. In addition, you should realize that such behavior is totally inconsistent with any definition of the relational model since the cursor/FETCH mechanism is an artifact of procedural language requirements... In relational data bases and SQL, the SELECT should retrieve a complete set of rows at the instant in time when it is executed. Any subsequent changes to those rows (if any are allowed) should not be reflected until a reSELECT is done. Jon Rosen
gnh@cci632.cci.com (George Hillenbrand) (06/27/91)
In article <25762@oolong.la.locus.com> jfr@locus.com (Jon Rosen) writes: > ... If the SELECT is only ordered on an index > and you are not doing anything else that requires prefetching the > entire answer set (like aggregate selection (HAVING) or something), > most DBMSs will not actually fetch the entire answer set but will > in fact retrieve it row-by-row for you. ... Which among the major relational databases (Oracle, Informix, Ingress, Sybase...) avoid the prefetch for a select of a set of records having a particular indexed field value?
gatynen@well.sf.ca.us (Gerard Tynen) (06/27/91)
Having last year built an embedded SQL precompiler for cobol and SQL Server, we came across this "commit closes all cursors" issue. We couldn't think of any reason to force a close on all cursors just because you do a commit. I think that what happened is that early versions of DB2 did this for some reason related to DB2 and MVS and so everybody else who did a precompiler did it also. Now this kind of behaviour has become standard. The NIST verification suites test for it, and I think also it is being assumed by the SAG commitee as well. We ended up leaving our cursors open after a commit which is nice, because you don't lose your place in the result set and can commit as often as you feel you need to. However, I just got a call from the customer I did the precompiler for and they want to change it back to having commits close all open cursors. I assume they want this in order to remain "standard" since there is no real reason I can think of to force this. --GT
dlm@hermes.dlogics.com (06/28/91)
nigel@cnw01.storesys.coles.oz.au (Nigel Harwood) writes: > On asking Oracle they said that you cannot do a commit in the middle > of fetching ? In other words in doing what I want I can only do one > commit and that's right at the end. How can I do that if I don't > know how many records I might have in the table ? There is no law that says "Thou shalt commit after every update". In fact, after you fetch a row, you can use UPDATE with the CURRENT OF CURSOR clause specifically to make use of the cursor information at hand. When you run out of rows in the result set, THEN you close the cursor, and THEN you commit. Committing after each update is inadvisable for performance reasons; knowing the number of rows in the result set is not especially important. Oracle will jump out of the FETCH loop when the result set is emptied. Refer to EXEC SQL WHEN SQLERROR ... in the manual. -- Dave Mausner, Sr Tech Consultant / Datalogics Inc / Chicago IL / 312-266-4450 dlm@hermes.dlogics.com "Don't talk about it -- just show me the code!"
mao@eden.Berkeley.EDU (Mike Olson) (06/28/91)
In <25731@well.sf.ca.us>, gatynen@well.sf.ca.us (Gerard Tynen) writes > We couldn't think of any reason to force a close on all cursors just > because you do a commit. if you're doing two-phase locking, and you release locks at commit time, you'd better close your cursors. changes by concurrent transactions will become visible at that time, too, which means that the set of prefetched values cached in the cursor may be wrong if you keep it open. mike olson postgres research group uc berkeley mao@postgres.berkeley.edu
epstein@world.std.com (Richard W Epstein) (06/28/91)
In article <25731@well.sf.ca.us> gatynen@well.sf.ca.us (Gerard Tynen) writes: >We couldn't think of any reason to force a close on all cursors just >because you do a commit. I think that what happened is that early [deleted...] A commit by definition makes any updates,inserts, and deleted permanent and releases locks. After this happens, where would your cursor point? Since you released your locks, you cannot be sure the records or even the table is still there or that you have access to them. Ingres has a command called 'savepoint' and allows you to rollback to the last savepoint without aborting the entire transaction. I don't think ANSI SQL has any provision for this. The real need for this seems to be in syncing with external devices/files which are not under the DBMS transaction control. Transactions loose some of their usefulness when all parts of the update cannot rollback. --Richard W. Epstein epstein@world.std.com
IAN@SLACVM.SLAC.STANFORD.EDU (06/29/91)
Oracle has SAVEPOINT as well. An Informix employee mentioned using the
hold option. Oracle has a hold cursor and a release cursor option. I
don't know whether they will solve the commit problem. I thought they
were used so that after a cursor was closed you could open the cursor
again with a different value in the predicate. You didn't need to
redeclare the cursor. In fact Oracle is not ansi standard in that it
allows you to re-open a cursor without closing it first, (with the
default option settings). What exactly does hold cursor mean to Informix
Ian MacGregor [Stanford Linear Accelerator Center]aland@informix.com (Colonel Panic) (06/29/91)
In article <1991Jun26.033043.22948@sunaus.oz> par@sirius.Aus.Sun.COM writes: > >nigel@cnw01.storesys.coles.oz.au (Nigel Harwood) writes: >> >> On asking Oracle they said that you cannot do a commit in the middle >> of fetching ? In other words in doing what I want I can only do one >> commit and that's right at the end. How can I do that if I don't >> know how many records I might have in the table ? >> >> I've got something horribly wrong, right ? >> This can't be real, can it ? > >Well, it does pose a problem for the developer - but you can some consolation >in knowing that this limitation isn't just Oracle's. Every commercial SQL >DBMS I've ever seen closes all open cursors [SQL/DS, DB2, Ingres ...] when >a commit is issued. This is an ANSI requirement; terminating a transaction (COMMIT or ROLLBACK) releases all locks and closes all cursors. Informix (at least) provides an optional cursor extension known as a persistent cursor or "hold" cursor. A cursor declared WITH HOLD, once opened, remains open (regardless of transactions) until explicitly closed (or until the database itself is closed). Commit/Rollback still releases all locks, so the developer must be careful in her/his integrity assumptions (any rows updated in the hold cursor will not necessarily remain locked, even under explicitly higher Isolation Levels). >Paul Riethmuller >Sun Microsystems, Brisbane. -- Alan Denney # aland@informix.com # {pyramid|uunet}!infmx!aland We'll sue your drummer in perfect four/four time We'll sue your manager for his last thin dime We'll sue your record label, hey now ain't this fun We'll sue your best friend if you still have one It ain't no crime, 'cause I'm a rock and roll lawyer -- The Austin Lounge Lizards
gatynen@well.sf.ca.us (Gerard Tynen) (06/30/91)
In article <1991Jun27.225555.27108@agate.berkeley.edu> mao@eden.Berkeley.EDU (Mike Olson) writes: >In <25731@well.sf.ca.us>, gatynen@well.sf.ca.us (Gerard Tynen) writes > >> We couldn't think of any reason to force a close on all cursors just >> because you do a commit. > >if you're doing two-phase locking, and you release locks at commit time, >you'd better close your cursors. changes by concurrent transactions will >become visible at that time, too, which means that the set of prefetched >values cached in the cursor may be wrong if you keep it open. I agree. Our problem was that we were building our cursors on top of Sybase browse mode, which doesn't lock anything. You are really working on a copy of the data. When you update on the cursor, it compares timestamps to see if anybody modified that row under your nose. Also, as you indicate, updates that change the result set are not reflected when you fetch unless you reopen the cursor. I think we just documented that updatable cursors only fetch data as it existed in the database at the time the select statement executed, and if the user wanted current data, they would have to close and reopen the cursor. This is bogus, I know, but I feel I can blame Sybase for this bogusicity. They are the only database I know of that allows database connections opened by the same process to deadlock each other. That means you can't update a table you are reading without blowing away the result set unless you use their lame browse mode, but then you really just get a copy of the data as it existed at some point in time. Hey. I'm ready to switch to Postgres! So anyway, back to the original point, I now understand that real cursors ought to be closed upon commit. My cursors weren't what I would call real cursors, after all. I would call them Sears Cursors. Hopefully Sybase 5.0 will not implement Sears Cursors... --GT
gatynen@well.sf.ca.us (Gerard Tynen) (06/30/91)
In article <1991Jun27.230131.2358@world.std.com> epstein@world.std.com (Richard W Epstein) writes: >In article <25731@well.sf.ca.us> gatynen@well.sf.ca.us (Gerard Tynen) writes: >>We couldn't think of any reason to force a close on all cursors just >>because you do a commit. I think that what happened is that early >[deleted...] > >A commit by definition makes any updates,inserts, and deleted permanent >and releases locks. After this happens, where would your cursor point? >Since you released your locks, you cannot be sure the records or >even the table is still there or that you have access to them. I believe that Sybase uses a different defintion of "commit". You can open a number of connections and if you commit on one connection, the other connections are not affected; their changes are still outstanding. Each connection is actually considered a separate user. So we were able to use one connection for reading, and another for DML. We didn't close the cursor, although if an update or insert affected the result set, the cursor would have to be reopened to see those changes. Also we didn't lock what we were reading, so yes, I agree with you that drastic changes could take place in the database and the cursor would not know anything about it. --GT
gatynen@well.sf.ca.us (Gerard Tynen) (06/30/91)
>>DBMS I've ever seen closes all open cursors [SQL/DS, DB2, Ingres ...] when >>a commit is issued. >> > >Not Informix. In our 4.0 product you can declare a cursor with hold >and it won't be closed during a commit. Also IBM Database Manager can declare cursors "with hold", and their positioning is maintained across commits... -GT
dlm@hermes.dlogics.com (06/30/91)
I still think this thread is missing the point.
The original question was: "how do i update while i am in a fetch loop; if i
update and commit, the cursor closes!". So everyone gets into a discussion
of when cursors close or don't close.
The answer is: "There is no need to commit until you are done with the fetch".
That way, the problem with unexpectedly closed cursors becomes moot, and the
guy can fix his code and get on with his life.
I am pretty sure the folks at oracle will agree with me on this. ;-)
--
Dave Mausner, Sr Tech Consultant / Datalogics Inc / Chicago IL / 312-266-4450
dlm@hermes.dlogics.com
"Don't talk about it -- just show me the code!"