Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - SQL Reference » SQL statements » Alphabetical list of SQL statements

CONNECT statement [ESQL] [Interactive SQL]

Establishes a connection to a database.

  • Shared memory connections
    [ TO database-server-name ]
    [ DATABASE database-file ]
    [ AS connection-name ]
    [ USER ] userid [ IDENTIFIED BY password ]
    database-server-name, database-file, connection-name, userid, password :
    { identifier | string | hostvar }
  • TCP/IP connections
    CONNECT USING connect-string
    connect-string : { identifier | string | hostvar }
  • 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.

For TCP/IP connections, a connect-string is a list of parameter settings of the form keyword=value, separated by semicolons, and must be enclosed in single quotes.


The CONNECT statement establishes a connection to the database identified by database-file running on the database server identified by database-server-name. This statement is not supported in procedures, triggers, events, or batches.

Shared memory connections are only supported for connections to database servers running on the same computer. To connect to a local database server using TCP/IP or to a database server running on a different computer, use the syntax for TCP/IP connections.

  • Embedded SQL behavior

    In Embedded SQL, if no database-server-name is specified, the default local database server is assumed (the first database server started). If no database-file 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 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 privilege checks on all dynamic SQL statements.

    Note For SQL Anywhere, only shared memory connections are supported with Embedded SQL. For UltraLite, both shared memory and TCP/IP connections can be used with Embedded SQL.
  • 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 );
    CREATE TABLE t2 (c1 int );

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

    When Interactive SQL is run in windowed mode, you are prompted for any missing connection parameters.

    When Interactive SQL is running in command-prompt mode (-nogui is specified when you start Interactive SQL from a command line) or batch mode, or 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 execute a CONNECT statement.

    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.

    When connecting to Interactive SQL, specifying CONNECT [ USER ] userid is the same as executing a SETUSER WITH OPTION userid statement.

    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.

    Note Both syntaxes are valid with Interactive SQL except that Interactive SQL does not support the hostvar argument.

    This SQL statement is not supported for SAP HANA databases.

If you use this statement in a procedure, do not specify the password (IDENTIFIED BY clause) as a string literal because the definition of the procedure is visible in the SYSPROCEDURE system view. For security purposes, specify the password using a variable that is declared outside of the procedure definition.



Side effects


  • ANSI/ISO SQL Standard

    Shared memory connections is an optional ANSI/ISO SQL Language Feature F771. TCP/IP connections is not in the standard.

  • Transact-SQL

    Both syntaxes are supported by Adaptive Server Enterprise.


The following are examples of CONNECT usage within Embedded SQL.

USER :userid IDENTIFIED BY :password;

The following examples assume that the SQL Anywhere sample database has already been started.

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


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


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

TO demo17

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