Re: COBOL/DB2 Date edit question

On 8/12/2007 at 6:17 PM, in message
<5i9m8uF3lvl7qU1@xxxxxxxxxxxxxxxxxx>,
Pete Dashwood<dashwood@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

"Graham Hobbs" <ghobbs@xxxxxxxxxxx> wrote in message
news:jgvub35e1pahgvi467v85db20119s42cub@xxxxxxxxxx
I hear what you say Michael. Might I scenario something (and if you
get bored with the subject I will understand) ..

My 'almost all common data types DB2 table' has 14 columns, one is a
key and in particular, three of the others are DB2 datatypes DATE,
TIME, TIMESTAMP. I populate the CICS screen from this table, any field
may be user changed, I need to edit all changes before they go back
into the table - typical CICS scenario.

My problem is to find the easiest/clearest way to edit these three
data types before update occurs. I suspect three ways are available:

1) my 'neat?' EXEC SQL.'s.
2) inline code like 'if month < 00 or >12 then error, etc'.
3) exec cics link 'editdate' with suitable commarea (and othe pgms for
TIME and TIMESTAMP) and test a return code. (your library routine)
4) some other way??
5) there is no especially favoured method.


You are suffering from the same problem many have encountered when
moving to
RDB; incompatibilities between language data types and RDB data types.
For
the most part, embedded SQL in COBOL does some very good data
conversions
uder the covers and ensures that the data presented from the DB to the
program is in a format that the program can use, and vice versa.

Looks like I started my previous thread too soon! :-)

However, more and more installations are moving away from embedded SQL
for a
number of reasons:

1. It is still tied back into procedural programming.
2. It is not as powerful as some of the emerging methods for data
manipulation which can utilise multiprocessors and deferred execution.
(Query Expressions, Lambda functions, etc.)

Obviously, most places are not going to move overnight into new database

technology. (And it requires using a language that supports the new
functions; COBOL doesn't.)

The modern trend is to place the editing and validation into stored
procedures that are triggered automatically when the data is updated.

This makes sense insofar as the validations are stored with the data,
and
are independent of the language being used. It doesn't matter if you
move to
Basic, or C++ or C# or whatever, your data still gets validated by the
RDBMS
back end. (It is also easier to progress from this into the new
technology,
should you decide to, now or later.)

You were close to this solution when you decided to validate the date
using
an RDB DATE function. All of the solutions you have outlined are
workable,
it is just a question of what you prefer. Michael thinks it is "obtuse";
I
disagree. I think it is perfectly sensible. (However, I understand
Michael's
objection. To a programmer, using program code to do things is the
solution.
Michael is a very experienced and excellent programmer, so to him this
looks
like a "sledgehammer to crack a nut'')

In fact, there is nothing wrong with using RDB functions to validate
data
that is being stored on a RDB, the only difference is WHERE you use the
function. If you use it from COBOL with embedded SQL, then there is a
good
argument to be made for simply using program code instead. If, on the
other
hand, you stored a procedure to do it, and triggered that procedure
whenever
the date was updated, you have removed the whole business from your
program.
You get a return saying it worked or it didn't and you can do whatever
you
want from there. You can also retrieve it, secure in the knowledge that
it
is "valid" in the wider sense of not just format validation..

An important point to note here is that this "back end" validation can
go
way beyond what you could easily do in program code. You can check the
new
date against other, previously validated dates now stored on the
database,
without having to retrieve them into a program. You have all of the
extensive SQL DATE manipulation functions immediately available, with no

program call overheads. For example, you can check whether the new date,

plus a number of days is greater, less, or equal to an existing date,
which
is, in turn, so many days away from another date which it must exceed,
and
so on. And that is all before you even start to include data which is
NOT a
date in the validation.

Stored procedures are becoming a way of life on many sites, and embedded
SQL
is showing a consequent decline.

I still have the issue I mentioned in my other message. Personally, I am
not so much concerned as to *where* the business logic / validations exist.
If it's in a stored procedure, that's fine (as long as I have access to look
at it!). My concern is that this appears (from my, granted, limited point
of view) to require a lot of 'back and forth' between the user, the
application, and the database. Is this not the case?

One piece of logic that we have in many of our user update type programs is
the "Do you really mean this" type screen. Here's how it works...

The user enters various fields on various screens. Secondary screens may or
may not be present depending on data entered on previous screens. At the
end of all of the input the user is presented with a screen detailing all of
the input he has entered, with a button to "Submit" or "Cancel". When he
submits then the data is actually sent to the database (or to the
mainframe!).

Within all of this a lot of validation has been going on. Dates have been
checked, fields have been validated to make sure they are allowed in
combination with other fields, etc. The final "Submit/Cancel" page will not
even be presented to the user until such time as all of the input has been
validated as being allowed.

Can this still be done with stored procedures and what have you? I'm not
trying to be argumentative. I simply see problems and I don't know the
solutions.

Thanks!

Frank

.