Difference between revisions of "OPEN DATABASE"

From Recital Documentation Wiki
Jump to: navigation, search
(Class)
Line 1: Line 1:
==Class==
 
Databases
 
  
  

Revision as of 15:02, 23 October 2009


Purpose

Sets the specified database as the default database for subsequent operations or SQL queries


Syntax

OPEN DATABASE [<database name> | ? [EXCLUSIVE | SHARED] [NOUPDATE] [VALIDATE]]


See Also

ADATABASES(), ADD TABLE, ALTER INDEX, ALTER TABLE, BACKUP DATABASE, CLOSE DATABASES, CLOSE TABLES, COMPILE DATABASE, CREATE DATABASE, CREATE TABLE, CREATE INDEX, CREATE VIEW, DATABASE EVENTS, DATABASE(), DB_DATADIR, DB_MAXWKA, DBUSED(), DISPLAY DATABASE, DISPLAY INDEXES, DISPLAY SCHEMAS, DISPLAY TABLES, DROP DATABASE, DROP INDEX, DROP TABLE, GETENV(), LIST DATABASE, LIST INDEXES, LIST SCHEMAS, LIST TABLES, LOCK TABLE, PACK DATABASE, REBUILD DATABASE, REINDEX DATABASE, RESTORE DATABASE, SET AUTOCATALOG, SET EXCLUSIVE, USE


Description

The OPEN DATABASE command sets the specified database, <database name> as the default database for subsequent operations or SQL queries. The database remains current until the end of the session or until the CLOSE DATABASES or another OPEN DATABASE command is issued. Tables from other databases can still be accessed, but must be indicated by including the database name in the table reference, database!table.

Databases in Recital are implemented as directories containing files that correspond to the tables and associated files in the database. Operating System file protection can be applied individually to the files for added security. The directories are sub-directories of the Recital data directory. The environment variable / symbol DB_DATADIR points to the current Recital data directory and can be queried using the GETENV() function. Files from other directories can be added to the database using the ADD TABLE command or via the database catalog and SET AUTOCATALOG functionality.

If the <database name> is omitted, a prompt will be displayed to enter the name of the database to be opened. If the question mark, '?', is included instead of the <database name>, the 'SELECT A FILE' dialog will be displayed, allowing the user to select the database to be opened. The dialog defaults to the DB_DATADIR directory. This is only applicable for Recital Terminal Developer: for Recital Database and Mirage Servers, the <database name> must be specified.

EXCLUSIVE | SHARED

Determines whether the database is opened EXCLUSIVE, which prevents other users from opening the database, or SHARED, which allows other shared users of the database. If neither keyword is included, the access is determined by the active SET EXCLUSIVE setting.

NOUPDATE

If the NOUPDATE keyword is included, the database is opened in read only mode.

VALIDATE

The VALIDATE keyword is included for language compatibility.

The OPEN DATABASE command does not cause the current working directory to be changed. When a database is opened, its catalog file is also opened. If no catalog file exists, it is automatically created. The catalog is a Recital table with an associated index tag, which stores information about the files in the database. The catalog is named <database>.cat, its index <database>.cam and it is opened with the alias name _<database>, e.g. the southwind database has the catalog southwind.cat, the index southwind.cam and the alias _southwind. It is opened in the last available workarea as determined by the DB_MAXWKA environment variable/symbol. If a database is open, this highest workarea is unavailable for use by other tables and an error will be returned if an attempt is made to open a table in this workarea.

The catalog has the following structure.


Field Name Type Width Description Index
PATH Character 255 File directory N
FILENAME Character 32 File name N
ALIAS Character 32 Alias Y
TYPE Character 3 File type N
TITLE Character 80 File title N
CODE Numeric 3 File group N
CATEGORY Character 10 File category N
ORDER Character 1 File sort tag N
DETAILS Memo 8 File details N


Each program or table has a record in the catalog, each index has multiple records. For a single index, there is one record for the file itself and one record for each component of the index key. For a production tagged index there is one record for each component of the index key.


Field Name Description
PATH The full directory path name for the file. This may or may not be the database's directory. Files added to the database catalog via AUTOCATALOG functionality may be located in other directories. This makes them accessible as part of the database without specifying the full path or using SET PATH.
FILENAME single-index-filename><number>. e.g. for a tag called 'address' on the example.dbf table with a key of city+state, there would be two records with the filenames example-address01 and example-address02.
ALIAS For program files, this is the program basename, e.g. 'test' for 'test.prg' and for all other files this is the associated table alias name.
TYPE dbf - Recital, FoxPro, dBase or Clipper tables or Recital Bridge Files with '.dbf' extensions, e.g. cisamdemo.dbf.

dbx - Recital, FoxPro, dBase or Clipper tagged index files with '.dbx', 'cdx' or '.mdx' file extensions. ndx - Single index files with '.ndx' and '.idx' file extensions. prg - Program source files with '.prg' file extension.

TITLE The file description if available, 'Database Catalog' if not.
CODE Code for internal use.
CATEGORY Data - Recital, FoxPro, dBase and Clipper tables and Recital Bridge Files with '.dbf' extensions, e.g. cisamdemo.dbf.

Index - Recital, FoxPro, dBase and Clipper tagged index files with '.dbx', 'cdx' or '.mdx' file extensions and single index files with '.ndx' and '.idx' file extensions. Program - Program source files with '.prg' file extension.

ORDER Order for internal use, tables (1), programs (6), indexes (7).
DETAILS File details: database, index keys, table names etc.


All files in the catalog become accessible when the database is opened, whether they are in the database directory itself or in alternative paths. Single index files included in the catalog will be opened when their associated table is opened. If a single index appears in the database catalog, but its file no longer exists, it will be removed from the catalog when its associated table is next opened. New tables, tagged indexes and single indexes created while the database is open, are added automatically to the database catalog.

The database catalog can be rebuilt using the REBUILD DATABASE command. The PACK DATABASE command issues the PACK command for all tables in the database catalog. The REINDEX DATABASE command rebuilds all indexes in the database catalog.

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


Example

EXEC SQL
    OPEN DATABASE hr EXCLUSIVE;
 
EXEC SQL
    SELECT staff_no, lastname FROM staff;
 
EXEC SQL
    CLOSE DATABASES;

Products

Recital Database Server, Recital Mirage Server, Recital Terminal Developer