User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements

Function

To establish a connection to a database.

Syntax

     Syntax 1

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

     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

Usage

ISQL and Embedded SQL

Permissions

None.

Side effects

None.

See also

• GRANT statement
• DISCONNECT statement
• SET CONNECTION statement

Description

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

Embedded SQL behavior In Embedded SQL, if no engine-name is specified, the default local database engine will be assumed (the first database engine started). If a local database engine is not running and the SQL Anywhere Client (DBCLIENT) is running, the default server will be assumed (the server name specified when the client was started). If no database-name is specified, the first database on the given engine or server will be assumed.

ISQL behavior If no database or engine is specified in the CONNECT statement, ISQL remains connected to the current database, rather than to the default engine and database. If a database name is specified without an engine name, ISQL will try to connect to the specified database on the current engine. If an engine name is specified without a database name, ISQL will connect to the default database on the specified engine. For example, in the following batch, 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.

(In Embedded SQL, WHENEVER, SET SQLCA and some DECLARE statements do not generate code and thus may appear before the CONNECT statement in the source file.)

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 will be prompted to type the missing information.

In Embedded SQL, the user ID and password are used for permission checks on all dynamic SQL statements. 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.

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 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.

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.

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.

The dictionary clause allows you to specify the directory that contains the data dictionary you want to log in to (files *.DFF).

A database directory can also be specified. This specifies the directory or list of directories that contain the data files you will be accessing in the upcoming session (files *.DAT).

For Syntax 2, a connect stringis a list of parameter settings of the form keyword=value. For a description of valid settings for the connection string, see "Database connection parameters". The connect string must be enclosed in single quotes.

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 are examples of CONNECT usage from ISQL.
  • Connect to a database from ISQL. ISQL will prompt for a user ID and a password.

         CONNECT
    

  • Connect to the default database as DBA, from ISQL. ISQL will prompt for a password.

         CONNECT USER "DBA"
    

  • Connect to the sample database as the DBA, from ISQL.

         CONNECT
         TO sademo
         USER "DBA"
         IDENTIFIED BY sql
    

  • Connect to the sample database using a connect string, from ISQL.

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