K.1 Integrated Database Access

One of the major features of SheerPower is its ability to perform database operations integrated into the language.

Contrasting Integrated Database Access with In-Memory Cluster Arrays

1. Purpose and Use Cases

Integrated Database Access:
Purpose: Designed for handling persistent data stored in external database tables. Ideal for applications that require robust data management, long-term storage, and complex queries across large datasets.
Use Cases: Suitable for business applications like managing payroll, customer information, or inventory data.
In-Memory Cluster Arrays:
Purpose: Acts as an in-memory data structure for managing and processing data temporarily during runtime. Optimized for speed and ideal for situations where data doesn't need to be persisted long-term.
Use Cases: Best for scenarios requiring high-performance, short-term data manipulation, like caching or real-time data processing.

2. Data Persistence

Integrated Database Access:
Data Persistence: Data is stored on disk, ensuring it persists across sessions and provides a long-term record that can be accessed, updated, and maintained over time.
Example: A payroll table storing employee salary details, which must be retained, updated, and potentially accessed by multiple users simultaneously. SheerPower supports full concurrency during database operations, allowing multiple users to interact with the data without conflicts.
In-Memory Cluster Arrays:
Data Persistence: Data is stored in memory, meaning it's volatile and typically lost when the application terminates unless explicitly writen to a file.
Example: A temporary list of active sessions during runtime.

3. Scalability

Integrated Database Access:
Scalability: Capable of handling large datasets stored on disk, with efficient indexing and querying mechanisms that allow for smooth performance even with extensive data volumes.
Example: Managing hundreds of millions of customer transactions in a CRM system, where the database can efficiently store, query, and process data across vast records.
In-Memory Cluster Arrays:
Scalability: Limited by the available system memory, making them ideal for smaller datasets (typically under a few million records) where ultra-fast access is crucial.
Example: Processing a list of recent transactions in a financial calculation module, where quick in-memory operations are prioritized over large-scale data handling.

4. Performance

Integrated Database Access:
Performance: Optimized for querying and retrieving data from large, persistent datasets, but involves disk I/O.
Example: Querying and sorting records from a product catalog.
In-Memory Cluster Arrays:
Performance: Extremely fast due to in-memory operations, ideal for real-time processing.
Example: Quickly sorting and filtering a list of user interactions.

5. Data Access and Manipulation

Integrated Database Access:
Data Access: Supports complex queries with filtering, sorting, and key lookups directly on persistent data.
Example: Extracting and displaying all customers in a specific region with filters applied.
In-Memory Cluster Arrays:
Data Access: Direct manipulation of data held in memory, with fast access to fields within each cluster.
Example: Iterating over and manipulating a list of active users in a chat application.

6. Security

Integrated Database Access:
Security: Prevents SQL injection attacks by separating data from functions. Data can be encrypted or managed with access controls.
Example: Securely storing and retrieving sensitive customer information.
In-Memory Cluster Arrays:
Security: While less exposed to risks, in-memory data is more volatile and typically not secured unless additional measures are taken.
Example: Temporarily storing session data.

7. Usage in Application Workflow

Integrated Database Access:
Usage: Typically used for operations that require consistent data retrieval and storage.
Example: Loading user profiles from a database when logging into an application.
In-Memory Cluster Arrays:
Usage: Best for temporary, high-speed data processing during runtime.
Example: Storing temporary calculations during a session.

Understanding these differences will help developers choose the appropriate data management strategy based on the needs of their application.

To access the payroll table in a database, you enter the following:

open table payroll: name '@..\data\payroll'

This opens the payroll table with a reference name of PAYROLL. The table is found in the data folder.
To print the salary field in a payroll table, you would give the name of the table and the name of the field:

print payroll(salary)

The EXTRACT/END EXTRACT block iterates through each row of a table. While doing so, it creates a COLLECTION of records. A collection can be a subset of the entire table using INCLUDE and EXCLUDE statements, and can be sorted by various criteria. To iterate through the collection, use the FOR/NEXT statements. When the extract has completed, after the END EXTRACT statement, the special variable _extracted will contain the number of records in the collection. A value of zero means that no records were extracted.

extract table products include products(in_stock) > 0 sort by products(product_code) end extract print 'Products found: '; _extracted for each products print products(product_code); " - "; products(name) next products

Key Lookup Methods in SheerPower

SheerPower provides three main methods for key lookups in tables:

Exact Key Lookup:
- Uses the set table statement to specify an exact key value for retrieval.
- Syntax:
  set table table_name, field key_field: key key_value
- Example:
  set table customers, field customer_id: key 'C123' if _extracted > 0 then print "Customer found: "; customers(name) else print "Customer not found." end if
Exact Key Lookup to Find All Duplicates:
When the extract has completed, after the end extract statement, the special variable _extracted will contain the number of records in the collection.
- Uses the extract table statement with the key clause to specify a key value for retrieval.
- Syntax:
  extract table table_name, field key_field: key key_value$
