Informix Guide to SQL: Syntax SQL Statements

Use the DATABASE statement to select an accessible database as the current database.

You can use the DATABASE statement to select any database on your database server. To select a database on another database server, specify the name of the database server with the database name.

If you specify the name of the current database server or another database server with the database name, the database server name cannot be uppercase.

Issuing a DATABASE statement when a database is already open closes the current database before opening the new one. Closing the current database releases any cursor resources held by the database server, which invalidates any cursors you have declared up to that point. If the user identity was changed through a SET SESSION AUTHORIZATION statement, the original user name is restored.

The current user (or PUBLIC) must have the Connect privilege on the database specified in the DATABASE statement. The current user cannot have the same user name as an existing role in the database.

In ESQL/C, you cannot include the DATABASE statement in a multistatement PREPARE operation.

You can determine the characteristics of a database a user selects by checking the warning flag after a DATABASE statement in the sqlca structure.

If the database has transactions, the second element of the sqlwarn structure (the sqlca.sqlwarn.sqlwarn1 field) contains a W after the DATABASE statement executes.

In an ANSI-compliant database, the third element of the sqlwarn structure (the sqlca.sqlwarn.sqlwarn2 field) contains the letter W after the DATABASE statement executes.

The fourth element of the sqlwarn structure (the sqlca.sqlwarn.sqlwarn3 field) contains the letter W after the DATABASE statement executes.

If the database is running in secondary mode, the seventh element of the sqlwarn structure (the sqlca.sqlwarn.sqlwarn6 field) contains the letter W after the DATABASE statement executes.

The EXCLUSIVE keyword opens the database in exclusive mode and prevents access by anyone but the current user. To allow others access to the database, you must execute the CLOSE DATABASE statement and then reopen the database without the EXCLUSIVE keyword.

The following statement opens the stores_demo database on the training database server in exclusive mode:

DATABASE  stores_demo@training  EXCLUSIVE 

If another user has already opened the database, exclusive access is denied, an error is returned, and no database is opened.

Related statements: CLOSE DATABASE and CONNECT

For discussions of how to use different data models to design and implement a database, see the Informix Guide to Database Design and Implementation.