ABAP Database Connectivity also known as ADBC provides Object Oriented approach to access the database. You can also access the DB dynamically using the ADBC.
Using ADBC, you can establish Connection the various DB as well as you can execute Database specific SQL commands. ADBC classes were introduced in ABAP release 6.10, but they were documented in Database connectivity as part of ABAP release 7.20.
Involved Classes
- CL_SQL_CONNECTION – This class handles the external DB connection. If you want to connect to any other DB other than your default DB, you need to instantiate the connection object with connection name.
- CL_SQL_STATEMENT – This class contains methods to execute the DB operations. When you instantiate an object for SQL, you can pass the Connection reference of type CL_SQL_CONNECTION. Use the Connection object if you want to execute the statement on other DB.
- CL_SQL_PREPARED_STATEMENT – This class is inherited from CL_SQL_STATEMENT. This class will accept the SQL Statement which is ready to be understood by DB. Since DB doesn’t need to convert translate this to internal format, you can execute this multiple times and improve the performance over using CL_SQL_STATEMENT.
- CL_SQL_RESULT_SET – Class would be used to get the result back from the SQL statements.
- CX_SQL_EXCEPTION – Will raise this exception if any of the error occurs
Demo Program using CL_SQL_STATEMENT
A simple demo program to read the data from default database table T100 using the provided input.
REPORT znp_ADBC_demo. * CLASS lcl_main DEFINITION. PUBLIC SECTION. METHODS: get_data, generate_output. PRIVATE SECTION. DATA: t_output TYPE STANDARD TABLE OF t100. ENDCLASS. "lcl_main DEFINITION * PARAMETERS: p_arbgb TYPE t100-arbgb OBLIGATORY DEFAULT '00'. START-OF-SELECTION. DATA: o_main TYPE REF TO lcl_main. CREATE OBJECT o_main. o_main->get_data( ). o_main->generate_output( ). * CLASS lcl_main IMPLEMENTATION. METHOD get_data. DATA: lo_sql TYPE REF TO cl_sql_statement, lo_result TYPE REF TO cl_sql_result_set, lo_exc TYPE REF TO cx_sql_exception, lt_cols TYPE adbc_column_tab, lv_query TYPE string, lo_output TYPE REF TO data, dref2 TYPE REF TO data. " Fill up the columns which you want to select from the DB " The order should match with the output table APPEND 'SPRSL' TO lt_cols. APPEND 'ARBGB' TO lt_cols. APPEND 'MSGNR' TO lt_cols. APPEND 'TEXT' TO lt_cols. CONCATENATE `SELECT * ` `FROM T100 ` `WHERE sprsl = '` sy-langu `' ` `AND ARBGB = '` p_arbgb `' ` INTO lv_query. TRY. CREATE OBJECT lo_sql. GET REFERENCE OF t_output INTO lo_output. lo_result = lo_sql->execute_query( lv_query ). lo_result->set_param_table( itab_ref = lo_output corresponding_fields = lt_cols ). IF lo_result->next_package( ) > 0. EXIT. ENDIF. CATCH cx_sql_exception INTO lo_exc. MESSAGE lo_exc TYPE 'I' DISPLAY LIKE 'E'. ENDTRY. ENDMETHOD. "get_Data METHOD generate_output. DATA: o_salv TYPE REF TO cl_salv_table. cl_salv_table=>factory( IMPORTING r_salv_table = o_salv CHANGING t_table = t_output ). o_salv->display( ). ENDMETHOD. "generate_otuput ENDCLASS. "lcl_main IMPLEMENTATION
Variations
You can also provide field = ? in the Query and use the method SET_PARAM to set the proper data reference to the field in question.
CONCATENATE
`SELECT * `
`FROM T100 `
`WHERE sprsl = '` sy-langu `' `
`AND ARBGB = ?`
INTO lv_query.
* After SQL Instance & Before Executing the statement
DATA: lo_arbgb TYPE REF TO data.
GET REFERENCE OF p_arbgb INTO lo_arbgb.
lo_sql->set_param( lo_arbgb ).
Explore more about ADBC on ABAP Keyword help.
Loading ...
Hi, Naimesh!
Very nice blog. Thx for it!
I have a question.
What is the advantage of this method of obtaining data?
Is there any way to validate the strings you input manually? The compiler won’t find the syntax errors and that opens a lot of space for errors =/
Hello Konstantin,
ADBC may not be ideal choice over the normal SELECT, but it is useful when you what to access different database other than your default. Like for a BPM (Business Process Monitoring) System, you would need to get data from your ECC, MW, Legacy etc. Using various DB connection, you can access data by leveraging ADBC. It can eliminate the use of Native SQL.
Thanks,
Naimesh Patel
Hello Fawcs,
The comments within the method implementations mention that, the SQL can’t be verified before hand as it largely depends on the database you want to access. I agree with you that this would be disadvantage of using ADBC.
As I pointed out in my comment to Konstantin, that it used heavily in BPM. As a best practice, first you read the metadata of a given table. Using the result of the metadata, you build up a custom table and build a query which you want to execute. This would limit the errors to certain extent.
When you make any error, method
execute_querywill raise an exception. If you want to try out, add statementUP TO 20 ROWSin the SQL and see what happens.Thanks,
Naimesh Patel
Is this better than using Native SQL? Is it faster?
Hello ABAPper,
I haven’t done any research yet on the performance of Naitve vs Access using ADBC. I would try to compare some results and publish it in an article.
Thanks,
Naimesh Patel
Thanks for the answer Naimesh,
It should be harder for us(ABAP Developers) to get used to this kind of syntax since we are used to the syntax check… But seing some hana codes, i’ve seen some similar SQL statements and it looks like we should get used to it