CONNECT Statement [ESQL] [Interactive SQL]

Syntax

Syntax 1

CONNECT
… [ TO engine-name ]
…[ DATABASE database-name ]
…[ AS connection-name ]
…[ USER ] userid [ IDENTIFIED BY ]

Syntax 2

CONNECT USING connect-string

Parameters

• engine-name: – identifier, string, or host-variable
• database-name: – identifier, string, or host-variable
• connection-name: – identifier, string, or host-variable
• userid: – identifier, string, or host-variable
• password: – identifier, string, or host-variable
• connect-string: – a valid connection string or host-variable

Examples

• Example 1 – CONNECT usage within Embedded SQL:

  EXEC SQL CONNECT AS :conn_name
  USER :userid IDENTIFIED BY :password;
  EXEC SQL CONNECT USER "dba" IDENTIFIED BY "sql";

• Example 2 – CONNECT usage from dbisql:

  • Connect to a database from dbisql. Prompts display for user ID and password:

  CONNECT

  • Connect to the default database as DBA, from dbisql. A password prompt displays:

  CONNECT USER "DBA"

  • Connect to the demo database as the DBA, from dbisql:

  CONNECT
  TO <machine>_iqdemo
  USER "DBA"
  IDENTIFIED BY sql

  where <machine>_iqdemo is the engine name.

  • Connect to the demo database using a connect string, from dbisql:

  CONNECT
  USING 'UID=DBA;PWD=sql;DBN=iqdemo'

Usage

The CONNECT statement establishes a connection to the database identified by database-name running on the server identified by engine-name.

Embedded SQL behavior—In Embedded SQL, if no engine-name is specified, the default local database server is assumed (the first database server started). If no database-name is specified, the first database on the given server is assumed.

The WHENEVER statement, SET SQLCA, and some DECLARE statements do not generate code and thus might appear before the CONNECT statement in the source file. Otherwise, no statements are allowed until a successful CONNECT statement has been executed.

The user ID and password are used for permission checks on all dynamic SQL statements. By default, the password is case-sensitive; the user ID is not.

For a detailed description of the connection algorithm, see System Administration Guide: Volume 1 > Sybase IQ Connections > How Sybase IQ Establishes Connections.

DBISQL behavior—If no database or server is specified in the CONNECT statement, dbisql remains connected to the current database, rather than to the default server and database. If a database name is specified without a server name, dbisql attempts to connect to the specified database on the current server. You must specify the database name defined in the -n database switch, not the database file name. If a server name is specified without a database name, dbisql connects to the default database on the specified server. For example, if this batch is executed while connected to a database, the two tables are created in the same database.

CREATE TABLE t1( c1 int );
CONNECT DBA IDENTIFIED BY sql;
CREATE TABLE t2 (c1 int );

No other database statements are allowed until a successful CONNECT statement has been executed.

The user ID and password are used for checking the permissions on SQL statements. If the password or the user ID and password are not specified, the user is prompted to type the missing information. By default, the password is case-sensitive; the user ID is not.

Multiple connections are managed through the concept of a current connection. After a successful connect statement, the new connection becomes the current one. To switch to a different connection, use SET CONNECTION. Executing a CONNECT statement does not close the existing connection (if any). Use DISCONNECT to drop connections.

Static SQL statements use the user ID and password specified with the -l option on the SQLPP statement line. If no -l option is given, then the user ID and password of the CONNECT statement are used for static SQL statements also.

Connecting with no password—If you are connected to a user ID with DBA authority, you can connect to another user ID without specifying a password. (The output of dbtran requires this capability.) For example, if you are connected to a database from Interactive SQL as DBA, you can connect without a password with the statement:

CONNECT other_user_id

In Embedded SQL, you can connect without a password by using a host variable for the password and setting the value of the host variable to be the null pointer.

AS clause—connection can optionally be named by specifying the AS clause. This allows multiple connections to the same database, or multiple connections to the same or different database servers, all simultaneously. Each connection has its own associated transaction. You might even get locking conflicts between your transactions if, for example, you try to modify the same record in the same database from two different connections.

Syntax 2—A connect-string is a list of parameter settings of the form keyword=value, and must be enclosed in single quotes.

Standards

Permissions

None