Re: Opinions on approach, please...

On Tue, 27 May 2008 06:04:08 -0700 (PDT), razor <iruddock@xxxxxxxxxxxxxxxx> wrote:

Hi Pete

Really good discussion this, and most useful to me. I've tried a bit
of this in the past but gave up when I got busy with 'real work' for
which I was to get paid for. I had a couple of problems that stopped
me in my tracks and I could not get answers to.

1. When we typically read a Customer Record in COBOL, we read with
lock and present the details to the user, allow them to amend, rewrite
and then unlock. How can we do this as an equivalent within a COBOL
program. Your routines would convert a very simple READ to a very
simple SQL statement. The user may take 10 mins to change the details
and the thing I don't understand is how we can ensure the record (row)
is locked and another user cannot change the data. I do admit that my
SQL knowledge is limited and I may be missing something here.

You never want to lock users out. Having a customer row shouldn't stop others from
browsing the customer table. Let them have a dirty read.

If you don't want to be bothered understanding database locks, here's a simple way to
detect collisions that will work on any database.

select row_version, name, address ... from customers ...

-- long delay for update and lunch --

update customers
set row_version = row_version + 1,
name = :hv-name, ....
where row_version = :hv-row-varsion

In the unlikely event you get an error, someone else updated the row while your user was
out to lunch.

2. My other problem was where I was trying to replicate the
'scrolling' of my screens. All of my screens have nine rows that allow
users to page up and down through a file (or between certain
parameters if dealing with say, an order lines file for order number
nnn). I have GUI and non-GUI versions of this and I don't bind these
objects to a data source as I want to retain the control within the
COBOL program. I use the cursors as you very first suggested with a
descending option when I need it and i also used a 'LIMIT 10' on each
one to only get 9 records. Anyway, when I came to try and replicate
the following code, I got problems:

FD ORDER-LINES.
01 ORDERL-REC.
03 ORDERL-KEY.
05 ORDERL-NO PIC 9(7).
05 ORDERL-PRODUCT PIC 9(6).
03 ORDERL-QTY PIC 9(7).

..
..
..
MOVE ORDERH-NO TO ORDERL-NO.
MOVE WS-LAST-PRODUCT TO ORDERL-PRODUCT
START ORDER-LINES KEY IS NOT LESS THAN ORDERL-KEY

PERFORM 9 TIMES
READ
BLAH, BLAH
END-PERFORM


Its a couple of years ago now but as I recall I could only pass one
parameter from the COBOL program to the dynamic part of the SQL code.
The way I tried to do it was by having an additional 13 character text
field as the key in the DB (order no and product code) and
manipulating this as a text string. This was obviously very wrong and
very clunky. How should I have done this Pete?

You can pass two parameters. To get the second page, say LIMIT 9 SKIP 9.
Make sure to use an ORDER BY that has no duplicates.

.