Skip to Content
Technical Articles

example with Open SQL Test Double Framework

Dear community, thanks to the blogs of Bunyanuch Saengnet and Suketu Dave, I became interested in the test doubles topic again. Unfortunately, ABAP unit tests don’t yet play the role in my day-to-day work as I would like – still, I’m sticking to the topic.

One important point in this context is the creation of test doubles (mocks) of database tables. So far I have had no idea how to do this. Because I had some time yesterday left, I worked with the Open SQL Test Double Framework. I’ve built a very simple example to see how it works in principle (no real world example, sorry). It’s just an “one hour code hack”, done on my SAP NetWeaver 7.52 Trial, that I want to share with you. Ok, let’s get started 🙂

I wrote a small, global class to read client customizing from table T000.

CLASS zcl_system_clients DEFINITION
                         PUBLIC
                         FINAL
                         CREATE PUBLIC.

  PUBLIC SECTION.
    TYPES clients TYPE TABLE OF t000 WITH KEY mandt.

    METHODS get_client
      IMPORTING
        client        TYPE mandt
      RETURNING
        VALUE(result) TYPE clients.
ENDCLASS.

CLASS zcl_system_clients IMPLEMENTATION.
  METHOD get_client.
    SELECT * FROM t000 AS clients
             INTO TABLE result
             WHERE mandt = client.
  ENDMETHOD.
ENDCLASS.

That’s the content of database table T000 in my SAP NetWeaver 7.52 Trial.

content of database table T000

Here is my local test class.

CLASS ltc_test DEFINITION FINAL
               FOR TESTING
               DURATION SHORT
               RISK LEVEL HARMLESS.

  PUBLIC SECTION.
    METHODS get_client_000 FOR TESTING.
    METHODS get_client_001 FOR TESTING.
    METHODS get_client_999 FOR TESTING.

  PRIVATE SECTION.
ENDCLASS.

CLASS ltc_test IMPLEMENTATION.
  METHOD get_client_000.
    DATA(cut) = NEW zcl_system_clients( ).
    DATA(result) = cut->get_client( '000' ).
    cl_aunit_assert=>assert_not_initial( result ).
  ENDMETHOD.

  METHOD get_client_001.
    DATA(cut) = NEW zcl_system_clients( ).
    DATA(result) = cut->get_client( '001' ).
    cl_aunit_assert=>assert_not_initial( result ).
  ENDMETHOD.

  METHOD get_client_999.
    DATA(cut) = NEW zcl_system_clients( ).
    DATA(result) = cut->get_client( '999' ).
    cl_aunit_assert=>assert_not_initial( result ).
  ENDMETHOD.
ENDCLASS.

This is the result when running the tests.

The tests for reading the customizing of clients 000 and 001 are ok. Client 999 doesn’t exist, the test fails. That’s fine – there is no client 999 in my system.

So either I have to create a client 999 in the database table T000 or I have to rethink my test. Another solution is to use a test double for the database table. Fortunately, this is possible with the class CL_OSQL_TEST_ENVIRONMENT … and exactly what I want to try.

Here’s the revision of my test class. Pay attention to the PRIVATE SECTION and the methods CLASS_SETUP and CLASS_TEARDOWN.

CLASS ltc_test DEFINITION FINAL
               FOR TESTING
               DURATION SHORT
               RISK LEVEL HARMLESS.

  PUBLIC SECTION.
    METHODS get_client_000 FOR TESTING.
    METHODS get_client_001 FOR TESTING.
    METHODS get_client_999 FOR TESTING.

  PRIVATE SECTION.
    CLASS-DATA osql_test_environment TYPE REF TO if_osql_test_environment.
    CLASS-METHODS class_setup.
    CLASS-METHODS class_teardown.
ENDCLASS.


