Unit tests play a big role in modern software development. Unit tests let us check parts of our code for correctness very quickly within seconds. Independence of unit tests on any external dependencies means independence on any external environments like network, file system, database etc

Most of SAP developments work with database and we need a good isolation technique to test them. ABAP SQL Test Double Framework in my opinion is one of the best implementations ever. It lets you create something like a virtual database with temporary data in any table, Open SQL selects and updates work with that data in test mode.

But unfortunately ABAP SQL Test Double Framework is available starting from 7.51 version. I work in company where most of project use releases 7.50 and lower and I cannot use it.

So I decided to create customer project that is similar to the standard framework.

The goals of project are:

• Support most of Open SQL syntax
• Minimum code adaptation effort to make code testable
• Support many Open SQL constructions like bind variables, select for all entries, subqueries etc

The project is called zsql_test_double_framework and it can be downloaded here.

The main difference and inconvenience is the need to write Open SQL dynamically in string variables. But support of most Open SQL constructions lets just to put your Open SQL statements in brackets without deep code refactoring.

Let’s go to the example.

Suppose we have an ABAP report that selects some data from database and displays data in ALV grid.

In the beginning report does not have any unit tests at all, the code is below.

TYPES: BEGIN OF ty_grid_line,
         carrname   TYPE scarr-carrname,
         countryfr  TYPE spfli-countryfr,
         cityfrom   TYPE spfli-cityfrom,
         airpfrom   TYPE spfli-airpfrom,
         countryto  TYPE spfli-countryto,
         cityto     TYPE spfli-cityto,
         airpto     TYPE spfli-airpto,
         fldate     TYPE sflight-fldate,
         price      TYPE sflight-price,
         currency   TYPE sflight-currency,
         paymentsum TYPE sflight-paymentsum,
       END OF ty_grid_line.

TYPES ty_grid TYPE STANDARD TABLE OF ty_grid_line WITH KEY carrname.

DATA: carrid TYPE scarr-carrid,
      fldate TYPE sflight-fldate.

SELECT-OPTIONS: s_carrid FOR carrid,
                s_fldate FOR fldate.

CLASS lcl_database_reader DEFINITION.
  PUBLIC SECTION.
    METHODS: read_data IMPORTING it_select_carrid TYPE typ_r_carrid
                                 it_select_fldate TYPE typ_r_fldate
                       EXPORTING et_data          TYPE ty_grid.
ENDCLASS.

CLASS lcl_application DEFINITION.
  PUBLIC SECTION.
    METHODS: start_of_selection.
ENDCLASS.

CLASS lcl_database_reader IMPLEMENTATION.
  METHOD read_data.

    SELECT scarr~carrname
           spfli~countryfr
           spfli~cityfrom
           spfli~airpfrom
           spfli~countryto
           spfli~cityto
           spfli~airpto
           sflight~fldate
           sflight~price
           sflight~currency
           sflight~paymentsum
      FROM sflight
      JOIN scarr ON scarr~carrid = sflight~carrid
      JOIN spfli ON spfli~carrid = sflight~carrid
                AND spfli~connid = sflight~connid
      INTO CORRESPONDING FIELDS OF TABLE et_data
      WHERE sflight~carrid IN it_select_carrid
        AND sflight~fldate IN it_select_fldate.
  ENDMETHOD.
ENDCLASS.

CLASS lcl_application IMPLEMENTATION.
  METHOD start_of_selection.
    DATA: lo_reader        TYPE REF TO lcl_database_reader,
          lt_data_for_grid TYPE ty_grid,
          lo_alv           TYPE REF TO cl_salv_table,
          lo_error         TYPE REF TO cx_root,
          lv_error_text    TYPE string.

    CREATE OBJECT lo_reader.
    lo_reader->read_data( EXPORTING it_select_carrid = s_carrid[]
                                    it_select_fldate = s_fldate[]
                          IMPORTING et_data          = lt_data_for_grid ).

    TRY.
        cl_salv_table=>factory( IMPORTING r_salv_table = lo_alv
                                CHANGING  t_table      = lt_data_for_grid ).

        lo_alv->display( ).
      CATCH cx_root INTO lo_error.
        lv_error_text = lo_error->get_text( ).
        MESSAGE lv_error_text TYPE 'I' DISPLAY LIKE 'E'.
    ENDTRY.
  ENDMETHOD.
ENDCLASS.

START-OF-SELECTION.
  PERFORM start_of_selection.
*&---------------------------------------------------------------------*
*& Form START_OF_SELECTION
*&---------------------------------------------------------------------*
*& Entry point
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM start_of_selection .
  DATA: lo_application  TYPE REF TO lcl_application.

  CREATE OBJECT lo_application.
  lo_application->start_of_selection( ).
ENDFORM.

The resulting ALV Grid look like the picture below.

Screenshot of result ALV Grid

Now let’s rewrite the report with zsql_test_double_framework to make it testable with unit tests.

