RE: SQL Syntax -- MIDRANGE-L

This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
Joe,

How would the average user call a stored procedure without additional
programming?  What tool calls stored procedures:  Query/400, Crystal
Reports, Excel?

Yes, the Queries break when you change the data.  We are going from BPCS
405CD to 8.1.  We have to pull up and change every query.  However, the
time to do this is probably less than the time it takes the average
programmer to generate two programs.

Good example on the 4 quantity fields in the item master.  I still feel
that most of this stuff that may be confusing to the user, (and which many
people use as a reason to restrict Query tools from the users), could be
alleviated by better database design.  Namely, more SQL views, (logical
files).  Awhile back I gave a simple example of a UDF that combines these
4 fields correctly.  Then I embedded the UDF into a view.  I've also
designed join logicals which have joined several files together, often
many back to themselves.  Doing this, (like a data warehouse), sure makes
the Queries easy to understand.

It appalls me the number of developers that are loathe to create
simplification views.  I suppose it is because it allows them to wear
their wizard cap (think of the cartoon "Shoe") and feel good because then
the users can see the difficulty in tying the data together.  That comment
was probably too negative.  I am sure most of them are more concerned
about the extra work involved, for them, when a new release comes out to
recreate these views, than the amount of work necessary, for the users, to
create meaningful reports.  Still negative - but more accurate.

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin




"Joe Pluta" <joepluta@PlutaBrothers.com>
Sent by: midrange-l-admin@midrange.com
10/29/2002 11:42 PM
Please respond to midrange-l

        To:     <midrange-l@midrange.com>
        cc:
        Fax to:
        Subject:        RE: SQL Syntax


> From: jpcarr@tredegar.com
>
> SO,   Respectfully, - watching a user do his own query over DB2/400
> straight into
> an Excel spreadsheet and then do a pivot table and produce a graph just
> before
> a management meeting - I would have to disagree and stand by my original
> position that the data belongs to the USER.

And using an interface layer (such as a stored procedure), they could do
this just fine and never even know where the data was stored - on the
network, much less the disk.  USERS DON'T NEED ACCESS TO THE DATABASE.

I guess I'm just incapable of explaining myself.  The user certainly owns
the data.  But they don't own the database.  It's my responsibility to
make
sure they can get at the data, but they shouldn't have to care how it's
stored physically.

Let's take an example.  In BPCS, the item master stores available
inventory
using four fields: opening balance, receipts, issues and adjustments.  In
most cases, users don't care about the individual fields, they just care
about the current available inventory.  They shouldn't have to know how
the
data is stored on the disk to do that, but in your world they do.

And what if I decide I need to change how the data is stored on disk?  All
of a sudden the user has to change EVERY QUERY THEY WROTE.  This is a
maintenance nightmare waiting to happen, and a very serious breach of
responsibility on the part of the programmer.  Those wonderful happy users
you talk about would have your head if you changed how the data was stored
on your machine.

Or say you need to merge with another corporation that uses a different
data
format.  Now the user has to modify their query to understand both the old
format and the new format, and once again update EVERY QUERY.

You think this is a good thing?  If so, you and I view the job of IT very
differently.  I think you're just not willing to do your job.

Because there's no reason you can't provide a flexible information layer
that would allow your users to access the data, yet still hide the
implementation from them.  This concept - hiding the implementation - is
so
universally accepted today that this insistence on direct SQL access to
raw
data boggles my mind.  It's completely opposed to every good programming
practice we've been fighting to put in place over the years.

What would it take to implement this tier?  Simple.  All access to the
database is through stored procedures, and the user only has access to the
elements that are available from those stored procedures.

Where do these elements come from?  Create a catalog of all relevant
business data.  And I'm going to go on a limb here and use the actual
singular form, datum.  Each datum would represent a business entity, such
as
a customer number or an available quantity.  These definitions for each
datum would be stored one level higher than the actual database.  You
would
assign the security and access rules at this level.

Below that would be MY level, the level of the programmer.  I would
determine how each datum related to physical fields on the disk.  A single
business data could conceivably cross rows, tables, and machines, but
their
storage would be completely transparent to the user.  As long as I
guaranteed the access to those business data, the users would be perfectly
happy.  The only time users need access to raw data is if I haven't been
responsive enough to their needs.

And if, for some reason, the users needed access to some particular piece
of
information that wasn't cataloged, we'd simply add a new catalog entry and
update the affected stored procedure correspondingly.

Is this feasible today?  In the world of stored procedures, it is most
assuredly possible.  However, it's work.  It's easier and faster to simply
give access to the user, and future changes be damned.

Short term, giving access to the database may solve some of YOUR problems
as
the programmer, but long term, you're creating an environment that has
potentially disastrous side effects.

It's a business decision, I suppose, but given the potential damage, it's
unlikely you can make a real business case for direct user access, even
read
only.  But you may disagree.  If so, more power to you.  I just pity you
when you have to change your database.  Because your users aren't going to
be so happy then.

Joe

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.