===========================================================================
BBS: PC Connect
Date: 03-03-92 (20:46) Number: 9964
From: NICK DAVIS Refer#: NONE
To: FRANK HAGAN Recvd: NO
Subj: DATABASE Conf: (11) R-QBasic
---------------------------------------------------------------------------
I created this TYPE-variable method of using the db/LIB routines for
reading and writing dBASE-format files because normally this is a
three-step process with db/LIB:

1) Get the record number, given a key, with GetKEY
2) Get the record with GetREC
3) Parse the record with multiple calls to GetFLD, one for each field

With over 600 data files and over 1,000 programs to worry about, I was
overwhelmed by the number of lines of code I'd have to put in each
program just to read and write records. I was also concerned that if
I didn't completely parse each record into its individual fields, I
would invariably mix up data from different records, leading to
disaster. I was also afraid of trying to keep track of a gazillion
variable names. Therefore, I came up with this technique for using
TYPE variables with db/LIB.

First, I created an INCLUDE file for each data file which describes
its layout. Note that the variable 'DeleteCode' must be the first
because db/LIB adds it to keep track of whether or not a record has
been deleted. I used ten- or fewer-character variable subnames and
made them match exactly the field names as db/LIB knew them. Finally,
the TYPE variable was made to match the file name for clarity's sake.
Following is an abbreviated version of one of the INCLUDE files:

TYPE CLIENTType'--------------------------------------------
'
' | File/Variable Name: CLIENT
' | Record Length: 184
' | Description: Client Master File
'
DeleteCode AS STRING * 1
' --------------------------D
KEY AS STRING * 6' CLIENT NO.
FIRM AS STRING * 30' Name of Firm
ADDRESS1 AS STRING * 30' Address Line 1
ADDRESS2 AS STRING * 30' Address Line 2
ADDRESS3 AS STRING * 30' Address Line 3
CITY AS STRING * 15' City
STATE AS STRING * 2' State
ZIP AS STRING * 10' Zip Code
ATTENTION AS STRING * 30' Contact
END TYPE
'
COMMON SHARED /Common.CLIENT/ CLIENT AS CLIENTType' Variable
COMMON SHARED /Common.CLIENT/ DBF.CLIENT, NDX.CLIENT' File Handles

Next, I created another INCLUDE file for each data file to do some
housework each time a data file was needed in a program. One such is:

OpenDBF DBF.CLIENT, Status, "CLIENT.DBF", DBFileType, mode
IF Status THEN PRINT "CLIENT"; Status: END
DBParams(DBF.CLIENT).Segment = VARSEG(CLIENT)
DBParams(DBF.CLIENT).Offset = VARPTR(CLIENT)
DBParams(DBF.CLIENT).Length = LEN(CLIENT)
OpenNDX NDX.CLIENT, Status, "CLIENT.NDX", NDXtype, NDXmode,_
KeyExp$, KeyLen, KeyType, mode
IF Status THEN PRINT "CLIENT"; Status: END

Note that the file handles which db/LIB creates for the data file and
the index file are called DBF.FileName and NDX.FileName in each case -
- again, keeping 600 files straight.

Finally, I created a general purpose INCLUDE file to go with every
program. It is:

'---------------------------------------------------------------------
' Parameters for dBASE File Read/Writes w/ TYPEs
'---------------------------------------------------------------------
'
TYPE dBParamsType
Segment AS INTEGER
Offset AS INTEGER
Length AS INTEGER
END TYPE
'
DIM dBParams(1 TO 100) AS dBParamsType
COMMON SHARED /Common.dBParams/ dBParams() AS dBParamsType

Note that the use of labelled COMMONs lets me freely add data file
INCLUDE files to a program at any time without having to worry about
conflict with existing COMMON blocks.

Then, a sample program would look something like this:

DEFINT A-Z
'$INCLUDE: 'CLIENT.BI'
'$INCLUDE: 'DBPARAMS.BI'
mode = 0 ' Existing File (Add 16 for network use)
'$INCLUDE: 'CLIENT.OP'

This last INCLUDE file is the one described above which opens a data
file and its corresponding index file.

Then, to read or write any data file, I created the following three
routines:
' SET a TYPE Variable to All Blanks
SUB Blank.Rec (n) STATIC
+++9E"kA FOR i =ATE0 S7=60 S11=75 V1 X4 s0=0

PCRelay:SPACE -> #606 RelayNet (tm)
4.11 Menlo Park, CA (415) 323-4193 * 10 lines 3gB
===========================================================================
BBS: PC Connect
Date: 03-03-92 (23:09) Number: 9975
From: NICK DAVIS Refer#: 9964
To: FRANK HAGAN Recvd: NO
Subj: DATABASE (2/3) Conf: (11) R-QBasic
---------------------------------------------------------------------------
----- Continued -----

