Category : Dbase (Clipper, FoxBase, etc) Languages Source Code
Archive   : FDIDOCS.ZIP
Filename : TUTOR.TXT

A Financial Dynamics Training Course

Version 3.0

May, 1989























Copyright May 1989 by Financial Dynamics, Inc.
All Rights Reserved. No part of this manual may be
copied without express consent of Financial Dynamics.


Table of Contents



Section 1: Introduction
Class Design . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Class Software . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Trademarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Acknowledgements . . . . . . . . . . . . . . . . . . . . . . . . . 8
Quick Reference Guide to Sidekick Editing Commands . . . . . . . . 9

Section 2: Compiling and Linking
File Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
CLP And BAT Files For Compiling. . . . . . . . . . . . . . . . . . 11
Linkers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Overlays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

Section 3: Overview of Structured Programming
Definition . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Clipper Procedures and Functions . . . . . . . . . . . . . . . . . 15
Parameter Passing. . . . . . . . . . . . . . . . . . . . . . . . . 17
Memory Variable Scope. . . . . . . . . . . . . . . . . . . . . . . 19
Procedure Syntax . . . . . . . . . . . . . . . . . . . . . . . . . 20
Function Syntax. . . . . . . . . . . . . . . . . . . . . . . . . . 21
Style. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

Section 4: Overview of the FDI Methodology
Definition . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Flow Diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Notes on Libraries . . . . . . . . . . . . . . . . . . . . . . . . 26

Section 5: Case Study
Machine Shop Product Cost Estimation System. . . . . . . . . . . . 27

Section 6: Assignment 1 - Starting A New Application
Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Updating the Menu Templates. . . . . . . . . . . . . . . . . . . . 31

Section 7: Assignment 2 - Write a Lookup Table BROWSE
Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Implementation . . . . . . . . . . . . . . . . . . . . . . . . . . 32

Section 8: Program Flow Of Data Maintenance
Hierarchy Diagram. . . . . . . . . . . . . . . . . . . . . . . . . 34

Section 9: Assignment 3 - Full Screen Data Editing
Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Implementation . . . . . . . . . . . . . . . . . . . . . . . . . . 35


Section 10: Assignment 4 - Data Validation
Using the Clipper VALID Command. . . . . . . . . . . . . . . . . . 36
Making Sure Data Is Entered. . . . . . . . . . . . . . . . . . . . 36
Use the FDI LOOKUP Function. . . . . . . . . . . . . . . . . . . . 37

Section 11: Assignment - Give The User Help
Overview of the Help Routines. . . . . . . . . . . . . . . . . . . 39
Hard-Coded Help. . . . . . . . . . . . . . . . . . . . . . . . . . 39
Memofield Help . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Lookup Table Help. . . . . . . . . . . . . . . . . . . . . . . . . 43
Implementation . . . . . . . . . . . . . . . . . . . . . . . . . . 44

Section 12: Assignment 6 - Layered Browse Screens
Overview of Group and Group_key. . . . . . . . . . . . . . . . . . 45
Implementation of Group and Group_key. . . . . . . . . . . . . . . 48

Section 13: Reporting Tricks
Make a Temporary Database Structure. . . . . . . . . . . . . . . . 49
Reporting From Arrays. . . . . . . . . . . . . . . . . . . . . . . 50
Use of printit, openit and closeit . . . . . . . . . . . . . . . . 52
Escaping From Reports. . . . . . . . . . . . . . . . . . . . . . . 53

Section 14: Multi-user Programming
Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Locking Rules. . . . . . . . . . . . . . . . . . . . . . . . . . . 56
Changes in Clipper's Behavior. . . . . . . . . . . . . . . . . . . 58
Locking A Series Of Databases. . . . . . . . . . . . . . . . . . . 59
Printers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
Making Sure Temporary Files Are Unique On A Network. . . . . . . . 62

Section 15: Tangents, Tricks and Techniques
Errorsys Program . . . . . . . . . . . . . . . . . . . . . . . . . 63
Displaying The Values Of Arrays While In The Debugger. . . . . . . 68
A Simple Data Encryption Technique . . . . . . . . . . . . . . . . 69
Maintaining Data Integrity Of Related Databases During Edits . . . 70
Building A Data Audit Trail. . . . . . . . . . . . . . . . . . . . 72
Saving The Database Status For An Interim Routine. . . . . . . . . 74
Speed - What Makes Systems Go Fast Or Slow . . . . . . . . . . . . 76





Section 1: Introduction


1.1 Class Design


This is an advanced Clipper programming course, designed to
teach a set of structured programming standards which we call
the FDI environment. Many definitions of 'structured
programming' abound. For the purposes of this course,
however, we will define it as 'modular, task-oriented
programming'.


Numerous benefits follow from using the programming
environment and methodology outlined in this course. For
example, we have found:


1. Greater ease of maintenance of existing code.
2. Measureably faster development time.
3. Faster debugging.
4. Maintenance is made easier when the original
author is not the one supporting the code.
5. Due to a proven methodology, we are able to spend
more time on what a client's paying for.
6. We are able to bid more jobs at a reasonable fixed
price, because our clients can see what the system
will look like during the initial development
meeting.


There are three distinct benefits to be derived from this
course. The first is the overview of the FDI environment that
will provide a structured approach to database application
development. The second benefit is much more detailed. Due to
our lengthy association with Clipper programming we are
usually able to teach a number of specific programming tricks
that are not being used by the class. The third benefit, the
interchange of ideas, is specific to each class, and is
dependent upon the students as well as the instructor. As
with any teaching/learning situation, a good deal of the
knowledge comes from the students. FDI welcomes this input
from class participants.


This course assumes that the students will use the Nantucket
Clipper Compiler to compile from a pseudo-dBASE III
environment. FDI feels that, at least at the time this course
is being written in spring 1989, there is no serious




competition to Clipper as the DBMS environment for our
applications.


It would be wonderful if we were able to say that, given this
course, you will be able to develop major systems overnight.
Not true. However, you will have a frame of reference for
developing large systems that work. We know the environment
works because we have been using it for the last four years.
FDI's standard policy is to guarantee our work. We do not send
invoices until the client has the system we have written, and
advises us to send a bill. We could not be so confident
without a proven system to back us up.


1.2 Class Software


Each computer used for the class will have the following
directories:


\class -> example system
\class\work -> student work directory
\std -> standards
\templates -> FDI .prg templates
\clipper -> clipper and libraries
\progs -> third party utilities




1.3 Trademarks


References are made to the following trademarks:

Clipper, from Nantucket
dBASE III PLUS, from Ashton-Tate
SideKick from Borland International
UI, from Wallsoft
Getit, from Communications Horizons
Netlib, from Communications Horizons
Reference(Clipper), from Pinnacle Publishing




1.4 Acknowledgements

The following people have directly contributed to the
development of this training manual, and the Financial
Dynamics Toolbox Library:

Steve DelBianco
Michael Horwith
Paul Fisher
Chris Jones
Tim Grant
Ed Bell


The following people, through direct contact or thorugh their
products and writing, have had an impact on our coding style
and substance.

Mike Schinkel, Dietzler, Schinkel & Weber, Atlanta, GA.

Art Still and Scott Hurlbert from Bakersfield, CA.

Greg Martin, Market Services Associates, Coeur d'Alene, ID.

Neil Weicher, Communications Horizons, New York, NY.

Rick Spence, Software Design Consultants, Munich, West Germany




1.5 Quick Reference Guide to Sidekick Editing Commands


CURSOR MOVEMENT Character left ............. l arrow
Character right ............ r arrow
Word left .................. ^l arrow
Word right ................. ^r arrow
Line up .................... u arrow
Line down .................. d arrow
Page up ....................
Page down ..................
To left on line ............
To right on line ...........
To top of page ............. ^
To bottom of page .......... ^
To top of file ............. ^
To end of file ............. ^

INS & DEL Insert mode off/on .........
Delete line ................ ^Y
Delete to end of line ...... ^QY
Delete right word .......... ^T
Delete char under cursor ...
Delete left character ......

BLOCK COMMANDS Mark block begin ............ F7
Mark block end............... F8
Hide/display block .......... ^KH
Copy block .................. F5
Move block .................. F6
Delete block ................ ^KY
Read block from disk......... ^KR
Write block to disk ......... ^KW
Sort block................... ^KS
Print block ................. ^KP
Paste block ................. ^KE


MISC EDITING COMMANDS
Get note File ................ F3
Save note file ............... F2
Find ......................... ^QF
Repeat last find ............. ^L
Find & replace ............... ^QA
Restore line ................ ^QL
Import from Screen ........... F4




Section 2: Compiling and Linking


