| Previous | Contents | Index |
OPEN TABLE table_name: NAME 'table_filename'
[, ACCESS INPUT | OUTIN] [, LOCK] [, DATAFILE filename]
[, OPTIMIZE OFF] [,CACHESIZE num]
|
| Example 15-3 OPEN TABLE |
|---|
open table cl: name 'sheerpower:samples\client', &
access input, lock
extract table cl
include cl(state) = 'CA'
exclude cl(phone)[1:3] = '619'
sort ascending by cl(last)
end extract
print 'List of California clients by last name'
for each cl
print cl(first); ' '; cl(last), cl(phone)
next cl
close table cl
end
List of California clients by last name
Dale Derringer (818) 223-9014
Earl Errant (408) 844-7676
|
The OPEN TABLE statement is used to open a data table. The table must be open in order to reference data in the table (i.e. change field data, add or delete records). table_name is a name (i.e. nickname) you assign to the table. You use this name to refer to the table within the program. The table name must be unique within the program or program system. If the table name is associated with another open table, an exception is generated. The table name must meet the requirements for variable names.
After the keyword NAME, is the file specification of the table file being opened. (See Chapter 16, Database Setup for information on legal table file names.) The file specification can be any valid string expression.
The ACCESS option tells Sheerpower whether to open the table for input (reading field data) or for input and output (reading, changing and adding field data). ACCESS input is the default open mode for table, meaning, that if no ACCESS option is provided, Sheerpower opens the table for INPUT.
When Sheerpower executes the OPEN statement, it searches for the table file specified. Sheerpower opens the table and assigns it the table name. If the table file does not exist, an exception is generated.
The ACCESS option determines how you can access the table.
The access options are:
The CACHESIZE option specifies how many megabytes (MB) of cache to allocate for this table. The range is from 1 to 2000 megabytes (2GB). If a default cachesize is not specified, Sheerpower attempts to select the optimal size for the cache.
The LOCK option causes Sheerpower to lock the table from write access by others. As long as the table is open, the table cannot be modified by others. This can speed up Sheerpower's table statements (EXTRACT statements especially). The particular effect depends on the file management system being used.
The DATAFILE option overrides the default datafile as specified by the table.
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.
CLOSE TABLE table_name
|
| Example 15-4 CLOSE TABLE |
|---|
open table cl: name 'sheerpower:samples\client'
extract table cl
include cl(state) = 'CA'
exclude cl(phone)[1:3] = '619'
sort ascending by cl(last)
end extract
print 'List of California Clients'
for each cl
print cl(first); ' '; cl(last), cl(phone)
next cl
close table cl
end
List of California Clients
Dale Derringer (818) 223-9014
Earl Errant (408) 844-7676
|
CLOSE TABLE closes a table from further access. Once the table is closed, you cannot reference records or add them, and you cannot change field data. table_name is the name associated with the table, the name assigned with the OPEN statement.
You can use the statement CLOSE ALL to close all open tables and other files. The END and STOP statements also close all open tables and files that the program was using.
ADD TABLE table_name
---
[LET] table_name(field) = expr
---
END ADD
|
| Example 15-5 ADD TABLE/END ADD - Add Table Record |
|---|
open table cl: name 'sheerpower:samples\client', access outin
input 'Enter ID number': id%
input 'Enter last name': last$
input 'Enter first name': first$
input 'Enter state': state$
input 'Enter phone': phone$
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
close table cl
end
Enter ID number? 12233
Enter last name? Jones
Enter first name? Tom
Enter state? NV
Enter phone? 2345556161
Adding Jones, Tom
|
ADD TABLE adds a record to a table. The ADD TABLE statement begins the add block. table_name is the name associated with the table that the record is going to be added to. END ADD marks the end of the block.
When ADD TABLE is executed, Sheerpower executes the block of code between the ADD TABLE 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. table_name(field) specifies a field in the table. expr is an expression. When Sheerpower 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
|
| Example 15-6 CANCEL ADD - Cancel Adding a Table Record |
|---|
open structure cl: name 'sheerpower:samples\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
end
Client ID? 14422
Last name? White
First name? exit
|
CANCEL ADD immediately transfers control to the next statement after the END ADD statement, without adding any of the current record to a table.
This statement can be used only within an ADD block---that is, between an ADD STRUCTURE and an END ADD pair of statements.
EXIT ADD
|
| Example 15-7 EXIT ADD - Exit When Adding a Table Record |
|---|
open table cl: name 'sheerpower:samples\client', access outin
add table 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'
end
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 ADDS the record to the table.
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.
| Previous | Next | Contents | Index |