B This chapter explains the INTOUCH data structure statements. One D of the major features of INTOUCH is its ability to perform database C 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:
A INTOUCH's data management system stores data file information in ] structures. (Chapter 16, Creating Structures, Field Definitions with SETUP tells how B structures are created.) A structure file contains the following information:
I In INTOUCH, you address the structure file and not the dataset directly.
< An INTOUCH structure is made up of records and7 fields. A structure looks something like this:
. FIELDS < / | \= / | \ > / | \? / | \ @ / | \ F R Client Last name First name 9 E Number J C +---------+---------------------------+-------------------+ J O _____ |8|0|5|4|3|C|a|s|s| | | | | | | | | | |C|a|t|h|y| | | | | | J R _____ |8|0|5|4|2|B|r|o|c|k| | | | | | | | | |B|u|d| | | | | | | | J D | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | S / positions
K In the above example, the records are the horizontal lines of information. D In the CLIENT structure above, there is a record for each customer.
C Each record consists of fields. For example, the customer records E alone might contain a field for the customer's ID number, last name, E first name, address, phone number, company name, etc. Each of these C pieces of data is stored in its own field--the name field, address F field, phone number field, etc.. The fields appear as columns in the diagram above.
I 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. D field_name is the name of a field in the structure. INTOUCH O searches for the field specified in the current record and reads its contents.
5 10 OPEN STRUCTURE cl: NAME 'tti_run:client' 3 20 EXTRACT STRUCTURE cl: KEY ID = '80522' ? PRINT cl(last), cl(first) ! Print last and first G ! fields from the CL structure END EXTRACT CLOSE STRUCTURE cl 30 END RNH Errant Earl
J The remainder of this chapter describes the data structure statements and how to use them.
9 OPEN STRUCTURE struc_name: NAME 'struc_filename' B [, 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 8 PRINT cl(first); ' '; cl(last), cl(phone) NEXT cl 40 CLOSE STRUCTURE cl 50 END RNH 0 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 I structure. The structure must be open in order to reference data in the < structure (i.e. change field data, add or delete records). I 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. I The structure name must be unique within the program or program system. N If the structure name is associated with another open structure, an exception I is generated. The structure name must meet the requirements for variable names.
@ After the keyword NAME, is the file specification of the p structure file being opened. (See Chapter 16, Creating Structures, Field Definitions with SETUP for information I on legal structure file names.) The file specification can be any valid string expression.
; The ACCESS option tells INTOUCH whether to open the K structure for input (reading field data) or for input and output (reading, E changing and adding field data). ACCESS input is the default I open mode for structures, meaning, that if no ACCESS option is provided, ' INTOUCH opens the structure for INPUT.
M When INTOUCH executes the OPEN statement, it searches for the structure file K specified. INTOUCH opens the structure and assigns it the structure name. A 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:
A The LOCK option causes INTOUCH to lock the structure from I write access by others. As long as the structure is open, the structure E cannot be modified by others. This can speed up INTOUCH's structure H 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
E file_spec is the file specification of the data file you want 6 to open. The file_spec can be any string expression.
C 14.3 CLOSE STRUCTURE struc_name
# CLOSE STRUCTURE struc_name
5 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 8 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. H Once the structure is closed, you cannot reference records or add them, A and you cannot change field data. struc_name is the name J 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
C 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 3 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
8 ADD STRUCTURE adds a record to a structure. The D ADD STRUCTURE statement begins the add block. struc_name is F the name associated with the structure that the record is going to be 6 added to. END ADD marks the end of the block.
C When ADD STRUCTURE is executed, INTOUCH executes the block of code J between the ADD STRUCTURE statement and END ADD. This block of code with ; LET statements is used to put data into the fields.
E LET assigns a value to the field specified. struc_name(field) C specifies a field in the structure. expr is an expression. I When INTOUCH executes the LET statement, it evaluates the expression and F assigns the value of this expression to the field specified. END ADD writes out the new record.
CANCEL ADD
C 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 H structure and transfers control to the next statement after the END ADD statement.
F 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
C 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
C EXIT ADD transfers control immediately to the corresponding / END ADD statement and performs the add.
G This statement is useful when you want to add a record but do not have E all the data. You can enter data into the first few fields and skip the rest of the fields.
$ DELETE STRUCTURE struc_name
C 10 OPEN STRUCTURE cl: NAME 'tti_run:client', ACCESS OUTIN ! 20 EXTRACT STRUCTURE cl ' INCLUDE cl(state) = 'CA' END EXTRACT 1 30 ! Delete all clients from California FOR EACH cl B 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 1 Deleting Dale Derringer...record deleted
< DELETE STRUCTURE deletes the current record from the > specified structure. struc_name is the structure name K associated with the structure that the record is going to be deleted from.
@ 14.6 LOCK | UNLOCK STRUCTURE
- [LOCK | UNLOCK] STRUCTURE struc_name
C 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 N 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 3 ! 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 G data record currently being accessed. INTOUCH automatically locks and J unlocks data when you work with it. However, you can use LOCK and UNLOCK ! if you want to do this manually.
E Normally, INTOUCH locks and unlocks data as needed. LOCK and UNLOCK E override INTOUCH's automatic locking features. LOCK STRUCTURE locks G the data currently being accessed, giving the program exclusive access D to the current record. No one else can access the data until it is unlocked.
F UNLOCK unlocks the current record or data. The record is put to disk C (if needed) and can again be accessed by others. struc_name 5 is the structure name associated with the structure.
% EXTRACT STRUCTURE struc_name # --- block of code ( [INCLUDE | EXCLUDE] cond_expr 8 [SORT [ASCENDING | DESCENDING] BY expression] --- END EXTRACT
5 10 OPEN STRUCTURE cl: NAME 'tti_run:client' $ 20 PRINT 'List of Clients' PRINT ! 30 EXTRACT STRUCTURE cl 8 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
I When INTOUCH does an extract, it examines each record in the structure. 7 For each record, INTOUCH executes the code between the / EXTRACT STRUCTURE and END EXTRACT H statements. For instance, here is a structure with client information:
D ID # LAST FIRST CITY STATE PHONE G +------+-----------+--------+--------------+--+----------+ G |80543 |Cass |Cathy | San Diego |CA|6197438582| G |80542 |Brock |Bud | Duluth |MN|2185554322| G |80522 |Errant |Earl | Monterey |CA|4088447676| G |80561 |Derringer |Dale | Los Angeles |CA|8182239014| G |80531 |Abott |Al | New York |NY|2025669892| G |80573 |Farmer |Fred | Miami |FL|3055527872|
G EXTRACT creates a list of clients. The above program example extracts I information from each record in the structure and displays each client's name and phone number.
J When INTOUCH does an extract, it examines each record of the structure in B order. If the KEY option is specified, only those records H with a key matching the KEY expression are examined. For each examined record, the following is done:
M INTOUCH then builds a list of extracted records. This list can be accessed D later with a FOR EACH loop. You can have up to 16 sort keys and 32 extract criteria.
INCLUDE cond_expr
5 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 8 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.
E cond_expr is a conditional expression that INTOUCH evaluates. * If the expression is TRUE, INTOUCH 8 includes the record in the extract list. If the = expression is FALSE, INTOUCH excludes the record from B the list. For example, the program above creates an extract list 5 containing only those clients located in California.
? NOTE: The conditional expression must match the field's F data type. For instance, if the field has a CHARACTER data type, the ) expression must be a string expression.
EXCLUDE cond_expr
5 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 8 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.
F cond_expr is a conditional expression. INTOUCH evaluates this 7 expression. If the expression is TRUE, INTOUCH ; excludes the current record from the extract list. J For example, the program above creates an extract list of all the clients A in the client structure---except those with an area code of 619.
? NOTE: The conditional expression must match the field's F data type. For instance, if the field has a CHARACTER data type, the ( expression must be a string expression.
. SORT [ASCENDING | DESCENDING] BY expr
5 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 8 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 7 in either ASCENDING or DESCENDING order. E expr is an expression whose value determines how to order the N list. INTOUCH evaluates the expression for each record and stores the value. L When all the records have been extracted, INTOUCH orders the list according to these values.
K You can sort in either ASCENDING or DESCENDING order. ASCENDING creates a K list in ascending order (lowest to highest). DESCENDING creates a list in H descending order (highest to lowest). The default is ascending order. ? String values are sorted according to the ASCII character set.
D INTOUCH does sorts in order. Therefore, you can use multiple sorts @ to order the list more and more specifically. For example, the E following program creates a list of clients. The clients are sorted 3 first by state and within each state by last name.
5 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 9 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
B When you sort fields that are filled with nulls (no data was ever I stored in them), the fields are sorted as though they were space filled.
4