Skip to Content
1

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

Nov 04, 2016 at 03:46 PM

469

avatar image

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?

SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Matthew Billingham
Nov 16, 2016 at 02:50 PM
1

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.
Share
10 |10000 characters needed characters left characters exceeded
Florian Pfeffer
Nov 04, 2016 at 09:23 PM
2

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded

I did wonder whether the manual check for a single table updated that - but I got distracted trying other things. I'll have a look and see. It does seem odd though that you can run the procedure in the console and get results, but not programmatically.

0

It turns out that this _SYS_STATISTICS_GLOBAL_TABLE_CONSISTENCY is not updated when you call CHECK_TABLE_CONSISTENCY manually.

0