Post

RFC_READ_TABLE via REST

Posted
By Manuel Laggner
7 min read
RFC_READ_TABLE via REST

Here’s an improved version of the article with more explanations and clearer language:

Introduction

There are situations where you may need direct access to data stored in a database table without relying on function modules or web services. SAP provides a specific function module, RFC_READ_TABLE, that allows direct database access via RFC. However, RFC might not always be available in all environments. To address this limitation, we decided to build a dedicated RESTful service that mimics the functionality of RFC_READ_TABLE for direct database access.

SICF Handler

The SICF service handler we developed is similar to the one used in the JSON Adapter for ABAP Function Modules. The main difference is that we removed the function module call and instead performed an authorization check at the table level using VIEW_AUTHORITY_CHECK. We then invoked our custom service method to handle the request.

Here is the ABAP code for the SICF handler:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115

METHOD if_http_extension~handle_request.

...

****************************
* Get form and header fields
****************************

" Retrieve the table name from the PATH_INFO parameter
path_info = server->request->get_header_field( name = '~path_info' ).
SPLIT path_info AT '/' INTO path_info lv_tabname.
TRANSLATE lv_tabname TO UPPER CASE.
IF lv_tabname IS INITIAL.
  http_error '404' 'Not Found' 'Empty request.' .

ENDIF.

lv_format = server->request->get_form_field( 'format' ).

...

****************************
* Check Authorization
****************************
CALL FUNCTION 'VIEW_AUTHORITY_CHECK'
  EXPORTING
    view_action     = 'S'
    view_name       = CONV viewname( lv_tabname )
  EXCEPTIONS
    table_not_found = 3
    OTHERS          = 99.

CASE sy-subrc.
  WHEN 0.
    " Authorization successful, proceed

  WHEN 3.
    " Table not found
    http_error '404' 'Not Found' 'The table you requested does not exist'.

  WHEN OTHERS.
    " Insufficient permissions
    http_error '403' 'Forbidden' 'You do not have permission to query this table'.

ENDCASE.

****************************
* Collect input data *
****************************
DATA(lv_columns) = server->request->get_form_field( 'columns' ).
DATA(lv_query) = server->request->get_form_field( 'query' ).
DATA(lv_rowcount) = server->request->get_form_field( 'rowcount' ).
DATA(lv_orderby) = server->request->get_form_field( 'orderby' ).

****************************
* Read the table data
****************************
TRY.
    DATA(lr_data) = read_table( 
      iv_tabname = lv_tabname 
      iv_columns = lv_columns 
      iv_query = lv_query 
      iv_rowcount = lv_rowcount 
      iv_orderby = lv_orderby ).

  CATCH cx_root INTO oexcp.
    etext = oexcp->if_message~get_longtext( preserve_newlines = abap_true ).
    IF etext IS INITIAL.
      etext = oexcp->get_text( ).

    ENDIF.
    http_error '500' 'Internal Server Error' etext.

ENDTRY.

" Prepare the response and serialize it to the chosen output format
CASE lv_format.

  WHEN 'XML'.
    serialize_xml( 
      EXPORTING
        data      = lr_data
        lowercase = lv_lowercase
        format    = lv_format
      IMPORTING
        o_string  = o_cdata 
    ).

    server->response->set_header_field( 
      name = 'Content-Type' 
      value = 'application/xml' 
    ).

  WHEN OTHERS. " Default to JSON format
    lv_format = 'JSON'.
    serialize_json(
      EXPORTING
        tabname   = lv_tabname
        data      = lr_data
        lowercase = lv_lowercase
        camelcase = lv_camelcase
      IMPORTING
        o_string  = o_cdata 
    ).

    server->response->set_header_field( 
      name = 'Content-Type' 
      value = 'application/json' 
    ).

ENDCASE.

...

ENDMETHOD.

Explanation:

  • Request Handling: The handle_request method is responsible for processing incoming HTTP requests. It retrieves the table name and other parameters from the request and performs an authorization check to ensure the user has the necessary permissions to access the requested table.
  • Authorization Check: We use the VIEW_AUTHORITY_CHECK function to verify whether the user has permission to access the specified table. If the authorization fails, the method returns an appropriate error response.
  • Input Data Parsing: The method collects additional parameters like columns, query, rowcount, and orderby from the request to refine the table query.
  • Data Retrieval: The method then calls the read_table function to fetch the table data based on the provided parameters.
  • Response Serialization: The result is serialized into either XML or JSON format, depending on the format parameter in the request. The appropriate content type is set in the response header (Content-Type).

Dynamically Creating the SQL Statement

In the service method, we dynamically generate an SQL query based on the HTTP request parameters. The SQL is built according to the columns, query conditions, row count, and ordering specified in the request.

