CONNECT

Syntax, Format 1:

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

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

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

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

Syntax, 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, Format 3:

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

Syntax, Format 4:

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

Syntax, Format 5:

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

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

Syntax, 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 store. For ODBC data stores, this is the DSN created via the Microsoft ODBC Data Source Administrator. or the HCOSS Manage Connections tool. 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. d`b_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.

OS Authentication:

When using Oracle, DB2 or SQL Server with ODBC or JDBC runtimes, you can achieve OS authentication using either of these two methods:

In the CONNECT statement, specify a user ID consisting of a single forward slash and either omit the password or specify all spaces
Completely omit the user ID and password from the CONNECT statement

For complete information on OS authentication requirements for your DBMS product, consult your DBMS documentation. While various DBMS products have differing requirements, the following is a brief summary:

SQL Server: The Windows user must have a corresponding SQL Server login.
DB2: The WIndows user must be a member of either the DB2USERS group or the DB2 ADMNS group.
Oracle: Create an Oracle user with the OS authentication prefix and include the domain name with the user name. For JDBC you can set REMOTE_OS_AUTHENT in init.ora or use the OCI driver rather than the thin driver (e.g. url=jdbc:oracle:oci:@localhost:1521:ORCL).

Comments:

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, Format 1:

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

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

Example, Format 2:

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

Example, Format 3:

     EXEC SQL
        CONNECT WITH PROMPT
     END-EXEC

Example, Format 4:

     EXEC SQL
        CONNECT RESET
     END-EXEC

Example, Format 5:

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

The example above uses a File DSN.

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