Difference between revisions of "OPEN DATABASE"
Yvonnemilne (Talk | contribs) |
Yvonnemilne (Talk | contribs) |
||
| (7 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
| − | |||
| − | |||
| − | |||
| − | |||
==Purpose== | ==Purpose== | ||
Sets the specified database as the default database for subsequent operations or SQL queries | Sets the specified database as the default database for subsequent operations or SQL queries | ||
| Line 12: | Line 8: | ||
==See Also== | ==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]], [[SQL Database Events|DATABASE EVENTS]], [[DATABASE()]], [[DB_DATADIR | + | [[ADATABASES()]], [[ADD TABLE]], [[ALTER INDEX]], [[ALTER TABLE]], [[BACKUP DATABASE]], [[CLOSE DATABASES]], [[CLOSE TABLES]], [[COMPILE DATABASE]], [[COPY DATABASE]], [[CREATE DATABASE]], [[CREATE TABLE]], [[CREATE INDEX]], [[CREATE VIEW]], [[SQL Database Events|DATABASE EVENTS]], [[DATABASE()]], [[DB_DATADIR]], [[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]], [[RESTORE DATABASE]], [[SET AUTOCATALOG]], [[SET DATADIR]], [[SET EXCLUSIVE]], [[SQL USE|USE]] |
| Line 18: | Line 14: | ||
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. | 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 | + | 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 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 | + | 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: for Recital Server, the <database name> must be specified. |
====EXCLUSIVE | SHARED==== | ====EXCLUSIVE | SHARED==== | ||
| Line 31: | Line 27: | ||
The VALIDATE keyword is included for language compatibility. | 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 | + | 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. 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. | The catalog has the following structure. | ||
| Line 103: | Line 99: | ||
==Example== | ==Example== | ||
<code lang="recital"> | <code lang="recital"> | ||
| − | + | OPEN DATABASE hr EXCLUSIVE | |
| − | + | SELECT staff_no, lastname FROM staff | |
| − | + | CLOSE DATABASE | |
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
</code> | </code> | ||
==Products== | ==Products== | ||
| − | Recital | + | Recital Server, Recital |
[[Category:Documentation]] | [[Category:Documentation]] | ||
[[Category:SQL]] | [[Category:SQL]] | ||
Latest revision as of 10:19, 26 August 2011
Contents
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, COPY DATABASE, CREATE DATABASE, CREATE TABLE, CREATE INDEX, CREATE VIEW, DATABASE EVENTS, DATABASE(), DB_DATADIR, 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, RESTORE DATABASE, SET AUTOCATALOG, SET DATADIR, 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 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: for Recital Server, 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. 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
OPEN DATABASE hr EXCLUSIVE SELECT staff_no, lastname FROM staff CLOSE DATABASE
Products
Recital Server, Recital