Here’s the ABAP code that generates the SQL statement:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

METHOD read_table.

  DATA: lv_columns  TYPE string,
        lv_rowcount TYPE i.

  DATA: lt_components TYPE cl_abap_structdescr=>component_table.

  DATA: lr_data TYPE REF TO data.

  FIELD-SYMBOLS: <t_data> TYPE STANDARD TABLE.

  lv_columns = iv_columns.

  TRY.
      lv_rowcount = iv_rowcount.
    CATCH cx_root.
  ENDTRY.

  TRY.
      " Handle column names
      IF lv_columns IS NOT INITIAL.
        TRANSLATE lv_columns TO UPPER CASE.

        " Support for both comma and semicolon as column separators
        REPLACE ALL OCCURRENCES OF ';' IN lv_columns WITH ','.     
        SPLIT lv_columns AT ',' INTO TABLE DATA(lt_columns).

        LOOP AT lt_columns ASSIGNING FIELD-SYMBOL(<s_column>).
          CONDENSE <s_column>.

        ENDLOOP.

      ENDIF.

      CLEAR lv_columns.

      " Retrieve column names via RTTI
      DATA(lo_struct_desc) = CAST cl_abap_structdescr( 
        cl_abap_structdescr=>describe_by_name( iv_tabname ) ).

      IF lt_columns IS INITIAL.
        " If no specific columns are provided, select all columns (*)
        lv_columns = '*'.
        lt_components = CORRESPONDING #( lo_struct_desc->get_included_view( ) ).

      ELSE.
        " If columns are specified, select them in the provided order
        LOOP AT lt_columns ASSIGNING <s_column>.
          READ TABLE lo_struct_desc->get_included_view( ) 
            INTO DATA(ls_component) WITH KEY name = <s_column>.
          IF sy-subrc EQ 0.
            IF lv_columns IS NOT INITIAL.
              lv_columns = lv_columns && `, `.

            ENDIF.

            lv_columns = lv_columns && ls_component-name.
            APPEND CORRESPONDING #( ls_component )
              TO lt_components.

          ENDIF.

        ENDLOOP.

      ENDIF.

      " Dynamically create the output table
      lo_struct_desc = cl_abap_structdescr=>create( lt_components ).
      DATA(lo_table_desc) = cl_abap_tabledescr=>create( lo_struct_desc ).

      CREATE DATA lr_data TYPE HANDLE lo_table_desc.
      ASSIGN lr_data->* TO <t_data>.

      " Execute the SELECT query
      SELECT (lv_columns)
        FROM (iv_tabname)
        WHERE (iv_query)
        ORDER BY (iv_orderby)
        INTO CORRESPONDING FIELDS OF TABLE @<t_data>
        UP TO @lv_rowcount ROWS.

      IF sy-subrc EQ 0.
        rr_data = lr_data.

      ENDIF.

    CATCH cx_root INTO DATA(lo_ex).
      RAISE EXCEPTION TYPE zcx_json
        EXPORTING
          message = lo_ex->if_message~get_text( ).
          
  ENDTRY.

ENDMETHOD.

Explanation:

  • Dynamic SQL Construction: The method dynamically constructs the SQL query based on the provided parameters (columns, query, rowcount, and orderby). It first checks whether the columns are specified and prepares a list of columns to query. If no columns are specified, it selects all columns (*).
  • Runtime Type Information (RTTI): We use RTTI to fetch the list of columns in the table and ensure that the selected columns exist in the table.
  • Data Fetching: The method then executes the SQL SELECT query and fetches the results into a dynamically created internal table.

Calling the RESTful Service

Once the service is set up, you can access the data by calling the RESTful service via a URL like this:

1

https://<server>:<port>/read_table/<tablename>?sap-client=<client>&format=<format>&query=<query>&rowcount=<rowcount>&columns=<columns>&orderby=<orderby>

Where:

  • <server>/<port>: Your server’s host name and port.
  • <tablename>: The name of the table (the user must have access to this table — S_TABU_NAM/S_TABU_DIS).
  • <format>: Either XML or JSON.
  • <query>: The SQL query (URL-encoded). An empty query will return all data.
  • <rowcount>: The number of rows to return.
  • <columns>: The specific columns you want in the response. (Leave empty for SELECT *).
  • <orderby>: The order of the result set (either ASCENDING, DESCENDING, or empty).

This solution provides a flexible, RESTful way to directly access SAP table data, replacing the RFC_READ_TABLE function module and offering a more modern, HTTP-based interface. By using this service, users can query SAP tables dynamically and get the data in the format they need, with built-in authorization checks and data handling.

programming, abap
sap abap
This post is licensed under CC BY 4.0 by the author.