Using Recital SQL

Creating a Recital Database

The create database statement 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 statement.

drop database [if exists] <database>

Opening a Recital Database

To open a database and set it as the default database for any subsequent SQL statements, use the open database statement.

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.

Recital also supports the MySQL compatible use statement to open a database.

use <database>

Creating a Recital Table using Recital SQL

The create table statement creates a new table in the current database, or a new free table if no database is open.

Table column names, data types and constraints can be defined. Table constraints can also be specified.

create table [if not exists] <table>
  (<column> <datatype> [<column constraint> [...]][,...] [<table constraint> [...]])

Data Types

Recital supports the following data types:

BIGINT
BIT
CHAR
CURRENCY
DATE
DATETIME
DECIMAL
DOUBLE
FLOAT
GENERAL
INTEGER
LOGICAL
LONG VARCHAR
LONG VARBINARY
MEDIUMINT
MEMO
NUMERIC
OBJECT
REAL
SHORT
SMALLINT
TEXT
TIME
TIMESTAMP
TINYINT
VARCHAR

Table Constraints

CHECK	ERROR	FOREIGN KEY
INDEX	KEY	ONCLOSE
ONDELETE	ONINSERT	ONOPEN
ONROLLBACK	ONUPDATE	PRIMARY KEY
UNIQUE

Column Constraints

AUTO_INCREMENT	AUTOINC	CALCULATED
CHECK	DEFAULT	DESCRIPTION
ERROR	FOREIGN KEY	NOCPTRANS
NOT NULL	NULL	PRIMARY KEY
RANGE	RECALCULATE	REFERENCES
SET CHECK	UNIQUE

Example

CREATE TABLE customer;
  (account_no CHAR(5) DESCRIPTION  "Account Code";
  DEFAULT strzero(seqno(),5),;
  title CHAR(3) DESCRIPTION "Personal Title",
  last_name CHAR(16) DESCRIPTION "Customer's Last Name",;
  first_name CHAR(10) DESCRIPTION "Customer's Given Name",;
  initial CHAR(2) DESCRIPTION "Customer's Middle Initial",;
  street CHAR(25) DESCRIPTION "Street Number and Name",;
  city CHAR(12) DESCRIPTION "City",;
  state CHAR(2) DESCRIPTION "State Abbreviation";
  CHECK rlookup(customer.state,state);
  ERROR "Invalid State",;
  zip CHAR(10) DESCRIPTION "Zip Code",;
  limit DECIMAL(11,2) DESCRIPTION "Credit Limit";
  RECALCULATE,;
  balance DECIMAL(11,2) DESCRIPTION "Credit Balance";
  RECALCULATE,;
  available DECIMAL(11,2) DESCRIPTION "Credit Available";
  CALCULATED limit-balance,;
  notes LONG VARCHAR DESCRIPTION "Customer Notes",;
  start_date DATE DESCRIPTION "Customer Start Date";
  DEFAULT date(),;
  ONOPEN "customer")

Loading Metadata from an Array or XML File

The create table statement also allows a new table to be created from metadata stored in an array or an XML file. In addition, data can optionally be loaded from the specified XML file.

create table [if not exists] <table>
  [from] xml <.xml file> [load] | from array <array>

Example

// Create table from XML file
SELECT * FROM customer;
  SAVE AS XML cust.xml
 
CREATE TABLE customer2;
  FROM XML cust.xml
 
// Create table from array
CREATE TABLE newtable FROM ARRAY meta1

Modifying a Table

The alter table statement allows columns in an existing table to be added, modified, renamed or dropped. Both table and column constraints may be added or dropped.

Add column(s), column constraint(s) or table constraint

alter table <table> add [column] (<column> <datatype> [<column constraints>] [,...])
  | <table constraint>

Alter column(s), column constraint(s) or table constraint

alter table <table> alter [column] <column> [set default <value> | drop default]
  | (<column> <datatype> [<column constraint>] [,...])
  | constraint (<column> set <column constraint> <value> [,...])
  | <table constraint>

Drop column(s), column constraint(s) or table constraint

alter table <table> drop [column] <column>
  | (<column> [,...])
  | constraint (<column> <column constraint> [,...]) | <table constraint>

Add check table constraint

alter table <table> set check <condition> [error <message>]

Rename column

alter table <table> rename (<column>,<new column>)

Creating an Index

Indexes may be created using the column and table constraints (foreign key, primary key, unique) and the create table or alter table statements or with the create index statement.

create [unique] index <index> on <table> (<column> [asc | desc] [,...])
create [unique] index <index> on <table> (<expression> [asc | desc])

Altering an Index

Existing indexes may be altered using the alter index statement.

alter index <index> on <table>

Inserting Records into a Recital Table using Recital SQL

INSERT

Selecting Records from a Recital Table Using Recital SQL

SELECT
DECLARE CURSOR
OPEN
FETCH
DROP CURSOR

Updating Records in a Recital Table using Recital SQL

UPDATE
BEGIN...END TRANSACTION
COMMIT
ROLLBACK

Deleting Records in a Recital Table using Recital SQL

DELETE

Obtain Information about a Recital Table using SQL

SYSBESTROWIDENTIFIER Description of a table’s optimal set of columns that uniquely identifies a row
SYSCOLUMNCONSTRAINTS Description of the constraints for a table’s columns
SYSCOLUMNPRIVILEGES Description of the access rights for a table’s columns
SYSCOLUMNS Description of the table columns available in the catalog
SYSCROSSREFERENCE Description of how one table imports the keys of another table
SYSEXPORTEDKEYS Description of the foreign key columns that reference the primary key columns
SYSIMPORTEDKEYS Description of the primary key columns that are referenced by the foreign key
SYSINDEXINFO Description of a table’s indices and statistics
SYSPRIMARYKEYS Description of the primary key columns in the table
SYSTABLECONSTRAINTS Description of the constraints for each table available in the catalog
SYSTABLEPRIVILEGES Description of the access rights for each table available in the catalog
SYSTABLES Description of the tables available in the catalog
SYSTABLETYPES Table types available in the database system
SYSVERSIONCOLUMNS Description of the columns in a table that are automatically updated when any row is updated

Executing Procedures and Prepared Statements using Recital SQL

CREATE PROCEDURE
DROP PROCEDURE
EXECUTE
EXECUTE IMMEDIATE
PREPARE
SYSRESULTSET Used to return the singleton result from any Recital expression

Using Recital SQL

Contents