topic Re: Unit testing database values after insertion in Application Development and Automation Discussions

Unit testing database values after insertion

former_member871985 — Mon, 31 Jul 2023 15:31:25 GMT

Hi experts,

I'm trying to use unit tests for writing access database tests, but I can't find a way to validate the correctness of inserted data. I'm using the ABAP SQL Test Double Framework : if_osql_test_environment.

I'm testing an insert method into the EKKO table, which is replace (with the framework) by the internal table MT_EKKO. While reading this table after the insert in the test environment, no new line is added. The test failed at my READ INDEX 2 on MT_EKKO. The insert return a sy-subrc of 0.

My goal is to check my new values in the MT_EKKO table to test in the future BAPI custom enhancement.

Is there a way to look up for data changes in the double table made with the framework?

REPORT.

CLASS lcl_test_bapi DEFINITION FINAL FOR TESTING DURATION SHORT RISK LEVEL HARMLESS .
  PRIVATE SECTION.
    CLASS-DATA:
      mo_sql_env     TYPE REF TO if_osql_test_environment.
    DATA:
      mt_ekko TYPE STANDARD TABLE OF ekko.
    CLASS-METHODS:
      class_setup,
      class_teardown.
    METHODS:
      setup,
      teardown,
      test_insert FOR TESTING.
ENDCLASS.