TYPES: BEGIN OF ty_grid_line,
         carrname   TYPE scarr-carrname,
         countryfr  TYPE spfli-countryfr,
         cityfrom   TYPE spfli-cityfrom,
         airpfrom   TYPE spfli-airpfrom,
         countryto  TYPE spfli-countryto,
         cityto     TYPE spfli-cityto,
         airpto     TYPE spfli-airpto,
         fldate     TYPE sflight-fldate,
         price      TYPE sflight-price,
         currency   TYPE sflight-currency,
         paymentsum TYPE sflight-paymentsum,
       END OF ty_grid_line.

TYPES ty_grid TYPE STANDARD TABLE OF ty_grid_line WITH KEY carrname.

DATA: carrid TYPE scarr-carrid,
      fldate TYPE sflight-fldate.

SELECT-OPTIONS: s_carrid FOR carrid,
                s_fldate FOR fldate.

CLASS lcl_database_reader DEFINITION.
  PUBLIC SECTION.
    METHODS: constructor IMPORTING io_db_layer TYPE REF TO zif_zosql_db_layer OPTIONAL,
      read_data IMPORTING it_select_carrid TYPE typ_r_carrid OPTIONAL
                          it_select_fldate TYPE typ_r_fldate OPTIONAL
                EXPORTING et_data          TYPE ty_grid
                RAISING   zcx_zosql_error.

  PRIVATE SECTION.
    DATA: go_db_layer  TYPE REF TO zif_zosql_db_layer.
ENDCLASS.

CLASS lcl_application DEFINITION.
  PUBLIC SECTION.
    METHODS: start_of_selection.
ENDCLASS.

CLASS lcl_database_reader IMPLEMENTATION.

  METHOD constructor.
    IF io_db_layer IS BOUND.
      go_db_layer = io_db_layer.
    ELSE.
      go_db_layer = zcl_zosql_test_environment=>get_db_layer_for_production( ).
    ENDIF.
  ENDMETHOD.

  METHOD read_data.

    DATA: ls_param  TYPE zosql_db_layer_param,
          lt_params TYPE zosql_db_layer_params,
          lv_select TYPE string.

    ls_param-param_name_in_select = ':it_select_carrid'.
    zcl_zosql_utils=>move_corresponding_table( EXPORTING it_table_src  = it_select_carrid
                                               IMPORTING et_table_dest = ls_param-parameter_value_range ).
    APPEND ls_param TO lt_params.

    CLEAR ls_param.
    ls_param-param_name_in_select = ':it_select_fldate'.
    zcl_zosql_utils=>move_corresponding_table( EXPORTING it_table_src  = it_select_fldate
                                               IMPORTING et_table_dest = ls_param-parameter_value_range ).
    APPEND ls_param TO lt_params.

    CONCATENATE
      'SELECT scarr~carrname'
           'spfli~countryfr'
           'spfli~cityfrom'
           'spfli~airpfrom'
           'spfli~countryto'
           'spfli~cityto'
           'spfli~airpto'
           'sflight~fldate'
           'sflight~price'
           'sflight~currency'
           'sflight~paymentsum'
      'FROM sflight'
      'JOIN scarr ON scarr~carrid = sflight~carrid'
      'JOIN spfli ON spfli~carrid = sflight~carrid'
                'AND spfli~connid = sflight~connid'
      'WHERE sflight~carrid IN :it_select_carrid'
        'AND sflight~fldate IN :it_select_fldate'
        INTO lv_select SEPARATED BY space.

    go_db_layer->select_to_itab( EXPORTING iv_select       = lv_select
                                           it_parameters   = lt_params
                                 IMPORTING et_result_table = et_data ).
  ENDMETHOD.
ENDCLASS.

CLASS lcl_application IMPLEMENTATION.
  METHOD start_of_selection.
    DATA: lo_reader        TYPE REF TO lcl_database_reader,
          lt_data_for_grid TYPE ty_grid,
          lo_alv           TYPE REF TO cl_salv_table,
          lo_error         TYPE REF TO cx_root,
          lv_error_text    TYPE string.

    CREATE OBJECT lo_reader.

    TRY.
        lo_reader->read_data( EXPORTING it_select_carrid = s_carrid[]
                                        it_select_fldate = s_fldate[]
                              IMPORTING et_data          = lt_data_for_grid ).


        cl_salv_table=>factory( IMPORTING r_salv_table = lo_alv
                                CHANGING  t_table      = lt_data_for_grid ).

        lo_alv->display( ).
      CATCH cx_root INTO lo_error.
        lv_error_text = lo_error->get_text( ).
        MESSAGE lv_error_text TYPE 'I' DISPLAY LIKE 'E'.
    ENDTRY.
  ENDMETHOD.
ENDCLASS.

START-OF-SELECTION.
  PERFORM start_of_selection.
*&---------------------------------------------------------------------*
*& Form START_OF_SELECTION
*&---------------------------------------------------------------------*
*& Entry point
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM start_of_selection .
  DATA: lo_application  TYPE REF TO lcl_application.

  CREATE OBJECT lo_application.
  lo_application->start_of_selection( ).
ENDFORM.

