When you start an R/3 application server, a connection is opened by default from the R/3 kernel to the R/3 database. This connection will be called the R/3 default connection from here on .All SQL commands that are transmitted from the R/3 kernel or from ABAP programs - no matter whether they are Open or Native SQL commands - automatically refer to this default connection, that is, they run in the context of the active DB transaction on this connection.The connection data like DB user name, password of this user or the database name are either taken from the profile parameters or from the correspondingly set environment variables (this is DB-specific).

As of Release 4.0B, you have the option to open other DB connections in addition to the default connection from within an ABAP program and you can access these databases via Native SQL commands.These additional connections can either be set up

• to the standard R/3 database or

• to another database of the same DB manufacturer or

• to a database of another DB manufacturer

These databases do not necessarily have to contain SAP components.However, you can only set up connections to database systems that are also supported by the R/3 Basis system.These are DB2, Informix, MS SQL Server, Oracle and SAP DB.If you only want to set up connections to databases of the same manufacturer, thus set up homogeneous connections only, this is already possible using the delivered R/3 software.However, if you want to set up a connection to a database that does not correspond to the DB platform of the R/3 database, you also need to install a DB-platform-specific DLL via which accesses to this database from R/3 can be routed.These DLLs are not part of a standard R/3 installation.In this case, contact your SAP consultant.You can find information on potential platform-specific restrictions in the special notes attached.

Configuration: table DBCON

Before it is possible to open an additional DB connection, all connection data which is required to identify the target database and for authentication against this database must be communicated to the ABAP runtime environment.To do this, you need to create an entry in the DBCON table which will contain all required connection data for each database connection that you want to set up in addition to the R/3 default connection.The DBCON table is in the R/3 default database and can be maintained using the table maintenance tool (transaction SM30) or , as of Release 4.6, using transaction DBCO.For each connection you need to enter the following information in this table:

• A logical connection name.The name entered here explicitly identifies a database connection.

• The database type.This determines which DB platform will be used for this connection.In principle, you can enter all DB platforms supported by the R/3 System.

• The database user under whose name you want to set up the connection.

• The password of this user for authentication against the database.This password is stored in encrypted form.

• The technical connection data required to set up the connection to the database.The connection data is DB-platform-dependent and generally includes the database name as well as the DB host on which the database runs.

Native SQL language commands for administrating several database connections

Only Native SQL interfaces allow you to set up additional database connections and access non-R/3 databases.However, all Open SQL commands will continue to be transferred to the default connection, that is to the R/3 database.
Commands were added to native SQL to open and to close a database connection as well as to set it up.If a new database connection is opened, a new DB transaction is started automatically on this connection. This transaction is independent of the transaction currently running on the R/3 default connection, that is, the transaction running there is not exited and all subsequent Open SQL commands will continue to be processed in this transaction (and not in the transaction started on the new DB connection).However, all subsequent Native SQL commands will be executed on the newly opened connection. No synchronization is carried out by the system between the transactions running on the different connections, that is, a 2 phase Commit log is NOT supported.

Opening a database connection

EXEC SQL.

ENDEXEC.

If the addition "AS <alias_name>" is entered, the open database connection is managed with the indicated alias name.If no alias name is entered, <con_name> is used as the name for the open connection.If you enter several alias names, you can open several connections to the same database simultaneously by using the same login name.This alias arrangement allows the execution of independent transactions on the same logical database connection (see example 2).The alias name can also be entered as a literal or as a variable.

If no entry is found in the DBCON table for the indicated connection name <con_name>, an ABAP runtime error occurs.If, on the other hand, the setup connection fails due to a DB error, for example, because the database in question is not online, then SY-SUBRC = 4 is set and the ABAP program assumes control.

After the connection has been successfully setup (SY-SUBRC = 0), the connection that was just opened automatically becomes the active connection, that is, from this time on, all subsequent Native SQL commands (and only these) will access the database associated with this connection.However, all subsequent Open SQL accesses still refer to the default connection. Nevertheless, the physical connection remains.If the connection is not closed explicitly with a "DISCONNECT" command (see below), it remains open until it will be closed implicitly when the corresponding internal mode is exited (the term "internal mode" is explained in the section on "Data area and Modularization Unit Organization" in the ABAP online documentation).After that, you can only access this connection if it has been opened again explicitly with a new "CONNECT" command.

