Using Recital SQL
Contents
- 1 Using Recital SQL
- 1.1 Creating a Recital Database
- 1.2 Opening a Recital Database
- 1.3 Creating a Recital Table using Recital SQL
- 1.4 Inserting Records into a Recital Table using Recital SQL
- 1.5 Selecting Records from a Recital Table Using Recital SQL
- 1.6 Updating Records in a Recital Table using Recital SQL
- 1.7 Deleting Records in a Recital Table using Recital SQL
- 1.8 Obtain Information about a Recital Table using SQL
- 1.9 Executing Procedures and Prepared Statements using Recital SQL
- 1.10 Summary
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
