| Previous | Contents | Index |
DELETE TABLE table_name
|
| Example 15-8 DELETE TABLE - Delete 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)
input 'State ': cl(state)
end add
print 'Client added'
delay
extract table cl
include cl(state) = 'PA'
end extract
// delete all clients from Pennsylvania
for each cl
print 'Deleting '; cl(first); ' '; cl(last) ;'...';
delete table cl
print 'record deleted'
next cl
close table cl
end
Client ID ? 87433
Last name ? Shores
First name? Sandy
Contact ? 8435557373
State ? PA
Client added
Deleting Sandy Shores...record deleted
|
DELETE TABLE deletes the current record from the specified table. table_name is the table name associated with the table that the record is going to be deleted from.
[LOCK | UNLOCK] TABLE table_name
|
| Example 15-9 LOCK/UNLOCK TABLE |
|---|
open table cl: name 'sheerpower:samples\client', access outin
extract table cl
include cl(state) = 'CA'
end extract
for each cl
print
print cl(first); ' '; cl(last)
lock table cl // give us exclusive access
line input default cl(phone), prompt 'Enter new phone ': phone$
if _exit then exit for
cl(phone) = phone$
unlock table cl // put the record out to disk
// and release it
next cl
close table cl
end
Keith Kent
Enter new phone 6199675021
Paul Johnson
Enter new phone EXIT
|
LOCK/UNLOCK TABLE can be used to lock or unlock the data record currently being accessed. Sheerpower automatically locks and unlocks data when you work with it. LOCK and UNLOCK override Sheerpower's automatic locking features allowing you to do it manually.
LOCK TABLE 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 TABLE unlocks the current record or data. The record is put to disk (if needed) and can again be accessed by others.
table_name is the name associated with the table.
[UNLOCK] TABLE table_name: COMMIT
[UNLOCK] ALL: COMMIT
|
| Example 15-10 UNLOCK TABLE: COMMIT |
|---|
unlock table payroll: commit |
| Example 15-11 UNLOCK ALL: COMMIT |
|---|
unlock all: commit |
COMMIT tells both ARS and the underlying operating system to write all cached data to disk. Using UNLOCK ALL with COMMIT causes the data on all tables in the program to be written to disk.
EXTRACT TABLE table_name
--- block of code
[INCLUDE | EXCLUDE] cond_expr
[SORT [ASCENDING | DESCENDING] BY expression]
---
END EXTRACT
|
| Example 15-12 EXTRACT TABLE - Extracting Records from a Structure |
|---|
open table cl: name 'sheerpower:samples\client'
print 'List of Clients'
print
extract table cl
print cl(first); ' '; cl(last), cl(phone)
end extract
close table cl
end
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 Sheerpower does an extract, it examines each record in the structure. For each record, Sheerpower executes the code between the EXTRACT TABLE and END EXTRACT statements. For instance, here is a table containing 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 table and displays each client's name and phone number.
When Sheerpower does an extract, it examines each record of the table 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:
Sheerpower 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
|
| Example 15-13 INCLUDE Within EXTRACT TABLE |
|---|
open table cl: name 'sheerpower:samples\client'
extract table 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 table cl
end
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 Sheerpower evaluates. If the expression is TRUE, Sheerpower includes the record in the extract list. If the expression is FALSE, Sheerpower 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
|
| Example 15-14 EXCLUDE Within EXTRACT TABLE |
|---|
open table cl: name 'sheerpower:samples\client'
extract table cl
exclude cl(phone)[1:3] = '619'
end extract
print 'List of Clients'
print
for each cl
print cl(first); ' '; cl(last), cl(phone)
next cl
close table cl
end
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
|
| Previous | Next | Contents | Index |