ABAP Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP Database Accesses →  Native SQL →  EXEC SQL - Embedded Native SQL →  EXEC SQL 

EXEC SQL - CONNECT

A connection must be defined with the database before Native SQL statements can be used. When AS ABAP is started, 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. These connections can be accessed in 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.

Other versions: 7.31 | 7.40 | 7.54

Note

When the work process is switched, followed by an implicit database commit, then the commit is executed on all open connections.

Open Connection

Syntax

EXEC SQL. 
  CONNECT TO dbs [AS con] 
ENDEXEC. 

Effect

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-like 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

Syntax

EXEC SQL. 
  SET CONNECTION {con|DEFAULT} 
ENDEXEC. 

Effect

This static Native SQL statement sets the current connection for all following static Native SQL statements. For con, a literal or a character-like host variable can be specified 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

Syntax

EXEC SQL. 
  GET CONNECTION :con 
ENDEXEC. 

Effect

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

Syntax

EXEC SQL. 
  DISCONNECT con 
ENDEXEC. 

Effect

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, a literal or a character-like host variable can be specified 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

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 
       WHERE con_name = @dbs 
       INTO @dbtype. 

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( ).