Difference between revisions of "Using Recital SQL"

Revision as of 15:12, 21 December 2009

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. If a program file with the name dbc_<database>_library.prg, exists in the database's directory, e.g. dbc_southwind_library.prg for the southwind demo database, a SET PROCEDURE...ADDITIVE is issued for this procedure library when the database is opened. When the database is closed, the procedure library is also closed.

Recital also supports the MySQL compatible [SQL USE|use]] statement to open a database.

use <database>

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

Summary