[Tfug] Still another RDBMS

[Nate]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well, I don't know exactly what RDBMS you're talking about, but I've
done exactly what you describe using cursors and PL/SQL in Oracle
before, and similar with Postgres before.  Cursors are server side.  You
can use the cursor to iterate only a portion of the result set and
return just that portion.  As for read/writer conflicts, it depends on
the database and the locking methods.  With Oracle, I think you had the
option of locking the result set, so that any writers would wait for the
lock to clear.  If you didn't, then you had the possibility of having
stale, but still atomic, data.  It depends on how long-lived your
application is.  You could have it not lock anything, and just "refresh"
by running the store procedure again after a certain time-out, or manual
intervention (refresh button).

But it's been a long time (5+ years) since I did anything with cursors,
so someone else might be able to be more specific.  I just mainly do web
stuff any more, and shy away from stored procedures except in cases
where I need the performance.

Nate

Bexley Hall wrote:
> Hi,
> 
> Sorry for the "stupid" questions... but I work in
> the Land of the Small so dealing with issues from
> the Land of the Plentiful is like learning a whole
> new way of thinking  :-(
> 
> Given that my world is resource starved, how can
> I cope with and *exploit* the abundance of resources
> in a typical DB server?
> 
> Hopefully, this example puts things in perspective:
> 
> Imagine an address book database.  Now you want to
> design a user interface to that DB.  Consider
> something
> like a Palm Pilot connected to that DB...
> 
> The user wants to be able to browse the entries in
> the DB -- sort of like flipping through the pages in
> a phonebook.  Of course, the PDA has a fixed size
> (small) screen so only about a dozen names can appear
> at any one time.
> 
> Also, there is really only enough room for the list
> of *names* on that screen -- definitely not enough to
> list ALL of the information associated with each
> name!
> 
> It's foolish to do a "SELECT * ..." on the DB and then
> scroll through the resulting data on the PDA -- that
> requires the PDA to be able to store the entire
> results of the query (I know folks with *thousands*
> of contacts in their address books so it is
> conceivable
> that several MB of data could be involved).
> 
> It is also foolish to do this since it would require
> client-side code to "filter" the results -- i.e.,
> "display entries 1 - 10", "display entries 11 - 20",
> etc.  That's something the RDBMS is *optimized* to
> do, already!  (e.g., "show me all the names that
> begin with 'G'").
> 
> Likewise, it is foolish to pull down *all* of the data
> associated with each entry -- since only the name and
> perhaps phone number are displayed on that top level
> "browse" screen.  Instead, wait for the user to pick
> *a* particular name and then issue a new query to
> the DB for the details of that entry.
> 
> I *think* cursors let me more efficiently access
> the DB in this manner.  But, I have some concerns
> about their implementation.
> 
> - are they a server-side feature (vs. client-side)?
>   I.e., that functionality could also be implemented
>   on the client-side (but at a much higher cost).
> - how do cursors address reader-writer conflicts?
>   I.e. what if an entry is inserted *before* (above)
>   the current cursor position?  (how will it ever
>   appear??)
> 
> Given that the sample application has LONG periods
> between apparent "motion" through the data, what
> other issues am I likely to face?  What other
> remedies are available to me??
> 
> (sigh)  Boy, it's a real PITA trying to force a
> marriage between these two worlds!  (small vs.
> plentiful)
> 
> Thanks for any pointers!
> --don
> 
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com 
> 
> _______________________________________________
> Tucson Free Unix Group - tfug at tfug.org
> Subscription Options:
> http://www.tfug.org/mailman/listinfo/tfug_tfug.org

- --
Nate
System Admin Manager
System Administration
Ext 220
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHMLFKRMRYK1K/wKQRAt5AAJ4iNtv8nFB4t7fSzyePjQ1IRrGo8QCfSSl9
zHNYAImqP5WzervJpd06jeE=
=ECUz
-----END PGP SIGNATURE-----