Contents Index CONFIGURE statement [Interactive SQL] CREATE COMPRESSED DATABASE statement

ASA SQL Reference
  SQL Statements

CONNECT statement [ESQL] [Interactive SQL]


Description 

Use this statement to establish a connection to a database.

Syntax 1 

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

engine-namedatabase-nameconnection-nameuseridpassword :
identifier | string | hostvar }

Syntax 2 

CONNECT USING connect-string

connect-string : { identifier | string | hostvar }

Parameters 

AS clause    A 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 may 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.

For more information on connection strings, see Connection parameters.

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 will be assumed (the first database server started). If no database-name is specified, the first database on the given server will be assumed.

The WHENEVER statement, SET SQLCA and some DECLARE statements do not generate code and thus may 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.

You can connect without explicitly specifying a password by using a host variable for the password and setting the value of the host variable to be the null pointer.

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 a detailed description of the connection algorithm, see Troubleshooting connections.

Interactive SQL behavior    If no database or server is specified in the CONNECT statement, Interactive SQL remains connected to the current database, rather than to the default server and database. If a database name is specified without a server name, Interactive SQL attempts to connect to the specified database on the current server. If a server name is specified without a database name, Interactive SQL connects to the default database on the specified server.

For example, if the following batch is executed while connected to a database, the two tables are created in the same database.

CREATE TABLE t1( c1 int )
go
CONNECT DBA IDENTIFIED BY SQL
go
CREATE TABLE t2 (c1 int )
go

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

In the user interface, if the password or the user ID and password are not specified, the user is prompted to type the missing information.

In Interactive SQL running in command prompt mode or batch mode, if you execute CONNECT without an AS clause, an unnamed connection is opened. If there is another unnamed connection already opened, the old one is automatically closed. Otherwise, existing connections are not closed when you run CONNECT.

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 the SET CONNECTION statement. The DISCONNECT statement is used to drop connections.

In Interactive SQL, the connection information (including the database name, your user ID, and the database server) appears in the title bar above the SQL Statements pane. If you are not connected to a database, Not Connected appears in the title bar.

Permissions 

None.

Side effects 

None.

See also 

GRANT statement

DISCONNECT statement [ESQL] [Interactive SQL]

SET CONNECTION statement [Interactive SQL] [ESQL]

SETUSER statement

Connection parameters

Standards and compatibility 
Examples 

The following are examples of CONNECT usage within Embedded SQL.

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

The following examples assume that the sample database (asademo.db) has already been started.

Connect to a database from Interactive SQL. Interactive SQL prompts for a user ID and a password.

CONNECT

Connect to the default database as DBA from Interactive SQL. Interactive SQL prompts for a password.

CONNECT USER "DBA"

Connect to the sample database as the DBA from Interactive SQL.

CONNECT
TO asademo
USER DBA
IDENTIFIED BY SQL

Connect to the sample database using a connect string, from Interactive SQL.

CONNECT
USING 'UID=DBA;PWD=SQL;DBN=asademo'

Once you connect to the sample database, the database name, your user ID, and the server name appear on the title bar: asademo (DBA) on asademo9.


Contents Index CONFIGURE statement [Interactive SQL] CREATE COMPRESSED DATABASE statement