Re: Database access as service pgm or bind by copy?

Hi James -

 >Most of the same people involved in that discussion also
 >subscribe to this list and I would really like to hear
 >if they have new insight upon the past 9 months of actually
 >doing it.  Especially if they will share with us -how- they
 >solved certain issues.

I'm one of the people involved in that discussion, and it just so happens
that over the last few weeks I've put together a service program that
provides some database functions.  In my case it was easier than a full
database server program because it does not provide any capability to update
the file at all, just to read it.

Since the file does not have a lot of fields that need to be accessed by
programs that don't need to update the file, I return the data as discrete
fields in the procedure parameter list.

The prototype for the main read procedure looks like this (I have removed
some of the white space to prevent wrapping):

D @_PD_read       pr              n
D* Read the first, next, last, previous,
D* or current record of a record set
D   @_b_hndl          32
D   @_b_oper           1
D   @_o_brkv           7p 2 options(*nopass:*omit)
D   @_o_valu           7p 2 options(*nopass:*omit)
D   @_o_mstr           9p 0 options(*nopass:*omit)
D   @_o_vtyp           1    options(*nopass:*omit)
D   @_o_fprd           9p 0 options(*nopass:*omit)
D   @_o_fqty           5p 0 options(*nopass:*omit)
D   @_o_fqtp           1    options(*nopass:*omit)
D   @_o_fmul           1    options(*nopass:*omit)
D   @_o_levl           2p 0 options(*nopass:*omit)

In this file, records are grouped into sets that have the same key fields
except the last one, which is a sequence number field.  @_b_hndl is a record
set handle that contains the key information in a format known only to the
service program.  If a program needs the key field information, there is a
procedure to retrieve the handle contents.  Likewise there is a procedure to
build a handle, though so far that is used only by other procedures in the
service program.

@_b_oper is an operator: F = get the first record in the set, N = get the
next record in the set, L = get the last record in the set, P = get the
previous record in the set, C = get the current record in the set.  When it
is F, it gets changed to N automatically; when it is L, it gets changed to P
automatically.  The return code is set to true if a record is found, set to
false otherwise.  This makes it very easy to build a DOW loop with the read
right in the loop itself, eliminating the double read that the DOU
proponents hate so much! <G,D,R>

c       eval      @handle =
c                 @_PD_get_other(@_i_prd#:@_i_cmp#:@date:'2':
c                 @schedulex:*zero:*zero)
c*
c       if        @handle <> *blanks
c*
c       eval      @operator = 'F'
c       dow       (@_PD_read(@handle:@operator:@pdbrkv:
c                    @pdvalu:*omit:@pdvtyp:@pdfprd:@pdfqty:
c                    @pdfqtp:@pdfmul:@pdlevl))
c do something
c       enddo
c*
c       endif

The first eval gets a handle.  The fields passed to @_PD_get_other are not
exactly key fields.  This file has record sets in a hierarchy.  That's why I
made a service program, so that the hierarchy processing for finding the
right record set would not need to be in every program.

If the handle is not blank, a record set was found.  The next eval sets the
operator to get the first record in the set.  The DOW loop reads through all
of the records in the set, returning the data in the fields that start with
@pd.

The *omit on the second line of the DOW is in place of a field name for a
field that I don't care about.  The *omit part of the prototype allows this.
The *nopass part of the prototype allows fields at the end of the list to
not even be in the list at all.

With this type of parameter passing, I can add fields anyplace in the actual
database record, but I have to add them at the end of the prototype.
Programs that don't need the new fields don't have to even be recompiled.
If I change the attributes of a field, then all of the programs do have to
at least be recompiled.  If not, decimal data errors or other unpredictable
behaviour can occur.  There is NOT a signature change from changing the
parameters of a procedure.

If the record had a lot of fields, this method would quickly become quite
cumbersome.

Ken
Southern Wine and Spirits of Nevada, Inc.
Opinions expressed are my own and do not necessarily represent the views of
my employer or anyone in their right mind. 

+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---