Understanding ADBC: Dynamic Database Connectivity in ABAP

Posted on by binarybuggs

ADBC

ADBC methods enable the transmission of database-specific SQL commands to a database system and facilitate processing of the results for establishing and managing database connections. While Native SQL statements provide solely static access to the Native SQL interface, ADBC allows for an object-oriented and dynamic approach to access.

ABAP Database Connectivity (ADBC) is an API within the AS ABAP that leverages ABAP Objects for interacting with the Native SQL interface. ADBC methods facilitate the transmission of database-specific SQL commands to a database system and enable processing of the results to establish and manage database connections. While Native SQL statements offer only static access to the Native SQL interface, ADBC provides an object-oriented and dynamic approach. Notably, ADBC (ABAP DataBase Connectivity) is not specific to HANA and is database-independent. Here’s an example of Native SQL statements within ADBC:

EXEC SQL.
<Native SQL statements to perform the requirement>
ENDEXEC.


EXEC SQL.
select matne mtart 
into : w_mara
from mara
where matnr =: p_matnr.
END EXEC.

Technically, ADBC facilitates the generation of native SQL code, which is executed at the database layer. However, ADBC streamlines and organizes the process of connecting to the database and transferring the native SQL code for execution at the database layer. In simpler terms, ADBC utilizes an object-oriented approach to establish connections to the database and perform required tasks.

Features of ADBC-

  1. Syntax check cannot be used, we will come to know about the issue at runtime
  2. exceptions needs to be handled
  3. always release the Query

There are 8 generic steps performed in an ADBC call

There are 8 generic steps performed in an ADBC call

  • 1. Set the database connection (CL_SQL_CONNECTION=>GET_CONNECTION)
  • 2. Instantiate the statement object (CL_SQL_STATEMENT)
  • 3. Construct the SQL using Concatenate syntax or string operation (check with SQL Console for syntax in HANA Studio or use t-code DBACOCKPIT if you are not on HANA DB yet)
  • 4. Issue Native SQL Call (EXECUTE_QUERY, EXECUTE_DDL, EXECUTE_UPDATE)
  • There are three methods to execute SQL statements.
    • EXECUTE_QUERY – For Queries (SELECT statements). An instance of CL_SQL_RESULT_SET is returned as the result of the query.
    • EXECUTE_DDL – For DDL (CREATE, DROP, or ALTER). No returning parameter.
    • EXECUTE_UPDATE – For DML (INSERT, UPDATE, or DELETE). Returns the number of table rows processed in ROWS_PROCESSED.
  • 5. Assign Target variable for result set (CL_SQL_RESULT_SET, methods SET_PARAM(), SET_PARAM_TABLE())
  • 6. Retrieve Result set (CL_SQL_RESULT_SET=>NEXT_PACKAGE)
  • 7. Close the query and release resources (CL_SQL_RESULT_SET method CLOSE())
  • 8. Close database connection (CL_SQL_CONNECTION; method CLOSE())

CX_SQL_EXCEPTION  to handle exceptionshe

to check if there is proper connection with the database

lr_sql_connection ?= cl_sql_connection=>get_connection( ).

Below is an Example for the same:-

*&———————————————————————*
*& Report zr_adbc_simple
*&
*&———————————————————————*
*&
*&
*&———————————————————————*
REPORT zr_adbc_simple.
“tables : snwd_bpa,snwd_so.
types :
begin of ty_res,
bp_id type snwd_bpa-bp_id,
company_name type snwd_bpa-company_name,
currency_code type snwd_so-currency_code,
total_gross_amount type snwd_so-gross_amount,
end of ty_res.

data : lv_stmt type string,
lo_stmt type ref to cl_sql_statement,
lo_res type ref to cl_sql_result_set,

lr_sql_connection TYPE REF TO cl_sql_connection,
lt_result type standard table of ty_res with empty key.

IF lr_sql_connection IS BOUND.

lv_stmt = |SELECT BP_ID, COMPANY_NAME, SO.CURRENCY_CODE, | &&
|SUM( SO.GROSS_AMOUNT ) as TOTAL_GROSS_AMOUNT | &&
|FROM SNWD_BPA AS BPA | &&
|INNER JOIN SNWD_SO AS SO | &&
|ON SO.BUYER_GUID = BPA.NODE_KEY | &&
|GROUP BY BP_ID, COMPANY_NAME, SO.CURRENCY_CODE |.

try.
“create statement object
lo_stmt = new cl_sql_statement( ).
“execute the query
lo_res = lo_stmt->execute_query( lv_stmt ).
“set output paramater
lo_res->set_param_table( ref #( lt_result ) ).
“fetch the result
lo_res->next_package( ).
“release the resource
lo_res->close( ).

catch cx_sql_exception into data(lx).
write lx->sql_message.

endtry.

endif.

cl_demo_output=>display_data( lt_result ).

Leave a comment