CONNECT

Syntax for Format 1

>>---EXEC SQL---CONNECT TO---.-------------.------------->
                             +-data_source-+ 

 >--.------------.------USER-.-------------------.-------->
    +-AS db_name-+           +-user-.-----------++
                                    +-.password-+ 

 >-.--------------------.-.-----------------------------.->
   +-WITH-.----.-PROMPT-+ +-RETURNING output_connection-+
          +-NO-+

 >------END-EXEC--------><
    

Syntax for Format 2

>>---EXEC SQL---CONNECT user--.------------------------.->
                              +-IDENTIFIED BY password-+
                              +-------'/'password------+ 

 >---.--------------.--------.--------------------.------->
     +--AT db_name--+        +--USING data_source-+

 >---.----------------------.----------------------------->
     +--WITH-.----.-PROMPT--+ 
             +-NO-+ 

 >---.-----------------------------.---END-EXEC---><
     +-RETURNING output_connection-+

Syntax for Format 3

>>----EXEC SQL---CONNECT WITH PROMPT-------------------->
  
 >---.------------------------------.---END-EXEC----><
     +-RETURNING output_connection -+

Syntax for Format 4

>>----EXEC SQL---CONNECT RESET-.--------.--END-EXEC-----><
                               +--name--+

Syntax for Format 5

>>----EXEC SQL--------CONNECT DSN input_connection------->

 >---.------------------------------.-------END-EXEC-----><
     +-RETURNING output_connection -+

Syntax for Format 6

>>----EXEC SQL---CONNECT USING input_connection---------->
 
 >-----.-------------.---.---------------------.---------->
       +--AS db_name-+   +--WITH-.----.-PROMPT-+
                                 +-NO-+

 >-----.------------------------------.------END-EXEC---->< 
       +--RETURNING output_connection-+

Parameters:

data_ source The name of the ODBC data source. If you omit data_source, the default ODBC data source is assumed. The data source can be specified as a literal or as a host variable.
db_name A name for the connection. Connection names can have as many as 30 characters, and can include alphanumeric characters and any symbols legal in filenames. The first character must be a letter. Do not use Embedded SQL keywords or CURRENT or DEFAULT or ALL for the connection name; they are invalid. If db_name is omitted, DEFAULT is assumed. db_name can be specified as a literal or a host variable.

When connecting to SQL Server, db_name is the database to which you are connecting.

user A valid user-id at the specified data source.
password A valid password for the specified user-id.
output_connection A PIC X(n) text string defined by ODBC as the connection string used to connect to a particular data source. Subsequently, you can specify this string as the input_connection in a CONNECT USING statement.
input_connection A PIC X(n) text string containing connection information used by ODBC to connect to the data source. The test string can be either a literal or a host variable.
RESET Resets (disconnects) the specified connection.
name You can specify name as CURRENT, DEFAULT or ALL.

Purpose:

The CONNECT statement attaches to a specific database using the supplied user name and password.

If you use only one connection, you do not need to supply a name for the connection. When you use more than one connection, you must specify a name for each connection. Connection names are global within a process. Named connections are shared by separately compiled programs that are linked into a single executable module.

After a successful CONNECT statement, all database transactions other than CONNECT RESET work through this most recently declared current connection. To use a different connection, use the SET CONNECTION statement.

To cause the ODBC run-time module to prompt at run-time for entry or confirmation of connection details, use CONNECT WITH PROMPT.

Use CONNECT DSN and CONNECT USING to simplify administration.

With CONNECT TO, CONNECT, CONNECT WITH PROMPT, CONNECT DSN and CONNECT USING, you can return connection information to the application.

Note:
  • If the INIT option of the SQL Compiler directive is used, an implicit connection to the database will be made at run-time. In this case, it is not necessary to execute an explicit CONNECT statement.
  • A File DSN cannot contain a password.

Example for Format 1

     MOVE 'servername' TO svr
     MOVE 'username.password' TO usr

     EXEC SQL
        CONNECT TO :svr USER :usr
     END-EXEC

The example above uses a File DSN.

Example for Format 2

     EXEC SQL
        CONNECT 'username.password' USING 'servername'
     END-EXEC      

Example for Format 3

     EXEC SQL
        CONNECT WITH PROMPT
     END-EXEC

Example for Format 4

     EXEC SQL
        CONNECT RESET
     END-EXEC

Example for Format 5

    EXEC SQL
        CONNECT USING 'FileDSN=Oracle8;PWD=tiger'
     END-EXEC

Example for Format 6

 01  connectString               PIC X(72) value
                   'DRIVER={Microsoft Excel Driver (*.xls)};'
                   &'DBQ=c:\demo\demo.xls;'
                   &'DRIVERID=22'
                   .

 procedure division.

     EXEC SQL
         CONNECT USING :connectString
     END-EXEC

The example above connects to an Excel spreadsheet without setting up a data source