2.1 File Types

The following file types are relevant to our discussion of
Clipper:

PRG Program files

ASCII files containing source code.

OBJ Object files:

Object files are created by compiling
source code. They are an interim step
between source and executable files.

EXE Executable files:

The result of a compiled system. In our
case, ROLODISK.EXE. EXE files are made by
linking OBJ files.

LIB Library Files

Library files contain compiled code that
is added to the EXE file during the
linking process.


CLP Clipper List Files

Clipper list files are ASCII files
containing lists of PRGs to be compilied
into a single OBJ file.

LNK Linker command Files

Plink86 link command files contain a list
of commands to be executed by the linker.
They are necessary if overlays are created
for a system.

OVL External overlay files

External overlay files are created by the
Plink86 linker.





2.2 CLP And BAT Files For Compiling


At the easiest level, applications may be compiled with the
command CLIPPER FILENAME. Clipper will automatically seek out
all sub-files and procedures called with the DO and SET PROC
command. This is a good method for testing, but a poor method
for system development.


Use CLP files to list the PRG's to be compiled. Financial
Dynamics has a utility called FILES.EXE that creates a list
of PRG's to be compiled.

Enter FILES *.PRG X.CLP

Remember that the first PRG called (MENU.PRG) must be at the
top of the CLP file.


CLP files are important because they allow faster development
and debugging. Although the full system must be linked each
time any part of the system is compiled, CLP files allow the
developer to only compile a small section of the system at a
time. The listing below shows the ROLODISK CLP files.

MENUSEC.CLP CHORESEC.CLP MAINTSEC.CLP REPSEC.CLP

MENU.PRG BCKUP.PRG CO_MAINT.PRG BIRTHDAY.PRG
LOGO.PRG REINDEX.PRG RO_MAINT.PRG BYCODES.PRG
CHO_MENU.PRG SETUP.PRG RO_PRINT.PRG CARD_PR.PRG
CO_SELEC.PRG MERGE1.PRG MERGE.PRG
HELP.PRG WRITELET.PRG PHONE.PRG
INITIAL.PRG ROLOLIST.PRG
INTRO.PRG LABELS.PRG
LETTER.PRG CODELIST.PRG
MERGHELP.PRG
NOTE1.PRG
NOTE2.PRG
PRC.PRG
REP_MENU.PRG
RO_SELEC.PRG



2.2 CLP And BAT Files For Compiling (continued)


The MENUSEC section will be the first section to be linked.
It is a good practice to put all the programs that may be
called from anywhere in the system, such as the procedure and
the help files, in MENUSEC. Furthermore, each section should
be logically constructed and named, with no section calling
a file from any other section except MENUSEC. This way if the
system ever requires overlays, the sectioning of source code
is already done.


To make life easier, use a BAT file to compile. The first four
lines of COMP.BAT file read :



CLIPPER @MENUSEC > MENUSEC.LOG
CLIPPER @CHORESEC > CHORESEC.LOG
CLIPPER @MAINTSEC > MAINTSEC.LOG
CLIPPER @REPSEC > REPSEC.LOG


The DOS redirect ( > ) is used to create a log file contain-
ing information about the compile, including any errors.



2.3 Linkers

At the time of this writing, the following are the most common
linkers being used:

PLINK86 - comes with Clipper. Very slow. Creates overlays.

LINK - from Microsoft. Faster than PLINK86.

TLINK - Turbo C's linker from Borland. Very fast.

New linkers have been reaching the market place, specifically
ALINK and RTLINK. These contain dynamic linking capabilities.


It is a good idea to test for errors in the compiles before
calling the linker in the BAT file. Now COMP.BAT reads :



CLIPPER @MENUSEC > MENUSEC.LOG
CLIPPER @CHORESEC > CHORESEC.LOG
CLIPPER @MAINTSEC > MAINTSEC.LOG
CLIPPER @REPSEC > REPSEC.LOG
IF NOT ERRORLEVEL 1 LINK MENUSEC+CHORESEC+MAINTSEC+REPSEC+EXTENDA,ROLODISK,,CLIPPER





2.4 Overlays

The PLINK86 command is easier to understand and more flexible
with the use of an LNK file.

Instead of :


PLINK86 VERBOSE FI MENUSEC,CHORESEC,MAINTSEC,REPSEC,EXTENDA LIB CLIPPER OU ROLODISK


the command may read :

PLINK86 @ROLODISK


@ROLODISK refers to a LNK file called ROLODISK.LNK with the
following lines in it. LNK files only work with PLINK86.

VERBOSE
FI MENUSEC,CHORESEC,MAINTSEC,REPSEC,EXTENDA
LIB CLIPPER
OU ROLODISK

If overlays are required, a LNK file must be used. The
overlays may be internal or external (there is no memory
difference) and are created by commands in the LNK file. The
following LNK file produces three internal overlays in one
overlay area for the ROLODISK system.

VERBOSE
FI MENUSEC,EXTENDA
OVERLAY NIL,$CONSTANTS
BEGINAREA
SECTION INTO ROLODISK CHORESEC
SECTION INTO ROLODISK MAINTSEC
SECTION INTO ROLODISK REPSEC
ENDAREA
LIB CLIPPER
OU ROLODISK





Section 3: Overview of Structured Programming


3.1 Definition


Definitions of what exactly constitutes structured programming
abound. For the purposes of this course, we will use FDI's
definition which says that structured programming is modular,
task-oriented programming where each module performs a single
task.


3.2 Clipper Procedures and Functions


Before we proceed with a discussion of Clipper procedures and
functions, it is useful to make a distinction between the two.
A function returns a value, and can itself be used as an
element in an expression or an argument for another function.
Procedures cannot be used in either of these situations. They
are simply blocks of code which constitute a task within a
program.


As with any programming language, Clipper has its own way of
handling subroutines (procedures and functions). Although
most students taking this course are familiar with the general
concepts of procedures and functions, several aspects of the
way Clipper works are worth noting here:


1. Clipper allows the same logical name to be used for
variables and subroutines. The same name cannot, however,
be used for a procedure and a function.


2. Functions can only return single values. In order to
return multiple values, the desired values must be
packed into a single variable. This variable is then
parsed to extract the separate values desired.


In addition to the above items, a thorough understanding of
the way Clipper handles the familiar tasks of call-by-value
and call-by-reference, issues of memory variable scope using
PUBLIC and PRIVATE, as well as the syntax of both procedures
and functions is helpful.




3.3 Parameter Passing


The following table shows the syntax of parameters passed to
procedures and functions using either call-by-reference or
call-by-value.

reference value


procedures x,y (x),(y)

functions @x,@y x,y

arrays always never



Sample code is provided below which illustrates the above
parameter passing possibilities.

Result
x = 1
DO examproc WITH x
? x --------------------> 2


x = 1
DO examproc WITH (x)
? x --------------------> 1
x = 1


EXAMFUNC(x)
? x --------------------> 1


EXAMFUNC(@x)
? x --------------------> 2



PROC examproc FUNC examfunc
PARA a,b PARA a
a = a + 1 a = a + 1
RETU RETU (a)




Parameters may optionally be passed from right to left, but
not from left to right. For example, it's ok to do the
following:

DO examproc WITH a,b,c,d or
DO examproc WITH a,b or
DO examproc

But, it's not ok to call a procedure like:

DO examproc with a,b,,d

In the called routine, there are several ways to handle the
fact that Clipper allows fewer than the complete parameter
list to be used. One way would be a CASE statement using the
built-in function PCOUNT. For example:

PROC examproc
PARA p1,p2,p3,p4
DO CASE
CASE PCOUNT() = 1
p2 = x
p3 = y
p4 = z
CASE PCOUNT() = 2
p3 = y
p4 = z
.
.
.
ENDCASE

A more efficient method, however, would be:

p1 = IF(TYPE[p1]=[U]),w,p1)
p2 = IF(TYPE[p2]=[U]),x,p2)


3.4 Memory Variable Scope


To illustrate how Clipper's PRIVATE and PUBLIC commands affect
the scope of variables, the following table has been included.
This table depicts how a variable, x, is affected as procedure
A calls B, which calls C, which calls D. Three cases are
investigated. In each of these, x is declared PUBLIC, is not
specified, and is declared PRIVATE, respectively.


