OPEN DATABASE

From Lianjapedia
Jump to: navigation, search

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, CLOSE DATABASES, CLOSE TABLES, COMPILE DATABASE, COPY DATABASE, CREATE DATABASE, CREATE TABLE, CREATE INDEX, CREATE VIEW, Database Events, DATABASE(), DATABASEEXISTS(), DBC(), 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, REMOVE TABLE, SET AUTOCATALOG, SET DATADIR, SET EXCLUSIVE, USE, TABLEEXISTS()

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.

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 Lianja 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. 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 The name of the file (tables, programs and single index files) e.g. example.dbf, exind.ndx. Or, a reference to a component of an index key. The reference is formatted as follows: <table-alias>-<tagname | 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 - Tables.

dbx - Tagged index files.
ndx - Single index files.
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 - Tables.

Index - Tagged and single index files.
Program - Program source files with '.prg' file extension.

ORDER valign="top"|Order for internal use, tables (1), programs (6), indexes (7).
DETAILS valign="top"|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_library.prg (.dbo compiled/runtime), exists in the database's directory, it is loaded as a system library. When the database is closed, the procedure library is also closed.

Example

OPEN DATABASE hr EXCLUSIVE
SELECT staff_no, lastname FROM staff
CLOSE DATABASE