topic Read data from database tables dynamically in Application Development and Automation Discussions

Read data from database tables dynamically

dschiener — Fri, 24 Feb 2023 08:18:25 GMT

Hi there,

I would like to know if it is possible to implement a generic logic to dynamically read from database tables.

I would like to do something like this:

CLASS reader DEFINITION
  CREATE PUBLIC.
  PUBLIC SECTION.

  METHODS read
    IMPORTING
      table TYPE REF TO data.
ENDCLASS.

CLASS reader IMPLEMENTATION.
  METHOD read.
    ##todo " how could I determine the run time type of table as string?
    DATA database_table TYPE string.
    FIELD-SYMBOLS <table_ref> TYPE ANY TABLE.
    ASSIGN table TO <table_ref>.
    ##todo " how could I read data dynamically from (database_table) my result table?
    SELECT
    FROM (database_table)
    FIELDS *
    INTO TABLE @<table_ref>.
  ENDMETHOD.
ENDCLASS.

START-OF-SELECTION.
  DATA table_content TYPE STANDARD TABLE OF pa0015 WITH EMPTY KEY.
  DATA(reader) = NEW reader( ).
  reader->read( table = REF #( table_content ) ).

BR,
Daniel

Re: Read data from database tables dynamically

Sandra_Rossi — Fri, 24 Feb 2023 08:40:58 GMT

The question has been asked a lot in the forum.

You are missing CREATE DATA.

Re: Read data from database tables dynamically

JurgenLootens — Fri, 24 Feb 2023 09:31:45 GMT

This code does something similar and should be of help.

You can pass the name of the database table, the filter string (where clause), the field_string (select clause) and opt to include a header line in the output.

The output is a list of strings, pipe delimited fields. But you can easily change that to be tab delimited by using cl_abap_char_utilities=>horizontal_tab instead.

*"----------------------------------------------------------------------

*"*"Local Interface:
*" IMPORTING
*" VALUE(FILTER_STRING) TYPE STRING OPTIONAL
*" VALUE(FIELD_STRING) TYPE STRING OPTIONAL
*" VALUE(TABLE_NAME) TYPE TABNAME OPTIONAL
*" VALUE(WITH_HEADER_LINE) TYPE XFELD DEFAULT 'X'
*" EXPORTING
*" VALUE(RESULT) TYPE ZTT_STRING_STRUCTURE
*" VALUE(MESSAGE) TYPE STRING
*"----------------------------------------------------------------------

DATA: field_catalog TYPE lvc_t_fcat,

complete_field_catalog TYPE lvc_t_fcat.

CALL FUNCTION 'LVC_FIELDCATALOG_MERGE'
EXPORTING
i_structure_name = table_name
CHANGING
ct_fieldcat = complete_field_catalog
EXCEPTIONS
inconsistent_interface = 1
program_error = 2
OTHERS = 3.

IF sy-subrc <> 0.
message = 'Invalid table name'. RETURN.
ENDIF.

DATA: select_clause TYPE TABLE OF edpline.

DATA: chosen_fields TYPE TABLE OF string.
SPLIT field_string AT ',' INTO TABLE chosen_fields.

IF chosen_fields IS INITIAL.
LOOP AT complete_field_catalog ASSIGNING FIELD-SYMBOL(<fcat>).
CHECK <fcat>-inttype CA 'CDNTP'.
APPEND <fcat>-fieldname TO select_clause.
APPEND <fcat> TO field_catalog.
ENDLOOP.

ELSE.

LOOP AT chosen_fields ASSIGNING FIELD-SYMBOL(<f>).
CONDENSE <f> NO-GAPS.
TRANSLATE <f> TO UPPER CASE.
READ TABLE complete_field_catalog ASSIGNING <fcat> WITH KEY fieldname = <f>.
IF sy-subrc <> 0.
message = `Field ` && <f> && ` is invalid`. RETURN.
ENDIF.

IF <fcat>-inttype NA 'CDNTP'.
message = `Field ` && <f> && ` has a type that is not supported`. RETURN.
ENDIF.

APPEND <fcat>-fieldname TO select_clause.
APPEND <fcat> TO field_catalog.
ENDLOOP.

ENDIF.

IF field_catalog IS INITIAL.
message = 'Empty column selection'. RETURN.
ENDIF.

LOOP AT field_catalog ASSIGNING <fcat>.
<fcat>-col_pos = sy-tabix.
ENDLOOP.

DATA: table_ref TYPE REF TO data.

" build a data structure with just the fields we need.
CALL METHOD cl_alv_table_create=>create_dynamic_table
EXPORTING
i_style_table = ''
it_fieldcatalog = field_catalog
IMPORTING
ep_table = table_ref
EXCEPTIONS
generate_subpool_dir_full = 1
OTHERS = 2.

IF sy-subrc <> 0.
message = 'Unexpected error. Please contact support'. RETURN.
ENDIF.

FIELD-SYMBOLS: <tab> TYPE table.
ASSIGN table_ref->* TO <tab>.

TRY.
SELECT (select_clause) FROM (table_name) INTO TABLE <tab> WHERE (filter_string).
CATCH cx_sy_dynamic_osql_semantics.
message = 'Invalid filter string'. RETURN.
ENDTRY.

IF <tab> IS INITIAL.
message = 'No data found'. RETURN.
ENDIF.

DATA: res TYPE zstring_structure.

IF with_header_line = 'X'.
LOOP AT field_catalog ASSIGNING <fcat>.
IF res IS INITIAL.
res-data = <fcat>-fieldname.
ELSE.
res-data = res-data && '|' && <fcat>-fieldname.
ENDIF.
ENDLOOP.
APPEND res TO result.
ENDIF.

LOOP AT <tab> ASSIGNING FIELD-SYMBOL(<record>).
CLEAR res.
DO.
FIELD-SYMBOLS: <field> TYPE any.
ASSIGN COMPONENT sy-index OF STRUCTURE <record> TO <field>.
IF sy-subrc <> 0. EXIT. ENDIF.

IF res IS INITIAL.
res-data = <field>.
ELSE.
res-data = res-data && `|` && <field>.
ENDIF.
ENDDO.

IF res IS NOT INITIAL.
APPEND res TO result.
ENDIF.

ENDLOOP.

Re: Read data from database tables dynamically

Sandra_Rossi — Fri, 24 Feb 2023 09:55:29 GMT

And you're also missing the Dereferencement operator (->*):

ASSIGN table->* TO <table_ref>.

Re: Read data from database tables dynamically

Sandra_Rossi — Fri, 24 Feb 2023 09:57:28 GMT

And of course, due to dereferencement, <table_ref> is no more a table reference, it's just a table.

You should also catch the situation when the input reference is not a reference to a table.

Re: Read data from database tables dynamically

dschiener — Fri, 24 Feb 2023 11:41:49 GMT

Hi Sandra,

of course you're right. I missed the dereferencement.

Re: Read data from database tables dynamically

dschiener — Fri, 24 Feb 2023 11:51:55 GMT

Hi all,

okay this is what I got now for the moment:

CLASS reader DEFINITION
  CREATE PUBLIC.

  PUBLIC SECTION.
    METHODS read
      IMPORTING
        table TYPE REF TO data.
ENDCLASS.

CLASS reader IMPLEMENTATION.
  METHOD read.
    ##todo " how can I determine the run time time of the input table dynamically?
    DATA database_table TYPE string VALUE 'PA0015'.

    FIELD-SYMBOLS <table_ref> TYPE ANY TABLE.

    ASSIGN table->* TO <table_ref>.

    SELECT
      FROM (database_table)
      FIELDS *
      WHERE attrib = '123456'
      INTO TABLE @<table_ref>.
  ENDMETHOD.
ENDCLASS.


START-OF-SELECTION.
  DATA table_content TYPE STANDARD TABLE OF pa0015 WITH EMPTY KEY.

  DATA(reader) = NEW reader( ).
  reader->read( table = REF #( table_content ) ).

The last step that is missing is the determination of the concrete run time type of the input parameter table of method read. sandra.rossi unfortunately I don't know how CREATE DATA would help me here.

I think by using CREATE DATA I could dynamically allocate / create data.
I don't think that I need to do this in my case because the caller of method read has already allocated memory for the importing internal table...

I think I would have to determine the type of the input table by using RTTI anyhow, but when I call e.g. cl_abap_tabledescr I don't get any processable content that would help me to retrieve the tables name (PA0015).

DATA(descriptor) = cl_abap_tabledescr=>describe_by_data_ref( p_data_ref = table ).

Maybe anyone can give me another hint again :)?

BR
Daniel

Re: Read data from database tables dynamically

Sandra_Rossi — Fri, 24 Feb 2023 12:49:40 GMT

Yes, you're right, I talked about CREATE DATA without checking your code, it seems it's not what you want to do (CREATE DATA dref TYPE TABLE OF (tablename).)

To know the DDIC table name used to define a variable, you need something like this (based on your Descriptor variable) - I do it in several lines so that it's more clear - NB: the code doesn't check whether "table" is really typed based on a database table, it could be a data element or a structure (but you can add more code if you want):

DATA(line_type) = ( CAST cl_abap_tabledescr( descriptor ) )->get_table_line_type( ).
IF line_type->is_ddic_type( ).
  DATA(table_name) = line_type->get_relative_name( ).
ENDIF.