Build Generalized Database Utilities

Sheerpower gives developers full access to database table metadata. This makes it easy to write generalized utilities, table browsers, report generators, validation tools, and other programs that inspect tables dynamically at runtime.

The ASK TABLE statement is used to inspect a table. The SET TABLE statement is used to change a table's current state, identity, or position.

ASK TABLE Statement

The ASK TABLE statement retrieves information about an open table. It can return table-level information, such as the number of fields, and field-level information, such as field names, data types, descriptions, headings, masks, and key status.

ASK TABLE Syntax

ask table table_name: table_option variable ask table table_name, field field_expr: field_option variable

Table-level options ask about the table as a whole. Field-level options ask about a specific field in the table.

A field can be specified by field number, field name, or another valid field expression.

ASK TABLE Table-Level Options

Option	Returns	Description
`fields`	Numeric	Returns the number of fields in the table.
`id`	String	Returns a table ID that can later be used with `SET TABLE table_name: ID str_expr`.

ASK TABLE Field-Level Options

Option	Returns	Description
`name`	String	Returns the name of the specified field. If the field expression is invalid, an empty string is returned.
`number`	Numeric	Returns the field number of the specified field. Fields are numbered sequentially. If the field does not exist, Sheerpower returns `0`. This is a simple way to validate that a field expression is valid.
`datatype`	String	Returns the field data type, such as `CH` for character or `IN` for integer.
`attributes`	String	Returns the Sheerpower field semantics for the specified field, such as `NUM` for number or `UC` for upper-case.
`length`	Numeric	Returns the length of the specified field.
`position`	Numeric	Returns the starting position of the specified field.
`description`	String	Returns the description for the specified field.
`prompt`	String	Returns the prompt for the specified field.
`heading`	String	Returns the report column heading for the specified field.
`printmask`	String	Returns the print mask for the specified field.
`screenmask`	String	Returns the screen mask for the specified field.
`help`	String	Returns the help text for the specified field.
`keyed`	Numeric Boolean	Returns `TRUE` if the specified field is a key field. Otherwise, returns `FALSE`.
`changeable`	Numeric Boolean	Returns `TRUE` if the specified field can be changed. Otherwise, returns `FALSE`.

ASK TABLE Example

open table cl: name 'sheerpower:samples\client' ask table cl: fields num_fields for i = 1 to num_fields clear ask table cl, field #i: name field_name$ ask table cl, field #i: number field_number ask table cl, field #i: datatype datatype$ ask table cl, field #i: attributes attributes$ ask table cl, field #i: description description$ ask table cl, field #i: prompt prompt$ ask table cl, field #i: position position ask table cl, field #i: length field_length ask table cl, field #i: heading heading$ ask table cl, field #i: printmask printmask$ ask table cl, field #i: screenmask screenmask$ ask table cl, field #i: help help_text$ ask table cl, field #i: keyed keyed ask table cl, field #i: changeable changeable print at 5, 5: '' print 'Name : '; field_name$ print 'Number : '; field_number print 'Data type : '; datatype$ print 'Attributes : '; attributes$ print 'Description : '; description$ print 'Prompt : '; prompt$ print 'Position : '; position print 'Field length : '; field_length print 'Rpt. heading : '; heading$ print 'Print mask : '; printmask$ print 'Screen mask : '; screenmask$ print 'Help : '; help_text$ print 'Keyed : '; keyed print 'Changeable : '; changeable delay next i close table cl end

This example opens the sample client table, asks how many fields it contains, and then loops through each field to display its metadata.

SET TABLE Statement

The SET TABLE statement changes the current state, identity, or position of an open table. It can find a record from a partial key, attach a table variable to a stored table ID, move to a specific extracted record, or reset the extracted-record count.

SET TABLE Syntax

set table table_name, field field_expr: partial key str_expr set table table_name: id str_expr set table table_name: pointer num_expr set table table_name: extracted 0

SET TABLE Options

Option	Description
`field field_expr: partial key str_expr`	Retrieves the first record matching the partial key in `str_expr`.
`id str_expr`	Sets a table to a table ID that was previously stored in a string variable using `ASK TABLE: ID`. This allows generalized routines to work with a table whose identity is not known until runtime.
`pointer num_expr`	Sets the table to the nth record in the current extracted collection. If the pointer is valid, `_EXTRACTED` is set to `1`. Otherwise, `_EXTRACTED` is set to `0`.
`extracted 0`	Resets the extracted-record count to zero so a new collection can be started. This is used with `EXTRACT TABLE table_name: APPEND`.

SET TABLE, FIELD: PARTIAL KEY

PARTIAL KEY retrieves the first record matching the partial key in str_expr.

This is useful when the user enters only the beginning of a keyed value, such as the first few letters of a last name.

Example

open table cl: name 'sheerpower:samples\client' input input 'Name': name$ set table cl, field last: partial key name$ print cl(id); ' '; cl(last) end

Sample Run

Name? D 80561 Derringer

SET TABLE: ID

SET TABLE: ID attaches a table variable to a table ID that was previously retrieved with ASK TABLE: ID.

This is useful for generalized routines because the routine can work with a table passed to it at runtime, instead of requiring the table name to be hard-coded.

Example

declare table str open table cl: name 'sheerpower:samples\client' ask table cl: id cl_id$ set table str: id cl_id$ extract table str end extract for each str print str(#1); ' '; str(#2) next str end

Sample Output

20000 Smith 20001 Jones 20002 Kent 23422 Johnson 32001 Waters 43223 Errant 80542 Brock 80543 Cass 80544 Porter 80561 Derringer 80573 Farmer

SET TABLE: POINTER

SET TABLE: POINTER sets the table to the nth record in the current extracted collection.

This is useful after an EXTRACT TABLE operation because it provides random access to any record in the extracted set.

If the pointer value is valid, _EXTRACTED is set to 1. If there are no records extracted, or if the number is out of range, no error message is generated and _EXTRACTED is set to 0.

Example

open table cl: name 'sheerpower:samples\client' extract table cl end extract set table cl: pointer 3 print cl(id); ' '; cl(last) end

Sample Output

23422 Johnson

SET TABLE: EXTRACTED 0

SET TABLE: EXTRACTED 0 sets the number of extracted records to zero. This causes a new extracted collection to be started.

This statement is used with EXTRACT TABLE table_name: APPEND.

Example

open table vend: name 'sheerpower:samples\vendor' set table vend: extracted 0 end

Problem: Generalized table utilities often need to work with tables whose names, fields, keys, and current records are not known until runtime.

Solution: ASK TABLE lets a program inspect table metadata, while SET TABLE lets the program reposition, reassign, or reset table access at runtime.

Efficiency: One routine can work across many tables without hard-coding table layouts, duplicating table-access logic, or adding extra abstraction layers.

Design insight: In many environments, generalized routines require separate database catalog queries, object reflection, ORM metadata, validation rules, and user-interface metadata. Sheerpower brings much of that information directly into the table system itself.

Takeaway: ASK TABLE answers questions about the table. SET TABLE controls how the program is attached to, positioned within, or collecting records from the table. Together, they make Sheerpower tables self-describing and dynamically accessible.

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.