ÚÄÄÄÄÄÄÄÄÄÄÄÄÂÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÂÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÂÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ¿
³ ³ ³ Not ³ ³
³ ³ PUBLIC ³ Specified ³ PRIVATE ³
ÃÄÄÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÄÂÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÄÂÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÄÂÄÄÄÄÄÄÄÄÄÄ´
³ PROC a ³ ³ 3 ³ ³ U ³ ³ U ³
ÃÄÄÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄ´
³ b ³ PUBLIC x ³ 3 ³ x = 5 ³ 3 ³ x = 5 ³ 5 ³
³ ³ x = 5 ³ ³ ³ ³ ³ ³
ÃÄÄÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄ´
³ c ³ x = 3 ³ 3 ³ x = 3 ³ 3 ³ PRIV x ³ 3 ³
³ ³ ³ ³ ³ ³ x = 3 ³ ³
ÃÄÄÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÄÁÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÄÁÄÄÄÄÄÄÄÄÄÄÅÄÄÄÄÄÄÄÄÄÄÄÁÄÄÄÄÄÄÄÄÄÄ´
³ d ³ 3 ³ 3 ³ 3 ³
³ ³ ³ ³ ³
ÀÄÄÄÄÄÄÄÄÄÄÄÄÁÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÁÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÁÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÙ





3.5 Procedure Syntax


A representative format for Clipper procedures is:

PROCEDURE
[PARA ]

[RETURN]



NOTES:
1. The RETURN is optional, but good programming technique
suggests that it should always be included. The end of the
source file is interpreted the same way.
2. The PROCEDURE is not needed if the source code
for this procedure is the first one in a source file. In
this case the procedure name will be the same as the name
of the source file.
3. Procedure definitions cannot be within another procedure
or function definition.
4. All procedures and functions are global in Clipper, and may
be called from any module, as long as the procedure is in
memory.



For procedures, the calling sequence is:

DO [WITH ]

where is the name of the procedure to execute.





3.6 Function Syntax


A representative format for Clipper functions is:

FUNCTION
[PARAMETERS ]

RETURN



NOTE: The returned above may itself be a function, and
may be a recursive call.


In Clipper, functions are not required to be assigned to a
variable, so the syntax:

RLOCK()

may begin a program line.



3.7 Style


By convention, all user-defined functions as well as all
reserved words will be capitalized. Everything else will
appear in lower case.

All data maintenance routines will be named ??_maint.prg.

All menus will be named ???_menu.prg.

All systems will use menu.prg as the system entry point.

The application procedure file is called prc.prg.



Section 4: Overview of the FDI Methodology


4.1 Definition


The FDI maintenance routine is the single most important part
of our development environment. Since the user's interaction
to the data is usually a large part of every database
application, as well as the first thing the user is involved
in, the maintenance routine must be simple to use, easy to
develop, and extremely flexible.

Our approach is to display the data in a custom screen, and
allow the user movement through the data and access to the
data by a scrolling window displaying the data. At any time,
a bottom linme light bar menu is available to the user by
pressing the slash [/] key.

Most of the code necessary to maintain a database is
standardized so that the controlling file may do little more
than call standard routines. This approach is called a
database engine. Basically, the FDI Toolbox is a set of
database engines that handle all of the routine chores of user
interfaces. In addition, the Toolbox is designed to be
extremely flexible so that it can hdandle non-standard, or
application specific problems as they arise.



4.2 Flow Diagram


The following diagram illustrates two concepts. The first is
the typical flow of all FDI systems. The second is the
division of source modules between application specific .PRG
files and those commonly used routines which have been placed
into libraries.


LIBRARY APPLICATION
³
³ menu
prologue <ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ>
³
std_set <ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ>
³
³ <ÄÄÄÄÄÄÄÄÄÄÄÄ> prc
³ <ÄÄÄÄÄÄÄÄÄÄÄÄ> setup
std_prc ³
³ ÚÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ¿
³ ³ MENU ³
³ ³ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄij
³ ³ ³
³ ³ ³
³ ³ ³
³ ³ ³
³ ³ ³
³ ³ ³
³ ÀÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÙ

After execution of the routines in prc.prg, we're off and
running with the topÄlevel menu currently displayed.




Summary of above 6 source code modules:


menu.prg:

This is the entry point for all our applications.


prologue:

Sets up the software environment.


std_set:

Sets up the hardware environment.


std_prc:

Contains a number of generic, commonly used procedures and
functions.

setup:

Any application specific defaults.


prc:

This source file does/contains three things:
1. Opens any needed database files.
2. Any routines generic to this application, but
are not truly generic to all applications.
3. Any pending additions to toolbox library.




4.3 Notes on Libraries


Our libraries contain a number of commonly used routines.
Traditionally, many developers have a separate .OBJ file for
each procedure. We have elected to put logical groupings of
routines into the same .OBJ file. For example, our BROWSE
routine cannot be used without our LIST_EM routine.


There are a number of third party utilities available with
which to make object libraries. The one we use is Microsoft
C's LIB.EXE program. This takes a collection of .OBJ object
modules and produces a single .LIB library from them. For
those not familiar with them, the use of libraries greatly
reduces the time needed for the linking process. Depending
on how the library is created, only procedures and/or
functions that are actually called by your system get linked
into the executable file.




Section 5: Case Study


Machine Shop Product Cost Estimation System


Greg Meyer, the sales manager for Southern Machine Works
needed a way to cost out product requests from his customers.
The system should be able to calculate the total finished cost
for any item the shop could produce. The total cost consisted
of the combined labor and material costs. Each finished good
was composed of subassemblies and parts. Subassemblies were
composed of other subassemblies and parts. Parts were the
lowest level component.


The system would have to allow entry of new parts,
subassemblies and finished goods as well as cost out existing
ones. Tom also knew that the data input would be input by
non-computer people, and so a simple user-interface was
essential.

Use this page for constructing a diagram of the necessary
database structures and data flows:


Section 6: Assignment 1 - Starting A New Application


6.1 Overview


First, copy all the template files (listed below) to your work
directory from \templates.


C.BAT
CHO_MENU.PRG
BR_MAINT.PRG
HELP.PRG
MAI_MENU.PRG
MENU.PRG
PRC.PRG
REINDEX.PRG
FS_MAINT.PRG
SETUP.DBF
STDCOLOR.DBF
TR_REP.PRG


The top level menu will be created in MENU.PRG and will have
the following items 1 through 4 in it. Also shown below are
the names of the source modules which contain the code for the
second level menus.


Menu level one Menu level 2 source file


1. Data Maintenance ----> MAI_MENU
2. Reports ----> REP_MENU
3. Chores ----> CHO_MENU
4. Quit ----> LEAVE



Several items are worth mentioning at this point.

o With full screen menus, no database files are open.
This helps to avoid data destruction in the event of
of a crash or other unforseen occurrences.

o If a database is open and nothing happens for a
given amount of time, the routine will timeout using
Neil Weicher's utilities.



6.2 Updating the Menu Templates


The following variables and statements must be changed when
updating menus:

source file

1. mhead2 menu.prg

2. PROMPT stacks and MESSAGES every XX_menu.prg

3. CASE statement every XX_menu.prg


NOTE:
mhead2 is the second line of the topscreen box that appears
above the menu. It is generally seeded with the name of the
application.


Section 7: Assignment 2 - Write a Lookup Table BROWSE


7.1 Overview

A major goal in programming is to write as little code as
possible. We will implement this browse screen in less than
12 lines of code by calling the Toolbox library.

7.2 Implementation

Edit the template co_maint.prg.

Note the naming convention used here --> xx_maint.prg is
used for all database maintenance routines.

Update the following variables:

module

A unique, two letter code for this maintenance procedure.

wtitle

The string that will appear above the fields as they list.

wfield

The string containing the bnames of the fields, including
any functions, that will be displayed using a macro.

mtitle

The title of the data maintenance routine.

Call the procedure that opens the database.

This is a call to the database opening procedure that
will be in the application procedure file prc.prg.

Update the GET stack.

All data maintenance routines need a custom GET stack. Be
sure to name it xx_gets with xx being the module name.

Edit prc.prg and update the database opening procedure.

Edit mai_menu.prg and update so we can call our lookup table.





Now you're ready to run the table to see if the browse screen
is working. All menu choices and cursor keystrokes should be
working.



Section 8: Program Flow Of Data Maintenance


Hierarchy Diagram

ÚÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ¿
³ CO_MAINT (APPL)³
³ ³
³ ÚÄÄÄÄÄÄÄÄÄÄÁÄÄÄÄÄÄÄÄÄÄÄÄÄÄ¿
³ ³ PROC Code (Prc - Appl) ³
³ ÀÄÄÄÄÄÄÄÄÄÄÂÄÄÄÄÄÄÄÄÄÄÄÄÄÄÙ
³ ÚÄÄÄÄÄÄÄÄÄÄÁÄÄÄÄÄÄÄÄÄÄÄÄÄÄ¿
³ ³ BROWSE (FDI) ³
³ ³ ÚÄÄÄÄÄÄÄÄÄÄÁÄÄÄÄÄÄÄÄÄÄÄÄÄÄ¿
³ ³ ³ LIST_EM (Prc - FDI) ³
³ ³ ÀÄÄÄÄÄÄÄÄÄÄÂÄÄÄÄÄÄÄÄÄÄÄÄÄÄÙ
³ ³ ÚÄÄÄÄÄÄÄÄÄÄÁÄÄÄÄÄÄÄÄÄÄÄÄÄÄ¿
³ ³ ³ GET_KEY (Fnc - FDI) ³
³ ³ ÀÄÄÄÄÄÄÄÄÄÄÂÄÄÄÄÄÄÄÄÄÄÄÄÄÄÙ
³ ³ ÚÄÄÄÄÄÄÄÄÄÄÁÄÄÄÄÄÄÄÄÄÄÄÄÄÄ¿
³ ³ ³ STD_KEYS (FDI) ³
³ ³ ³ ÚÄÄÄÄÄÄÄÄÄÄÁÄÄÄÄÄÄÄÄÄÄÄÄ¿
³ ³ ³ ³ STD_E (Prc - FDI) ³
³ ³ ³ ³ ÚÄÄÄÄÄÄÄÄÄÄÁÄÄÄÄÄÄÄÄÄÄÄÄ¿
³ ³ ³ ³ ³ STD_INIT (Prc - FDI) ³
³ ³ ³ ³ ÀÄÄÄÄÄÄÄÄÄÄÂÄÄÄÄÄÄÄÄÄÄÄÄÙ
³ ³ ³ ³ ÚÄÄÄÄÄÄÄÄÄÄÁÄÄÄÄÄÄÄÄÄÄÄÄ¿
³ ³ ³ ³ ³ STD_REPL (Prc - FDI) ³
³ ³ ³ ³ ÀÄÄÄÄÄÄÄÄÄÄÂÄÄÄÄÄÄÄÄÄÄÄÄÙ
³ ³ ³ ÀÄÄÄÄÄÄÄÄÄÄÂÄÄÄÄÄÄÄÄÄÄÄÄÙ
³ ³ ÀÄÄÄÄÄÄÄÄÄÄÂÄÄÄÄÄÄÄÄÄÄÄÄÄÄÙ
³ ÀÄÄÄÄÄÄÄÄÄÄÂÄÄÄÄÄÄÄÄÄÄÄÄÄÄÙ
ÀÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÙ




Section 9: Assignment 3 - Full Screen Data Editing


9.1 Overview


The full screen database editing technique is similar to the
browse technique. You do all the same things with one
additions:


Create xx_frame.


9.2 Implementation

Several utilities exist to aid in writing screens. Your
instructor will assist you with this assignment as well as
demonstrate one of these utilities to you. The one we have
used at FDI is UI by Wallsoft, Inc.




Section 10: Assignment 4 - Data Validation

10.1 Using the Clipper VALID Command

Validations are done using the Clipper VALID command. This
is a command which allows context sensitive data validation.

It does have one notable drawback. It only works if the
cursor lands on the GET you are validating.

Some examples of common uses of the VALID command are:

o Make sure a field is filled in (not empty).

o Make sure a field value is greater than another field.
(e.g. Ending dates must come after starting dates.)

o Using FDI 'Lookup' routines.

o Calculating values to appear on the screen.


10.2 Making Sure Data Is Entered

@ x,y GET M->user_id PICT [@!] VALID ! EMPTY(M->user_id)

If this is the first field in the GET stack then this is
foolproof. If not, the valid may not work. Why?


10.3 Use the FDI LOOKUP Function

The LOOKUP function, as all VALID routines, returns a value.
In this case, a logical value (.T. or .F.).

The syntax of the function is as follows:

@ x,y GET M->type PICT [@!] VALID LOOKUP([xcodes],; && name of alias
M->type ,; && value to find
.T. ,; && warn the user if not found
[desc] ,; && name of field to display in xcode.dbf
ROW() ,; && row to display [desc]
COL()+2) && column to display [desc]


The first parameter, the alias to look in, is required. All
other parameters are optional, and will default as defined
below:

Value to find ....................... READVAR()
Warn the user if not found .......... .F.
Name of field to display ............ No default, but no data
displayed if not sent.
Row to display [desc] ............... ROW()
Column to display [desc] ............ COL()+2



10.4 Seeding A Value From A Valid

A nice technique when querying for data ranges for reporting:

date1 = DATE()
date2 = DATE()

@ x,y GET date1 VALID STUFFIT(LDOM(date1),@date2)
@ROW()+2,y GET date2

FUNC stuff_it && used to stuff an input value into another variable during a valid
PARA stuff_value, stuff_var
stuff_var = stuff_value
RETURN .T.


FUNC ldom && return last day of the mointh for any given date
PARA xdate
PRIV mdate
mdate = xdate-DAY(xdate)+34 && position in next month
RETU mdate-DAT(mdate)



Section 11: Assignment - Give The User Help


11.1 Overview of the Help Routines

All help in our systems is accessed by pressing the F1 key.
The F1 key is hardcoded in Clipper to look for the Help.Prg,
as if the statement "SET KEY 28 to help" was in your
Prologue.Prg file. Help may be called from any wait state:
READ, MENU TO, WAIT, INPUT, ACCEPT. INKEY(0) is not a wait
state, so LASTKEY() must be tested to see if the value if 28.
As with any SET KEY statement, tghree parameters are sent to
Help.Prg:

Input variable, Line number, and Calling procedure.

These parameters are interpreted in order to decide the
correct help routine to display.


11.2 Hard-Coded Help

There are three hardcoded help routines in the Toolbox. These
are the screens that appear during the use of BROWSE,
MEMOEDIT, and WINDOW. BROWSE and WINDOW help are called
directly from the library. MEMOEDIT help is called by an IF
statement in the application HELP.PRG.

* Help.prg
PARAM call_prg,line_number,input_var

IF call_prg = [HELP] && return immediately if HELP is calling on itself!
RETURN
ENDIF

CO_PUSH()

PRIVATE malias,wtitle,wfields,mtitle,top_row,bot_row,;
right_col,group_key,group,mscreen,memopassive

STORE [] TO group_key,group,mscreen
malias = SELE()
module = IF(TYPE([module])=[U],[],module)
M->type = DEFAULT([M->type],[])
memopassive = .T.

* special case for memo editing:
IF call_prg = [MEMOEDIT] .AND. ! memopassive
DO hc_help WITH [memokeys]
CO_POP()
RETU
ENDIF





11.3 Memofield Help

The Toolbox has the ability to allow users, or more likely
liaisons, to define there own help screens. These screens are
kept in a database called help.prg. They are called by the
application help file by seeking for input variable, calling
program and module in help.prg.

PRIV mr,mc

DO helpfile

ivar = SUBS(input_var+SPAC(10),1,10)
cprg = SUBS(call_prg +SPAC(10),1,10)

SEEK ivar+cprg+M->module
IF ! EOF() && help screen exists
CO_CHG(c_help)
mr = ROW()
mc = COL()
DO CASE && decide which quarant to put it in
CASE mr < 11 .AND. mc < 40
mr = 12
mc = 39
CASE mr >= 11 .AND. mc < 40
mr = 1
mc = 39
CASE mr < 11 .AND. mc >= 40
mr = 12
mc = 4
CASE mr >= 11 .AND. mc >= 40
mr = 1
mc = 4
CASE mr >= 11 .AND. mc >= 40
mr = 1
mc = 4
ENDCASE
mscreen = SAVESCR(mr-1,mc-2,mr+14,mc+40)
FRAMEBOX(mr-1,mc-1,mr+12,mc+37)
MEMOEDIT(helpmemo,mr,mc,mr+10,mc+35,.F.)
RESTSCR(mscreen)
ENDIF
SELE (malias)



11.3 Memofield Help (continued)

In order to activate the capability to write user help
screens, add the following lines to MENU.PRG.:

SET KEY -9 TO helpsys

and after programming control in MENU.PRG:

DO helpsys


11.4 Lookup Table Help

The help that usually proves most valuable to the user is
called lookup, or window help. Some examples follow:

* decide which help program to call:
DO CASE

CASE input_var = [UNIT] .AND. module $ [PS/TH]
mscreen = SAVESCR(6,1,22,79)
DO seekhelp WITH [units]
mtitle = [Therapy Unit Codes Table]
wtitle = [CodeÄÄÄDescriptionÄÄÄÄÄÄÄÄÄÄÄÄÄÄ]
wfields = [code+' ³ '+desc]
DO window WITH 7,12,16
M->&input_var = IF(ESC(),M->&input_var,code)
RESTSCR(mscreen)
KEYBOARD(CHR(13))

