Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Showing results for 
Search instead for 
Did you mean: 
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.

 
2 Comments