ABAP - Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP Database Accesses → 

Database Connections

An AS ABAP uses database connections to access databases. A database connection defines the address of the database system (database host), the database user, and hence the associated database schema that is accessed. The Open SQL and Native SQL statements of an ABAP program and the AMDP Framework use a database connection of the current work process to access a database. By default, the standard connection is used to access the ABAP database schema of the standard AS ABAP database. By opening a secondary connection, it is also possible to access databases or database schemas other than the standard database. This makes possible a number of options, for example, data can be passed to and committed in other databases or in other database schemas. The secondary database does not need to be part of an AS ABAP here, but it does need to be supported by SAP. Connections called service connections can also be used to access the standard database.

Possible Database Connections

When Open SQL, Native SQL, or AMDP is used in an ABAP program, the database interface uses a database connection of the current work process to access a database. Every work process always has a standard connection to the standard database. In addition, secondary connections to other databases or database schemas can be defined. Service connections can also still be opened to the standard database.

Standard Connection

Each AS ABAP work process always has a standard connection to the standard database and this connection cannot be closed. It is shared by all internal sessions. If the standard connection is used, the work process acts as a database user assigned to the ABAP database schema.

Example

Specifies the standard connection explicitly in Open SQL. It would not be necessary to specify the connection in the statements DELETE and INSERT. The statement COMMIT CONNECTION, however, makes an explicit database commit possible on the standard connection.

