Sometimes you may need to directly access data from a table without any function module or webservice. SAP offers a dedicated function module for direct database access via RFC: RFC_READ_TABLE. But RFC is unfortunately not available everywhere, so we decided to create a dedicated restful service to provide a similar function as RFC_READ_TABLE.
SICF handler
Our SICF service handler is basically the same as in the JSON Adapter for ABAP Function Modules, but we removed the call to the function module and replaced it with an authority check on table leven (VIEW_AUTHORITY_CHECK) and the call to our service method:
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
METHOD if_http_extension~handle_request.
...
****************************
* Get form and header fields
****************************
" Get table name from PATH_INFO
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.
" all good :)
WHEN 3.
" table not found
http_error '404' 'Not found' 'The table you have requested has not been found'.
WHEN OTHERS.
" insufficient permissions
http_error '403' 'Not allowed' 'You are not allowed to query this table'.
ENDCASE.
****************************
* get 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
****************************
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 response. Serialize to output format stream.
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. " the others default to JSON.
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.
Dynamically create the SQL statement
In our service method we dynamically generate the SQL statement according to the get parameters of the HTTP call:
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
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.
" Wanted columnns
IF lv_columns IS NOT INITIAL.
TRANSLATE lv_columns TO UPPER CASE.
" support for , AND ; as column separator
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.
" 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.
" take the columns in the order of the table
lv_columns = '*'.
lt_components = CORRESPONDING #( lo_struct_desc->get_included_view( ) ).
ELSE.
" take the columns in the order of the column list
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.
" create the outtab dynamically
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>.
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.
Call the restful service
Using the code above, we can call the restful service to get all wanted table entries from the chosen table: https://<server>:<port>/read_table/<tablename>?sap-client=<client>&format=<format>&query=<query>&rowcount=<rowcount>&columns=<columns>&orderby=<orderby>
where the values can be inserted like:
<server>/<port>: your server name and port<tablename>: the table name (the calling user needs access to this table - S_TABU_NAM/S_TABU_DIS)<format>: Either XML or JSON<query>: The SQL query (url encoded). An empty query returns all data<rowcount>: The amount of rows to be returned<columns>: the columns you want in the response (Empty = SELECT *)<orderby>: ASCENDING, DESCENDING or empty