CASE input_var = [MOD] .AND. module = [PS]
mscreen = SAVESCR(6,1,22,79)
DO seekhelp WITH [mocode]
mtitle = [Modality Codes Table]
wtitle = [CodeÄÄÄDescriptionÄÄÄÄÄÄÄÄÄÄÄÄÄÄ]
wfields = [code+' ³ '+desc]
DO window WITH 7,12,16
M->&input_var = IF(ESC(),M->&input_var,code)
RESTSCR(mscreen)
KEYBOARD(CHR(13))

ENDCASE

SELE (malias)

IF input_var <> [XCHOICE]
CURS_ON()
ELSE
CURS_OFF()
ENDIF
CO_POP()
RETURN



11.5 Implementation

In order to create a pop-up help window do the following:

Define the CASE statement in help.prg.

Save the portion of the screen to be used for the window.

Seek the value help is offered for with the seekhelp
procedure.

Set mtitle, wtitle and wfields.

Call the window procedure with at least the first three corner
point parameters.

Redefine the variable.

If needed, KEYBOARD an enter key.



Section 12: Assignment 6 - Layered Browse Screens


12.1 Overview of Group and Group_key


Creating a layered browse screen is a great technique for
handling parent/child relationships. The key to this
technique is the libraries use of group and group_key.

It is a common problem to have to work with data subsets. For
example, if the master record for a purchase order is on the
screen and the user wants to see the line items in the PO, the
problem is to look at the 80,000 record po_line database and
only work with the 15 records associated with this PO, number
1000. Assuming one rejects SET FILTER as a viable solution,
and without copying to a temp file, here is another
possibility:

Structure for po_head.dbf : Master file, one record per
purchase order:

Structure for database: po_head.dbf
Number of data records: 14357
Date of last update : 05/15/89
Field Field Name Type Width Dec
1 NUM Numeric 6
2 VENDOR Character 3
3 STATUS Character 1
.
.
.
Structure for po_line.dbf : Transaction file, one record per
purchase order line item:

Structure for database: G:po_line.dbf
Number of data records: 85793
Date of last update : 05/15/89
Field Field Name Type Width Dec
1 NUM Numeric 6
2 LOC Numeric 2
3 SEQ_NUM Numeric 3
.
.
.



Assume the screen currently displays PO Number 000556. The
goal is to pop a window with the line items in it, and allow
the typical
Add/Edit/Delete etc. functions on the data subset. The po_line
database is indexed on the num field.

Before calling your data maintenance routine add the lines:

group = po_head->num
group_key = [num]

From this point on, in your data maintenance routine, use the
following
functions:

BOTT() instead of GO BOTT
TOP() instead of GO TOP
OFF() instead of EOF() or BOF()

FUNC off && test for primary key grouping
PRIV mret
IF EOF() .OR. BOF()
mret = .T.
ELSE
IF EMPTY(M->group)
mret = .F.
ELSE
mret = group # &group_key
ENDIF
ENDIF
RETU mret

FUNC top && get to the top of this group, or top of file
IF EMPTY(M->group)
GO TOP
ELSE
SEEK M->group
ENDIF
RETURN ([])



FUNC bott && get to the bottom of this group, or bottom of file
IF EMPTY(M->group)
GO BOTT
ELSE && group is defined
IF TYPE([M->group]) <> [N]
SET SOFTSEEK ON
SEEK SOFTSEEK(M->group)
SET SOFTSEEK OFF
ELSE
LOCA FOR .F. WHILE &group_key # M->group
ENDIF
IF ! BOF()
SKIP -1
ENDIF
ENDIF
RETURN ([])


FUNC softseek && trim last char of string and add 1 ascii value
PARA mval
RETU SUBS(mval,1,LEN(mval)-1) + CHR(ASC(SUBS(mval,-1,1))+1)



12.2 Implementation of Group and Group_key


Implementation involves the following steps to add a menu
choice called Transactions, and to have a layered browse
screen appear:

Declare a non-standard menu choice using the scrmenu and/or
browmenu variable.

Initialize the proc_key variable.

Write the procedure XX_T in the module XX_maint.

Write the procedure YY_MAINT.

Change the file opening section in XX_MAINT if necessary.

Add the file opening procedure in PRC if necessary.



Section 13: Reporting Tricks


13.1 Make a Temporary Database Structure


Very often in reporting it is convenient to put the data being
reported in a temporary database structure. Here is a
technique to create the structure:

In the apppplication:

SELE 0
CREATE temp2&msta.
USE temp2&msta EXCLUSIVE
DO make_field WITH [client ] ,[C], 8,0
DO make_field WITH [job ] ,[C],12,0
DO make_field WITH [cat ] ,[C], 5,0 && Prod->
DO make_field WITH [type ] ,[C], 1,0 && Prod->type or T)ime
DO make_field WITH [desc ] ,[C],50,0 && Prod->
DO make_field WITH [est_amt] ,[N],12,2 && Prod->est_amt
DO make_field WITH [estimate],[C],8 ,0
DO make_field WITH [billed ] ,[N],12,2 && Prod->inv_amt
DO make_field WITH [po_num ] ,[C], 6,0 && P_PMTS->
DO make_field WITH [po_amt ] ,[N],10,2 && P_PMTS->
DO make_field WITH [vendor ] ,[C], 8,0 && P_PMTS->
DO make_field WITH [inv_num] ,[C],12,0 && P_PMTS->inv_num
DO make_field WITH [cost ] ,[N],12,2 && P_PMTS->inv_amt
DO make_field WITH [house ] ,[L],1,0
DO make_field WITH [in_house],[N],12,2 && TIME->hrs * bill_rate
DO make_field WITH [final ] ,[N],12,2
DO make_field WITH [recon ] ,[N],12,2
USE
CREATE temp1&msta. FROM temp2&msta.
USE temp1&msta. ALIAS detail EXCLUSIVE


In the library:

PROC make_field && adds a field to current database (Extended)
PARAM name,type,len,dec
APPE BLAN
REPL field_name WITH name,; && You'll create a field from this record.
Field_type WITH type,;
Field_len WITH len,;
field_dec WITH dec
RETURN



13.2 Reporting From Arrays


In the continual quest to shorten development time of writing
reports, we have found the technique of reporting from arrays
to be flexible and simple to maintain.

* tr_rep.prg

DO printit && from the TOOLBOX, sets up for printing
ESCBREAK() && if they escape in printit ...

* open databases, check indices
DO setup
DO travel
INDE ON DTOS(app_date)+type+emp TO temp&msta
?? condense && generic print codes
* headings for report
head1 = [Employee Travel Approval Departing Travel Travel]
head2 = [Name Destination Date Date Per Diem Amount]
head3 = [________________________ ______________________ __________ __________ ________ _______]
tabline = [1 2 3 4 5 6]

mcols = 6
PRIV line[mcols],; && for one line of report
tab[mcols],; && for column position
totals[mcols],; && for totaling numeric fields
totcols[mcols],; && for identifying fields to total
pict[mcols] && for picture for each field

* fill array for column positions
FOR i = 1 TO mcols
tab[i] = AT(LTRIM(STR(i,2,0)),tabline) + 2
NEXT i

* fill array for column pictures
AFILL(pict,[@X])
pict[6] = [9999999.99]

* fill array telling which columns to total
AFILL(totcols,.F.)
totcols[6] = .T.



13.2 Reporting From Arrays (continued)


* seed totaling array

AFILL(totals,0)
page = 0
DO WHIL ! EOF() .AND. ! ESC()
IF PROW() > 50 .OR. page = 0
page = page + 1
@ 1,tab[1] SAY [Date: ]+DTOC(DATE())
@ PROW(),1 SAY CENTER(TRIM(mcomp_name),132)
@ PROW()+1,tab[1] SAY [Page: ]+ALLTRIM(STR(page))
@ PROW()+1,1 SAY CENTER([Travel Report],132)
@ PROW()+2,1 SAY CENTER([Office Code: ]+TRIM(setup->o_code),132)
@ PROW()+1,1 SAY CENTER([Activity Code: ]+TRIM(setup->a_code),132)
@ PROW()+3,tab[1] SAY head1
@ PROW()+1,tab[1] SAY head2
@ PROW()+0,tab[1] SAY head3
ENDIF
line[1] = emp && these are field names in travel.dbf
line[2] = dest
line[3] = app_date
line[4] = from
line[5] = per_diem
line[6] = advance
@ PROW()+1,1 SAY []


