This chapter explains the INTOUCH data structure statements. One of the major features of INTOUCH is its ability to perform database operations as part of the language. The data structure statements allow you to manipulate data structures in your own programs.
List of Data Structure Statements:
INTOUCH's data management system stores data file information in structures. (Chapter 16, Creating Structures, Field Definitions with SETUP tells how structures are created.) A structure file contains the following information:
In INTOUCH, you address the structure file and not the dataset directly.
An INTOUCH structure is made up of records and fields. A structure looks something like this:
FIELDS
/ | \
/ | \
/ | \
/ | \
/ | \
R Client Last name First name
E Number
C +---------+---------------------------+-------------------+
O _____ |8|0|5|4|3|C|a|s|s| | | | | | | | | | |C|a|t|h|y| | | | | |
R _____ |8|0|5|4|2|B|r|o|c|k| | | | | | | | | |B|u|d| | | | | | | |
D | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
S
positions
In the above example, the records are the horizontal lines of information. In the CLIENT structure above, there is a record for each customer.
Each record consists of fields. For example, the customer records alone might contain a field for the customer's ID number, last name, first name, address, phone number, company name, etc. Each of these pieces of data is stored in its own field--the name field, address field, phone number field, etc.. The fields appear as columns in the diagram above.
To reference a field, you indicate the structure and the field you want. The field name is enclosed in parentheses.
struc_name(field_name)
struc_name is the name associated with the structure. field_name is the name of a field in the structure. INTOUCH searches for the field specified in the current record and reads its contents.
10 OPEN STRUCTURE cl: NAME 'tti_run:client'
20 EXTRACT STRUCTURE cl: KEY ID = '80522'
PRINT cl(last), cl(first) ! Print last and first
! fields from the CL structure
END EXTRACT
CLOSE STRUCTURE cl
30 END
RNH
Errant Earl
The remainder of this chapter describes the data structure statements and how to use them.
OPEN STRUCTURE struc_name: NAME 'struc_filename'
[, ACCESS INPUT | OUTIN] [, LOCK] [, DATAFILE filename]
[, OPTIMIZE OFF]
10 OPEN STRUCTURE cl: NAME 'tti_run:client', &
ACCESS INPUT, LOCK
20 EXTRACT STRUCTURE cl
INCLUDE cl(state) = 'CA'
EXCLUDE cl(phone)[1:3] = '619'
SORT ASCENDING BY cl(last)
END EXTRACT
30 PRINT 'List of California clients by last name'
FOR EACH cl
PRINT cl(first); ' '; cl(last), cl(phone)
NEXT cl
40 CLOSE STRUCTURE cl
50 END
RNH
List of California clients by last name
Dale Derringer (818) 223-9014
Earl Errant (408) 844-7676
The OPEN STRUCTURE statement is used to open a data structure. The structure must be open in order to reference data in the structure (i.e. change field data, add or delete records). struc_name is a name (i.e. nickname) you assign to the structure. You use this name to refer to the structure within the program. The structure name must be unique within the program or program system. If the structure name is associated with another open structure, an exception is generated. The structure name must meet the requirements for variable names.
After the keyword NAME, is the file specification of the structure file being opened. (See Chapter 16, Creating Structures, Field Definitions with SETUP for information on legal structure file names.) The file specification can be any valid string expression.
The ACCESS option tells INTOUCH whether to open the structure for input (reading field data) or for input and output (reading, changing and adding field data). ACCESS input is the default open mode for structures, meaning, that if no ACCESS option is provided, INTOUCH opens the structure for INPUT.
When INTOUCH executes the OPEN statement, it searches for the structure file specified. INTOUCH opens the structure and assigns it the structure name. If the structure file does not exist, an exception is generated.
The ACCESS option determines how you can access the structure.
The access options are:
The LOCK option causes INTOUCH to lock the structure from write access by others. As long as the structure is open, the structure cannot be modified by others. This can speed up INTOUCH's structure statements (EXTRACTs especially). The particular effect depends on the file management system being used.
The DATAFILE option overrides the default datafile as specified by the structure.
DATAFILE file_spec
file_spec is the file specification of the data file you want to open. The file_spec can be any string expression.
14.3 CLOSE STRUCTURE struc_name
CLOSE STRUCTURE struc_name
10 OPEN STRUCTURE cl: NAME 'tti_run:client'
20 EXTRACT STRUCTURE cl
INCLUDE cl(state) = 'CA'
EXCLUDE cl(phone)[1:3] = '619'
SORT ASCENDING BY cl(last)
END EXTRACT
30 PRINT 'List of California Clients'
FOR EACH cl
PRINT cl(first); ' '; cl(last), cl(phone)
NEXT cl
40 CLOSE STRUCTURE cl
50 END
RNH
List of California Clients
Dale Derringer (818) 223-9014
Earl Errant (408) 844-7676
CLOSE STRUCTURE closes a structure from further access. Once the structure is closed, you cannot reference records or add them, and you cannot change field data. struc_name is the name associated with the structure, the name assigned with the OPEN statement.
You can use the statement CLOSE ALL to close all open structures and other files.
ADD STRUCTURE struc_name
---
[LET] struc_name(field) = expr
---
END ADD
10 OPEN STRUCTURE cl: NAME 'tti_run:client', ACCESS OUTIN
20 INPUT 'Enter ID number': id%
INPUT 'Enter last name': last$
INPUT 'Enter first name': first$
INPUT 'Enter state': state$
INPUT 'Enter phone': phone$
30 ADD STRUCTURE cl
PRINT
PRINT 'Adding '; last$; ', '; first$
LET cl(id) = id%
LET cl(last) = last$
LET cl(first) = first$
LET cl(state) = state$
LET cl(phone) = phone$
END ADD
40 CLOSE STRUCTURE cl
50 END
RNH
Enter ID number? 12233
Enter last name? Jones
Enter first name? Tom
Enter state? NV
Enter phone? 2345556161
Adding Jones, Tom
ADD STRUCTURE adds a record to a structure. The ADD STRUCTURE statement begins the add block. struc_name is the name associated with the structure that the record is going to be added to. END ADD marks the end of the block.
When ADD STRUCTURE is executed, INTOUCH executes the block of code between the ADD STRUCTURE statement and END ADD. This block of code with LET statements is used to put data into the fields.
LET assigns a value to the field specified. struc_name(field) specifies a field in the structure. expr is an expression. When INTOUCH executes the LET statement, it evaluates the expression and assigns the value of this expression to the field specified. END ADD writes out the new record.
CANCEL ADD
10 OPEN STRUCTURE cl: NAME 'tti_run:client', ACCESS OUTIN
ADD STRUCTURE cl
INPUT 'Client ID': cl(id)
IF _EXIT THEN CANCEL ADD
INPUT 'Last name': cl(last)
IF _EXIT THEN CANCEL ADD
INPUT 'First name': cl(first)
IF _EXIT THEN CANCEL ADD
PRINT 'Adding client'
END ADD
20 END
RNH
Client ID? 14422
Last name? WHITE
First name? EXIT
CANCEL ADD cancels the current adding of a record to a structure and transfers control to the next statement after the END ADD statement.
This statement can only be used within an ADD block---that is, between an ADD STRUCTURE and an END ADD pair of statements.
EXIT ADD
10 OPEN STRUCTURE cl: NAME 'tti_run:client', ACCESS OUTIN
ADD STRUCTURE cl
INPUT 'Client ID ': cl(id)
INPUT 'Last name ': cl(last)
INPUT 'First name': cl(first)
INPUT 'Contact ': cl(contact)
IF _EXIT THEN EXIT ADD
INPUT 'Salesman ': cl(salesman)
INPUT 'Mother ': cl(mother)
INPUT 'Comment ': cl(comment)
END ADD
PRINT 'Client added'
20 END
RNH
Client ID ? 11111
Last name ? Hollerith
First name? Herman
Contact ? EXIT
Client added
EXIT ADD transfers control immediately to the corresponding END ADD statement and performs the add.
This statement is useful when you want to add a record but do not have all the data. You can enter data into the first few fields and skip the rest of the fields.
DELETE STRUCTURE struc_name
10 OPEN STRUCTURE cl: NAME 'tti_run:client', ACCESS OUTIN
20 EXTRACT STRUCTURE cl
INCLUDE cl(state) = 'CA'
END EXTRACT
30 ! Delete all clients from California
FOR EACH cl
PRINT 'Deleting '; cl(first); ' '; cl(last) ;'...';
DELETE STRUCTURE cl
PRINT 'record deleted'
NEXT cl
40 CLOSE STRUCTURE cl
50 END
RNH
Deleting Keith Kent...record deleted
Deleting Paul Johnson...record deleted
Deleting Wayne Waters...record deleted
Deleting Earl Errant...record deleted
Deleting Cathy Cass...record deleted
Deleting Pete Porter...record deleted
Deleting Dale Derringer...record deleted
DELETE STRUCTURE deletes the current record from the specified structure. struc_name is the structure name associated with the structure that the record is going to be deleted from.
[LOCK | UNLOCK] STRUCTURE struc_name
10 OPEN STRUCTURE cl: NAME 'tti_run:client', ACCESS OUTIN
20 EXTRACT STRUCTURE cl
INCLUDE cl(state) = 'CA'
END EXTRACT
30 FOR EACH cl
PRINT
PRINT cl(first); ' '; cl(last)
LOCK STRUCTURE cl ! Give us exclusive access
LINE INPUT DEFAULT cl(phone), PROMPT 'Enter new phone ': phone$
IF _EXIT THEN EXIT FOR
cl(phone) = phone$
UNLOCK STRUCTURE cl ! Put the record out to disk
! and release it
NEXT cl
40 CLOSE STRUCTURE cl
50 END
RNH
Keith Kent
Enter new phone 6199675021
Paul Johnson
Enter new phone EXIT
You can use LOCK/UNLOCK STRUCTURE to lock or unlock the data record currently being accessed. INTOUCH automatically locks and unlocks data when you work with it. However, you can use LOCK and UNLOCK if you want to do this manually.
Normally, INTOUCH locks and unlocks data as needed. LOCK and UNLOCK override INTOUCH's automatic locking features. LOCK STRUCTURE locks the data currently being accessed, giving the program exclusive access to the current record. No one else can access the data until it is unlocked.
UNLOCK unlocks the current record or data. The record is put to disk (if needed) and can again be accessed by others. struc_name is the structure name associated with the structure.
EXTRACT STRUCTURE struc_name
--- block of code
[INCLUDE | EXCLUDE] cond_expr
[SORT [ASCENDING | DESCENDING] BY expression]
---
END EXTRACT
10 OPEN STRUCTURE cl: NAME 'tti_run:client'
20 PRINT 'List of Clients'
PRINT
30 EXTRACT STRUCTURE cl
PRINT cl(first); ' '; cl(last), cl(phone)
END EXTRACT
40 CLOSE STRUCTURE cl
50 END
RNH
List of Clients
Earl Errant (408) 844-7676
Al Abott (202) 566-9892
Bud Brock (218) 555-4322
Cathy Cass (619) 743-8582
Dale Derringer (818) 223-9014
Fred Farmer (305) 552-7872
When INTOUCH does an extract, it examines each record in the structure. For each record, INTOUCH executes the code between the EXTRACT STRUCTURE and END EXTRACT statements. For instance, here is a structure with client information:
ID # LAST FIRST CITY STATE PHONE
+------+-----------+--------+--------------+--+----------+
|80543 |Cass |Cathy | San Diego |CA|6197438582|
|80542 |Brock |Bud | Duluth |MN|2185554322|
|80522 |Errant |Earl | Monterey |CA|4088447676|
|80561 |Derringer |Dale | Los Angeles |CA|8182239014|
|80531 |Abott |Al | New York |NY|2025669892|
|80573 |Farmer |Fred | Miami |FL|3055527872|
EXTRACT creates a list of clients. The above program example extracts information from each record in the structure and displays each client's name and phone number.
When INTOUCH does an extract, it examines each record of the structure in order. If the KEY option is specified, only those records with a key matching the KEY expression are examined. For each examined record, the following is done:
INTOUCH then builds a list of extracted records. This list can be accessed later with a FOR EACH loop. You can have up to 16 sort keys and 32 extract criteria.
INCLUDE cond_expr
10 OPEN STRUCTURE cl: NAME 'tti_run:client'
20 EXTRACT STRUCTURE cl
INCLUDE cl(state) = 'CA'
END EXTRACT
PRINT 'List of California Clients'
PRINT
FOR EACH cl
PRINT cl(first); ' '; cl(last), cl(state)
NEXT cl
CLOSE STRUCTURE cl
30 END
RNH
List of California Clients
Keith Kent CA
Paul Johnson CA
Wayne Waters CA
Earl Errant CA
Cathy Cass CA
Pete Porter CA
Dale Derringer CA
The INCLUDE statement includes records depending on the value of a conditional expression.
cond_expr is a conditional expression that INTOUCH evaluates. If the expression is TRUE, INTOUCH includes the record in the extract list. If the expression is FALSE, INTOUCH excludes the record from the list. For example, the program above creates an extract list containing only those clients located in California.
NOTE: The conditional expression must match the field's data type. For instance, if the field has a CHARACTER data type, the expression must be a string expression.
EXCLUDE cond_expr
10 OPEN STRUCTURE cl: NAME 'tti_run:client'
20 EXTRACT STRUCTURE cl
EXCLUDE cl(phone)[1:3] = '619'
END EXTRACT
30 PRINT 'List of Clients'
PRINT
FOR EACH cl
PRINT cl(first); ' '; cl(last), cl(phone)
NEXT cl
CLOSE STRUCTURE cl
40 END
RNH
List of Clients
Earl Errant (408) 844-7676
Al Abott (202) 566-9892
Bud Brock (218) 555-4322
Dale Derringer (818) 223-9014
Fred Farmer (305) 552-7872
The EXCLUDE statement excludes records from the extract list, depending on the value of a conditional expression.
cond_expr is a conditional expression. INTOUCH evaluates this expression. If the expression is TRUE, INTOUCH excludes the current record from the extract list. For example, the program above creates an extract list of all the clients in the client structure---except those with an area code of 619.
NOTE: The conditional expression must match the field's data type. For instance, if the field has a CHARACTER data type, the expression must be a string expression.
SORT [ASCENDING | DESCENDING] BY expr
10 OPEN STRUCTURE cl: NAME 'tti_run:client'
20 EXTRACT STRUCTURE cl
SORT ASCENDING BY cl(last)
END EXTRACT
PRINT 'List of Clients'
PRINT
30 FOR EACH cl
PRINT cl(first); ' '; cl(last), cl(phone)
NEXT cl
40 CLOSE STRUCTURE cl
50 END
RNH
List of Clients
Al Abott (202) 566-9892
Bud Brock (218) 555-4322
Cathy Cass (619) 743-8582
Dale Derringer (818) 223-9014
Earl Errant (408) 844-7676
Fred Farmer (305) 552-7872
The SORT statement sorts the records in an extract list in either ASCENDING or DESCENDING order. expr is an expression whose value determines how to order the list. INTOUCH evaluates the expression for each record and stores the value. When all the records have been extracted, INTOUCH orders the list according to these values.
You can sort in either ASCENDING or DESCENDING order. ASCENDING creates a list in ascending order (lowest to highest). DESCENDING creates a list in descending order (highest to lowest). The default is ascending order. String values are sorted according to the ASCII character set.
INTOUCH does sorts in order. Therefore, you can use multiple sorts to order the list more and more specifically. For example, the following program creates a list of clients. The clients are sorted first by state and within each state by last name.
10 OPEN STRUCTURE cl: NAME 'tti_run:client'
EXTRACT STRUCTURE cl
SORT ASCENDING BY cl(state)
SORT ASCENDING BY cl(last)
END EXTRACT
20 PRINT 'List of Clients'
PRINT
FOR EACH cl
PRINT cl(last); ', '; cl(first), cl(state)
NEXT cl
30 CLOSE STRUCTURE CL
40 END
RNH
List of Clients
Cass, Cathy CA
Derringer, Dale CA
Errant, Earl CA
Farmer, Fred FL
Brock, Bud MN
Abott, Al NY
When you sort fields that are filled with nulls (no data was ever stored in them), the fields are sorted as though they were space filled.