DELETE FROM demo_update CONNECTION default.
INSERT demo_update CONNECTION default
  FROM @( VALUE #( id = 'X'  col1 = 1 col2 = 2 col3 = 3 col4 = 4 ) ).
COMMIT CONNECTION default.

Secondary Connections

A secondary connection is a database connection to a secondary database defined by an entry in the database table DBCON. The table key CON_NAME of the table DBCON is the name (in uppercase letters) of the secondary connection under which it can be specified explicitly in ABAP. The remaining columns describe the properties of the secondary connection, such as the database system, the database user, and the physical address.

Entries in the database table DBCON are created and modified using the central DBA Cockpit tool. If the DBA Cockpit tool is not available in a system for some reason, transaction DBCO can be used instead (but this requires some expertise). DBCON should not be accessed in any other way. More specifically, the table DBCON cannot be displayed using the Data Browser tool.

The database user name of the database user used to log on the database connection connection to the database system is part of the definition of a secondary connection in the table DBCON. An Open SQL statement that uses a secondary connection accesses only that database schema that is assigned to this user.

A secondary connection must be specified explicitly before it can be used in Open SQL or Native SQL. If possible, the secondary connection is opened for the current work process or an existing inactive secondary connection with the same name is reused. Secondary connections cannot be used in AMDP.

Notes

Example

Uses a secondary connection in Open SQL. The connection can be entered and a check is made to see whether it exists in the database table DBCON. If no secondary connection exists in Open SQL, the non-handleable exception DBSQL_UNKNOWN_CONNECTION is raised.

DATA conn TYPE dbcon-con_name.
cl_demo_input=>request( CHANGING field = conn ).

SELECT SINGLE @abap_true
       FROM dbcon
       WHERE con_name = @conn
       INTO @DATA(dbtype).
IF sy-subrc <> 0.
  cl_demo_output=>display( 'Connection not in DBCON' ).
  RETURN.
ENDIF.

DELETE FROM demo_update CONNECTION (conn).
INSERT demo_update CONNECTION (conn)
  FROM @( VALUE #( id = 'X'  col1 = 1 col2 = 2 col3 = 3 col4 = 4 ) ).
COMMIT CONNECTION (conn).

Service Connections to the Standard Database

A service connection is a database connection defined by specifying its name in an ABAP program. The name of a service connection is R/3*name and consists of the prefix R/3* (in uppercase letters) and a definable case-sensitive name that can have between 1 and 26 alphanumeric characters.

A service connection is always a database connection to the standard database and inherits all settings from the standard connection automatically.

When a service connection is requested in Open SQL. Native SQL, or AMDP, it is opened for the current work process (if possible) or an existing inactive service connection with the same name is reused.

Notes

Example

Uses a service connection called R/3*service_conn in Open SQL.

DELETE FROM demo_update CONNECTION R/3*service_conn.
INSERT demo_update CONNECTION R/3*service_conn
  FROM @( VALUE #( id = 'X'  col1 = 1 col2 = 2 col3 = 3 col4 = 4 ) ).
COMMIT CONNECTION R/3*service_conn.

Management of Database Connections

Database connections are managed by the ABAP runtime environment This is done at the work process level and the internal session level. Each time an AS ABAP is started, a standard connection is opened for every work process and this connection cannot be closed. Alongside the standard connection, 15 further secondary connections or service connections can be opened for each work process. A maximum of 16 database connections can be open for each work process. On certain databases, it may not be possible to reach this number. If more than 16 database connections are opened, the runtime error DBSQL_NO_MORE_CONNECTION occurs.

Opening and Closing Secondary Connections and Service Connections

Open SQL and Native SQL can request secondary connections or service connections. AMDP can only request service connections. A secondary connection or a service connection is requested as follows:

If no inactive database connection can be activated after the specified name, it is opened for the current work process and activated for the current internal session.

A secondary connection or service connection is closed explicitly in Native SQL using the following:

If closed explicitly in Native SQL, all database changes in the current database LUW of the connection that were not yet committed on the database are discarded.

Open SQL does not have a statement that closes a database connection explicitly. Any secondary connection or service connection that is inactive for a specific period of time (approximately 15 min by default) is closed by the ABAP runtime environment implicitly.

Note

Generally speaking, database connections should only be closed implicitly by the ABAP runtime environment. Database connections should only be closed explicitly if it can be guaranteed that they are not required for some time in the current process, since it takes a significant amount of resources to recover a connection.

Example

Opens three service connections with Open SQL, ADBC, and after EXEC SQL. The connection opened using Open SQL is closed again using Native SQL, which requires its name to be specified in uppercase letters.

SELECT *
       FROM scarr
       INTO TABLE @DATA(itab)
       CONNECTION r/3*demo1.

DATA(con) = cl_sql_connection=>get_connection( `R/3*Demo2` ).

EXEC SQL.
  CONNECT TO 'R/3*Demo3'
ENDEXEC.

EXEC SQL.
  DISCONNECT 'R/3*DEMO1'
ENDEXEC.

Active and Inactive Secondary Connections and Service Connections

An open secondary connection or service connection can be active or inactive. Once opened in Open SQL, Native SQL, or AMDP, a database connection is active and can be used by Open SQL, Native SQL, or AMDP. The secondary connection or service connection becomes inactive as soon as the current database LUW on this connection is ended. This can occur as follows:

An inactive open secondary connection or service connection is reused by the ABAP runtime environment if it needs to be reopened for its work process. Once activated in an internal session, a secondary connection or service connection can be reused here regardless of whether it is active or inactive. When an inactive connection is reused, it is activated implicitly and a new database LUW is opened. In this case, it is not necessary to open it again explicitly in Native SQL either.

Note

When a database connection is closed explicitly in Native SQL it is actually closed and not just set to inactive. The next request must then reopen the connection for the current work process.

Example

Requests a service connection R/3*DEMO using ADBC and uses it for an SQL statement. The statement COMMIT CONNECTION ends the database LUW of the connection and sets it from active to inactive. The connection can be shared and the connection name does not contain any lowercase letters, which means it is reused by being specified in the Open SQL statement INSERT. The connection is reactivated here and a new database LUW is started. A further COMMIT CONNECTION statement ends this LUW and deactivates the connection.

DATA(conn) = CONV dbcon-con_name( `R/3*DEMO` ).

NEW cl_sql_statement( con_ref = cl_sql_connection=>get_connection(
                                  con_name = conn
                                  sharable = abap_true )
                    )->execute_update( `DELETE FROM demo_update` ).
COMMIT CONNECTION (conn).

INSERT demo_update CONNECTION (conn)
  FROM @( VALUE #( id = 'X'  col1 = 1 col2 = 2 col3 = 3 col4 = 4 ) ).
COMMIT CONNECTION (conn).

Secondary Connections and Service Connections in the Internal Session

Active open secondary connections or service connections can only be used within the internal session in which they are opened. An active open secondary connection or service connection can be shared by Open SQL, Native SQL, and AMDP within an internal session. In Native SQL or AMDP, the open connections cannot have any lowercase letters in the name and in ADBC, dedicated open connections must be made available.

When the internal session is closed, any changes made using the connections are committed and the connections are set to inactive. When an ABAP program is called that contains a return to the called program (using SUBMIT AND RETURN or CALL TRANSACTION), the states of any secondary connections or service connections opened here are preserved as active or inactive. They are not, however, passed to the called program. If a secondary connection or service connection with the same name is requested here, a further connection of the same type is opened.

Any secondary connection or service connection that is made inactive within an internal session by its database LUW being ended can be reused in the same session without being requested explicitly, not just in Open SQL, but also in Native SQL or AMDP.

Example

An Open SQL statement requests a service connection R/3*DEMO and then calls a further program. The called program requests a service connection with the same name. After this, two service connections with the same name are open and active for the current work process, up until the end of the internal session of the called program. When a return is made from the called is exited, its service connection is deactivated, just as the service connection of the calling program is deactivated when it is exited. No database commit was made before the call, which means that the isolation level of the database determines whether the change made in the caller is visible in the called program.

Calling Program

DELETE FROM demo_update CONNECTION r/3*demo.

SUBMIT ... AND RETURN.

Called Program

SELECT *
       FROM demo_update
       INTO TABLE @DATA(itab)
       CONNECTION r/3*demo.

Displaying Secondary Connections and Service Connections

The program DBCONINFO shows all database connection of all work processes in the current AS ABAP. The name R/3 in the column ConName identifies the standard connection. Other names indicate the secondary connections and service connections. The column ConState shows the states ACTIVE, INACTIVE, and DISCONNECTED. The column Hdl indicates whether a connection is a secondary connection or a service connection. Identically named secondary and service connections can occur for the following reasons:

Example

The program DBCONINFO can be used to scan the examples shown in this section while they are being executed step by step in ABAP Debugger.

Database Access Using Secondary Connections and Service Connections

Secondary connections and service connections are opened and used in different ways in Open SQL, Native SQL, and AMDP. A secondary or service connection active for an internal session can be shared by Open SQL, Native SQL, or AMDP. Connections used by Open SQL, however, cannot have any lowercase letters in their names and ADBC can reserve connections for itself exclusively.

Open SQL

The following additions can be used to use a database connection other than the standard connection in Open SQL:

If cases where the addition CONNECTION is specified using the name of a secondary connection from the database table DBCON or using the name of a service connection in an Open SQL statement, the following applies:

When a secondary connection is used, an Open SQL statement only accesses the database schema assigned to the database user name associated with the secondary connection. Open SQL can only access database tables and views that are defined in ABAP Dictionary in the current AS ABAP, which means that every database table or view accessed using a secondary connection must exist and be active as a transparent database or view with the same name and matching structure in ABAP Dictionary in the current AS ABAP, regardless of the secondary database and database schema. For the database tables or views of the secondary database, Open SQL assumes that their type information corresponds exactly with that of the local definition. This prerequisite is essential, for example, for the correct interpretation of the database contents and, if necessary, their conversion with respect to the ABAP types of the target fields. If this prerequisite is not met, wrong data or runtime errors can be caused by reads or writes (depending on the database system in question). Because the ABAP runtime environment cannot ensure the consistency of the type descriptions in the local and remote databases, the consistency must be guaranteed by the relevant application program.

The name of a secondary connection or service connection specified after CONNECTION is transformed into uppercase letters internally. This must be respected when Native SQL accesses the connection explicitly. Conversely, an Open SQL statement can reuse database connections active in Native SQL or AMDP only if their names do not contain any lowercase letters.

Notes

Example

Specifies a database connection in Open SQL.

DATA(conn) = CONV dbcon-con_name( `...` ).

DELETE FROM demo_update CONNECTION (conn).

Native SQL - ADBC

To use a database connection other than the standard connection in ADBC, the static method GET_CONNECTION of the class CL_SQL_CONNECTION can be used. The return value of the method is a reference to a connection object that can be passed to other ADBC objects. Using the input parameter CON_NAME, the method can be passed the name of a secondary connection from the database table DBCON or a service connection. These names are case-sensitive. An additional input parameter, SHARABLE, specifies how active connections can be reused. The activated connection can be used in Open SQL, Native SQL, and AMDP.

A new connection is always activated for the current internal session by opening a new connection or activated an inactive connection of the current work process. No existing active connection of the name passed to CON_NAME is used. A connection activated like this can only be used exclusively using the returned connection object in ADBC.

Once executed, GET_CONNECTION gets a reference to a connection object. The connection object represents the connection activated or reused using GET_CONNECTION in ADBC and can be passed to the instance constructors of the classes CL_SQL_STATEMENT and CL_SQL_PREPARED_STATEMENT. The SQL statements of these classes are then executed on this connection in its database LUW.

Notes

Example

Specifies a database connection in ADBC.

DATA(conn) = CONV dbcon-con_name( `...` ).

NEW cl_sql_statement( con_ref = cl_sql_connection=>get_connection(
                                  con_name = conn
                                  sharable = abap_true )
                     )->execute_update( `DELETE FROM demo_update` ).

Native SQL - EXEC SQL

The following special statements can be used to implement a database connection other than the standard connection in Native SQL embedded between EXEC SQL and ENDEXEC

The statement CONNECT TO can be used to activate a secondary connection or a service connection. Here, the addition AS can be used to open the same connection more than once under different names. The entries made here are case-sensitive.

After the statement CONNECT TO, all subsequent Native SQL statements embedded between EXEC SQL and ENDEXEC in the current internal session use the connection activated or reused using CONNECT TO. The statement SET CONNECTION can be used to redirect to the previously activated connection of the current internal session. Here, a name defined with AS must be specified using this connection. More specifically, the standard connection can be activated by specifying DEFAULT. The statement SET CONNECTION is ignored by the database LUWs of the connections in question.

Note

When a name is specified after AS in the statement CONNECT TO, multiple database connections with the same original name can be activated and used in the same internal session. These are indicated in the output of the program DBCONINFO by the different values in the column Hdl.

Example

Specifies a database connection after EXEC SQL.

DATA(conn) = CONV dbcon-con_name( `...` ).

EXEC SQL.
  CONNECT TO :conn
ENDEXEC.

EXEC SQL.
  DELETE FROM demo_update
ENDEXEC.

AMDP

To use a database connection other than the standard connection to execute an AMDP procedure implementation, the names of service connections can be passed to the input parameter CONNECTION of the associated AMDP method. The names are case-sensitive here. Secondary connections cannot be used.

Note

The service connections that can be used by AMDP are also connections that can be used by Open SQL, Native SQL, and AMDP, as long as the appropriate conditions are met.

Example

Specifies a database connection in AMDP.

DATA(conn) = CONV dbcon-con_name( `R/3*...` ).

TRY.
    NEW cl_demo_amdp_connection(
          )->get_scarr( EXPORTING
                          connection = conn
                          clnt       = sy-mandt
                        IMPORTING
                          carriers = DATA(result) ).
  CATCH cx_amdp_error INTO DATA(amdp_error).
    ...
ENDTRY.

Interaction of Open SQL, Native SQL, and AMDP

A secondary connection or service connection active within an internal session is shared by Open SQL, Native SQL, and AMDP with the following exceptions:

Shared connections are activated as follows:

When a shared connection is activated, it is reused whenever the same internal session is used subsequently in Open SQL, Native SQL, and AMDP, regardless of how it is activated.

Shared connections can be closed using Native SQL, as shown above. The connection can then no longer be used. When a closed connection is specified in Open SQL, it is opened again implicitly. If a closed connection is reused in Native SQL or AMDP an exception is raised.

Note

When sharing database connections, it should be noted that the name of a database connection is always converted to uppercase letters in Open SQL. In Native SQL and AMDP, on the other hand, the name is case-sensitive. To access a database connection activated in Open SQL in Native SQL or AMDP, the connection must be specified in uppercase letters. Conversely, an Open SQL statement cannot use a database connection activated using Native SQL AMDP if its name contains lowercase letters. This mainly affects service connections and names defined using the addition AS of the statement CONNECT TO. Secondary connections, on the other hand, must be specified in uppercase letters in Native SQL too (in accordance with their spelling in the table DBCON).

Example

Uses a shared service connection in ADBC and Open SQL. The modifying statements are executed in a database LUW. If the name of the service connection contains lowercase letters or if the parameter SHARABLE is used with the value abap_false in ADBC, separate connections with two different database LUWs would be produced. Accessing the same database table would then usually cause a lock situation.

DATA(conn) = CONV dbcon-con_name( `R/3*DEMO` ).

NEW cl_sql_statement( con_ref = cl_sql_connection=>get_connection(
                                  con_name = conn
                                  sharable = abap_true )
                     )->execute_update( `DELETE FROM demo_update` ).

INSERT demo_update CONNECTION (conn)
  FROM @( VALUE #( id = 'X'  col1 = 1 col2 = 2 col3 = 3 col4 = 4 ) ).

Database Connections and Transactions

Every active database connection creates a separate transaction context or is associated with its own database LUW. This means that database changes on one connection can be committed or rolled back independently of changes on other database connections. In this way, for example, log data can be saved and committed on a secondary connection without modifying the database LUW of the standard connection.

Database changes can be committed or rolled back as follows for database connections:

The statements COMMIT WORK and ROLLBACK WORK work similarly, as do the implicit database commits and database rollbacks, which are applied to all active connections.

A secondary connection or service connection is always given the state inactive when its database changes are committed or rolled back. A connection made inactive in this way can, however, continue to be used in the same internal session without being opened explicitly, not just in Open SQL, but also in Native SQL or AMDP. The first time an inactive secondary connection or service connection previously active in the current internal session is reused, it is made active again and a new database LUW is opened.

Notes

Example

If the statement COMMIT CONNECTION default were not used, the following program section would produce a lock situation. This because the standard connection (Open SQL here) and a service connection (Native SQL here) would be used in independent database LUWs to access the same row of a database table.

INSERT demo_update FROM @( VALUE #( id = 'X' ) ).
DELETE FROM demo_update.
COMMIT CONNECTION default.

DATA conn TYPE dbcon-con_name VALUE 'R/3*DEMO'.
EXEC SQL.
  CONNECT TO :conn
ENDEXEC.
EXEC SQL.
  INSERT INTO demo_update VALUES( :sy-mandt, 'X', 1, 2, 3, 4 )
ENDEXEC.