Prototyping with Generic Table Reader

[Former Member]

By now you know about SAP Gateway for Microsoft and how it can help to building solutions that bring SAP data to Microsoft Office. A prerequisite for building such a solution is the availability of a suitable OData service. Sometimes you will face the situation that such a service is not available but you would like to build a quick prototype of your Windows solution before investing time and energy into the development of an OData service.

Here is an idea:

With the help of the function module RFC_READ_TABLE you can build an OData service that can generically read any DDIC table. Such a service could be used by the GWM Excel Add-In or GWM Visual Studio Add-In to build quick prototypes.

Here a screenshot of the Company Code table (T001) when testing function module RFC_READ_TABLE:

As can be seen in this screenshot, the output of RFC_READ_TABLE isn’t perfect for consumption in an OData service. In this blog I will briefly explain how to utilize this function module and wrap it in a way to make it more suitable so you can finally do something like this:

Example: Reading Company Code table (T001) via generic table reader OData service from GWM Excel Add-In:

Example: Reading Plants table (T001W) via generic table reader OData service from the GWM Excel Add-In:

Important Information about RFC_READ_TABLE

Please read note 382318 (FAQ|Function module RFC_READ_TABLE) before you continue. It states “Avoid to use the external generic table access (with function module RFC_READ_TABLE) in your solutions. The function is not meant to be publicly used.”

Some other points to consider about RFC_READ_TABLE:

It will only facilitate the READ operation (no CREATE, UDATE or DELETE). It can’t read all tables under the sun - some work and some don’t. Especially tables that are wider than 512 characters are not supported. Also the function module can’t directly be used in the SAP Gateway Service Builder (SEGW). It will need to be wrapped in another function module to make it suitable for Service Builder. The error handling needs to be improved to meet the Service Builder requirements. And finally the return structure is quite unsuitable as each table entry is returned as a long string.

If you are cool with that, then please continue reading. Otherwise please disconnect at this time. 😉

Here a high-level outline of the tasks:

• Create a generic DDIC structure that will be used as return table
• Create a new wrapper function module
• Create a new OData Service
• Consuming OData Service from GWM Excel Add-In or .NET Application

To appreciate this blog you need to be familiar with the process on how to create OData services with SAP Gateway.

Create a generic DDIC structure that will be used as return table

I create a new DDIC structure in SE11 (with name ZDATA_FORMATTED), which will be used by the new function module to return the table entries. I am limiting the number of columns to 20. As for the data type I am using SYCHAR512, which will allow for 512 characters per column.

Note: The settings of this structure will have two implications for the OData service: 1) For tables that have more than 20 fields only the first 20 fields are returned and 2) any field value that is longer than 512 characters will be truncated.

Create a new Wrapper Function Module

To speed up things, I copy the original RFC_READ_TABLE to my own function module (with name Z_READ_TABLE).

Now I adjust the interface of my function module starting with the Import tab. I removed some of the parameters and only retain these:

On the Export tab I add parameter RETURN, which will be used to return error messages in a format that the Service Builder likes:

On the Tables tab, here I set all existing parameters to Optional and then I add a new parameter DATA_FORMATTED of type ZDATA_FORMATTED (which I created earlier in the first step):

I retain all the Exceptions that were copied from the original function module.

As for source code, I first delete all existing code and replace it with the following sample coding, which should give you an idea on how to wrap the original function module in a way to make it usable in the Service Builder:

Source Code Example

