Using Recital SQL

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 name>

Opening a Recital Database

• OPEN DATABASE
• USE

Creating a Recital Table using Recital SQL

• CREATE TABLE
• ALTER TABLE
• ALTER INDEX
• CREATE INDEX
• TABLE CONSTRAINTS
• COLUMN CONSTRAINTS

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