- Example:
  extract table products, field product_code: key 'A1'
    include products(in_stock) > 0
    sort by products(product_code)
  end extract
  for each products
    print products(product_code); " - "; products(name)
  next products
Partial Key Lookup:
- Uses the extract table statement with the partial key clause to specify a partial key value for retrieval.
- Syntax:
  extract table table_name, field key_field: partial key partial_key_value$
- Example:
  extract table products, field product_code: partial key 'A1'
    include products(in_stock) > 0
    sort by products(product_code)
  end extract
  for each products
    print products(product_code); " - "; products(name)
  next products
Key Range Lookup:
- Uses the extract table statement with the from ... to ... clause to specify a range of key values for retrieval.
- Syntax:
  extract table table_name, field key_field: from start_key_value$ to end_key_value$
- Example:
  extract table orders, field order_date: from '2023-01-01' to '2023-12-31'
  sort by orders(order_date)
  end extract
  for each orders
  print orders(order_date); " - "; orders(total)
  next orders

Note: Unlike SQL and many other query languages, SheerPower is careful to separate data from functions. This makes it impossible for hackers to launch SQL INJECTION ATTACKS against a website that uses SheerPower on the back end.

In order to access a specific record in a table, it must be current. A successful key lookup causes a record to be current. Within an extract/end extract block, each record becomes current. Within a for each/next block, each record also becomes current.

In general, table records are first collected using extract/end extract and then iterated through using for each/next. This is done as two steps in order for collected records to be optionally sorted.

After any operation that could make a record current, the internal variable _extracted will be zero if no record was found or will reflect the number of records found.

An extract/end extract block can contain:

SheerPower Extract Block with Examples

exclude nn

The exclude nn statement is used to exclude records from the extract block that match the logical expression nn.

Example:

extract table employees exclude employees(age) < 18 exclude employees(status) = 'inactive' end extract for each employees print employees(name); " - "; employees(age) next employees

This example excludes employees who are under 18 years old and those who have an inactive status from the extract block.

include nn

The include nn statement is used to include only those records that match the logical expression nn.

Example:

extract table sales include sales(region) = 'North' include sales(amount) > 1000 end extract for each sales print sales(id); " - "; sales(region); " - $"; sales(amount) next sales

This example includes only sales records from the North region with an amount greater than $1000.

exit extract

The exit extract statement is used to exit the extract block and with the collected records up to that point.

Example:

extract table orders if orders(date) < '2023-01-01' then exit extract end if end extract for each orders print orders(id); " - "; orders(date) next orders

This example exits the extract block if an order date is before January 1, 2023, and processes the collected records.

cancel extract

The cancel extract statement is used to cancel the entire extract block without collecting any records.

Example:

extract table inventory if inventory(quantity) = 0 then cancel extract end if end extract for each inventory print inventory(item); " - "; inventory(quantity) next inventory

This example cancels the extract block if any item has a quantity of 0, resulting in no records being processed.

sort by xxx

The sort by xxx statement is used to sort the records in ascending order based on the expression xxx.

Example:

extract table customers sort by customers(last_name) end extract for each customers print customers(last_name); ", "; customers(first_name) next customers

This example sorts the customer records by their last names in ascending order.

sort descending by xxx

The sort descending by xxx statement is used to sort the records in descending order based on the expression xxx.

Example:

extract table products sort descending by products(price) end extract for each products print products(name); " - $"; products(price) next products

This example sorts the product records by their prices in descending order.

Explanation

The extract table block creates a collection of table records. When the EXTRACT BLOCK is exited, the special variable _extracted contains the number of records in the most recent extract. Typically after an extract block is a FOR EACH block that iterates through the collection.

open table client : name '@..\safe\client' open table detail: name '@..\safe\detail' extract table client end extract print 'Records found: '; _extracted print if len(a$) > 0 then my_id$ = a$ // try entering 80561 for a$ else my_id$ = '80522' end if print 'Look up this ID: '; my_id$ set table client, field id: key my_id$ if _extracted = 0 then print 'Unknown ID: '; my_id$ else print client(last); ' '; client(first) end if print print 'Clients in California, but excluding those from area code 619' extract table client include client(state) = 'CA' exclude client(phone)[1:3] = '619' sort ascending by client(last) end extract for each client print client(id);' '; client(first); ' '; client(last); ' '; client(phone) next client print print 'Clients excluding those from area code 619' extract table client exclude client(phone)[1:3] = '619' end extract for each client print client(first); ' '; client(last);' ';client(phone) next client print print 'Major and minor sorting' extract table client sort ascending by client(state) sort ascending by client(last) end extract for each client print client(last); ', '; client(first); ' '; client(state) next client print print 'List of clients with last name starting with an R' extract table client, field last: partial key 'R' end extract print for each client print client(first); ' '; client(last) next client print print 'Re-extract from an already extracted table. Perhaps to do some sorting, etc.' extract table client include client(state) = 'CA' end extract reextract table client exclude client(phone)[1:3] <> '619' sort ascending by client(last) end extract print 'List of California Clients in Area Code 619' for each client print client(first); ' '; client(last); ' ';client(phone) next client print set table detail: extracted 0 extract table detail, field lineid : & key '10301001' to '10302000', append sort by detail(prodnbr) sort by detail(invnbr) end extract extract table detail, field lineid : & key '10311001' to '10312000', append sort by detail(prodnbr) sort by detail(invnbr) end extract print 'Prod'; tab(7); 'Line ID'; tab(17); 'Quantity' for each detail print detail(prodnbr); tab(7); detail(lineid); & tab(17); detail(qty) next detail

