ABAP Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP - Database Accesses →  ADBC - ABAP Database Connectivity →  Examples of ADBC 

ADBC, DDL, and DML

The example demonstrates the use of the DDL- and DML statements with ADBC.

Other versions: 7.31 | 7.40 | 7.54

Source Code

REPORT demo_adbc_ddl_dml.

PARAMETERS  p_name TYPE c LENGTH 10 DEFAULT 'mytab'.
SELECTION-SCREEN SKIP.
PARAMETERS: p_create RADIOBUTTON GROUP grp,
            p_insert RADIOBUTTON GROUP grp,
            p_select RADIOBUTTON GROUP grp,
            p_drop   RADIOBUTTON GROUP grp.
SELECTION-SCREEN SKIP.
PARAMETERS  p_key TYPE i DEFAULT 1.

CLASS adbc DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS main.
  PRIVATE SECTION.
    CLASS-DATA: dbname TYPE string,
                sql TYPE REF TO cl_sql_statement,
                wa1 TYPE c LENGTH 10,
                wa2 TYPE c LENGTH 10,
                err TYPE REF TO cx_sql_exception .
    CLASS-METHODS: create RAISING cx_sql_exception,
                   insert RAISING cx_sql_exception,
                   select RAISING cx_sql_exception,
                   drop   RAISING cx_sql_exception.
ENDCLASS.

CLASS adbc IMPLEMENTATION.
  METHOD main.
    dbname = 'ABAP_DOCU_DEMO_' && p_name.
    TRY.
        CREATE OBJECT sql.
        IF p_create = 'X'.
          create( ).
          MESSAGE 'Create was successful' TYPE 'S'.
        ELSEIF p_insert = 'X'.
          insert( ).
          MESSAGE 'Insert was successful' TYPE 'S'.
        ELSEIF p_select = 'X'.
          select( ).
          MESSAGE 'Select was successful' TYPE 'S'.
        ELSEIF p_drop   = 'X'.
          drop( ).
          MESSAGE 'Drop was successful' TYPE 'S'.
        ENDIF.
      CATCH cx_sql_exception INTO err.
        MESSAGE err TYPE 'I' DISPLAY LIKE 'E'.
    ENDTRY.
  ENDMETHOD.
  METHOD create.
    sql->execute_ddl(
      `CREATE TABLE ` && dbname   &&
      `( val1 char(10) NOT NULL,` &&
      `  val2 char(10) NOT NULL,` &&
      `  PRIMARY KEY (val1) )` ).
  ENDMETHOD.
  METHOD insert.
    DO 100 TIMES.
      wa1 = sy-index.
      wa2 = sy-index ** 2.
      sql->execute_update(
       `INSERT INTO ` && dbname && ` ` &&
       `VALUES ('` && wa1 && `','` && wa2 && `')` ).
    ENDDO.
  ENDMETHOD.
  METHOD select.
    DATA: result TYPE REF TO cl_sql_result_set,
          msg    TYPE c LENGTH 30,
          key    TYPE c LENGTH 10,
          dref   TYPE REF TO data,
          rc     TYPE i.
    key = p_key.
    result = sql->execute_query(
      `SELECT val1, val2 ` &&
      `FROM ` && dbname && ` ` &&
      `WHERE val1 = ` && `'` && key && `'` ).
    GET REFERENCE OF wa1 INTO dref.
    result->set_param( dref ).
    GET REFERENCE OF wa2 INTO dref.
    result->set_param( dref ).
    rc = result->next( ).
    IF rc > 0.
      WRITE: 'Result:' TO msg,
             wa1 TO msg+10,
             wa2 TO msg+20.
    ELSE.
      msg = 'No entry found'.
    ENDIF.
    result->close( ).
    MESSAGE msg TYPE 'I'.
  ENDMETHOD.
  METHOD drop.
    sql->execute_ddl(
     `DROP TABLE ` && dbname ).
  ENDMETHOD.
ENDCLASS.

START-OF-SELECTION.
  adbc=>main( ).

Description

The program is the dynamic equivalent of Native SQL, for example. By using ADBC you can choose the name of the database. Instead of being executed statically between EXEC and ENDEXEC, the SQL statements are dynamically passed as string expressions to methods of the CL_SQL_STATEMENT class.

Inserting rows that already exist leads to an exception and need not be handled using sy-subrc.