How to execute an OpenSQL query stored in variable?

Question

 DATA: QUERY TYPE STRING,
  Q2 TYPE STRING,
  MAINQUERY TYPE STRING.
 QUERY = 'SELECT MARA~MATNR MARA~MBRSH MARA~MTART MARA~ERSDA MARA~PACKCODE FROM MARA INTO TABLE ITAB'.

Q2 = 'WHERE MARA~MBRSH = IDNAME.'.
CONCATENATE QUERY Q2 INTO MAINQUERY.
WRITE: /3 MAINQUERY.
MAINQUERY.

Is it possible to store a concatenated select query in variable and execute?? Is there any keyword to run a string in the variable for abap??

...why? How do you plan to provide an appropriate target table? — vwegert, Jul 11 '17 at 14:16
What is the problem you want to solve? Which parts of the select statement have to be variable, which can be fixed? — Gerd Castan, Jul 12 '17 at 09:06

score 0 · Answer 1 · edited Jul 18 '17 at 10:31

Yes, it is possible with native sql injection using via cl_sql_statement class, you can check the documentation on the internet. However, you should be careful, as native sql injections could be dangerous. You can check demo program ADBC_DEMO.

  DATA: con_ref    TYPE REF TO cl_sql_connection,
    l_sqlerr_ref TYPE REF TO cx_sql_exception.
  DATA:
  l_stmt     TYPE string,
  l_stmt_ref TYPE REF TO cl_sql_statement.

  CREATE OBJECT con_ref.
  con_ref = cl_sql_connection=>get_connection( ).
  " create a statement object
  l_stmt_ref = con_ref->create_statement( ).

  "Create query
  CONCATENATE
   'insert into' p_tabsim 'select * from' p_tabreal
  INTO l_stmt SEPARATED BY space.
  TRY .
     l_stmt_ref->execute_update( l_stmt ).

  CATCH cx_sql_exception INTO l_sqlerr_ref.
    MESSAGE 'Problem' TYPE 'W'.
  *    RAISE EXCEPTION l_sqlerr_ref.
  ENDTRY.
  con_ref->commit( ).
  con_ref->close( ).

There is another way to do it which is dynamic select query creation, but you must type "select" key word. Here is some useful information.

Suncatcher · Answer 2 · 2018-03-25T13:48:45.913

Solution based on Oguz's proposal:

 TYPES:  BEGIN OF ty_result,
    matnr TYPE mara-matnr,
    mbrsh TYPE mara-mbrsh,
    mtart TYPE mara-mtart,
    ersda TYPE mara-ersda,
    packcode TYPE mara-packcode,
  END OF ty_result.

DATA:
  lt_result TYPE TABLE OF ty_result,
  lo_result_set TYPE REF TO cl_sql_result_set,
  lx_sql TYPE REF TO cx_sql_exception.

 DATA: QUERY TYPE STRING,
            Q2 TYPE STRING,
  MAINQUERY TYPE STRING.
  QUERY = | SELECT MATNR, MBRSH, MTART, ERSDA, PACKCODE FROM MARA |.
  Q2 = | WHERE MBRSH = 'M' |.

CONCATENATE QUERY Q2 INTO MAINQUERY.

TRY.
  lo_result_set = NEW cl_sql_statement( )->execute_query( mainquery ).
  lo_result_set->set_param_table( REF #( lt_result ) ).

  " Get result
  lo_result_set->next_package( ).
  lo_result_set->close( ).

  CATCH cx_sql_exception INTO lx_sql.
    " Error handling
  WRITE: lx_sql->get_text( ).
ENDTRY.

  IF lt_result IS NOT INITIAL.

    DATA: o_alv TYPE REF TO cl_salv_table.
    DATA: lx_msg TYPE REF TO cx_salv_msg.

    TRY.
        cl_salv_table=>factory(
          IMPORTING
            r_salv_table = o_alv
          CHANGING
            t_table      = lt_result ).
      CATCH cx_salv_msg INTO lx_msg.
    ENDTRY.

ENDIF.

o_alv->display( ).

You should carefully revise your SQL-query as native SQL is highly-dependent on DB-backend. For example, ~ signs in your query are incompatible with most databases, and commas/capitalization is often needed for columns.

There are couple of ways to accomplish this task: you can also use deprecated EXEC SQL, or generate subroutine pool, but these constructs are strongly discouraged nowadays. ABDC is the most simple and up-to-date, as Oguz wisely stated.

How to execute an OpenSQL query stored in variable?

2 Answers2