Using Recital SQL

From Recital Documentation Wiki
Revision as of 15:12, 21 December 2009 by Yvonnemilne (Talk | contribs)

Jump to: navigation, search

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