ABAP Database Connectivity

Use ABAP Database Connectivity (ADBC) to select data. ADBC is an API for the Native SQL interface of the AS ABAP that is based on ABAP Objects and is recommended over EXEC SQL. Reference: https://help.sap.com/http.svc/rc/abapdocu_750_index_htm/7.50/en-US/abenadbc.htm

 data: go_exc   type ref to cx_sql_exception,  
      go_conn  type ref to cl_sql_connection,
      c_dbs type dbcon-con_name value 'DBNAME'.    
    
 try.  
    go_conn = cl_sql_connection=>get_connection( con_name = c_dbs ).  
   catch cx_sql_exception into go_exc.  
    gv_text = |{ text-e03 } { c_dbs }|.  
    message gv_text type 'S' display like 'E'.  
    leave list-processing.  
 endtry.  
   
   

EXEC SQL

Select data from non-SAP system DB.

1) Create connection entry in table DBCON, you will need to have a DB userid and password and the connection string.
con_name = descriptive name
dbms = mss
user_name = sql_userid
password = ****
con_env = MSSQL_SERVER=mather-mri-002 MSSQL_DBNAME=MRIAccess

ABAP:
2) Establish connection:
data: con_name like dbcon-con_name value 'MRI'.
* Connect to external DB
EXEC SQL.
CONNECT TO :con_name
ENDEXEC.
EXEC SQL.
SET CONNECTION :con_name
ENDEXEC.
IF sy-subrc <> 0.
MESSAGE e009 WITH 'Unable to connect to' con_name.
ENDIF.

3) Sample Code to Select Data:

* Drop subquery tables
EXEC SQL.
IF EXISTS(SELECT TABLE_NAME FROM MRIACCESS.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'ZSAP_ACCTN')
DROP TABLE MRIACCESS..ZSAP_ACCTN
IF EXISTS(SELECT TABLE_NAME FROM MRIACCESS.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'ZSAP_SAPCO')
DROP TABLE MRIACCESS..ZSAP_SAPCO
IF EXISTS(SELECT TABLE_NAME FROM MRIACCESS.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'ZSAP_ENTIT')
DROP TABLE MRIACCESS..ZSAP_ENTIT
ENDEXEC.

* Create subquery tables
EXEC SQL.
CREATE TABLE MRIAccess..ZSAP_ACCTN ( acctn VARCHAR(9))
CREATE TABLE MRIAccess..ZSAP_SAPCO ( sapco VARCHAR(4))
CREATE TABLE MRIAccess..ZSAP_ENTIT ( entit VARCHAR(6))
ENDEXEC.
IF sy-subrc <> 0.
MESSAGE e009 WITH 'Failed to create subquery tables'.
ENDIF.
* Populate subquery tables
LOOP AT s_acctn.
EXEC SQL.
insert into MRIAccess..ZSAP_ACCTN values (:s_acctn-low)
ENDEXEC.
IF sy-subrc <> 0.
MESSAGE e009 WITH 'Failed to populate subquery tables'.
ENDIF.
ENDLOOP.
LOOP AT s_sapco.
EXEC SQL.
insert into MRIAccess..ZSAP_SAPCO values (:s_sapco-low)
ENDEXEC.
IF sy-subrc <> 0.
MESSAGE e009 WITH 'Failed to populate subquery tables'.
ENDIF.
ENDLOOP.
LOOP AT s_entit.
EXEC SQL.
insert into MRIAccess..ZSAP_ENTIT values (:s_entit-low)
ENDEXEC.
IF sy-subrc <> 0.
MESSAGE e009 WITH 'Failed to populate subquery tables'.
ENDIF.
ENDLOOP.

* Now select data from extrnal DB
* All records not extracted
EXEC SQL PERFORMING append_itab.
SELECT g.acctnum, e.saporder, e.sapco, e.EntityID, j.entrdate,
j.amt, j.descrpn, j.ref, j.Item into :itab
FROM
MRIPROD..ENTITY e
INNER JOIN MRIPROD..JOURNAL j ON j.ENTITYID = e.ENTITYID
INNER JOIN MRIPROD..GACC g ON j.ACCTNUM = g.ACCTNUM
WHERE
j.source = :p_sourc AND
g.acctnum NOT IN (select acctn from MRIAccess..zsap_acctn) AND
e.sapco NOT IN (select sapco from MRIAccess..zsap_sapco) AND
e.entityid NOT IN (select entit from MRIAccess..zsap_entit) AND
j.basis = :p_basis AND
j.dtsapul IS NULL
ENDEXEC.
IF p_update = 'X'. "Update
EXEC SQL.
UPDATE MRIPROD..JOURNAL
set dtSAPUL = :v_now
FROM
MRIPROD..ENTITY e
INNER JOIN MRIPROD..JOURNAL j ON j.ENTITYID = e.ENTITYID
INNER JOIN MRIPROD..GACC g ON j.ACCTNUM = g.ACCTNUM
WHERE
j.source = :p_sourc AND
g.acctnum NOT IN (select ACCTN from MRIACCESS..ZSAP_ACCTN) AND
e.sapco NOT IN (select sapco from MRIAccess..zsap_sapco) AND
e.entityid NOT IN (select entit from MRIAccess..zsap_entit) AND
j.basis = :p_basis AND
j.dtsapul IS NULL
ENDEXEC.
ENDIF.
*&---------------------------------------------------------------------*
*& Form append_itab
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM append_itab.
APPEND itab.
ENDFORM. " append_itab

4) Close or reset connection
** Reset to "default connection"
EXEC SQL.
SET CONNECTION DEFAULT
ENDEXEC.