Re: Fetch previous

"David Fitzjarrell" <oratune_at_msn.com> wrote in message news:32d39fb1.0204141424.5debb505_at_posting.google.com...
> Comments embedded.
>
> "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
news:<dxhu8.33996$CH1.27531_at_sccrnsc02>...
> > The original question was rather short on what language he wanted to do
it
> > in.(not specified)
>
> That does not justify issuing a terse response.
>
> > ... ODBC could support it since OCI does, and all the other
> > MS access methods could also since at their root they are making OCI
calls.
>
> So does SQL*Plus however it doesn't support such functionality. There
> are specific calls within OCI to handle scrollable cursors, none of
> which are shown to exist for ODBC or OLEDB, making them unable to
> correctly interpret such cursors. Also, the 'other MS access methods'
> are not likely to contain these calls. Likewise for SQLJ; I know of
> no MS interfaces to Oracle that utilise SQLJ or the more esoteric OCI
> functions. ODBC and OLEDB are basic interfaces to database engines;
> usually what works for one works for all, and the one-off
> functionalities of each product are usually left out.
>

One can write an ODBC driver that supports oci scrollable cursors. Just because no one has does not make it not possible. The same goes for other MS interfaces. All those interfaces beneath the covers do is make OCI calls. The MS interfaces are just interfaces, someone else in a lot of cases writes the driver underneath. I think oo4o supports scrollable cursors in 9i.

> > He didn't cite that he had looked in the documentation - it is online, 2
or
> > three minutes looking at it would say yes. I don't mind giving complete
> > answers if the person makes some attempt at trying to find the answer
first.
> > Jim
>
> So for you to provide a life preserver to a drowning man you need
> proof that he failed a basic swimming course ... I'd hate to be on a
> cruise ship with you.
>

This isn't a cruise ship and he is not a drowing man. He didn't scream help; he just asked a question. I find it hard to believe that you don't know the difference between someone asking a question and someone drowning or similar mortal peril. I, like over 99% of the other people on the planet, would make signifigant effort to preserve any human life in danger. This isn't that case. At least with drowning people they thank you afterwards. Notice he didn't. Don't take it out on me that he is ungrateful.
Jim

