why ADBC Courser is invalid?

[former_member754878]

Hi there,

I have try to use ADBC to download very big( > 8G) internal table, the code like below:

LO_CONN = cl_sql_connection=>get_connection('R/3*DEMO'). 
L_STMT_REF = NEW cl_sql_statement( con_ref = LO_CONN ).
L_STMT = `SELECT * from xxx`.
L_RES_REF = L_STMT_REF->EXECUTE_QUERY( statement = L_STMT
                                         hold_cursor = 'X' ).
GET REFERENCE OF WA_xxx INTO l_dref.
L_RES_REF->set_param_struct( l_dref ).

TABLE_LINE_COUNT = 0.
CLEAR: IT_xxx.
WHILE L_RES_REF->next( ) > 0.
    APPEND WA_xxx TO IT_xxx.
    TABLE_LINE_COUNT = TABLE_LINE_COUNT + 1.
    IF TABLE_LINE_COUNT >= MAXSIZE.
      PERFORM CONV_TABLE_TO_TEXT TABLES IT_xxx
        USING LV_FILE
              LS_TABLE .
      TABLE_LINE_COUNT = 0.
      CLEAR: IT_xxx,LS_TABLE .
    ENDIF.
  ENDWHILE.
  IF IT_xxx IS NOT INITIAL.
    PERFORM CONV_TABLE_TO_TEXT TABLES IT_xxx
        USING LV_FILE
              LS_TABLE .
    TABLE_LINE_COUNT
    CLEAR: IT_xxx,LS_TABLE .
    TABLE_LINE_COUNT = 0.

  ENDIF.

the CONV_TABLE_TO_TEXT is call GUI_DOWNLOAD function to download data to local computer.

the first cycle is correctly download the data, but second cycle in the while raise the invalid courser exception in 'L_RES_REF->next( )', I debug find next() call ADBC fetch function return sy-subrc = 9 (invalid courser). The courser have checked and it is valid before next() call ADBC fetch function. The revoke code like below.

cursor already exhausted? if yes, do nothing but return with 0.
  IF me->cursor = c_invalid_cursor.
    rows_ret = 0.
    RETURN.
  ENDIF.

  CALL 'C_DB_FUNCTION' ID 'FUNCTION' FIELD 'DB_SQL'
                       ID 'FCODE'    FIELD c_fcode_fetch_cursor
                       ID 'CONNAME'  FIELD me->con_ref->con_name
                       ID 'CONDA'    FIELD me->con_ref->con_da
                       ID 'CURSOR'   FIELD me->cursor
                       ID 'BOUND'    FIELD me->outvals_bound
                       ID 'OUTVALS'  FIELD me->parameters->param_tab
                       ID 'SQLCODE'  FIELD sql_code
                       ID 'SQLMSG'   FIELD sql_msg.

  IF sy-subrc = 0.
*   a row was fetched
    rows_ret = 1.
    add_to_rows_fetched( rows_ret ).

*   set the OUTVALS_BOUND flag; this avoids "reassignement" of the
*   output variables the next time this method is called, i.e. the
*   next fetch call uses the same output variables as the previous
*   call; this optimization works for sure only for the Oracle
*   implementation of the DBDS interface
    IF ( me->outvals_bound IS INITIAL ) AND ( me->con_ref->get_dbms( ) = 'ORA' ).
      me->outvals_bound = 'X'.
    ENDIF.
  ELSE.
    DATA error_code LIKE sy-subrc.
    error_code = sy-subrc.

*   in any case close the cursor
    close_cursor( ).
    rows_ret = 0.

    IF error_code <> 4.
*     call error handling macro.
      m_handle_error error_code sql_code sql_msg.
    ENDIF.
  ENDIF.

so why valid courser raise invalid courser exception after ADBC fetch function?

[gdey_geminius]

Hi Chen,

Please do not use the parameter "HOLD_CURSOR" while calling the EXECUTE_STATEMENT. Only pass the select query.

Please find sample code below:

DATA:
  lt_data type <your data type>,
  lt_corr TYPE adbc_column_tab.

DATA(lo_statement)  = cl_sql_connection=>get_connection( lv_dbco_name )->create_statement( ).

lt_corr = VALUE #(
     ( 'PAYCODE' )
     ( 'DATEOFFICE' )
     ( 'IN1' )
     ( 'IN2' )
     ( 'OUT1' )
     ( 'OUT2' )
   ).

"Execute query
DATA(lo_result_set) = me->go_statement->execute_query(
  |<select quey>|
).

"Get reference of the data
GET REFERENCE OF lt_data INTO lo_data.

"Set table name
lo_result_set->set_param_table(
  EXPORTING
    itab_ref             =  lo_data                " Reference to Output Variable
    corresponding_fields =  lt_corr                " List of Columns of the Internal Table
).

"Get packages
WHILE lo_result_set->next_package( ) GT 0.

  APPEND LINES OF lt_data TO gt_t_data.

  CLEAR lt_data.

ENDWHILE.

Thanks,

Gourab

[gdey_geminius]

Hi Chen,

The code snippet that you have shared contains only one loop, but you mentioned the issue is with second loop.Would you share the complete code snippet?

Just a guess, are you using 2 select statement with different parameters? You can check out below SAP doc for the same.

