EXEC SQL - CONNECT
A connection must be defined with the database before
Native SQL statements can be used.
When you start AS ABAP , a standard
connection from the database interface to the central AS ABAP
database is opened. This connection is defined as the current connection
for embedded Native SQL statements and as a standard connection
for Open SQL statements when an ABAP
program is started. Use the following SAP-specific Native SQL
statements, to start additional connections to other database systems.
You can access these connections in the static Native SQL .
The possible connections between AS ABAP and the database
systems are defined in the database table
DBCON . Entries in the database table DBCON can be
created and modified using the DBA
Cockpit tool.
Latest notes: When the work process is switched, followed by an
implicit database commit , then the commit
is executed on all open connections.
Open Connection
ABAP Syntax EXEC SQL.
CONNECT TO dbs [AS con]
ENDEXEC.
What does it do? This static Native SQL statement opens a
connection to the database system dbs and makes this the current
connection. In other words, all the following Native SQL
statements work with the database system named in dbs . If a
connection to the specified database system already exists, then this is
used; otherwise, a new connection is set up.
dbs can be specified as a literal or as a
host variable containing a name from the column CON_NAME in
the database table DBCON . The
database system listed there must be supported by SAP and the technical
data for the connection must be entered. The column DBMS in the
database table DBCON contains an abbreviation for the type of
database system.
The addition AS can be used to specify a name con for
the connection. A literal or a character-type host variable can be
specified for con . Its content is used as its name. The
connection can then be selected using this name in the static
Native
SQL statement SET CONNECTION .
Selecting a Connection
ABAP Syntax EXEC SQL.
SET CONNECTION {con|DEFAULT}
ENDEXEC.
What does it do? This static Native SQL statement sets the current
connection for all following static Native SQL statements. For
con , you can specify a literal or a character-like host variable
that must contain the name of a connection already started. The name of
the connection can be specified either as the database system from the
database table DBCON , as given in the static Native SQL
statement CONNECT TO , or as the name assigned there using the
AS addition. DEFAULT is used to set the standard
connection to the central database system of the current AS ABAP .
Determining the Connection
ABAP Syntax EXEC SQL.
GET CONNECTION :con
ENDEXEC.
What does it do? This static Native SQL statement assigns the name
of the current connection to con . con expects a
character-like host variable. If the connection has been set up using
the static Native SQL statement CONNECT TO and a name was
given to the connection using AS , then this name is assigned. If
the connection was set up without using a name assignment, the name of
the database system from the database table DBCON is used. If the
current connection is the standard connection to the central database of
the AS ABAP , con is
assigned the value "DEFAULT" .
Close connection
ABAP Syntax EXEC SQL.
DISCONNECT con
ENDEXEC.
What does it do? This Native SQL statement closes the connection
con . If con is not the current connection, this is not
affected. If con is the current connection, the standard
connection to the central database of the AS ABAP is
simultaneously set as the current connection for all following static
Native SQL statements.
For con , you can specify a literal or a character-like host
variable that must contain the name of a connection already started. If
a name was assigned while starting the connection using the static
Native SQL statement CONNECT TO with the addition AS ,
then this name must be used. Otherwise, the name of the database system
from the DBCON database table must be used. The standard
connection "DEFAULT" cannot be closed.
Example ABAP Coding Starts a connection to an Oracle database and
imports all entries of a column in the database table SCARR .
PARAMETERS dbs TYPE dbcon-con_name.
DATA carrid_wa TYPE scarr-carrid.
DATA dbtype TYPE dbcon_dbms.
SELECT SINGLE dbms
FROM dbcon
INTO dbtype
WHERE con_name = dbs.
IF dbtype = 'ORA'.
TRY.
EXEC SQL.
CONNECT TO :dbs
ENDEXEC.
IF sy-subrc <(><<)>> 0.
RAISE EXCEPTION TYPE cx_sy_native_sql_error.
ENDIF.
EXEC SQL.
OPEN dbcur FOR
SELECT carrid
FROM scarr
WHERE mandt = :sy-mandt
ENDEXEC.
DO.
EXEC SQL.
FETCH NEXT dbcur INTO :carrid_wa
ENDEXEC.
IF sy-subrc <(><<)>> 0.
EXIT.
ELSE.
cl_demo_output=>write( |{ carrid_wa }| ).
ENDIF.
ENDDO.
EXEC SQL.
CLOSE dbcur
ENDEXEC.
EXEC SQL.
DISCONNECT :dbs
ENDEXEC.
CATCH cx_sy_native_sql_error.
MESSAGE `Error in Native SQL.` TYPE 'I'.
ENDTRY.
ENDIF.
cl_demo_output=>display( ).
Documentation extract taken from SAP system, � Copyright SAP AG. All rights reserved