Re: Scrollable Cursors and searching with SQL

> > Hi All, > > I am new to SQL and find it somewhat frustrating having come from an ISAM > environment. Our applications are highly interactive regarding record selection > etc. yet it appears that SQL is limited in these areas. > > I would like to know if it is possible to locate a record and > then from that position scroll forward backwards etc. ? > > If I have a scrollable cursor can I search within it, without having to step > through each record? > > If not are there other ways of tackling these problems? > > Thanks in advance > Theo > theo@rascal.co.za > Oh why not - I've run into this sort of question so often from ISAM converts that I'm going to go ahead and go public. We might even wish to elaborate on this and include it in the FAQ. ---------- Sure. You don't HAVE to have a cursor to read data. You can just SELECT column(s) INTO program_var(s) FROM table(s) WHERE condition(s) (caution, record must be unique) All the cursor gives you is a) a set of data that matched your conditions, b) the ability to lock that data while you update it. It is merely a harness to slip around a SELECT statement. You can also do multiple reads against a table. So if you want NEXT/PREVIOUS or RELATIVE reads from your current 'position', you can do a second query for some data and then use that info for positioning. (kludgy workaround - used only by those who don't really get it). You can also use the single cursor, read through it until you find the data you want and THEN display it. (Less kludgy, but still not using cursors to their full potential) I realize that this seems less powerful than ISAM. The key to it is in realizing that YOU control what data the cursor will point to, and the order ^^^ it is read. Once you make that paradigm shift then you'll start to understand the power of the CURSOR and its harnessed SELECT. As an exercise, ask yourself what information you want from your database and then fetch it, and only it. cheers j. _____________________________________________________________________________ Jack Parker - Hewlett Packard, BSMC Boise, Idaho, USA jparker@hpbs3645.boi.hp.com _____________________________________________________________________________ "A thing is bigger for being shared" - Gaelic _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________