CLASS lcl_test_bapi IMPLEMENTATION.
  "Setup and teardown
  METHOD class_setup.
    mo_sql_env = cl_osql_test_environment=>create( i_dependency_list = VALUE #( ( 'EKKO' ) ) ).
  ENDMETHOD.
  METHOD class_teardown.
    mo_sql_env->destroy( ).
  ENDMETHOD.
  METHOD setup.
    mt_ekko = VALUE #( ( mandt = sy-mandt ebeln = '1111111111' bstyp = 'F' bsart = 'NB' aedat = '20230703' ernam = 'setup_method' ) ).
    mo_sql_env->insert_test_data( mt_ekko ).
  ENDMETHOD.
  METHOD teardown.
    CLEAR: mt_ekko.
    mo_sql_env->clear_doubles( ).
  ENDMETHOD.

  "Test method
  METHOD test_insert.

    DATA ls_ekko TYPE ekko.
    ls_ekko = VALUE #( mandt = sy-mandt ebeln = '1234567890' bstyp = 'F' bsart = 'NB' aedat = '20230101' ernam = 'FromInsert' ).
    INSERT INTO ekko VALUES ls_ekko.
    cl_abap_unit_assert=>assert_subrc( ).

    READ TABLE mt_ekko INTO DATA(ls_test) INDEX 2.
    cl_abap_unit_assert=>assert_subrc( msg = 'MT_EKKO doesn''t have a second line.' ). "Test failed here

    cl_abap_unit_assert=>assert_equals(
      EXPORTING
        act = ls_test-ernam
        exp = 'FromInsert' ).

  ENDMETHOD.
ENDCLASS.

A good example of what I'm trying to do (with the MODIFY keyword) is the class LTC_TEST_DML_STMNTS from the package SABP_UNIT_DOUBLE_OSQL_DEMO. The downside of this approach is the code only check sy-subrc, and return the value to test. I will no be able to do that in my tests.

BR,

Antoine

Re: Unit testing database values after insertion

Sandra_Rossi — Mon, 31 Jul 2023 18:20:17 GMT

MANDT missing maybe?

mt_ekko = VALUE #( ( ebeln = '1234567890' bstyp = 'F' bsart = 'NB' aedat = '20230703' ernam = 'setup_method' ) ).

mo_sql_env->insert_test_data( mt_ekko ).

Re: Unit testing database values after insertion

former_member871985 — Tue, 01 Aug 2023 07:24:00 GMT

I forgot this one thanks. But even with the MANDT, nothing change. The subrc of INSERT still at 0, but the double (mt_ekko) still with only one line (from the setup method) in the debugger.

Re: Unit testing database values after insertion

Sandra_Rossi — Tue, 01 Aug 2023 12:50:36 GMT

No issue in my ABAP 7.57 system, with this minimal reproducible example:

REPORT.
CLASS ltc_main DEFINITION
      FOR TESTING
      DURATION SHORT
      RISK LEVEL HARMLESS.
  PRIVATE SECTION.
    METHODS test FOR TESTING.
    CLASS-DATA mo_sql_env     TYPE REF TO if_osql_test_environment.
    DATA mt_ekko TYPE STANDARD TABLE OF ekko.
    CLASS-METHODS class_setup.
    CLASS-METHODS class_teardown.
    METHODS setup.
ENDCLASS.
CLASS ltc_main IMPLEMENTATION.
  METHOD class_setup.
    mo_sql_env = cl_osql_test_environment=>create( i_dependency_list = VALUE #( ( 'EKKO' ) ) ).
  ENDMETHOD.
  METHOD setup.
    mt_ekko = VALUE #( ( ebeln = '1234567890' ) ).
    mo_sql_env->clear_doubles( ).
    mo_sql_env->insert_test_data( mt_ekko ).
  ENDMETHOD.
  METHOD test.
    DATA: ls_ekko TYPE ekko.
    ls_ekko-mandt = sy-mandt.
    ls_ekko-ebeln = '1234567890'.
    sy-subrc = 0.
    INSERT INTO ekko VALUES ls_ekko.
    cl_abap_unit_assert=>assert_subrc( exp = 4 ).
  ENDMETHOD.
  METHOD class_teardown.
    mo_sql_env->destroy( ).
  ENDMETHOD.
ENDCLASS.

Re: Unit testing database values after insertion

former_member871985 — Tue, 01 Aug 2023 13:56:26 GMT

Thanks for your time and sorry for not being clear.

My insert test is also correct (the sy-subrc returned is 0 because no data exist with the same key) but the row newly inserted isn't in my internal table mt_ekko. As I'm trying to read a second line, this is where I got an error: on my READ TABLE INDEX 2.

My goal is to check my new values in the mt_ekko table to test in the future BAPI custom enhancement.

A good example of what I'm trying to do (with the MODIFY keyword) is the class LTC_TEST_DML_STMNTS from the package SABP_UNIT_DOUBLE_OSQL_DEMO. The code test if the price modification is correct. The problem, this test only checks the return price based on the MODIFY subrc :

" CUT
METHOD change_price.
  DATA wa TYPE sflight.

  SELECT SINGLE *
         FROM sflight
         INTO wa
         WHERE carrid = carrid AND
               connid = connid AND
               fldate = fldate.

  IF sy-subrc <> 0.
    new_price = - 1.
    RETURN.
  ENDIF.
  wa-price = wa-price * factor / 100.

  MODIFY sflight FROM wa.

  IF sy-subrc = 0.
    new_price = wa-price.
  ELSE.
    new_price = - 2.
  ENDIF.
ENDMETHOD.
" End CUT


" Test method (check the 10% discount has been made)
NEW cl_osql_dml_statements( )->change_price(
    EXPORTING
       carrid = 'ZZ'
       connid = '1234'
       fldate = sy-datum
       factor = 90
     IMPORTING new_price = DATA(new_price) ).

  cl_aunit_assert=>assert_equals(
   EXPORTING
     exp = exp          " Called with : invoke_and_assert( 360 )
     act = new_price  ).
"End test method

Where the modification takes place? As it's not visible on the double internal table, neither in the DB ... How to validate modified values?

Re: Unit testing database values after insertion

Sandra_Rossi — Tue, 01 Aug 2023 14:45:38 GMT

What "double internal table" are you talking about? The concept of ABAP SQL Test Double is only in database.

Can't you ask a question with minimal reproducible code that we can copy and paste? (the same as I did)

Otherwise people will stop looking at your question if you give the important information only in the comments.

Re: Unit testing database values after insertion

former_member871985 — Thu, 03 Aug 2023 14:00:17 GMT

What "double internal table" are you talking about?

To what I understood, my MT_EKKO internal table replace the EKKO table while executing unit tests.

Can't you ask a question with minimal reproducible code that we can copy and paste?

I have rewritten my question to me clearer. I hope it's better.

Re: Unit testing database values after insertion

Sandra_Rossi — Thu, 03 Aug 2023 14:40:46 GMT

To what I understood, my MT_EKKO internal table replace the EKKO table while executing unit tests.

Wrong. The database table is duplicated in the database under another name and is used instead of EKKO (NB: I'm not expert, I don't know what is exactly behind the scene).

Re: Unit testing database values after insertion

former_member871985 — Thu, 03 Aug 2023 16:03:32 GMT

I misunderstood how the ABAP SQL Double Framework works...

As shown here, and as sandra.rossi pointed out the framework create a temporary database rather than modifying the internal table given in the setup method (MT_EKKO in my example).

So, in order to read and check modified values, you have to make another select in the test method to assert modifications.

"Test method
METHOD test_insert.

  DATA ls_ekko TYPE ekko.
  ls_ekko = VALUE #( mandt = sy-mandt ebeln = '1234567890' bstyp = 'F' bsart = 'NB' aedat = '20230101' ernam = 'FromInsert' ).
  INSERT INTO ekko VALUES ls_ekko.
  cl_abap_unit_assert=>assert_subrc( ).

  SELECT * FROM EKKO INTO TABLE @DATA(lt_ekko).

  READ TABLE lt_ekko INTO DATA(ls_test) INDEX 2.
  cl_abap_unit_assert=>assert_subrc( msg = 'MT_EKKO doesn''t have a second line.' ).

  cl_abap_unit_assert=>assert_equals(
    EXPORTING
      act = ls_test-ernam
      exp = 'FromInsert' ).

ENDMETHOD.

Re: Unit testing database values after insertion

former_member871985 — Thu, 03 Aug 2023 16:07:56 GMT

I just figured it out what you was saying.

Thanks for your answers and your time. I submit an answer with a minimal explanation on what to do to solve this.