Difference between revisions of "Using Navigational Data Access Commands in Recital"

Revision as of 12:36, 7 January 2010

Using Navigational Data Access Commands in Recital

Why use Navigational Data Access Commands

Creating a Recital Database

The create database command is used to create a new database. This will create a sub-directory in the DB_DATADIR directory and a database catalog with the same name as the database specified.

create database [if not exists] <database>

A database, its tables and other associated files can be physically deleted using the drop database command.

drop database [if exists] <database>

Example

create database test
drop database test

Opening a Recital Database

To open a database and set it as the default database for any subsequent operations, use the open database command.

open database <database> [EXCLUSIVE | SHARED] [NOUPDATE] [VALIDATE]

The open database command triggers the DBC_OPENDATA database event. If a dbc_opendata.prg program file exists in the database's directory, this will be run. If the dbc_opendata.prg program returns .F. (False), the open database operation will be abandoned.

Databases can also have an associated procedure library that is activated automatically when the database is opened. The procedure library must follow the naming convention dbc_<database>_library.prg and must exist in the database's directory. When the database is closed, the procedure library is also closed.

open database southwind

Creating a Recital Table

Tables can be created using the SQL create table statement. For full details, please see Using Recital SQL. The following alternatives are also supported.

The copy command can be used to copy all or part of an active table to a new table. The records to be copied can be restricted using while and for conditions.

copy to <filename> [<scope>] [fields <field list>] [while <condition>] [for <condition>] 

Example

use example order account_no
copy to new;
  rest;
  fields account_no, last_name;
  while account_no < "00050";
  for state = "MA"

The copy structure command allows the entire structure or selected fields from an existing active table to be copied to a new table.

copy structure to <tablename> [fields <field list>] 

Example

use example
copy structure to new;
  fields account_no, last_name

The copy structure extended command copies out all or part of a table's structure as the records of a new table. These records can then be modified, deleted or new records added as with any Recital table. The create from command uses such records as the source structure for a new table.

copy structure extended to <structure-tablename> [fields <field list>]

create <tablename> from <structure-tablename> 

Example

use example
copy structure extended to stru_example;
  fields account_no, last_name
// modify records in stru_example table as required
create new from stru_example

The create worksurface in the Recital Workbench provides a full screen form for creating new Recital tables.

create <tablename> 

Example

create new

Opening a Recital Table

• SELECT
• SELECT()
• USE

Navigating a Recital Table

• GOTO
• GOTO()
• INDEX
• REINDEX
• SEEK
• SEEK()
• SKIP

Inserting Records into a Recital Table

• APPEND FROM
• BLANK
• GATHER
• GENERATE
• REPLACE

Updating Records in a Recital Table

• GATHER
• REPLACE

Selecting Records from a Recital Table

• AVERAGE
• COUNT
• DISPLAY
• LIST
• SCAN
• SCATTER
• SUM
• TOTAL

Adding Records to a Recital Table

• APPEND
• APPEND BLANK
• INSERT

Deleting Records in a Recital Table

The deletion of records in a Recital table is a two stage process. The delete command is used to mark a record for deletion. At this stage, the marked record is hidden if set deleted is on. With set deleted set to off, the record is still accessible as normal. Record lists (list and display) will show the deletion marker * next to the record number and the deleted() function will return .T. (true). A record marked for deletion can have the mark removed using the recall command.

delete [<scope>][for | while <condition as logical>] 

recall [<scope>][for | while <condition as logical>] 

set deleted on | off 

set deleted off
use example
goto 10
delete
echo deleted() // .T.
recall
echo deleted() // .F.

To physically remove records marked for deletion, the pack command must be issued on the table. The table must be open for exclusive use.

use example exclusive
pack

The zap command can be used to physically delete all the records from a table immediately. The table must be open for exclusive use.

use example exclusive
zap

Obtain Information about a Recital Table

Table name, table status, cursor and alias information can be determined as follows:

• dbf() - return the name of the table in the current or specified cursor or with the specified alias

character = dbf([cursor as numeric | alias as character])

• inuse() - check if a specified table is open

logical = inuse(alias as character)

• used() - check if a table is open in the current or specified cursor or with the specified alias

logical = used([cursor as numeric | alias as character])

• alias() - return the table alias name for the current or specified cursor

character = alias([cursor as numeric])

• select() - return the cursor number for the current cursor or the specified alias

numeric = select([alias as character])

The current record pointer position is obtained as follows:

• recno() - return the record number of the active or specified table

numeric = recno([cursor as numeric | alias as character])

• bof() - evaluate if the record pointer is at the beginning of file marker

logical = bof([cursor as numeric | alias as character])

• eof() - evaluate if the record pointer is at the end of file marker

logical = eof([cursor as numeric | alias as character])

Recital supports a wide range of display and list commands giving information about Recital tables and their associated files and the environment generally. The display commands differ from list commands in character moded GUI environments, where they pause every screen until a key is pressed. You can cancel any further output at this point by pressing the escape key. When the output is sent to a file or printer, the pause is disabled.

• display / list - list records from the active and related tables

list [<scope>] [fields <field list>] [while <condition>] [for <condition>] [to file <filename> | print]

• display / list structure - list the structure of the active table

list structure [to file <filename> | print]

• display / list index - list details of the open indexes of the active table

list indexes [to file <filename> | print]

Further information about fields can be obtained with these functions:

• field() / fieldname() - return name of specified field in the current or specified cursor or with the specified alias

character = field[name](field as number[, cursor as numeric | alias as character]]

• FLDLIST() - return a comma-separated list of field names in the current or specified cursor or with the specified alias

character = fieldlist([cursor as numeric | alias as character]]