Code Explanation:

1. Open Tables:

These lines open two tables named client and detail from the specified file paths (@..\safe\client and @..\safe\detail). The tables are made available for further operations like extracting records, querying, and sorting.

2. Extract All Records:

This block extracts all records from the client table. The _extracted variable holds the number of records extracted, which is then printed to inform the user how many records were found.

3. Set ID Based on Input:

This conditional block checks if the string a$ has a length greater than 0 (i.e., it is not empty). If a$ is not empty, my_id$ is set to the value of a$; otherwise, my_id$ is set to the default ID '80522'.

4. Lookup Record by ID:

This block sets the client table to look for a record with an id matching my_id$. The _extracted variable will hold the number of matching records (either 0 or 1).

5. Print Client Information:

If no record was found (_extracted = 0), the program prints "Unknown ID". Otherwise, it prints the client's last and first names.

6. Extract Clients from California (Excluding Area Code 619):

This block extracts records from the client table where the state is 'CA', excluding those whose phone number starts with '619'. The results are sorted in ascending order by the last name.

7. Print Extracted Clients:

This loop iterates over each extracted client and prints their ID, first name, last name, and phone number.

8. Extract Clients Excluding Area Code 619:

This block extracts records from the client table, excluding those whose phone number starts with '619'. No sorting is specified.

9. Print Extracted Clients:

This loop iterates over each extracted client and prints their first name, last name, and phone number.

10. Sort Clients by State and Last Name:

This block extracts and sorts clients first by state (major sort) and then by last name (minor sort).

11. Print Sorted Clients:

This loop iterates over each sorted client and prints their last name, first name, and state.

12. Extract Clients with Last Name Starting with 'R':

This block extracts records from the client table where the last name starts with the letter 'R'.

13. Print Clients with Last Name Starting with 'R':

This loop iterates over each client whose last name starts with 'R' and prints their first and last names.

14. Re-extract Clients from California:

This block extracts records from the client table where the state is 'CA'. The records are held for further re-extraction.

15. Re-extract and Sort Clients in Area Code 619:

This block further filters the already extracted records, keeping only those whose phone number starts with '619', and sorts them by last name.

16. Print Sorted California Clients in Area Code 619:

This loop iterates over the re-extracted records and prints the first name, last name, and phone number of clients in California with the '619' area code.

17. Extract Details with Specific Line IDs (First Range):

This block clears previous extractions (extracted 0) and extracts records from the detail table with lineid between '10301001' and '10302000'. The results are sorted by prodnbr and invnbr.

18. Extract Details with Specific Line IDs (Second Range):

This block appends to the previous extraction, extracting records with lineid between '10311001' and '10312000'. The results are sorted similarly by prodnbr and invnbr.

19. Print Header for Details:

Prints a header for the detail records, aligning columns for Prod, Line ID, and Quantity using tab() for spacing.

20. Print Extracted Details:

This loop iterates over each extracted detail record and prints the product number, line ID, and quantity, aligned under the header.

Closing Tables in SheerPower

In SheerPower, after you've finished working with a table, it's important to properly close it to free up system resources and ensure that all data operations are completed correctly. The correct syntax for closing a table is:

close table tablename

Example Usage

Opening a Table:
First, you open the table using the open table command.

open table payroll: name '@..\data\payroll'
Performing Operations:
You can perform various operations like extracting, filtering, sorting, and iterating over records.

extract table payroll include payroll(salary) >= 100000 sort descending by payroll(salary) end extract for each payroll print payroll(name); ", "; payroll(salary) next payroll
Closing the Table:
Once you've completed your operations, use the close table statement to close the table.

close table payroll

Why Close Tables?

Closing tables ensures that:

All changes to the table are saved.
System resources are freed up.
The program avoids potential issues related to open file handles or data locks.

Using close table tablename is the recommended practice to maintain the integrity and efficiency of your SheerPower applications.

Hide Description

a$ b$ x y

Enter or modify the code below, and then click on RUN

Looking for the full power of Sheerpower?
Check out the Sheerpower website. Free to download. Free to use.