The CREATE CONNECTION statement creates a new connection object consisting of a Connection section name in the local nucleus.ini file, a remote database name, and a user name and password that can be used to log in to the remote database. A connection object is used by linked tables to connect to the remote database.
No special privileges are required to create a new connection. However, a valid user authorization and password for accessing the remote database must be supplied as part of the CREATE CONNECTION statement.
Note that the user authorization defined in the connection must possess the privileges required to query each remote table that will be subsequently referenced in CREATE TABLE...WITH CONNECTION statements, otherwise those attempts to create linked tables using the connection will fail with an "insufficient privileges" error.
The connection name argument is an identifier that names the connection object uniquely: it cannot match any other connection name in the database. Connection names can be up to 128 characters long. It is strongly recommended that none of the SAND CDBMS SQL keywords be used as a connection name, as this may cause problems when referencing the connection in CREATE TABLE...WITH CONNECTION statements.
Refers to a [CONNECTION connection-section] entry in the local nucleus.ini file. This Connection section contains the host and port information that will be used to connect to the remote database specified by database-name. Note that the Connection section name must also match the connection name used to start the remote database.
Is the remote database that will be accessed with this connection.
Is a user name that can be used to log in to the remote database.
Is the password, if applicable, for the specified authorization. Note that it must be preceded by a slash ( / ).
The connection object brought into existence with the CREATE CONNECTION command contains all of the information required to access a remote database:
- the Connection section (providing the host name and port number used by the database)
- the database name
- a user name/password that can be used to gain entry to the database.
The parameters of this command are not verified against the nucleus.ini file or the remote database when the connection is created, but rather only when the connection is included in a CREATE TABLE...WITH CONNECTION statement. Therefore the remote database specified in the CREATE CONNECTION statement does not actually have to be running when this command is executed. This also means that any mistake in the connection information will not generate an error until a subsequent attempt to create a linked table.
Note that if a remote database is started with a different connection name, or on a different host and/or port, from what is specified in the connection object, a linked table defined on this connection object will not be able to reach the database. Similarly, if the user authorization stored in the connection object is dropped from the remote database, or that user's password is altered, linked tables using this connection object will be unable to connect to the remote database. A connection object cannot be modified after it is created, so a connection object can only be changed by dropping it, then re-creating it with the changed information.
A connection can be deleted with the DROP CONNECTION command, but in order to do so, the connection must not have any dependent tables. If dependent tables exist, they must be removed prior to executing the DROP CONNECTION command.
The following statement creates a connection object called conn1 that can connect to the database db01 using information contained in the [CONNECTION cn1] section of the nucleus.ini file, with the user name x23 and password g87sFd9a as login credentials:
CREATE CONNECTION conn1
TO DATABASE db01
WITH AUTHORIZATION x23 / g87sFd9a;
After the command executes successfully and the connection is created, it can be used to create a linked table with the CREATE TABLE...WITH CONNECTION command. But if any of the connection details are erroneous (for example, the section cn1 does not exist in the nucleus.ini file, or the user password is spelled wrong), the CREATE TABLE statement that references this connection object will fail.