RE: ODBC (was RE: Green screen - it's time is over ) -- MIDRANGE-L

> -----Original Message-----
> From: Walden H. Leverich
>
> OK, I'm totally confused then. How can you have ONE (as in 1, uno, single,
> less than 2) program that handles ANY and ALL interaction with a file? It
> handles updates? Inserts? Deletes? Queries (get price, get
> description, list
> items where price < 100, etc.) Man, that must be one hell of a
> program. Any
> chance you could post a small one so we can see that?

A single program that handles all the updates, inserts and deletes is not
that difficult.  It's the same code you have in your standard maintenance
application, Walden, except it's in batch.  We call it a CRUD server, for
Create, Read, Update and Delete, and it's been around for a long, long time.
In a typical transaction environment, this will handle the majority of your
database functions.   While a "small" one still requires more than a few
lines of code, I'll make an old template available for download at the
following URL:

http://www.plutabrothers.com/pcs400/cussrv.txt

You can use this program as a template for any CRUD server.  Only the lines
that have an "@" in the first position change from file to file.  It also
has a "set get" function; this is the same code used to load the first panel
in your standard maintenance program.

A surprising percentage of your files don't need much more than that.
Typical transaction-based business applications don't do much more than get
a record and update it.

This may not be enough for your entire application, of course.  As you begin
to move from a transaction-processing environment to a data mining
application, you may need more flexible access.  If a specific type of query
is used over and over, then you can create a second server, which we call
the QUERY server.  It handles some more advanced query functions, such as
resolving foreign keys, expanding hierarchies, and the like.  For example,
you can design a QUERY server that will gather all the records associated
with an order, including history, allocations, GL postings, the whole nine
yards.

You'll object that now you have TWO programs that access the same file.
Guilty as charged, but with extenuating circumstances - I'm not adding logic
to a bunch of application prgorams, I'm instead creating a specific server
to handle these types of queries.  As long as I don't go hog wild creating
dozens of servers for every file, I'm okay.

Because I'll let you in on a little secret: I'm not a stickler for "one
file, one program".  I'm a stickler for "the fewer programs accessing a file
the better", along with an absolutely inviolable "any program that updates
the database is on the server".  In fact, I usually insist that only one
program UPDATES a given file, but for certain files I may need some more
powerful query servers.

Heck, I may have half a dozen for some files that are used in many different
situations.  But that's still easier to maintain than trying to search
through hundreds or even thousands of application programs to see which ones
access a given file.  Especially since, with SQL, you have to do string
searches.

So, for advanced business logic queries - that is, ones that can be
identified by business application design and need to be done repeatedly,
then additional servers can be added as needed.  They should probably be
added about with the frequency that you would add a logical file to a
physical.

Of course, in some shops, I've seen physicals with over 100 logicals.
Whoever was in charge of that database design is NOT the person I would have
in charge of my server definitions.

And then finally you have your ad hoc purposes, such as "price < 100".  Well
of course these are best served via SQL.  This is what SQL was designed for!
Have at it.  I have no problem with SQL where it's the RIGHT answer: ad hoc
queries and set-based processing.  But even in this case, you don't have to
use column names on the client - you can use attribute names instead and map
those attributes into column names on the host.  If not, database changes
will force you to change your client code.

Does this make sense?