Remark

If a database connection that was opened using a "CONNECT" command is not closed explicitly with a "DISCONNECT" command again, it will be invalidated (that is, it will have to be opened with a "CONNECT" again for further accesses) when the internal mode is closed, however, the physical connection remains in place.The advantage is that it will not have to be set up again in the case of a new "CONNECT".However, "reusing" a connection that was previously opened only works if there are no open transactions on this connection, that is, if the last transaction on this connection was completed using COMMIT or ROLLBACK.

Setting the active database connection

EXEC SQL.

ENDEXEC.

You can also enter the keyword DEFAULT as an alias name.This causes the R/3 default connection to become the active connection again.

Again, <alias_name> can also be entered directly as a literal or as a variable value in form of :<var>. However, the entered alias name must be known from a preceding "CONNECT" statement (that is a statement executed within the same internal mode).If the database connection had been opened without explicitly specifying an alias name, the connection name <con_name> must be used in the "SET" statement as the <alias_name>.

If there is no opened connection for the entered connection name, SY-SUBRC = 4 is set.

Closing a database connection

EXEC SQL.

ENDEXEC.

The "DISCONNECT" command also closes the physical connection to the database.If you are setting up a new connection, you must first re-establish this physical connection, which may be a rather time-consuming operation since the corresponding system resources will have to be requested on the client side as well as on the database server side.Therefore, you should only explicitly close a connection with the "DISCONNECT" command if the operations executed on this connection are only running occasionally.However, if you want to use this connection all the time, it should remain open.

If the <alias_name> does not correspond to any previously opened DB connection, an ABAP runtime error occurs.

Example 1: Accessing remote DB in a subroutine

FORM remote_access USING con_name LIKE dbcon-con_name.

* This form performs some database operations on connection 'con_name'.

* The form does not require that the caller has already opened this

* connection nor does it require that 'con_name' is "active". After

* the form has finished the "default connection" is active

* Test if connection 'con_name' has already been opened

  EXEC SQL.

    SET CONNECTION :con_name

  ENDEXEC.

*   Connection not yet opened.

    EXEC SQL.

      CONNECT TO :con_name

    ENDEXEC.

*     error handling

    ENDIF.

  ENDIF.

* Do something on connection 'con_name'

  EXEC SQL.

    ...

  ENDEXEC.

* Commit the changes on 'con_name'

  EXEC SQL.

    COMMIT.

  ENDEXEC.

* Reset to "default connection"

  EXEC SQL.

    SET CONNECTION DEFAULT

  ENDEXEC.

ENDFORM.

Example 2: Implementing independent transactions

DATA con_name LIKE dbcon-con_name VALUE 'ABC'.

* Open a connection C1 to a database identified by the logical name

* 'ABC'. This implicitly starts a transaction T1.

EXEC SQL.

  CONNECT TO :con_name AS 'C1'

ENDEXEC.

* error handling

ENDIF.

* Do some database operations on connection C1

EXEC SQL.

...

ENDEXEC.

* Open a second connection C2 to the same database 'ABC'. This

* implicitly starts transaction T2 which became the "active" one.

* Transaction T1 still remains open.

EXEC SQL.

  CONNECT TO :con_name AS 'C2'

ENDEXEC.

* error handling

ENDIF.

* Do some operations on connection C2

EXEC SQL.

...

ENDEXEC.

* Commit transaction T2. This commit does not have any effect on

* transaction T1.

EXEC SQL.

  COMMIT

ENDEXEC.

* Resume transaction T1 on connection C1

EXEC SQL.

  SET CONNECTION 'C1'

ENDEXEC.

EXEC SQL.

...

ENDEXEC.

* Commit transaction T1 on connection C1

EXEC SQL.

  COMMIT

ENDEXEC.

* Reset to the default connection

EXEC SQL.

  SET CONNECTION DEFAULT

ENDEXEC.