Finally, I created a general purpose INCLUDE file, DBPARAMS.BI, to be
used with every program. It is:

'---------------------------------------------------------------------
' Parameters for dBASE File Read/Writes w/ TYPEs
'---------------------------------------------------------------------
'
TYPE dBParamsType
Segment AS INTEGER
Offset AS INTEGER
Length AS INTEGER
END TYPE
'
DIM dBParams(1 TO 100) AS dBParamsType
COMMON SHARED /Common.dBParams/ dBParams() AS dBParamsType

Note that the use of labelled COMMONs lets me freely add data file
INCLUDE files to a program at any time without having to worry about
conflict with existing COMMON blocks.

Then, a sample program would look something like this:

DEFINT A-Z
'$INCLUDE: 'CLIENT.BI'
'$INCLUDE: 'DBPARAMS.BI'
mode = 0 ' Existing File (Add 16 for network use)
'$INCLUDE: 'CLIENT.OP'

This last INCLUDE file is the one described above which opens a data
file and its corresponding index file.

Then, to read or write any data file, I created the following three
routines:
' SET a TYPE Variable to All Blanks
SUB Blank.Rec (n) STATIC
Segment = DBParams(n).Segment
Firstx = DBParams(n).Offset
First& = Firstx - (Firstx < 0) * 65536
Last& = First& + DBParams(n).Length - 1
DEF SEG = Segment
FOR i& = First& TO Last&: POKE i&, 32: NEXT
DEF SEG
END SUB
' GET a Record into a TYPE Variable
SUB GetFullREC (FileNumber, Status, RecordNumber#) STATIC
GetREC FileNumber, Status, RecordNumber#, a$
IF Status THEN EXIT SUB' db/LIB ERROR
IF LEFT$(a$, 1) = "*" THEN Status = -1: EXIT SUB' Record Deleted
ASegment = SSEG(a$)
AOffsetx = SADD(a$)
AOffset& = AOffsetx - (AOffsetx < 0) * 65536
Segment = DBParams(FileNumber).Segment
Offsetx = DBParams(FileNumber).Offset
Offset& = Offsetx - (Offsetx < 0) * 65536
L = LEN(a$) - 1
FOR i = 0 TO L
DEF SEG = ASegment: n = PEEK(AOffset& + i)
DEF SEG = Segment: POKE Offset& + i, n
NEXT
DEF SEG
END SUB

----- Continued Next Message -----

PCRelay:SPACE -> #606 RelayNet (tm)
4.11 Menlo Park, CA (415) 323-4193 * 10 lines 3gB
===========================================================================
BBS: PC Connect
Date: 03-03-92 (23:10) Number: 9976
From: NICK DAVIS Refer#: 9964
To: FRANK HAGAN Recvd: NO
Subj: DATABASE (3/3) Conf: (11) R-QBasic
---------------------------------------------------------------------------
----- Continued -----

' PUT a TYPE Variable into a Record
SUB PutFullREC (FileNumber, Status, RecordNumber#) STATIC
Length = DBParams(FileNumber).Length
a$ = STRING$(Length, 32)
ASegment = SSEG(a$)
AOffsetx = SADD(a$)
AOffset& = AOffsetx - (AOffsetx < 0) * 65536
Segment = DBParams(FileNumber).Segment
Offsetx = DBParams(FileNumber).Offset
Offset& = Offsetx - (Offsetx < 0) * 65536
L = Length - 1
FOR i = 0 TO L
DEF SEG = Segment: n = PEEK(Offset& + i)
DEF SEG = ASegment: POKE AOffset& + i, n
NEXT
DEF SEG
PutREC FileNumber, Status, RecordNumber#, a$
END SUB

Then, assuming that I've used GetKEY to get a record number given a
key, I can then use:

Blank.Rec DBF.CLIENT' -- Just in case
GetFullREC DBF.CLIENT, Status, record#

I now have available CLIENT.KEY, CLIENT.FIRM, etc., assuming that
Status = 0.

All the PEEK and POKE stuff above can be avoided (and the program
speeded up) if you have a block memory move routine such as BCopy from
QuickPack Pro.

I know that this seems like a lot of overhead to worry about, but once
these building blocks are in place, life becomes a lot easier
witqdb/LIB, particularly in some of my programs which open 40+ files at
once. Note that I've left out all the stuff related to checking to
see if a record is locked in a network environment, but that's a story
for another day.

== Nick ==


PCRelay:SPACE -> #606 RelayNet (tm)
4.11 Menlo Park, CA (415) 323-4193 * 10 lines 3gB