FUNCTION z_read_table. *"---------------------------------------------------------------------- *"*"Local Interface: *"  IMPORTING *"     VALUE(QUERY_TABLE) LIKE  DD02L-TABNAME *"     VALUE(ROWCOUNT) LIKE  SOID-ACCNT DEFAULT 0 *"  EXPORTING *"     VALUE(RETURN) TYPE  BAPIRET1 *"  TABLES *"      OPTIONS STRUCTURE  RFC_DB_OPT OPTIONAL *"      FIELDS STRUCTURE  RFC_DB_FLD OPTIONAL *"      DATA STRUCTURE  TAB512 OPTIONAL *"      DATA_FORMATTED STRUCTURE  ZDATA_FORMATTED *"  EXCEPTIONS *"      TABLE_NOT_AVAILABLE *"      TABLE_WITHOUT_DATA *"      OPTION_NOT_VALID *"      FIELD_NOT_VALID *"      NOT_AUTHORIZED *"      DATA_BUFFER_EXCEEDED *"----------------------------------------------------------------------   DATA ls_data TYPE sychar512.   DATA ls_data_formatted TYPE zdata_formatted.   DATA lt_row TYPE TABLE OF sychar512.   DATA ls_row TYPE sychar512.   FIELD-SYMBOLS <wa> TYPE any.   DATA lv_col_no TYPE i.   DATA lv_message1 TYPE sy-msgv1.   DATA lv_message2 TYPE sy-msgv1.   DATA lv_message3 TYPE sy-msgv1.   DATA lv_message4 TYPE sy-msgv1.   TRANSLATE query_table TO UPPER CASE.   CALL FUNCTION 'RFC_READ_TABLE'     EXPORTING       query_table          = query_table       delimiter            = ';'       rowcount             = rowcount     TABLES       options              = options       fields               = fields       data                 = data     EXCEPTIONS       table_not_available  = 1       table_without_data   = 2       option_not_valid     = 3       field_not_valid      = 4       not_authorized       = 5       data_buffer_exceeded = 6       OTHERS               = 7.   IF sy-subrc <> 0.     CASE sy-subrc.       WHEN 1. lv_message1 = 'Table not available'.       WHEN 2. lv_message1 = 'Table without data'.       WHEN 3. lv_message1 = 'Option not valid'.       WHEN 4. lv_message1 = 'Field not valid'.       WHEN 5. lv_message1 = 'Not authorized'.       WHEN 6. lv_message1 = 'Data buffer exceeded'.       WHEN OTHERS. lv_message1 = 'An error occurred'.     ENDCASE.     CALL FUNCTION 'BALW_BAPIRETURN_GET1'       EXPORTING         type       = 'E'         cl         = '01'         number     = '319'         par1       = lv_message1         par2       = lv_message2         par3       = lv_message3         par4       = lv_message4       IMPORTING         bapireturn = return.     EXIT.   ENDIF.   LOOP AT data INTO ls_data.     SPLIT ls_data AT ';' INTO TABLE lt_row.     CLEAR ls_data_formatted.     ls_data_formatted-key_value = sy-tabix.     lv_col_no = 1.     LOOP AT lt_row INTO ls_row.       lv_col_no = lv_col_no + 1.       ASSIGN COMPONENT lv_col_no OF STRUCTURE ls_data_formatted TO <wa>.       <wa> = ls_row.     ENDLOOP.     APPEND ls_data_formatted TO data_formatted.   ENDLOOP. ENDFUNCTION.

Based on this function module, now I create an OData service.

Create a new OData Service

In transaction SEGW I create a new project with name ZGWM_READ_TABLE. Then I create a new Entity Type with name TableEntry. The easiest way to do this is to use the Import -> RFC/BOR Interface feature. This screenshot shows my final list of properties:

Optional: After the import, I make a couple of tweaks to the structure to facilitate the consumption of this OData service:

• I set all attributes, with the exception of the KeyValue, to Nullable.
• I introduce an additional property called TableNameFilter (Nullable and Filterable). I will use this parameter as a filter parameter.
• I provide labels for each property to make them more easily identifiable.

Next I create an Entity Set for the Entity Type. Once the Entity Set exists, then I can do the mapping for the GetEntityset(Query) operation. I map to the newly created function module Z_READ_TABLE. I use the Propose Mapping feature. Now I only need to do one additional manual change. I add new entry for the mapping of the entity set property TableNameFilter to the data source parameter QUERY_TABLE.

The Service Builder will automatically recognize that the data source parameter RETURN should be mapped for error/message handling. Additionally I have to flag data source parameter ROWCOUNT should be flagged as ‘Max Hits’.

Now it is time to generate the OData service. Once successfully generated, I can now register it in (transaction /IWFND/MAINT_SERVICE).  Here a screenshot of my registered service:

Consuming OData Service

Now I can consume this service from different consumption tools, like the GWM Visual Studio Add-In or GWM Excel Add-In. Via the filter parameter TableNameFilter I can specify which table should be queried. This allows me to read any table in the SAP System (provided that it is supported by the underlying function module RFC_READ_TABLE). When testing make sure to add the filter (otherwise the RFC will throw an error). The URL should look like this:
... /ZGWM_READ_TABLE_SRV/TableEntryCollection?$filter=TableNameFilter eq 'T001'

Here a simple example of using this OData service and GWM from a Windows Forms application (reading the Currency Text table TCURT):

Another example of using the same OData service from GWM Excel Add-In (reading table TCURT):

I hope you find this generic table reader OData service helpful, especially when building prototypes. Later when building a solution for productive usage then you will need to build a new tailored OData service.

Taking this further

Here a couple of improvement ideas to take this even further:

• The number of columns could be easily expanded beyond the 20 by simply extending the data structure.
• To overcome some of the limitations of the function module RFC_READ_TABLE, you could copy the coding and enhance your copy (e.g. the DATA_BUFFER_EXCEEDED exception could be easily dealt with, which would make this function module usable for many more tables).
• The function module has a very helpful parameter called OPTIONS that could be used for filtering. This parameter lets you pass a ‘SQL WHERE clause’ (e.g. you could read table T001 and limit the result by a specific country specifying a WHERE clause like this “LAND1 eq ‘US’”). When creating the mapping of your OData service you could specify a WHERE clause or create a filter parameter to set the WHERE clause.
• You could build tailored OData Service by copying the OData service and adjust it to a specific table (e.g. set proper column headings).