Skip to content

ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  Native SQL →  ADBC - ABAP Database Connectivity →  ADBC - CL_SQL_STATEMENT 

ADBC - Queries

Queries can be executed using the following instance method from class CL_SQL_STATEMENT:

  • EXECUTE_QUERY

The method has a mandatory input parameter STATEMENT of type string that must be passed to a SELECT statement with correct syntax. As with DML statements, the method SET_PARAM can be used to bind ABAP data objects to place holders ?.

As the result of a query, a reference to an object of the class CL_SQL_RESULT_SET is returned in the return value RESULT_SET. The methods of this object allow access to the results set of the query. To preserve the results set beyond the end of a database LUW, the input parameter HOLD_CURSOR of the EXECUTE_QUERY method can be filled with "X".

The class CL_SQL_RESULT_SET of the result object provides the following instance methods for reading the results sets into ABAP data objects:

  • SET_PARAM, NEXT, and CLOSE

    These methods provide access to individual rows and columns of the results set. Using SET_PARAM, compatible ABAP data objects must be assigned to the columns from left to right by passing corresponding data references for each column to the method. NEXT is used to address the rows of the results set one after another. The return value is 1 if the row can be addressed and 0 if not. Reads are closed using CLOSE. If the parameter binding between two calls of NEXT is to be modified, the method CLEAR_PARAMETERS must be called first.
  • SET_PARAM_STRUCT, NEXT, and CLOSE

    These methods provide access to individual rows of the results set. SET_PARAM_STRUCT must be used to assign a fully compatible ABAP structure to the rows of the results set by passing corresponding data references to the method. An internal table that specifies the names and order of the columns to be read can be passed to the parameter CORRESPONDING_FIELDS. For the remaining methods, the same applies as to SET_PARAM.
  • SET_PARAM_TABLE, NEXT_PACKAGE, and CLOSE

    These methods provide access to multiple rows of the results set. SET_PARAM_TABLE must be used to assign a fully compatibly structured internal table to the rows of the results set by passing a corresponding data reference to the method. As with SET_PARAM_STRUCT, a CORRESPONDING_FIELDS parameter is used to specify which columns are to be transported. Here, NEXT_PACKAGE is used instead of NEXT. It reads 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. 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. The same applies when changing parameter bindings and to CLOSE as to SET_PARAM.

Other versions: 7.31 | 7.40 | 7.54


Notes

  • A data reference to an indicator variable with the built-in type INT2 from ABAP Dictionary can be passed to the optional input parameter IND_REF of the method SET_PARAM. Here, the value -1 can be used to display whether a zero value existed on the database.

  • For security reasons, it is better to define the parameters of a query using the placeholder ? rather than chaining dynamic content. This is also a way of preventing SQL injections. If the statement only contains static content from the program and dynamic content from outside the program is possible only in operand positions (using placeholders), the statement cannot be modified from outside.

  • In assignments between fields in database tables and ABAP data objects, a mapping takes place between the ABAP types and the database types. The ABAP types must match the database types. If they do not match, conversions must be made in the Native SQL interface. These conversions are platform-dependent and can raise exceptions.

  • The method EXECUTE_QUERY can also be used to call stored procedures. In databases that meet the requirements, internal tables, for example, can then be bound to the results set. This is not possible with the method EXECUTE PROCEDURE (see the example under this method).


Example

Uses ADBC to read rows sequentially from a database table filled previously using ABAP SQL to a work area.

DELETE FROM demo_update. 
INSERT demo_update FROM TABLE @( 
  VALUE #( ( id = 'X' col1 =  1 col2 =  2 col3 =  3 col4 =  4 ) 
           ( id = 'Y' col1 = 5 col2 = 6 col3 = 7 col4 = 8 ) ) ). 

DATA result TYPE demo_update. 
TRY. 
    DATA(query) = NEW cl_sql_statement( )->execute_query( 
       `SELECT client, id, col1, col2, col3, col4 ` && 
      `       FROM demo_update ` && 
      `       WHERE client = '` && sy-mandt && `' `  ). 
    query->set_param_struct( struct_ref = REF #( result ) ). 
    WHILE query->next( ) > 0. 
      cl_demo_output=>write( result ). 
    ENDWHILE. 
    query->close( ). 
  CATCH cx_sql_exception INTO DATA(exc). 
    cl_demo_output=>display( exc->get_text( ) ). 
    RETURN. 
ENDTRY. 

cl_demo_output=>display( ).

Example

Uses ADBC to read rows from to a database table filled previously with ABAP SQL to an internal table.

DELETE FROM demo_update. 
INSERT demo_update FROM TABLE @( 
  VALUE #( ( id = 'X' col1 =  1 col2 =  2 col3 =  3 col4 =  4 ) 
           ( id = 'Y' col1 = 5 col2 = 6 col3 = 7 col4 = 8 ) ) ). 

DATA result TYPE TABLE OF demo_update WITH EMPTY KEY. 
TRY. 
    DATA(query) = NEW cl_sql_statement( )->execute_query( 
       `SELECT client, id, col1, col2, col3, col4 ` && 
      `       FROM demo_update ` && 
      `       WHERE client = '` && sy-mandt && `' `  ). 
    query->set_param_table( itab_ref = REF #( result ) ). 
    query->next_package( ). 
    query->close( ). 
  CATCH cx_sql_exception INTO DATA(exc). 
    cl_demo_output=>display( exc->get_text( ) ). 
    RETURN. 
ENDTRY. 

cl_demo_output=>display( result ).

Executable Examples

Further Example

The program ADBC_QUERY allows interactive queries that are executed using ADBC to be entered.