FOR i = 1 TO mcols
@ PROW(),tab[i] SAY line[i] PICT pict[i]
IF totcols[i]
totals[i] = totals[i] + line[i]
ENDIF
NEXT
SKIP
ENDDO

FOR i = 1 TO mcols
IF totcols[i]
@ PROW(),tab[i] SAY ULINE(TRAN(0,pict[i]))
ENDIF
NEXT
@ PROW()+2,tab[1] SAY [Totals...]

FOR i = 1 TO mcols
IF totcols[i]
@ PROW(),tab[i] SAY totals[i] PICT pict[i]
ENDIF
NEXT
@ PROW()+1,1 SAY []

FOR i = 1 TO mcol
IF totcols[i]
@ PROW(),tab[i] SAY REPL([=],LEN(TRAN(0,pict[i])))
ENDIF
NEXT

DO closeit
CLOS DATA
RETU




13.3 Use of printit, openit and closeit


Before every report using the Toolbox, we always set up the
printer with the procedures PRINTIT or OPENIT.


PRINTIT prompts the user to set up the printer, and checks for
the printer with the Clipper ISPRINTER() function. To invoke,
just add DO printit in the report. If a custom prompt is
required, send it as a parameter:

DO printit WITH [Please put checks in printer.]

OPENIT works the same way as printit, except that it gives the
user a choice of output: Printer, Screen, or File.

Both PRINTIT and OPENIT update a public variable called
mdevice to the value of [P], [S] ir [F].

[CLOSEIT] sets output to the screen and resets mdevice to [S].



13.4 Escaping From Reports

If the user wishes to escape from a long printing or
calculating routine, we use a BEGIN/END SEQUENCE structure.

For example, in the menu calling the report:

* REP_MENU.PRG
PRIVATE xchoice,mscreen2,menuloop
menuloop = .T.
DO WHIL menuloop
BEGIN SEQUENCE
CO_CHG(c_menus)
@ 24,0
DO box WITH 8,20,7,40,[Reports]
CO_CHG(curr_grp,c_sayget)
@ 11,25 PROMPT [1. Job Status ] MESSAGE [Report workorder status on one or all jobs]
@ ROW()+1,25 PROMPT [2. Job Listing ] MESSAGE [Master listing of all jobs]
@ ROW()+1,25 PROMPT [3. Payroll ] MESSAGE [Report payroll records one or all employees]
MENU TO xchoice
DO CASE
CASE xchoice = 1
CASE xchoice = 2
CASE xchoice = 3
OTHERWISE
menuloop = .F.
BREAK
ENDCASE
END SEQUENCE
DO sub_menu_clean
ENDDO
RETURN




Escaping From Reports (continued)

In the report:
.
.
.
DO WHILE ! EOF() .AND. ESCBREAK()
DO jobheader
SELE workord
SEEK job->jobnum
@ PROW()+2,5 SAY [Completed Workorders:]
@ PROW()+1,1 SAY bodyhead
DO WHILE job->jobnum = jobnum .AND. complete .AND. ! EOF() .AND. ESCBREAK()
DO jobbody
ENDDO
@ PROW()+3,5 SAY [Not Completed Workorders:]
@ PROW()+1,1 SAY bodyhead
DO WHILE job->jobnum = jobnum .AND. ! complete .AND. ! EOF() .AND. ESCBREAK()
DO jobbody
ENDDO
@ PROW()+1,0
IF ! EMPTY(mjob)
EXIT
ENDIF
SELE job
SKIP
ENDDO
.
.
.

In the Toolbox library:

FUNC escbreak
IF ESC()
CLOSE DATA
IF mdevice $ [PF]
DO closeit
ENDIF
BREAK
ENDIF
RETU .T.




Section 14: Multi-user Programming


14.1 Overview

Multi-user programming in the Clipper environment requires
that the developer understand a number of design, syntax, and
programmatic differences in the way Clipper performs. On the
other hand, this is not as intimidating as it seems since all
Toolbox calls are automatically compatible with multi-user
programming.

Much of this chapter is based on the article by Rick Spence
that first appeared in Reference(Clipper), Volume III, No. 1.

A Clipper system knows it is multi-user by the inclusion of
the statement:

SET EXCLUSIVE OFF

This statement, in our standards, goes into MENU.PRG after the
call to PROLOGUE.PRG and STD_SET.PRG.




14.2 Locking Rules

Clipper has three states of locking avaliable to the
developer:

EXCLUSIVE USE - Most Restrictive

FILE LOCKING

RECORD LOCKING - Least Restrictive

The commands that require locks are:

Command Lock Required

APPEND FROM ............ FLOCK
DELETE WHILE ........... FLOCK
DELETE ................. RLOCK
GET [fieldname] ........ RLOCK
PACK ................... EXCLUSIVE USE
REINDEX ................ EXCLUSIVE USE
REPLACE WHILE .......... FLOCK
REPLACE ................ RLOCK
RECALL WHILE ........... FLOCK
RECALL ................. RLOCK
UPDATE ON .............. FLOCK
ZAP .................... EXCLUSIVE USE



Locking Rules (continued)


Some of the locks in the previous table are only required by
design, not by Clipper's requirement. For example, any time
a FLOCK is required, a RLOCK could be used in the WHILE
statement:

REPL x WITH y WHILE RLOCK()

The problem with this design is being able to trace and
correct the error if he lock fails halfway through the
process.



Locks are released by issuing the command UNLOCK or issuing
a lock in the same select area, or by the command UNLOCK ALL
for all select areas.

A file that opened with USE EXCL must be closed and reopened
shared.


Clipper provides a function to test if the file was accessed
and/or locked successfully. This function is called NETERR().
NETERR returns .T. if any error occurred in performing a lock
or file open.



14.3 Changes in Clipper's Behavior


Clipper batch commands behave differently dependent upon the
status of EXCLUSIVE


For example: SUM amount TO mval

IF EXCLUSIVE is set ON, Clipper will read the records by
groups into memory buffers.

IF EXCLUSIVE is set OFF, Clipper will go back to the disk
and read each record individually.

Therefore, if a system is designed to run under either
single or multi, you should set EXCLUSIVE ON during
single user mode for faster processing of batch commands.




In addition, the number of records is determined
differently.

IF EXCLUSIVE is set ON, Clipper determines the number of
records by looking at the database header.

IF EXCLUSIVE is set OFF, Clipper determines the number
of records by looking at the length of the file.



RLOCK() and FLOCK() automatically refresh the buffer from
the disk. A SKIP 0 is not needed to ensure that the
current disk data is loaded into the buffer.

UNLOCK will automatically write the buffer to disk.
UNLOCK ALL refreshes all buffers except the header.

SEEK will automatically refresh the buffer from the disk.

COMMIT will commit the DOS buffers to the disk.




14.4 Locking A Series Of Databases


Some transactions, such as billing or posting, require access
to a series of databases, or they should not begin. This
function makes sure that all our available before it starts
processing.


SET EXCL OFF

SELE 0
USE invoice
SELE 0
USE shipping
SELE 0
USE picking

DECL f_to_lock[3]
f_to_lock[1] = [invoice]
f_to_lock[2] = [shipping]
f_to_lock[3] = [picking]

IF LOCKALL(f_to_lock)
? [all locked]
ELSE
? [not all locked]
ENDIF

UNLOCKALL(f_to_lock)

FUNC lockall
PARA array
PRIV mfile,mlocked,i,j
mlocked = .T.
FOR i = 1 TO LEN(array)
mfile = array[i]
SELE &mfile
IF ! FLOCK()
mlocked = .F.
EXIT
ENDIF
NEXT
IF ! mlocked
FOR j = 1 TO i-1
mfile = array[j]
SELE &mfile
UNLOCK
NEXT
ENDIF
RETU mlocked

FUNC unlockall
PARA array
PRIV mfile,mfailed,i
FOR i = 1 TO LEN(array)
mfile = array[i]
SELE &mfile
UNLOCK
NEXT
RETU []


14.5 Printers










[ THIS PAGE INTENTIONALLY LEFT BLANK]



14.6 Making Sure Temporary Files Are Unique On A Network


When we write for networks, we use an assembler function that
will tell us the station number on Novell networks. On other
networks, assuming the stations aren't diskless, we put a text
file in the root directory of the boot disk called station.txt
that contains a unique station number. This is fine with hard
disks, somewhat less satisfactory with floppy boot disks.

Once we have a station number we save it as a literal to a
public variable called msta. When we need a temp file we can:

COPY TO temp&msta
USE temp&msta EXCL ALIAS sales




Section 15: Tangents, Tricks and Techniques


15.1 Errorsys Program


This Errorsys gives developers information to the screen and
writes a message to an ASCII file for review when the client
calls.

