This blog is a prepublication of that documentation.
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 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.
When Open SQL and Native SQL are 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.
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.
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.
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).
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 or Native SQL, 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.
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, this number cannot always be reached. If more than 16 database connections are opened, the runtime error DBSQL_NO_MORE_CONNECTION occurs.
Open SQL and Native SQL can request secondary connections or service connections. A secondary connection or a service connection is requested as follows:
A secondary connection or service connection is closed explicitly in Native SQL using the following:
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.
An open secondary connection or service connection can be active or inactive. Once opened in Open SQL or Native SQL, a database connection is active and can be used by Open SQL or Native SQL. 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:
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).
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 and Native SQL within an internal session. In Native SQL, 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 in both Open SQL and Native SQL without being requested explicitly.
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.
DELETE FROM demo_update CONNECTION r/3*demo.
SUBMIT ... AND RETURN.
SELECT *
FROM demo_update
INTO TABLE @DATA(itab)
CONNECTION r/3*demo.
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.
Secondary connections and service connections are opened and used in different ways in Open SQL and Native SQL. A secondary or service connection active for an internal session can be shared by Open SQL and Native SQL. Connections used by Open SQL, however, cannot have any lowercase letters in their names and ADBC can reserve connections for itself exclusively.
The following additions can be used to use a database connection other than the standard connection in Open SQL:
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 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).
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 and in Native SQL.
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` ).
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 both Open SQL and Native SQL, 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.
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
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.
A secondary connection or service connection active within an internal session is shared by Open SQL and Native SQL, with the following exceptions:
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, 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, on the other hand, the name is case-sensitive. To access a database connection activated in Open SQL, the connection must be specified in uppercase letters in Native SQL. Conversely, an Open SQL statement cannot use a database connection activated using Native SQL 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 ) ).
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, both by Open SQL and Native SQL, without being opened explicitly. 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.
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 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 and Native SQL are 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.
- By default, both Open SQL and Native SQL use the standard connection to access the ABAP database schema of the standard AS ABAP database.
- In all places where a database connection can be specified explicitly in ABAP, the standard connection can also be specified using the predefined name DEFAULT.
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.
Notes
- Secondary connections can address any number of database schemas in the standard database or databases other than the standard database as secondary databases. An AS ABAP can, for example, access the SAP HANA database of an independently operated SAP HANA appliance.
- One prerequisite for a secondary database is that it must be a database system supported by SAP. Only this system has software for Open SQL and Native SQL access (as a shared library).
- If the secondary database is a database system other than the standard database of the current AS ABAP, the Database Shared Library (DBSL) provided for this database by SAP and client software provided by the database vendor must be installed here in front of the access through a secondary connection from the table DBCON. Information about creating the DBCON entry and installing the additional software is described in the standard SAP Notes for every supported database system.
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 or Native SQL, it is opened for the current work process (if possible) or an existing inactive service connection with the same name is reused.
Notes
- The definable name is not associated with the entries in the database table DBCON and is used only to identify a service connection. Hence, specifying a database connection from the database table DBCON for name does not mean that this database connection is used.
- Service connections to the SAP standard database are useful for performing operations in a database LUW, which does not depend on the LUW of the standard connection. It should be noted that the current isolation level is responsible for determining whether a read from a database LUW can access data modified in a different database LUW, before this data is committed using a database commit.
- Like secondary connections, service connections add to the number of open connections of the current work process. There can be a maximum of 16 open connections, including the standard connection itself.
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, this number cannot always be reached. 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. A secondary connection or a service connection is requested as follows:
- Implicitly in Open SQL by specifying the name of the connection after the addition CONNECTION
- Explicitly and implicitly in Native SQL using the following
- The method GET_CONNECTION of the ADBC class CL_SQL_CONNECTION
- Using the input parameter CONNECTION of AMDP procedure implementations (for service connections only)
- The statement CONNECT TO after EXEC SQL
A secondary connection or service connection is closed explicitly in Native SQL using the following:
- The method CLOSE of the ADBC class CL_SQL_CONNECTION
- The statement DISCONNECT after EXEC SQL
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 or Native SQL, a database connection is active and can be used by Open SQL or Native SQL. 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:
- Using the statements COMMIT CONNECTION or ROLLBACK CONNECTION for this connection.
- Using the Native SQL statements COMMIT WORK or ROLLBACK WORK after EXEC SQL for this connection
- Using the methods COMMIT and ROLLBACK of the ADBC class CL_SQL_CONNECTION for this connection
- Using the statements COMMIT WORK and ROLLBACK WORK for all connections
- In an implicit database commit or database rollback for all connections
- When closing the internal session in which the connection was opened (here a COMMIT CONNECTION is executed for the connection implicitly)
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 and Native SQL within an internal session. In Native SQL, 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 in both Open SQL and Native SQL without being requested explicitly.
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:
- Identically named connections are opened in called programs
- Connections are opened in ADBC, where the value abap_false is used for the parameter SHARABLE of the method GET_CONNECTION of the class CL_SQL_CONNECTION
- Connections are opened using CONNECT TO after EXEC SQL, where AS is used to specify an explicit name
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 and Native SQL. A secondary or service connection active for an internal session can be shared by Open SQL and Native SQL. 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:
- The addition CONNECTION for read statements
- The identical addition CONNECTION for write statements
- If the specified connection is not yet active in the current internal session, it is either opened and given the state active or an open inactive connection is reused and given the state active (if a connection exists for the current work process). When the connection is activated, a database LUW is opened for it and this can be used by Open SQL and Native SQL.
- If the specified connection is already active in the current internal session, the Open SQL statement uses this connection and works in its database LUW. The connection can be activated using Open SQL or Native SQL.
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 only if their names do not contain any lowercase letters.
Notes
- The type of a database object specified in an Open SQL statement using the addition CONNECTION does not necessarily need to match the type of the database object with the same name in the secondary database. For example, a view with the same name in the secondary database can be accessed by specifying a database table (or a database table by specifying a view) if they have the same structure.
- The prerequisite that an identically named database object with a suitable structure must exist in ABAP Dictionary in the current system for views accessed using a secondary connection is particularly important for external views.
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 and in Native SQL.
- Passing abap_true to SHARABLE:
- If the connection passed to CON_NAME is not yet active in the current internal session, it is either opened and given the state active or an open inactive connection is reused and given the state active (if a connection exists for the current work process). When the connection is activated, a database LUW is opened for it.
- If the connection passed to CON_NAME is already active in the current internal session, this connection and its database LUW are used. The connection can be activated using Open SQL or Native SQL.
- Passing abap_false to SHARABLE (default value):
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
- abap_false can be used for the parameter SHARABLE of the method GET_CONNECTION to specify the use of a database connection and its database LUW explicitly. Database connections that are activated differently are usually available to all accesses in Open SQL and Native SQL.
- If abap_false is passed to the parameter SHARABLE of the method GET_CONNECTION then multiple database connections with the same name can be activated and used within a single internal session. These are indicated in the output of the program DBCONINFO by the different values in the column Hdl.
- When opening exclusive connections for connection objects, the maximum number of connections for each work process must not be exceeded. Exclusive connection should be closed explicitly once they are no longer needed and if there are a large number of connection objects.
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 – 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.
- If the passed connection is not yet active in the current internal session, it is either opened and given the state active or an open inactive connection is reused and given the state active (if a connection exists for the current work process). When the connection is activated, a database LUW is opened for it and this can be used by Open SQL and Native SQL.
- If the specified connection is already active in the current internal session, the procedure implementation is called using this connection and works in its database LUW. The connection can be activated using Open SQL or Native SQL.
Note
The service connections that can be used by AMDP are also connections that can be used by both Open SQL and Native SQL, 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.
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
- CONNECT TO
- SET CONNECTION
- If no name is specified using AS, the following applies:
- If the connection specified after CONNECT TO is not yet active in the current internal session, it is either opened and given the state active or an open inactive connection is reused and given the state active (if a connection exists for the current work process). When the connection is activated, a database LUW is opened for it and this can be used by Open SQL and Native SQL.
- If the connection specified after CON_NAME is already active in the current internal session, this connection and its database LUW are reused. The connection can be activated using Open SQL or Native SQL.
- If a name is specified using AS, the following applies:
- If the connection specified after CONNECT TO is not yet active under this name in the current internal session, it is either opened and given the state active or an open inactive connection is reused and given the state active (if a connection exists for the current work process). When the connection is activated, a database LUW is opened for it. A connection with a name defined using AS is only used by Native SQL embedded between EXEC SQL and ENDEXEC. Open SQL and other kinds of Native SQL do not use this type of connection.
- If the connection specified after CON_NAME is already active under this name in the current internal session, this connection and its database LUW are reused. The connection can only have been activated using CONNECT TO.
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.
Interaction of Open SQL and Native SQL
A secondary connection or service connection active within an internal session is shared by Open SQL and Native SQL, with the following exceptions:
- A connection activated using ADBC that uses the value abap_false for the parameter SHARABLE of the method GET_CONNECTION of the class CL_SQL_CONNECTION can only be used exclusively using the associated connection object.
- A connection activated after EXEC SQL with CONNECT TO using a name specified after AS can only be used between EXEC and ENDEXEC.
- By using the CONNECTION addition of Open SQL statements to activate the connection
- By using the method GET_CONNECTION of the ADBC class CL_SQL_CONNECTION to activate the connection. The valueabap_true is used for the parameter SHARABLE here.
- By passing the name to the input parameter CONNECTION of an AMDP method to activate the connection.
- By using the statement CONNECT TO after EXEC SQL to activate the connection, without specifying a name after AS.
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, 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, on the other hand, the name is case-sensitive. To access a database connection activated in Open SQL, the connection must be specified in uppercase letters in Native SQL. Conversely, an Open SQL statement cannot use a database connection activated using Native SQL 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 CONNECTION and ROLLBACK CONNECTION trigger targeted database commitss or database rollbacks on specific connections.
- In ADBC, the instance methods COMMIT and ROLLBACK of the class CL_SQL_CONNECTION trigger database commits or database rollbacks for the connection of the current connection object.
- In static Native SQL, the statements COMMIT WORK and ROLLBACK WORK after EXEC SQL trigger database commits or database rollbacks for the current connection.
- When a database connection is made inactive implicitly at the end of an internal session, a database commit is triggered.
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, both by Open SQL and Native SQL, without being opened explicitly. 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
- All Open SQL and Native SQL reads made on a shared active database connection in an internal session are applied in the same database LUW.
- The static Native SQL statement SET CONNECTION is ignored by the database LUWs of the connections in question.
- Secondary connections and service connections in an internal session cannot be used in called programs, which means that a called program always activates its own connection and hence its own database LUW, even if the same connection name is used.
- Secondary connections, and more specifically service connections, can be used to implement independent database LUWs on a secondary database or on the standard database. Care must be taken to avoid lock situations here.
- Working with multiple database connections, namely independent database LUWs, can produce lock situations in which only one work process is involved: A program changes a database row on the first connection and tries to change the same row on a second connection. This results in the program waiting for the lock of the first database LUW, without this first LUW ever being able to continue. This situation can only be resolved by ending the work process. This is done automatically for dialog processes, but must be done manually for background jobs. It is therefore not advisable to change the same table within the a single program using multiple database connections.
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.
No comments:
Post a Comment