Skip to Content

Retrieve results of HANA stored procedure (built in - consistency_check) without output parameter

From the SQL console, I run

call check_table_consistency(  'CHECK', 'SAPQ13', 'ZTABLE' );

and get a nice results set back when there are inconsistencies.

It would be useful to be able to check programmatically for table consistency. So, I've created a AMDP method,

    TYPES: BEGIN OF check_result_ty,
             schema_name   TYPE c LENGTH 128,
             table_name    TYPE c LENGTH 128,
             column_name   TYPE c LENGTH 128,
             part_id       TYPE c LENGTH 128,
             error_code    TYPE c LENGTH 128,
             error_message TYPE c LENGTH 128,
           END OF check_result_ty.
    TYPES check_results_ty TYPE STANDARD TABLE OF check_result_ty
                            WITH DEFAULT KEY.
...
METHODS check_consistency EXPORTING VALUE(e_results) TYPE check_results_ty.

It's the implementation of the method I'm having trouble with. I want to get the results (if there are any) into the e_results parameter. But the stored procedure check_consistency, doesn't have any output parameters. From the console, I get a result set - is it possible to get this inside my method and pass it to my e_results parameter?

Or is there another way of getting the consistency check results for a single table?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Nov 16, 2016 at 02:50 PM

    In the end I managed it with ADBC.

    PARAMETERS schema TYPE c LENGTH 10.
    PARAMETERS table TYPE tabname.
    
    TRY.
        DATA(sql) = NEW cl_sql_statement( ).
        DATA(result) = sql->execute_query( |CALL check_table_consistency( 'CHECK', '{ schema }', '{ table }' ) WITH OVERVIEW| ).
    
        TYPES: BEGIN OF result_ty,
                 schema_name   TYPE string,
                 table_name    TYPE string,
                 column_name   TYPE string,
                 part_id       TYPE i,
                 error_code    TYPE i,
                 error_message TYPE string,
               END OF result_ty.
        DATA results TYPE STANDARD TABLE OF result_ty.
        GET REFERENCE OF results INTO DATA(results_ref).
        result->set_param_table( results_ref ).
        result->next_package( ).
        result->close( ).
    
        IF results IS INITIAL.
          WRITE: / 'Table', table, 'is consistent'.
        ELSE.
          WRITE: / 'Table', table, 'is not consistent'.
          LOOP AT results INTO DATA(res).
            WRITE: / res-schema_name, res-table_name, res-column_name, 
                     res-part_id, res-error_code, res-error_message.
          ENDLOOP.
        ENDIF.
    
      CATCH cx_root INTO DATA(error).
        MESSAGE error TYPE 'I'.
    ENDTRY.
    
    Add comment
    10|10000 characters needed characters exceeded

  • Nov 04, 2016 at 09:23 PM

    Hello Matthew,

    I do not know a way to catch the result directly from the procedure call.

    But you can determine the check results from table _SYS_STATISTICS.GLOBAL_TABLE_CONSISTENCY in which the procedure the results persists.

    Regards,
    Florian

    Add comment
    10|10000 characters needed characters exceeded