Difference between revisions of "Using Recital SQL"

From Recital Documentation Wiki
Jump to: navigation, search
(→‎Opening a Recital Database)
(→‎Creating a Recital Table using Recital SQL)
Line 39: Line 39:
 
* ALTER INDEX
 
* ALTER INDEX
 
* CREATE INDEX
 
* CREATE INDEX
−
* TABLE CONSTRAINTS
+
====Table Constraints====
−
* COLUMN CONSTRAINTS
+
 
 +
 
 +
{| class="wikitable"
 +
|[[CHECK Table Constraint|CHECK]]||[[ERROR Table Constraint|ERROR]]||[[FOREIGN KEY Table Constraint|FOREIGN KEY]]
 +
|-
 +
|[[INDEX Table Constraint|INDEX]]||[[KEY Table Constraint|KEY]]||[[ONCLOSE Table Constraint|ONCLOSE]]
 +
|-
 +
|[[ONDELETE Table Constraint|ONDELETE]]||[[ONINSERT Table Constraint|ONINSERT]]||[[ONOPEN Table Constraint|ONOPEN]]
 +
|-
 +
|[[ONROLLBACK Table Constraint|ONROLLBACK]]||[[ONUPDATE Table Constraint|ONUPDATE]]||[[PRIMARY KEY Table Constraint|PRIMARY KEY]]
 +
|-
 +
|[[UNIQUE Table Constraint|UNIQUE]]||||
 +
|-
 +
|}
 +
 
 +
 
 +
====Column Constraints====
 +
 
 +
 
 +
{| class="wikitable"
 +
|[[AUTO_INCREMENT Column Constraint|AUTO_INCREMENT]]||[[AUTOINC Column Constraint|AUTOINC]]||[[CALCULATED Column Constraint|CALCULATED]]
 +
|-
 +
|[[CHECK Column Constraint|CHECK]]||[[DEFAULT Column Constraint|DEFAULT]]||[[DESCRIPTION Column Constraint|DESCRIPTION]]
 +
|-
 +
|[[ERROR Column Constraint|ERROR]]||[[FOREIGN KEY Column Constraint|FOREIGN KEY]]||[[NOCPTRANS Column Constraint|NOCPTRANS]]
 +
|-
 +
|[[NOT NULL Column Constraint|NOT NULL]]||[[NULL Column Constraint|NULL]]||[[PRIMARY KEY Column Constraint|PRIMARY KEY]]
 +
|-
 +
|[[RANGE Column Constraint|RANGE]]||[[RECALCULATE Column Constraint|RECALCULATE]]||[[REFERENCES Column Constraint|REFERENCES]]
 +
|-
 +
|[[SET CHECK Column Constraint|SET CHECK]]||[[UNIQUE Column Constraint|UNIQUE]]||
 +
|-
 +
|}
  
 
===Inserting Records into a Recital Table using Recital SQL===
 
===Inserting Records into a Recital Table using Recital SQL===

Revision as of 10:59, 22 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. 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

  • CREATE TABLE
  • ALTER TABLE
  • ALTER INDEX
  • CREATE INDEX

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

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

Retrieved from "https://www.recitalsoftware.com/wiki/index.php?title=Using_Recital_SQL&oldid=11887"