FUNC print_error
PARA name, line
PRIV err_choice,mscreen,mret,etype
mret = .F.
etype = PROCNAME()
DO errorwrite
SET DEVI TO SCRE
SET CONS ON
SET PRINT OFF
* ----- BEEP/BOXX/SAY the error
DO errorbeep
SAVE SCREEN TO mscreen
BOXX(2,2,PROCNAME() + [ Proc ] + M->name + [ line ] + LTRIM(STR(M->line))
+[, ] + M->info + [: ] + _1)
REST SCREEN FROM mscreen
IF YES_NO([Do you want to continue printing])
SET DEVI TO PRINT
SET PRINT ON
mret = .T.
ELSE
DO breakout
ENDIF
RETU mret

* ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ
FUNC undef_error
PARA name, line, info, model, _1
etype = PROCNAME()
DO errorwrite
* ----- BEEP/BOXX/SAY the error
DO errorbeep
SAVE SCREEN TO mscreen
BOXX(2,2,PROCNAME() + [ Proc ] + M->name + [ line ] + LTRIM(STR(M->line))
+[, ] + M->info + [: ] + _1)
INKEY(0)
DO breakout
RETU .F.



Errorsys Program (continued)

* ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ
FUNC db_error
PARA name, line, info
PRIV err_choice,mscreen,ret_val,etype,mfield,i,highnum
etype = PROCNAME()
mfield = []

IF [numeric overflow] $ LOWER(info)
PRIV fname[FCOUNT()],;
ftype[FCOUNT()],;
fwide[FCOUNT()],;
fdec[FCOUNT()]
AFIELDS(fname,ftype,fwide,fdec)
FOR i = 1 TO FCOUNT()
mfield = fname[i]
IF ftype[i] <> [N]
LOOP
ENDIF
highnum = REPL([9],fwide[i]-IF(fdec[i]=0,0,fdec[i]+1))
IF fdec[i]=0
highnum =highnum + [.] + REPL([9],fdec[i])
ENDIF
IF &mfield > VAL(highnum)
EXIT
ENDIF
NEXT
ENDIF

_1 = mfield
DO errorwrite

* ----- BEEP/BOXX/SAY the error
DO errorbeep
SAVE SCREEN TO mscreen
BOXX(2,2,PROCNAME() + [ Proc ] + M->name + [ line ] + LTRIM(STR(M->line))
+[, ] + M->info + [, Field=] + IF(!EMPTY(_1),_1,[]))
INKEY(0)
DO breakout
RETU .F.



Errorsys Program (continued)

* ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ
FUNC open_error
PARA name, line, info, model, _1
PRIV err_choice,mscreen,mret,_5,etype
etype = PROCNAME()

* ----- Record the error in the error database
M->_5 = [DOSERROR ] + STR(DOSERROR(),1,0)
IF DOSERROR() = 4 && already too many files open, close to write to disk
DO yes_no WITH [Too many files open. Invoke Debugger ?]
IF myn = [Y]
ALTD()
ENDIF
CLOSE DATA
ELSEIF DOSERROR() = 5 && phf 10:22:23 4/21/1989
mret = .F. && caused by attempting to use a file that is
ENDIF && used exclusively by another station
DO errorwrite

* ----- BEEP/BOXX/SAY the error
DO errorbeep
SAVE SCREEN TO mscreen
BOXX(2,2,PROCNAME() + [ Proc ] + M->name + [ line ] + LTRIM(STR(M->line))
+[, ]+ M->info +[, ]+ M->model,;
M->_1 + [ (] + LTRIM(M->_5) + [)])
INKEY(0)
DO breakout
RETU mret

* ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ
FUNC expr_error
PARA name, line, info, model, _1, _2, _3
PRIV err_choice,mscreen,ret_val,etype
etype = PROCNAME()

* ----- Record the error in the error database
DO errorwrite

* ----- BEEP/BOXX/SAY the error
DO errorbeep
SAVE SCREEN TO mscreen
BOXX(2,2,PROCNAME() + [ Proc ] + M->name + [ line ] + LTRIM(STR(M->line))
+[, ]+ M->info,;
IF(DEFINED([M->_1]),CVAL(M->_1),[])+IF(DEFINED([M->_2]),+[ and
]+CVAL(M->_2),[])+IF(DEFINED([M->_3]),+[ and ]+CVAL(M->_3),[]))
INKEY(0)
DO breakout
RETU .F.


Errorsys Program (continued)

* ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ
FUNC misc_error
PARA name,line,info,model
PRIV xchoice,malias,mscreen,etype
etype = PROCNAME()
IF LOWER(info) = [run error]
_5 = [DOSERROR ] + STR(DOSERROR(),1,0)
ENDIF

* ----- Record the error in the error database
DO errorwrite

* ----- BEEP/BOXX/SAY the error
DO errorbeep
SAVE SCREEN TO mscreen
BOXX(2,2,PROCNAME() + [ Proc ] + M->name + [ line ] + LTRIM(STR(M->line))
+[, ] + M->info +[, ] + M->model)
INKEY(0)
DO breakout
RETU .F.

* ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ
PROC errorbeep
TONE(60,1)
RETU

* ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ
PROC breakout
CLEAR GETS
BREAK
RETU

* ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ
PROC errorwrite
PRIV handle,mstr,len
IF ! FILE([errorsys.txt])
handle = FCREATE([errorsys.txt])
ELSE
handle = FOPEN([errorsys.txt],2)
ENDIF
mstr = CHR(13)+CHR(10) +;
CHR(13)+CHR(10) +;
DTOC(DATE()) + [ ] +;
TIME() + [ ] +;
SUBS(M->etype+SPAC(10),1,10) + [ ] +;
SUBS(M->name+SPAC(10),1,10) + [ ] +;
SUBS(ALLTRIM(STR(M->line,0))+SPAC(5),1,5) + [ ] +;
SUBS(M->info+SPAC(25),1,25) +;
CHR(13)+CHR(10) +;
IF(DEFINED([M->model]),SUBS(M->model+SPAC(20),1,20),[]) + [ ] +;
IF(DEFINED([M->_1]),SUBS(CVAL(M->_1)+SPAC(20),1,20),[]) + [ ] +;
IF(DEFINED([M->_2]),SUBS(CVAL(M->_2)+SPAC(20),1,20),[]) + [ ] +;
IF(DEFINED([M->_3]),SUBS(CVAL(M->_3)+SPAC(20),1,20),[]) + [ ] +;
IF(DEFINED([M->_4]),SUBS(CVAL(M->_4)+SPAC(20),1,20),[]) + [ ] +;
IF(DEFINED([M->_5]),SUBS(CVAL(M->_5)+SPAC(20),1,20),[]) + [ ]

len = FSEEK(handle,0,2) && number of bytes in file
FSEEK(handle,len,0) && move to end of file
FWRITE(handle,mstr)
FCLOSE(handle)
RETU


Errorsys Program (continued)

* ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ
FUNC cval && return char val
PARA mval
PRIV mret
DO CASE
CASE TYPE([mval]) = [C]
mret = mval + [ :C]
CASE TYPE([mval]) = [N]
mret = LTRIM(STR(mval)) + [ :N]
CASE TYPE([mval]) = [D]
mret = DTOC(mval) + [ :D]
CASE TYPE([mval]) = [L]
mret = IF(mval,[T],[F]) + [ :L]
CASE TYPE([mval]) = [A]
mret = IF(mval,[T],[F]) + [ :A]
ENDCASE
RETU mret



15.2 Displaying The Values Of Arrays While In The Debugger

This is useful for looking at arrays while you are in the
debugger.

FUNC da && used for debugging ... prints an array
PARA aname,autoprint
autoprint = DEFAULT([autoprint],.F.)
mprint = .F.
SAVE SCREEN TO temp
@ 0,0 CLEAR
IF ! autoprint
DO openit
ELSE
DO printit WITH [NO]
ENDIF
CLEAR
@ 1,0 SAY [Data in array: ]+aname
PRIV i
@ 1,0
mln = 1
FOR i = 1 TO LEN(&aname)
IF i/20 = INT(i/20) .AND. mdevice = [S]
mln = mln + 1
@ mln,0
WAIT
CLEAR
@ 1,0 SAY [Data in array: ]+aname
mln = 1
ENDIF
IF TYPE('&aname.[i]') <> [U]
mln = mln + 1
@ mln,5 SAY i
@ mln,20 SAY &aname.[i] PICT [99999999999.9]
ENDIF
IF ESC()
EXIT
ENDIF
NEXT
mln = mln + 1
@ mln,0
DO closeit
REST SCREEN FROM temp
RETU []



15.3 A Simple Data Encryption Technique


