4D Doc Center : USE DATABASE

Home

4D v14

USE DATABASE



	USE [LOCAL \| REMOTE] DATABASE {DATAFILE <Complete pathname> \| SQL_INTERNAL \| DEFAULT} [AUTO_CLOSE]

Description

The USE DATABASE command is used to designate an external database as the current database, in other words, the database to which the next SQL queries in the current process will be sent. All types of SQL queries are concerned: queries included in the Begin SQL/End SQL structure, SQL EXECUTE or SQL EXECUTE SCRIPT commands, etc.

Note: For more information about external databases, please refer to the description of the CREATE DATABASE command.

If you are working in a single-user configuration, the external database must be located on the same machine as your 4D.
If you are working in remote mode, the external database can be located on the local machine or on the 4D Server machine.

If you are using 4D in remote mode, the REMOTE keyword can be used to designate an external database located on 4D Server.
For security reasons, this mechanism only works with native remote connections, in other words, in the context of a remote 4D database connected with 4D Server. Connections via ODBC or pass-through connections are not allowed.
If no keyword is specified, the LOCAL option is used by default. If you are using 4D in local mode, the REMOTE and LOCAL keywords are ignored: connections are always local.

To designate an external database to be used, pass its complete pathname (access path + name) in the DATAFILE clause. The path can be expressed either in the POSIX syntax, or in the system syntax. It can be absolute or relative to the structure file of the main 4D database.
In remote mode, if the REMOTE keyword is passed, this parameter designates the database path from the server machine. If it is omitted or if the LOCAL keyword is passed, this parameter designates the database path on the local 4D machine.

Important: You must designate a valid external database, and one where access control has not been activated (by assigning a password to the Designer). Otherwise, an error is generated.

In order to reset the main database as the current database, execute the command while passing the SQL_INTERNAL or DEFAULT keyword.

Pass AUTO_CLOSE if you want to physically close the external database after its use; in other words, when you change the current database. In fact, since opening an external database is an operation that requires some time, for optimization reasons 4D keeps information stored in memory concerning external databases opened during the user session. This information is kept as long as the 4D application remains launched. Subsequent opening of the same external database is therefore faster. However, this prevents the sharing of external databases among several 4D applications because the external database remains open in read/write for the first application that uses it. If several 4D applications must be able to use the same external database simultaneously, pass the AUTO_CLOSE keyword in order to physically release the external database after its use.

This restriction does not apply to processes of the same application: different processes of an application can always access the same external database in read/write without it being necessary to force it to close.
Note that when several processes use the same external database, it is physically released only when the last process that uses it is closed, even when the AUTO_CLOSE option has been passed. You should take this functioning into account for operations that involve inter-application sharing or deletion of external databases.

Example

Use of an external database for a request then return to the main database:

 Begin SQL

    USE DATABASE DATAFILE 'C:/MyDatabase/Names'

    SELECT Name FROM emp INTO :tNames1

    USE DATABASE SQL_INTERNAL

 End SQL