Dec 172017
 
Interactively create and modify database files while running Clipper.
File ONTHEFLY.ZIP from The Programmer’s Corner in
Category Dbase Source Code
Interactively create and modify database files while running Clipper.
File Name File Size Zip Size Zip Type
INTERACT.TXT 5566 1942 deflated

Download File ONTHEFLY.ZIP Here

Contents of the INTERACT.TXT file


Title: INTERACTIVE CREATION/MODIFICATION OF .dbf FILES


Introduction:

In a world dominated by glamorous arrays, UDFs, and
DBEDIT(), one Clipper feature that frequently goes unnoticed
is the ability to interactively create and modify database
files.

Interactive .dbf creation is invaluable if a user
accidentally deletes a database. Although the data cannot
be recovered, at least you can recreate the structure and
thus prevent your application from crashing. Interactive
.dbf modification is useful when you have updated your
application and need to modify database structures 'on the
fly'.


Main Discussion:


Creating a new .dbf file:

There are three basic steps when creating a .dbf file. The
first is to use the CREATE command to make a "structure
extended" file. This structure extended file will serve as
the template which will determine the structure of your
target .dbf file.

CREATE produces a structure extended file with four fields:
"Field_name", "Field_type", "Field_len", and "Field_dec".
The structure of this file is as follows:

Name Type Length Description
Field_name C 10 Name of the field
Field_type C 1 Type of field (C, N, L, M)
Field_len N 3 Length of field
Field_dec N 3 Decimals for numeric fields

The next step is to append records to this structure
extended file that correspond to the fields you desire in
your target .dbf. For example, if you wanted to include the
field Custno of type character with length 8, you would use
the following code:

CREATE tempfile
APPEND BLANK
REPLACE Field_name WITH 'custno'
REPLACE Field_type WITH 'C'
REPLACE Field_len WITH 8
REPLACE Field_dec WITH 0

Please note that although Field_len is limited to three
digits, it is possible to create character fields with
length greater than 999 characters. To do so, Field_dec
must be set to the INT() of the desired length divided by
256, and Field_len must be equal to the remainder
(modulus) of the length divided by 256. For example, if you
wished to have a character field of length 1200 characters,
the Field_dec would be 4 [INT(1200/256)] and the Field_len
would be 176 (1200 % 256).

When you have finished appending records (one for each field
in your target .dbf), the final step is to CREATE your
target .dbf FROM the structure extended file. This command
creates a new .dbf file with field definitions directly
corresponding to the records in the structure extended file.


Modifying the structure of an existing .dbf file:

It is relatively easy to modify an existing .dbf structure
'on the fly' by using the COPY TO STRUCTURE EXTENDED
command. This command is similar to CREATE, except that the
new template file contains the fields definitions of the
specified .dbf file. You may then use REPLACEs and/or
APPENDs to change or add field information as necessary.
Once you have finished modifying the information in the
structure extended file, use CREATE FROM to create a new
.dbf file containing those field definitions.


Examples:

The following code illustrates how the Grumpfish Library
phone directory module interactively creates the file
phone.dbf if it does not already exist. The newest version
of this routine supports merge mailings, which necessitated
adding a logical field (Mail) to the phone.dbf structure.
Rather than ask my users to go in and modify the structure
themselves, I have written this routine to also check for
the existence of the logical field, and if it does not
detect it, modify the structure of phone.dbf accordingly.

*** create phone.dbf if necessary
IF ! FILE('phone.dbf')
SELE 0
CREATE temphone
APPEND BLANK
REPLACE Field_name WITH 'Fname'
REPLACE Field_type WITH 'C'
REPLACE Field_len WITH 15
APPEND BLANK
REPLACE Field_name WITH 'Lname'
REPLACE Field_type WITH 'C'
REPLACE Field_len WITH 15
APPEND BLANK
REPLACE Field_name WITH 'Phone'
REPLACE Field_type WITH 'C'
REPLACE Field_len WITH 12
APPEND BLANK
REPLACE Field_name WITH 'Ext'
REPLACE Field_type WITH 'C'
REPLACE Field_len WITH 4
APPEND BLANK
REPLACE Field_name WITH 'Comp'
REPLACE Field_type WITH 'C'
REPLACE Field_len WITH 20
APPEND BLANK
REPLACE Field_name WITH 'Addr'
REPLACE Field_type WITH 'C'
REPLACE Field_len WITH 25
APPEND BLANK
REPLACE Field_name WITH 'City'
REPLACE Field_type WITH 'C'
REPLACE Field_len WITH 15
APPEND BLANK
REPLACE Field_name WITH 'State'
REPLACE Field_type WITH 'C'
REPLACE Field_len WITH 2
APPEND BLANK
REPLACE Field_name WITH 'Zip'
REPLACE Field_type WITH 'C'
REPLACE Field_len WITH 5
APPEND BLANK
REPLACE Field_name WITH 'Mail'
REPLACE Field_type WITH 'L'
REPLACE Field_len WITH 1
CREATE phone FROM temphone
USE
ERASE temphone.dbf
ENDIF

* modify structure if old phone.dbf
IF TYPE('mail') != 'L'
COPY TO gfphone STRUCTURE EXTE
USE gfphone
APPE BLANK
REPLACE Field_name WITH 'Mail'
REPLACE Field_type WITH 'L'
REPLACE Field_len with 1
CREATE newphone FROM gfphone
USE newphone
APPEND FROM phone
USE
RENAME phone.dbf TO phone.old
RENAME newphone.dbf TO phone.dbf
ERASE gfphone.dbf
ENDIF



Biographical Brief:

*** Greg Lief is a Clipper consultant/software developer
who has written the Grumpfish Library, a collection
of linkable desktop utilities for Summer '87.


 December 17, 2017  Add comments

Leave a Reply