open database

Open a database

Syntax

open_db_stmt:
          {OPEN DATABASE | USE} {database_name | db-uri} [open_mode]
     |    {OPEN DATABASE | USE} database_name AS UNION OF db-uri[, db-uri]...

open_mode:
          open_mode_spec
     |    IN open_mode_spec [MODE]

open_mode_spec:
          SHARE | EXCLUSIVE | READ ONLY

Description

The OPEN DATABASE statement is used to open a database. The database to be opened is specified by the identifier database_name or db-uri. If the optional open_mode argument is not specified, the default open mode for the connection will be used.

The default open mode for the connection is SHARE, if not set explicitly by the SET OPEN MODE statement.

The default open mode can also be set by the underlying TFS configuration. See defaultopenmode in the SQL Configuration Options section.

If EXCLUSIVE, a database can only be opened when no other connections have the database open. If READ ONLY, a database can only be accessed for reading but not for updating. Any attempt to start an update transaction or execute an INSERT, UPDATE or DELETE statement will return an error.

Different instances of database database_name that are stored on separate TFSs can be opened as a union by specifying the db-uri for each TFS. Each database instance is opened in read-only mode. Access to the content of the databases must be made through normal SELECT statements that are executed inside a read-only transaction. Note that a database union is a union of different instances of the same database schema (i.e., definition) contained on separate TFSs. This is not to be confused with the standard SQL union of SELECT statements operation.

When an encrypted database is opened, the correct encryption passcode for the database must be supplied prior to OPEN DATABASE call. This can be done with the ".p" command interface option in rdm-sql.

Note that SQL will automatically open a previously unopened database that is explicitly used to qualify a table name as, for example, in the statement: "select * from bookshop.author".

If you want to connect a remote TFS, you must first start the TFS Server. In the bin directory of the installation, run the rdm-tfs executable.

Example

open bookshop exclusive;

insert into author values "BarrieJ", "Barrie, J. M. (James Matthew)", "M", 1860, 1937,
    "Scottish author and dramatist, best remembered today as the creator of Peter Pan.";
	...
open database nsfawards as union of "tfs-tcp://Northeast_TFS:1650", "tfs-tcp://Southeast_TFS:1650",
	"tfs-tcp://Midwest_TFS:1650", "tfs-tcp://West_TFS:1650";

start read only transaction;
select state, sum(amount) from award join sponsor on sponsor_nm = name group by state;