External Database Connection with ABAP

It is sometimes necessary to connect to an external database in SAP. After the database connections are made using the DBACOCKPIT transaction code, external databases can be accessed via ABAP, and queries can be written.

First, the connection will be prepared using DBACOCKPIT.

This example is intended for Microsoft SQL Server.

1. A name should be given to the connection. This name will be used when connecting to the database via ABAP.
2. The database to be connected to will be selected.
3. The username to be used in the connection will be provided.
4. The password to be used in the connection, along with a field to re-enter it for confirmation, will be entered.
5. The host address (either IP address or domain) of the database will be specified.
6. The name of the database to be connected will be indicated.
7. This can be the same as the value entered in the DB field.

After the connection is completed as outlined above, queries can be written using the example codes below.

Pay attention to what is written between exec sql and endexec statements, as they may cause dumps and syntax errors.

data connection_name type dbcon-con_name value 'ConnectionName'.

types : begin of ts_customer,
          name    type string,
          surname type string,
          phone   type string,
          email   type string,
        end of ts_customer.

data : ls_customer  type ts_customer,
       lt_customers type standard table of ts_customer.

try.
    exec sql.
      connect to :connection_name
    endexec.

  catch cx_sy_native_sql_error into data(lx_sy_native_sql_error).
    data(lv_err_text)  = lx_sy_native_sql_error->get_text( ).
endtry.

data(lv_status) = 'Active'.

exec sql.

  OPEN dbcur for

  SELECT C.NAME,
         C.SURNAME,
         C.PHONE,
         C.EMAIL
    FROM [dbo].[Customers] as C
   WHERE C.STATUS = :lv_status

endexec.

do.
  exec sql.
    FETCH NEXT dbcur INTO: ls_customer-name,
                           ls_customer-surname,
                           ls_customer-phone,
                           ls_customer-email
  endexec.

  if sy-subrc ne 0.
    exit.
  endif.

  append ls_customer to lt_customers.
  clear: ls_customer.

enddo.

exec sql.
  CLOSE dbcur
endexec.