This technique is not going to give anyone at the CIA much
trouble, but it is quick and easy to implement. When the user
looks at the data in dBASE it will be a little more difficult
to understand.

To implement:

REPLACE pw->password WITH CODE(M->password,[e]) && encrypts the password
M->password = CODE(pw->password,[d]) && decrypts the password

The functions:


FUNC code && encode/decode a string based on the first characters ASCII value
PARA mstr,mcode
PRIV mlen,offset
mlen = LEN(mstr)
IF LOWER(mcode) = [e]
offset = ASC(SUBS(mstr,1,1))
ELSE
offset = ROUND(ASC(SUBS(mstr,1,1))/2,0)
ENDIF
FOR i = 1 TO mlen
mstr = SUBS(mstr,1,i-1)+OFFSET(SUBS(mstr,i,1),IF(LOWER(mcode) = [e],offset,-offset)) +
IF(i NEXT
RETU mstr

* ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ
FUNC offset
PARA mchar,offset && encrypt or decrypt
RETU CHR(ASC(mchar)+offset)




15.4 Maintaining Data Integrity Of Related Databases During Edits

In a relational system, the developer is responsible for
making sure the system holds together. For example, in a
billing system, assume a customer changes name from ABC
Widgets to XYZ Widgets. In order to keep a mnemonic code, we
want to change the customer code to XYZ in the customer
database. However, we have related data in the shipping,
picking, and orders databases.


In the customer maintenance routine there will be a replace
procedure that replaces fields with edited memory variables.


PROC cu_repl
IF GLOBAL_REPL(M->cus_num,[shipping],[cus_num],1,.T.) .AND.;
GLOBAL_REPL(M->cus_num,[picking],[cus_num],2,.F.) .AND.;
GLOBAL_REPL(M->cus_num,[orders],[cus_num],1,.F.)
DO std_repl
ENDIF
RETU



Maintaining Data Integrity During Edits (continued)


FUNC global_repl && global Replace
PARA var,; && Variable to test and replace with
dbf,; && target database
t_fld,; && target field
use_index,; && Which Index Order? - 0 = no index
ask && ask whether to change key field
PRIV oldkey,newkey,not_open,oldarea
IF ADDING()
RETU .F.
ENDIF
use_index = IF(TYPE([use_index])=[N],use_index,0)
ask = IF(TYPE([ask])=[U],.F.,ask)
oldkey = &var
newkey = M->&var
IF M->&var <> M->oldkey
oldarea = SELECT()
mquit = .F.
IF M->ask
mquit = ! YES_NO([Change key field? (Y/N)])
ENDIF
IF ! mquit
@ 24,0
@ 24,0 SAY [Please wait. Updating related files ...]
not_open = SELE(dbf) = 0 && it was not already open
IF not_open
SELE 0
DO &dbf
ELSE
SELE &dbf
ENDIF
oldord = INDEXORD()
IF use_index = 0 && Do not use index
SET ORDE TO 0
IF FIL_LOCK(10)
REPL &t_fld WITH M->newkey FOR &t_fld = M->oldkey
UNLOCK
ENDIF
ELSE
SET ORDE TO (use_index)
SEEK oldkey
DO WHILE FOUND()
IF REC_LOCK(10)
REPL &t_fld WITH M->newkey
UNLOCK
ENDIF
SEEK oldkey
ENDDO
ENDIF
SET ORDE TO oldord
IF not_open
USE && close it
ENDIF
ENDIF && myn = [Y]
SELE (oldarea)
ENDIF && M->&var <> oldkey
RETU ! mquit



15.5 Building A Data Audit Trail

Some systems require an audit trail to be built into them. For
example, a scheduling system needed a trail in order to know
who scheduled or canceled a particular patient or class. In
addition, any edited data had to be audited.

In the application program the users logged in and seeded a
public variable, mid, with their id number. A database,
audit.dbf, is used to maintain the audit trail.


Structure for database : \meh\nyu\audit.dbf
Number of data records : 69
Date of last update : 05-26-1989
Field Field name Type Width Dec
----- ---------- ---- ----- ---
1 DATE Date 8
2 TIME Character 5
3 ID Character 3
4 DESC Character 80
** Total ** 97




Building A Data Audit Trail (continued)


The call to the procedure:

DO aud_update WITH ([Scheduled Patient: ] + patient->pbar_id + [ into Class ] + class->code)

Another call to the procedure:

DO aud_edit WITH (M->pbar_id)

The auditing procedure:

PROC aud_update
PARA mdesc
PRIV malias
malias = SELE()
SELE audit
APPE BLAN
REC_LOCK(5)
REPL date WITH DATE(),;
time WITH SUBS(TIME(),1,5),;
desc WITH mdesc,;
id WITH mid
SELE (malias)
RETU

PROC aud_edit
PARA mcode
FOR i = 1 TO FCOUNT()
mfield = FIELD(i)
IF &mfield # M->&mfield
DO aud_update WITH [Edit ] + ALIAS() + [,] + mcode + [ Field: ] + mfield ;
+ [ ] + CTYPE(&mfield) + [->] + CTYPE(M->&mfield)
ENDIF
NEXT
RETU


FUNC ctype
PARA mval
PRIV mret
DO CASE
CASE TYPE([mval]) = [C]
mret = mval
CASE TYPE([mval]) = [N]
mret = LTRIM(STR(mval))
CASE TYPE([mval]) = [D]
mret = DTOC(mval)
CASE TYPE([mval]) = [L]
mret = IF(mval,[T],[F])
ENDCASE
RETU mret



15.6 Saving The Database Status For An Interim Routine

These functions will save the status of the open databases
(alias,index order and record pointer) and restore that status
after an interim routine has run.

DO testa
GOTO 6
DO testb
SET ORDER TO 2
GOTO 2
DO testc
GOTO 9
DO testd
GOTO 11

* Display Status
DO dispstat
WAIT


* Save Status

PRIV alias[4],indexord[4],recno[4]
PRIV cursele
cursele = 0
SAVEDATA(alias,indexord,recno,@cursele)

CLOSE DATA
WAIT

* Restore Status
RESTDATA(alias,indexord,recno,cursele)

* Display Status
DO dispstat
WAIT
RETU


PROC dispstat
CLEAR
i = 1
SELE (i)
DO WHILE ! EMPTY(ALIAS())
? ALIAS()
? INDEXORD()
? RECNO()
i = i + 1
SELE (i)
ENDDO
RETU



Saving The Database Status For An Interim Routine (continued)


PROC testa
DO make_ntx WITH [testa],[testa],[issue]
DO open_file WITH [testa],[testa]
RETU

PROC testb
DO make_ntx WITH [testb],[testb1],[issue]
DO make_ntx WITH [testb],[testb2],[type]
DO open_file WITH [testb],[testb1],[testb2]
RETU

PROC testc
DO open_file WITH [testc]
RETU

PROC testd
DO make_ntx WITH [testd],[testd],[issue]
DO open_file WITH [testd],[testd]
RETU

FUNC savedata
PARA alias,indexord,recno,cursele

cursele = SELE()
FOR i = 1 TO LEN(alias)
SELE (i)
alias[i] = ALIAS()
indexord[i] = INDEXORD()
recno[i] = RECNO()
NEXT
RETU .T.

FUNC restdata
cursele = SELE()
FOR i = 1 TO LEN(alias)
SELE (i)
mfile = alias[i]
DO &mfile
SET order to indexord[i]
GOTO recno[i]
NEXT
SELE (cursele)
RETU .T.



15.7 Speed - What Makes Systems Go Fast Or Slow


Speed in database processing comes down to one issue - record
pointer movement.


There are, admittedly, other issues that make systems run
faster and slower - faster screen clears and draws, less
frequent database opening and closing, but, after all is said
and done, record pointer movement is by far the greatest cause
of speed problems.


The following is a list of commands and issues, and their
effect on pointer movement:

SET FILTER TO - Never, never, never, never, never, never
... By far the worst offender when it comes to slowing
down your systems. Never use this command. Use group and
group_key. Copy to temp.dbf if you must. Hire a
consultant if you can't figure out a way around this
command.


SET RELA TO - Since pointer movement slows down systems,
pointer movement in more than one database slows down
systems more.


LOCATE, SUM, REPLACE, or any other command used in batch
mode without a WHILE delimiter.


Record length has a direct relationship to speed, the
longer the record length, the slower the pointer
movement.


The proximity of the primary index order to the physical
record number order has an effect on speed. Occasionally
copying to a temp with the primary index in use, and
renaming and indexing the temp will spped up the system.


SET EXCLUSIVE OFF in a single user system will cause much
unneeded disk I/O.


File handles are cheap, use lots of indices.