ADBC Prepared Statement

Thanks,

Gourab

[former_member754878]

Hi Gourab,

thanks for you response, my second loop means second cycle in while statement. And I have read through the doc, the question is why checked valid courser is not valid after call c_db_function fetch data in next().

[gdey_geminius]

Hi Chen,

Please do not use the parameter "HOLD_CURSOR" while calling the EXECUTE_STATEMENT. Only pass the select query.

Please find sample code below:

DATA:
  lt_data type <your data type>,
  lt_corr TYPE adbc_column_tab.

DATA(lo_statement)  = cl_sql_connection=>get_connection( lv_dbco_name )->create_statement( ).

lt_corr = VALUE #(
     ( 'PAYCODE' )
     ( 'DATEOFFICE' )
     ( 'IN1' )
     ( 'IN2' )
     ( 'OUT1' )
     ( 'OUT2' )
   ).

"Execute query
DATA(lo_result_set) = me->go_statement->execute_query(
  |<select quey>|
).

"Get reference of the data
GET REFERENCE OF lt_data INTO lo_data.

"Set table name
lo_result_set->set_param_table(
  EXPORTING
    itab_ref             =  lo_data                " Reference to Output Variable
    corresponding_fields =  lt_corr                " List of Columns of the Internal Table
).

"Get packages
WHILE lo_result_set->next_package( ) GT 0.

  APPEND LINES OF lt_data TO gt_t_data.

  CLEAR lt_data.

ENDWHILE.

Thanks,

Gourab

[former_member754878]

Hi Gourab,

I also have below question.

1.How to set the size of each package in your sample code?

2.How to download in batches instead of after reading all the data in the table in while cycle?

[Sandra_Rossi]

kechencq Good question. The official SAP documentation says: "It reads out at most the number of rows that are passed to the input parameter UPTO. If no value is passed to UPTO, all the rows are read out."

So, to read packages of 10000 lines each.

WHILE lo_result_set->next_package( upto = 1000 ) GT 0.

[Sandra_Rossi]

The parameter HOLD_CURSOR = 'X' can be used, but it's unrelated to the question. It just means that a Database Commit done between the reading of two packages should not close the cursor, otherwise a database commit inside the loop without HOLD_CURSOR = 'X' would raise a runtime error.

[gdey_geminius]

Hi Chen,

You can set the package size as suggest by Sandra.

Regarding batches, you can leverage any date or timestamp field which are available in the DB and download the data in chucks(a perticular time frame, for example 6 months of data at a time). Or look out for any field based on which you can categorized the data and pull one category at a time, for example, sales organization.

Also, you can run the program in background to ensure timeout error doesn't happen.

Thanks,

Gourab

[Sandra_Rossi]

This statement is illogical:

WHILE lo_result_set->next_package( ) GT 0.

because NEXT_PACKAGE( ) loads all the rows at once, and the second execution will always return 0 (0 row loaded).

Either load all lines at once:

lo_result_set->next_package( ).
LOOP AT lt_data ASSIGNING FIELD-SYMBOL(<line>).
  ...
ENDLOOP.

or load/process them by packages:

WHILE lo_result_set->next_package( upto = 1000 ) GT 0.
  LOOP AT lt_data ASSIGNING FIELD-SYMBOL(<line>).
    ...
  ENDLOOP.
  CLEAR lt_data. " important before next NEXT_PACKAGE
ENDWHILE.

[gdey_geminius]

Yes Sandra, correct. As "upto" parameter is missing in the sample code snippet, it will pull all the data in a single package. So, upto parameter should be added.

"Execute query
DATA(lo_result_set) = me->go_statement->execute_query(
  |<select quey>|
).

"Get reference of the data
GET REFERENCE OF lt_data INTO lo_data.

"Set table name
lo_result_set->set_param_table(
  EXPORTING
    itab_ref             =  lo_data                " Reference to Output Variable
    corresponding_fields =  lt_corr                " List of Columns of the Internal Table
).

"Get packages
WHILE lo_result_set->next_package( upto = 10000 ) GT 0.

  APPEND LINES OF lt_data TO gt_t_data.

  CLEAR lt_data.

ENDWHILE.

Thanks,

Gourab

[Sandra_Rossi]

It's "cursor", not "courser".

[Sandra_Rossi]

The parameter HOLD_CURSOR = 'X' can be used, but it's unrelated to the question. It just means that a Database Commit done between the reading of two packages should not close the cursor, otherwise a database commit inside the loop without HOLD_CURSOR = 'X' would raise a runtime error.

Concerning the question, the answer is the same as Gourab except that the parameter UPTO must be used to contain the package size (below 10000 lines per package):

WHILE lo_result_set->next_package( upto = 10000 ) GT 0.
  LOOP AT lt_data ASSIGNING FIELD-SYMBOL(<line>).
    ...
  ENDLOOP.
  CLEAR lt_data. " important before next NEXT_PACKAGE
ENDWHILE.

Excerpts from the official ABAP documentation:

• "It reads out at most the number of rows that are passed to the input parameter UPTO. If no value is passed to UPTO, all the rows are read out."
• "In each call of NEXT_PACKAGE, the rows read are appended to the internal table without deleting the previous contents and the number of rows read is returned in the return value ROWS_RET."