> >
> > "David Fitzjarrell" <oratune_at_msn.com> wrote in message
> > news:32d39fb1.0204140709.392db899_at_posting.google.com...
> > > Well, THAT was short and to the point, wasn't it? And, actually, it
> > > wasn't 'to the point' at all, since only OCI and SQLJ support
> > > scrollable cursors in 9i.
> > >
> > > As an example, for SQLJ the general FETCH syntax for positional
> > > iterators is:
> > >
> > > #sql { FETCH :iter INTO :x, :y, :z };
> > >
> > > This is actually an abbreviated version of the following syntax.
> > >
> > > #sql { FETCH NEXT FROM :iter INTO :x, :y, :z };
> > >
> > > This suggests the pattern for alternatively moving to the previous,
> > > first, or last row in the result set. (Unfortunately, JDBC 2.0--after
> > > which the movement methods were modeled--uses previous(), whereas the
> > > FETCH syntax, which is patterned after SQL, employs PRIOR. In case you
> > > should forget this inconsistency, the SQLJ translator will also accept
> > > FETCH PREVIOUS.)
> > >
> > > #sql { FETCH PRIOR FROM :iter INTO :x, :y, :z };
> > > #sql { FETCH FIRST FROM :iter INTO :x, :y, :z };
> > > #sql { FETCH LAST FROM :iter INTO :x, :y, :z };
> > >
> > >
> > > There is also syntax to pass a numeric value for absolute or relative
> > > movements, to move to a particular (absolute) row, or to move forward
> > > or backward from the current position:
> > >
> > > #sql { FETCH ABSOLUTE :n FROM :iter INTO :x, :y, :z };
> > > #sql { FETCH RELATIVE :n FROM :iter INTO :x, :y, :z };
> > >
> > >
> > >
> > > OCI is a bit more complex; let's assume a result set is returned by
> > > the SQL query:
> > >
> > > SELECT empno, ename FROM emp
> > >
> > > and that the table EMP has 14 rows. One usage of scrollable cursors is
> > > illustrated in the following code snippet:
> > >
> > > ...
> > > /* execute the scrollable cursor in the scrollable mode */
> > > OCIStmtExecute(svchp, stmthp, errhp, (ub4) 0, (ub4)
> > > 0, (CONST OCISnapshot *)
> > > NULL, (OCISnapshot *) NULL,
> > > OCI_STMT_SCROLLABLE_READONLY );
> > >
> > > /* Fetches rows with absolute row numbers 6, 7, 8. After this call,
> > > OCI_ATTR_CURRENT_POSITION = 8, OCI_ATTR_ROW_COUNT = 8 */
> > > checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 3,
> > > OCI_FETCH_ABSOLUTE, (sb4) 6,
> > > OCI_DEFAULT);
> > >
> > > /* Fetches rows with absolute row numbers 6, 7, 8. After this call,
> > > OCI_ATTR_CURRENT_POSITION = 8, OCI_ATTR_ROW_COUNT = 8 */
> > > checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 3,
> > > OCI_FETCH_RELATIVE, (sb4) -2,
> > > OCI_DEFAULT);
> > >
> > > /* Fetches rows with absolute row numbers 14. After this call,
> > > OCI_ATTR_CURRENT_POSITION = 14, OCI_ATTR_ROW_COUNT = 14 */
> > > checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1,
> > > OCI_FETCH_LAST, (sb4) 0, OCI_DEFAULT);
> > >
> > > /* Fetches rows with absolute row number 1. After this call,
> > > OCI_ATTR_CURRENT_POSITION = 1, OCI_ATTR_ROW_COUNT = 14 */
> > > checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 1,
> > > OCI_FETCH_FIRST, (sb4) 0, OCI_DEFAULT);
> > >
> > > /* Fetches rows with absolute row numbers 2, 3, 4. After this call,
> > > OCI_ATTR_CURRENT_POSITION = 4, OCI_ATTR_ROW_COUNT = 14 */
> > > checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 3,
> > > OCI_FETCH_NEXT, (sb4) 0, OCI_DEFAULT);
> > >
> > > /* Fetches rows with absolute row numbers 3,4,5,6,7. After this call,
> > > OCI_ATTR_CURRENT_POSITION = 7, OCI_ATTR_ROW_COUNT = 14. It is assumed
> > > the user's define memory is allocated. */
> > > checkprint(errhp, OCIStmtFetch2(stmthp, errhp, (ub4) 5,
> > > OCI_FETCH_PRIOR, (sb4) 0, OCI_DEFAULT);
> > > ...
> > > }
> > > checkprint (errhp, status)
> > > {
> > > ub4 rows_fetched;
> > > checkerr (errhp, status);
> > > checkerr(errhp, OCIAttrGet((CONST void *) stmthp, OCI_HTYPE_STMT,
> > > (void *) &rows_fetched, (uint *) 0,
> > > OCI_ATTR_ROWS_FETCHED, errhp));
> > > }
> > > ...
> > >
> > >
> > > All of the above code samples came from the 9i documentation. I hope
> > > this helps you.
> > >
> > >
> > > "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
> > news:<XFXt8.6183$V44.50_at_rwcrnsc53>...
> > > > yes.
> > > > Jim
> > > > "News" <jjdenboer_at_ncrvnet.nl> wrote in message
> > > > news:a98vv9$51s$1_at_news.solcon.nl...
> > > > > Hi,
> > > > >
> > > > > Does anybody know if it is possible in Oracle 9i to do a fetch
> > previous ?
> > > > >
> > > > > --
> > > > > Regards,
> > > > >
> > > > > Johan den Boer
> > > > > email : j.denboer_at_ns.nl
> > > > >
> > > > >
> > > > >
> > > > >
Received on Sun Apr 14 2002 - 18:50:01 CDT