Open SQL SELECT statement was replaced with method zif_zosql_db_layer->select_to_itab and SQL was passed to it as parameter of type string.

Most of SQL syntax was preserved, some additional was added to pass selection screen parameters as bind variables to SQL.

Now let’s create tests with the help of fake database of zsql_test_double_framework.

CLASS ltc_unittests DEFINITION FOR TESTING
  DURATION SHORT
  RISK LEVEL HARMLESS.

  PUBLIC SECTION.
    METHODS: test_method FOR TESTING RAISING zcx_zosql_error.
ENDCLASS.

CLASS ltc_unittests IMPLEMENTATION.
  METHOD test_method.

    CONSTANTS: lc_connid TYPE spfli-connid VALUE 1.

    " SETUP
    DATA: lo_test_environment TYPE REF TO zif_zosql_test_environment,
          lo_test_db_layer    TYPE REF TO zif_zosql_db_layer.

    lo_test_environment = zcl_zosql_test_environment=>create( ).
    lo_test_db_layer = lo_test_environment->get_db_layer_for_unit_tests( ).

    DATA: lt_scarr   TYPE TABLE OF scarr,
          ls_scarr   TYPE scarr,
          lt_spfli   TYPE TABLE OF spfli,
          ls_spfli   TYPE spfli,
          lt_sflight TYPE TABLE OF sflight,
          ls_sflight TYPE sflight.

    " GIVEN
    ls_scarr-carrid   = 'TC'.
    ls_scarr-carrname = 'Test Carrier'.
    APPEND ls_scarr TO lt_scarr.

    lo_test_environment->insert_test_data( lt_scarr ).

    ls_spfli-carrid    = 'TC'.
    ls_spfli-countryfr = 'CO1'.
    ls_spfli-cityfrom  = 'Test City From'.
    ls_spfli-airpfrom  = 'AP1'.
    ls_spfli-countryto = 'CO2'.
    ls_spfli-cityto    = 'Test City To'.
    ls_spfli-airpto    = 'AP2'.
    ls_spfli-connid    = lc_connid.
    APPEND ls_spfli TO lt_spfli.

    lo_test_environment->insert_test_data( lt_spfli ).

    ls_sflight-fldate     = '20220511'.
    ls_sflight-price      = '123'.
    ls_sflight-currency   = 'USD'.
    ls_sflight-paymentsum = '12'.
    ls_sflight-carrid     = 'TC'.
    ls_sflight-connid     = lc_connid.
    APPEND ls_sflight TO lt_sflight.

    lo_test_environment->insert_test_data( lt_sflight ).

    " WHEN
    DATA: lo_reader        TYPE REF TO lcl_database_reader,
          lt_selected_data TYPE ty_grid.

    CREATE OBJECT lo_reader
      EXPORTING
        io_db_layer = lo_test_db_layer.

    lo_reader->read_data( IMPORTING et_data = lt_selected_data ).

    " THEN
    DATA: lt_expected TYPE ty_grid,
          ls_expected TYPE ty_grid_line.

    ls_expected-carrname   = 'Test Carrier'.
    ls_expected-countryfr  = 'CO1'.
    ls_expected-cityfrom   = 'Test City From'.
    ls_expected-airpfrom   = 'AP1'.
    ls_expected-countryto  = 'CO2'.
    ls_expected-cityto     = 'Test City To'.
    ls_expected-airpto     = 'AP2'.
    ls_expected-fldate     = '20220511'.
    ls_expected-price      = 123.
    ls_expected-currency   = 'USD'.
    ls_expected-paymentsum = 12.
    APPEND ls_expected TO lt_expected.

    cl_aunit_assert=>assert_equals( act = lt_selected_data exp = lt_expected ).
  ENDMETHOD.
ENDCLASS.

Now let’s explain the code of test.

First we need to create test environment with the code:

lo_test_environment = zcl_zosql_test_environment=>create( ).

The test environment contains fake database data that is used in database operations in test mode.

Then we create test instance of interface ZIF_ZOSQL_DB_LAYER:

lo_test_db_layer = lo_test_environment->get_db_layer_for_unit_tests( ).

Then we initialize fake database tables from internal tables with the method insert_test_data:

lo_test_environment->insert_test_data( lt_scarr ).

Then we create instance of database reader object. We pass test database layer instance to optional parameter to switch SELECT operations from real database to temporary test environment.

Then we just call production code of read_data method, get result and check it against expected grid data.

This is demonstration of using of Open SQL Test Double in version not supported by SAP Open SQL Test Double Framework.

More cases of code adaptation for using of zsql_test_double_framework are described in documentation.

The project is not restricted to only selection operations. It also supports insert, update, modify and delete operations, for more info see documentation.

Hope it will help you to write more useful tests in your projects.

As a bonus the project contains program that lets to run SQL statements in Open SQL syntax and get result in ALV Grid.

Then program is called ZOSQL_RUN_SQL and is included in the project. During SQL execution authorization checks are perfomed with S_TABU_DIS and S_TABU_NAM authorization objects.