CLASS ltc_test IMPLEMENTATION.
  METHOD class_setup.
    osql_test_environment = cl_osql_test_environment=>create( VALUE #( ( 'T000' ) ) ).
    DATA(clients_test_data) = VALUE zcl_system_clients=>clients( ( mandt = '000' mtext = 'Test double' )
                                                                 ( mandt = '001' mtext = 'Test double' )
                                                                 ( mandt = '999' mtext = 'Test double' ) ).
    osql_test_environment->insert_test_data( clients_test_data ).
  ENDMETHOD.

  METHOD class_teardown.
    osql_test_environment->destroy( ).
  ENDMETHOD.

  METHOD get_client_000.
    DATA(cut) = NEW zcl_system_clients( ).
    DATA(result) = cut->get_client( '000' ).
    cl_aunit_assert=>assert_not_initial( result ).
  ENDMETHOD.

  METHOD get_client_001.
    DATA(cut) = NEW zcl_system_clients( ).
    DATA(result) = cut->get_client( '001' ).
    cl_aunit_assert=>assert_not_initial( result ).
  ENDMETHOD.

  METHOD get_client_999.
    DATA(cut) = NEW zcl_system_clients( ).
    DATA(result) = cut->get_client( '999' ).
    cl_aunit_assert=>assert_not_initial( result ).
  ENDMETHOD.
ENDCLASS.

This is the result when rerunning the tests.

The test double seems to have worked. Unfortunately I couldn’t find a way to easily and quickly trace whether the test double or the database table were accessed. Some hints from the experts?

Ok, that’s all I have to say about my little research trip 🙂 Who is also interested in the topic: This blog and this page helped me.

 

Best regards, thanks for reading and please stay healthy

Michael

 

P.S.: Please support the virtual wishing well.

P.S.S.: Not tired of reading blogs? Check this blog by Paul Hardy.

11 Comments
You must be Logged on to comment or reply to a post.
  • Hi Michael,

     

    thanks for this post, I will try this also. For now I just worked with ECATT Containers and mock data.

    Your example is perfect for testing the SQL statements itself.

     

    Kind regards

    Matthias

    • Hi Matthias, so far, I had no idea how to test methods with access to database tables. The appropriate data would have to be available in the database tables for this. It’s difficult to ensure. You would have separated test data and test cases. Not good. With the Open SQL Test Double Framework you can keep both together. I hope to do more with it. It’s a big step for me to change work behavior to base on unit tests 😉

  • Hi Michael,

    Thanks for the post and I’m glad to know that you’ve developed an interest in this skill area.

    Regarding the question you posed at the end of your article – How to easily and quickly trace whether the test double or the database table were accessed, here is one quick suggestion from my side:

    Just immediately either before or after the assert statements, implement a SELECT * on the DB Table you would like to check the entries for – in this case its t000, and fetch it into an internal table (say lt_t000) like SELECT * FROM t000 INTO TABLE DATA(lt_t000)

    Then place a debugger point on this SELECT query and check for the values of lt_t000.

    Happy Learning!

    Best Regards,
    Suketu Dave.

  • Hi Michael,

    you can put some dummy values in your osql test double, that do not exist in your original db table, like you did with 999 and check if your result line is = ( mandt = 999 mtext = ‘Text double’ ) with the method assert_equals.

     

    • Hi Nadejda, that’s an approach 🙂 However, if you transfer your application to another SAP ERP system of another customer, there could actually be a client 999 there. Admittedly, very unlikely 😉

  • I have begun to use this same test double methodology in my own testing.  It is very handy once you get the hang of it.

    i would second Nadjeda Sacara.  Put in some test values you know to be bogus in the real table.  Debugging can also be done, as was already suggested.

    regards,

    justin

  • I think that Open SQL Test Double Framework is a cool feature. But it’s available since 7.5*. I found helpful techniques showed in book “Test-Driven Development in Microsoft .NET”. In chapters 5 and 10 authors disscussed about utilizing unit testing framework for testing DAL layer. In examples they demonstrate Gateway pattern for organizing DAL and Transaction Services for control real DB changes in fixture methods. Adaptation of these techniques works from ABAP 7.02 and above.

    • Very good hint, Sergey! Some years ago, I started to provide my customizing for an application via class (object). That had a lot of impacts. The whole representation switched to class.

      Today, even simple ALVs I’ve written have a class to provide the data of the list. The list is itself an object! It’s build by different other classes with a clear focus of every involved class (composition).

      Life is a lot easier when you think of a lot of things as a class or interface 🙂