ARTICLE
Open SQL - Secondary Database Connections
You can use the CONNECTION addition to execute Open SQL
commands on databases other than the R/3 standard database. This gives
you a number of possibilities, for example, you can transfer and update
data in other databases. It is irrelevant whether the other database
belongs to a AS ABAP . However, it
is a prerequisite that it is a database system supported by SAP, because
only in such a system is the software needed for an Open SQL
access available in form of a shared library.
Setting Up a Secondary Database Connection
To a access another database you need an entry in the table
DBCON that describes the access
data of the database. If the secondary database is from a different
manufacturer than the database of the current
AS ABAP , you will need the SAP
shared library as well as the client software provided by the
manufacturer. Information on creating the DBCON entry and
installing the additional software is described in the standard SAP
Notes for every supported database system.
On AS ABAP you can create and change entries in the DBCON
database table using the central tool
DBA Cockpit .
Standard Database Connection
Every work process has a standard database connection to the SAP
standard database. It is communally used by all internal sessions. You
can also explicitly access this database connection using the name
DEFAULT . The DEFAULT connection can also be specified
dynamically using CONNECTION (name) , where the name field
must have the value 'DEFAULT' .
Accessing Other Database Tables
If you use a database connection to access database tables that are not
in the SAP standard database, there must also be a database table with
the same name and identical type in the ABAP Dictionary of the
local AS ABAP . Open SQL assumes that the type information
of these "remote" database tables corresponds exactly with that of the
local database table. This prerequisite is essential for the correct
interpretation of the database contents, for example, and, if necessary,
their conversion with regards to the ABAP type of the target fields (see
INTO Clause ). If this prerequisite is
not met, this can lead to wrong data or runtime errors when reading or
writing, depending on the database system. 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.
Database Connections and Transactions
Every database connection forms its own transaction context. This means
that database changes on one connection can be saved (using
COMMIT ) or discarded (using
ROLLBACK ) independently of changes on other database
connections. You can, for example, commit and store protocol data on a
secondary database connection without affecting the current
transaction running on the standard SAP
database connection.
Secondary database connections are not known outside the limits of an
internal session. So, if a program opens a database connection and then
calls another program - with SUBMIT for
example - that opens a connection to the same database, you have two
different connections and therefore two different database transactions.
For the first Open SQL command that requests a specific database
connection, the system opens a corresponding connection. All subsequent
commands (in the same internal session) for the same "remote" database
use the same database connection and all form a database transaction.
The transaction is ended by:
A COMMIT CONNECTION or
ROLLBACK CONNECTION on this connection
A COMMIT WORK or
ROLLBACK WORK
A database commit or
database rollback on this database
connection in Native SQL ,
An implicit commit by a screen change, specifically the statements
CALL SCREEN ,
CALL DIALOG , CALL TRANSACTION ,
MESSAGE
A Remote Function Call RFC
, specifically the statements
CALL FUNCTION ... DESTINATION ,
CALL FUNCTION ... STARTING NEW TASK ,
CALL FUNCTION ... IN BACKGROUND
, and WAIT UNTIL .
In summary, a database transaction is completed at the latest, when the
application program reaches a state in which a change of work process
could occur.
Note:
Working with parallel database connections, that is parallel
transactions, can lead to lock situations that only one work process is
involved in: A program changes a database row on the first connection
and tries to change the same row on a second connection. This will
result in the program waiting for the lock of the first transaction,
without this first transaction ever being able to continue. You can only
resolve this situation by ending the work process. This is done
automatically for dialog processes, but must be done manually for
background jobs. You should therefore not change the same table in one
program on multiple database connections.
Interaction with Native SQL Connections .
In addition to the above, you can also explicitly open a connection
using a CONNECT with Native SQL
. Native SQL also allows you to explicitly close and
disconnect a database connection using DISCONNECT . In this case,
the database transaction in question is closed and corresponding
database changes are discarded. Using the method GET_CONNECTION -
belonging to the ADBC class
CL_SQL_CONNECTION or to the
Native SQL statement GET CONNECTION
- you can configure the current Native SQL connection. With
Native SQL you can also access a connection that was
opened with Open SQL . In this case it is sufficient to convert
the active Native SQL connection using the method SET
CONNECTION or the statement SET CONNECTION . It is not
necessary to open it using CONNECT TO .
Management of Database Connections
The database connections are automatically managed by the runtime
system. If a transaction is saved on a database connection (
COMMIT ) or rolled back (
ROLLBACK ), it can be reused by the runtime system. You can open a
maximum of 10 database connections for each work process. On certain
databases, you may not be able to reach this number.
Documentation extract taken from SAP system, � Copyright SAP AG. All rights reserved