"David Cressey" <cressey73@[EMAIL PROTECTED]
> wrote in message
news:Y358k.2$0f.1@[EMAIL PROTECTED]
>
> This is extremely interesting to me. I want to question what you are
> saying
> and maybe even differ with you, but I don't want to start another one
of
> those sterile usenet debates. I've read enough of what you write to
have
> respect for your opinions, and I hope that's mutual.
Indeed. But I hear the words of Harvey "The Wolf" Keitel carried faintly
on
the breeze... :-)
> So I hope we can
> explore this subject in some detail, and generate more light than heat.
Let's have go.
> I'm more familiar with embedded SQL as an interface to application code
> than
> I am with ODBC or JDBC interfaces. (I never programmed in COBOL but I
> taught database programming to COBOL programmers). I suspect that low
> level
> programming carries a cost with it, and that part of that cost is that
> programmers continue to "think like programmers", instead of learning
to
> think in SQL. That could be part of the attraction of OTLT and EAV to
> them.
>
> But the embedded SQL that I used was not "dynamic", as I understand the
> term. The precompiler processed the embedded SQL, opened a database
> whose
> metadata was supposed to be the same as the target database, rewrote
the
> SQL in some lower level language that I didn't need to know, and
replaced
> the emebedded SQL with calls to these generated lower level routines
> before
> passing the result to the compiler.
Yep, that's plain old embedded SQL.
> We had something we called "dynamic"
> SQL as well. But that involved generating SQL at run time, and letting
> an
> SQL interpreter parse it. I suspect you are using "dynamic" in a
> different
> sense than I am, but I'm not sure.
I mean something different (I think). Dynamic SQL is somewhere
intermediate
between embedded SQL and coding to an API directly. With embedded SQL
you
somehow construct your SQL statement on the fly by concatenating strings
or
whetever. (It doesn't matter where it comes from; you could even get the
user to key it by hand at run time.) You then send the SQL statement off
to
the server and use the DESCRIBE statement to get a descriptor back that
tells you what the output (if any) of the query is going to look like.
You
then set up a buffer with a sufficient amount of storage and a description
of where each column value will go in the buffer (the details are host
language-specific). You then tell the server to execute the statement,
and
then fetch successive rows into the buffer and iterate over the returned
columns. It sounds complicated, but having done it once you find (1)
it's
not that hard after all, and (2) the same bit of code is reusable
virtually
everywhere you need to do such a thing.
> In any event, the interesting thing is, IMO, not so much at what point
> the
> SQL is parsed and bound, but how automatic the process is of generating
> the
> SQL needed to reference and maintain a new code table.
That's a "how long is a bit of string" question. It could be easy or you
could want all kinds of bells and whistles.
> Back in the day,
> new code tables didn't occur often enough to do some heavy automation of
> the
> process, but the necessary coding was so well understood that the
manual
> effort was relatively trivial.
Agreed, although I suspect the turnaround would still be measured in days
at
least, whereas adding a row to a lookup table takes seconds. The case we
have to answer is why we think those days are worth it. My
argument--which
I won't spell out here--is that someone still spends days dealing with the
new code, it's just someone else and they do it later, over and over
again.
The net benefit to the business of being able to improvise new codes
willy-nilly will usually be near zero or worse in the long term. I
currenty
deal with two systems that have development teams that are maybe 25-50%
bigger than they need to be to because productivity is so low owing to
mysterious bits of the business model that are encoded in EAV and OTLT
tables instead of being made manifest in the database design. The
knowledge
of these things is transmitted orally, like the folklore it is, and then
code written to manifest the real business model at run time.
> So the idea of storing all in one table "to
> save programming effort" seemed like a bad joke to me. And the "delay
> due
> to the cranky DBA" was not the kind of issue in my context that it is
in
> some of today's programmers.
>
> In all my years of visiting newgroups and other forums, I've never
quite
> learned why programmers think that database programming is so difficult.
It is, doing it the way some of them do it. I just finished a re-write of
a
batch job that consisted of over 14,000 lines of code and sent 2.5 million
queries to the server, and ran for 4.5 hours. When I'd finished with it,
it
was 600 lines of code and it sent 6 queries to the server and ran in 12
seconds. And I am pretty sure one of the six queries isn't needed.
> I
> think this discussion might help me understand that. Thanks for a
> considered reply.
Roy
|
