ADBC – ABAP DataBase Connectivity
In this article, we would check about Database Connectivity. Although the title says, SAP ABAP for HANA, but let me clarify, ADBC (ABAP DataBase Connectivity) is not the proprietary feature of HANA. This property is database independent. Years ago even before we heard about HANA, ABAPer used to connect to underlying database explicitly using native SQL and perform the needful activity. If you have ever had the opportunity to work in that area, then you would remember how you used something like below code snippet (with or without knowing what you were doing).
EXEC SQL.
<Native SQL statements to perform the requirement>
ENDEXEC.Why was there the need to use Native SQL?
Answer: Performance is not always the culprit. The most generic reason why Native SQL was used is, the database tables were not available in SAP Data Dictionary. Yes, you read it right. There are numerous tables in the database, which do not find the dignity of residing at both places (database and SAP Data Dictionary). And business might be using those database specific tables for some business case. In such cases, native SQL used to be the life saver.
Some salient features of Native SQL
- Native SQL allows us to use database-specific SQL statements in an ABAP program
- No Syntax check of the SQL statement is performed. If there is an issue, we come to know only at runtime.
- The data is transported between the database table and the ABAP program using host variables. Host variables? Forget it. It is the same work areas and variables (line in open SQL) which have additional “:’ (colon) in front.
For the sake of clarity:
For the sake of clarity:
EXEC SQL.
SELECT matnr mtart bismt
INTO :wa_mara
FROM mara
WHERE matnr = :p_matnr
ENDEXEC.The above example does not justify the usage of native SQL, as MARA should reside at both places. Just replace MARA with something like ORA_INV_MGT table which is not available in SE11. ? So, in the above example concentrate on : P_MATNR and : WA_MARA (the host variables).
Let us also recap the salient features of Open SQL
1. Open SQL provides a uniform syntax and semantics for all of the database systems supported by SAP. Therefore it is called Open. Open to all Database.
What does the above statement mean?
ABAP Programs that only use Open SQL statements will work in any SAP system, regardless of the below database system.
2. Open SQL statements can only work for database tables that have been created/replicated in the ABAP Dictionary
3. Open SQL can be used via secondary database connections too
I think we have built up enough background and refresher to finally come to the topic of the day, i.e. ADBC. If native SQL was already doing what Open SQL could not do, then what was the need of introducing another jargon ADBC. Sometimes if you make something look complex, people tend to think it superior and better. But ADBC is not just another bombastic word. It is definitely better than native SQL as explained below.
ADBC is an object base API. This API determines where native SQL calls have been made and supports exception handling better. Technically, ADBC writes native SQL which would be executed at the database layer. But, ADBC makes the process of connecting to the database and transferring the native SQL code to be executed at database layer smoother and organized. In simple terms, the object-oriented approach is used by ADBC to connect to the database and perform the needed task.
Object Oriented approach bring with it flexibility and ADBC is found in WHERE USED LIST and also error handling of the same native SQL code is better in ADBC.
Salient feature of ADBC
- Just like native SQL, syntax checker cannot catch issues in the code which the underlying database is expecting. We need to handle the exceptions properly (usually cx_sql_exception is implemented).
- Hashed and Sorted tables are not allowed as the target. So, the standard table is still the king.
- If you are using ADBC, do not forget to handle the client/mandt explicitly in your code.
- ADBC does not necessarily release the allocated memory/resource on the DB. As a good practice, we should always close the query.
There are 8 generic steps performed in an ADBC call
- Set the database connection (CL_SQL_CONNECTION=>GET_CONNECTION)
- Instantiate the statement object (CL_SQL_STATEMENT)
- 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)
- 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. - Assign Target variable for result set (CL_SQL_RESULT_SET, methods SET_PARAM(), SET_PARAM_TABLE())
- Retrieve Result set (CL_SQL_RESULT_SET=>NEXT_PACKAGE)
- Close the query and release resources (CL_SQL_RESULT_SET method CLOSE())
- Close database connection (CL_SQL_CONNECTION; method CLOSE())
Important Classes in ADBC
We have been singing Object Oriented Approach for quite some time in this article, so some of the classes and methods do need a mention here. What do you guys say? The above 8 steps help us narrow down to three important classes in ADBC.
- CL_SQL_CONNECTION
- CL_SQL_STATEMENT
- CL_SQL_RESULT_SET
Error handling is one of the important advantages of ADBC so CX_SQL_EXCEPTION is the fourth important class in ADBC.
Below code shows the usage of ADBC in ABAP which has HANA as the database. The most important part is building the native SQL correctly (using string operations or CONCATENATE statement) as per the database and passing it in the string.
If you are in HANA, it is a good practice to test the native SQL in SQL editor at HANA Studio.
If the database is not HANA and you do not have SQL editor (HANA studio) do not be disheartened. You can still check the native SQL at DBACOCKPIT. It is shown a little below in this article.
For HANA Database user, your first ADBC program is below. The ADBC API in the program is self-explanatory and easy to implement. So, EXEC SQL – ENDEXEC would definitely be the thing of the past. This program is for those lucky ones who are already in HANA database. Others can scroll down below to find the program for the non-HANA system. This program would not return any result if you are not in HANA, as the native SQL is dependent on the database. The native SQL written below is compatible with HANA only.
* Type for output
TYPES: BEGIN OF ty_result,
matnr TYPE matnr,
mtart TYPE mtart,
maktx TYPE maktx,
END OF ty_result.
* Data declaration
DATA: lr_sql_connection TYPE REF TO cl_sql_connection,
lr_sql_statement TYPE REF TO cl_sql_statement,
lr_sql_result_set TYPE REF TO cl_sql_result_set,
lr_sql_exception TYPE REF TO cx_sql_exception,
lr_sql_parameter_invalid TYPE REF TO cx_parameter_invalid,
lr_parameter_invalid_type TYPE REF TO cx_parameter_invalid_type,
lr_salv_exception TYPE REF TO cx_salv_msg,
lr_salv_alv TYPE REF TO cl_salv_table,
lt_result TYPE STANDARD TABLE OF ty_result,
ls_result TYPE ty_result,
lr_data TYPE REF TO data,
lv_where_clause_statement TYPE string,
lv_error_text TYPE string,
lv_where_mandt TYPE string,
lv_where_spras TYPE string.
* Selection screen fields
SELECT-OPTIONS : s_matnr FOR ls_result-matnr,
s_mtart FOR ls_result-mtart.
* Connect to dabatabse (HANA or Non-HANA)
* 1 Set the database connection
PERFORM make_db_connection.
* Instantiate SQL Statement
* i.e Get the SQL Statement reference using the instance of the connection
* 2. Instantiate the statement object
PERFORM ini_sql_statement.
* Prepare Native SQL statements
* 3. Construct the SQL using Concatenate syntax or string operation
PERFORM prepare_native_sql_string.
* Using the reference of the statement call, the respective methods to execute the query
* 4. Issue Native SQL Call
PERFORM issue_native_sql_call.
* Get the result of the query in a table
* 5. Assign Target variable for result set
PERFORM assign_target_result.
* 6. Retrieve Result set
PERFORM retrieve_complete_result_set.
* 7. Close the query, release resource
PERFORM close_query.
* 8. Close DB Connection
PERFORM close_db_connection.
* 9. Display output
PERFORM display_result.
**&---------------------------------------------------------------------*
**& Sub Routines
**&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*& Form MAKE_DB_CONNECTION
*&---------------------------------------------------------------------*
* Connect to database
*----------------------------------------------------------------------*
FORM make_db_connection .
TRY.
* Get the DB (HANA/Non HANA) Connection
* If we do not pass the DB name, it would pull the default database
lr_sql_connection ?= cl_sql_connection=>get_connection( ).
* 10. Catch errors/exceptions (if any)
CATCH cx_parameter_invalid_type INTO lr_parameter_invalid_type.
lv_error_text = lr_parameter_invalid_type->get_text( ).
MESSAGE e000 WITH lv_error_text.
CATCH cx_parameter_invalid INTO lr_sql_parameter_invalid.
lv_error_text = lr_sql_parameter_invalid->get_text( ).
MESSAGE e001 WITH lv_error_text.
CATCH cx_sql_exception INTO lr_sql_exception.
lv_error_text = lr_sql_exception->get_text( ).
MESSAGE e001 WITH lv_error_text.
CATCH cx_salv_msg INTO lr_salv_exception.
lv_error_text = lr_salv_exception->get_text( ).
MESSAGE e001 WITH lv_error_text.
ENDTRY.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form INI_SQL_STATEMENT
*&---------------------------------------------------------------------*
* Instantiate the statement object
*----------------------------------------------------------------------*
FORM ini_sql_statement .
IF lr_sql_connection IS BOUND.
TRY.
* Get the SQL Statement reference using the instance of the connection
CREATE OBJECT lr_sql_statement
EXPORTING
con_ref = lr_sql_connection. " Database Connection
* 10. Catch errors/exceptions (if any)
CATCH cx_parameter_invalid_type INTO lr_parameter_invalid_type.
lv_error_text = lr_parameter_invalid_type->get_text( ).
MESSAGE e000 WITH lv_error_text.
CATCH cx_parameter_invalid INTO lr_sql_parameter_invalid.
lv_error_text = lr_sql_parameter_invalid->get_text( ).
MESSAGE e001 WITH lv_error_text.
CATCH cx_sql_exception INTO lr_sql_exception.
lv_error_text = lr_sql_exception->get_text( ).
MESSAGE e001 WITH lv_error_text.
CATCH cx_salv_msg INTO lr_salv_exception.
lv_error_text = lr_salv_exception->get_text( ).
MESSAGE e001 WITH lv_error_text.
ENDTRY.
IF lr_sql_connection IS NOT BOUND.
MESSAGE 'No reference to SQL Statements made' TYPE 'I'.
LEAVE LIST-PROCESSING.
ENDIF.
ELSE.
MESSAGE 'No connection established' TYPE 'I'.
LEAVE LIST-PROCESSING.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form PREPARE_NATIVE_SQL_STRING
*&---------------------------------------------------------------------*
* Construct the SQL using Concatenate syntax or string operation
*----------------------------------------------------------------------*
FORM prepare_native_sql_string .
* In line data declaration and converting selection option to a where clause string for S_MATNR
DATA(lr_seltab) = cl_lib_seltab=>new( it_sel = s_matnr[] ).
DATA(lv_where_clause_sel) = lr_seltab->sql_where_condition( iv_field = 'M.MATNR' ).
* In line data declaration and converting selection option to a where clause string for S_MTART
DATA(lr_seltab2) = cl_lib_seltab=>new( it_sel = s_mtart[] ).
DATA(lv_where_clause_sel2) = lr_seltab2->sql_where_condition( iv_field = 'M.MTART' ).
*--------------------------------------------------------------------*
* Begin of script for HANA Database
*--------------------------------------------------------------------*
* Construct the SQL in SQL Console Eclipse and put it in a string ( Native SQL Only )
* Modern sysntax for concatenation
lv_where_clause_statement = | SELECT M.MATNR, M.MTART, T.MAKTX |
&& | FROM MARA AS M INNER JOIN MAKT AS T |
&& | ON M.MATNR = T.MATNR |
&& | WHERE M.MANDT = '{ sy-mandt }' |
&& | AND T.SPRAS = '{ sy-langu }' |
&& | AND { lv_where_clause_sel } |
&& | AND { lv_where_clause_sel2 } |
&& | ORDER BY M.MATNR |.
*--------------------------------------------------------------------*
* End of script for HANA Database
*--------------------------------------------------------------------*
** Modern sysntax for Concatenation
* lv_where_mandt = |'| && |{ sy-mandt }| && |'|.
* lv_where_spras = |'| && |{ sy-langu }| && |'|.
*
* lv_where_mandt = |M.MANDT = | && | { lv_where_mandt }|.
* lv_where_spras = |T.SPRAS = | && | { lv_where_spras }|.
*
**--------------------------------------------------------------------*
** Begin of script for ORACLE Database
**--------------------------------------------------------------------*
** Construct the SQL in SQL Console Eclipse and put it in a string ( Native SQL Only )
* lv_where_clause_statement = | SELECT M.MATNR, M.MTART, T.MAKTX |
* && | FROM MARA M, MAKT T |
* && | WHERE M.MATNR = T.MATNR |
* && | AND { lv_where_mandt } |
* && | AND { lv_where_spras } |
* && | AND { lv_where_clause_sel } |
* && | AND { lv_where_clause_sel2 } |.
**--------------------------------------------------------------------*
** End of script for ORACLE Database
**--------------------------------------------------------------------*
* If you find difficulty in understanding above concatenate/string operation,
* Then check below. It does the same thing as above.
* CONCATENATE '''' sy-mandt '''' INTO lv_where_mandt.
* CONCATENATE '''' sy-langu '''' INTO lv_where_spras.
*
* CONCATENATE 'M.MANDT = ' lv_where_mandt INTO lv_where_mandt SEPARATED BY space.
* CONCATENATE 'T.SPRAS = ' lv_where_spras INTO lv_where_spras SEPARATED BY space.
*
* construct the sql in sql command editor in dbacockpit
* below sql works for oracle database
* concatenate 'SELECT M.MATNR, M.MTART, T.MAKTX'
* 'FROM MARA M, MAKT T'
* 'WHERE M.MATNR = T.MATNR'
* 'AND' lv_where_mandt
* 'AND' lv_where_spras
* 'and' lv_where_clause_sel
* 'and' lv_where_clause_sel2
* into lv_where_clause_statement separated by space.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form ISSUE_NATIVE_SQL_CALL
*&---------------------------------------------------------------------*
* Issue Native SQL Call
*----------------------------------------------------------------------*
FORM issue_native_sql_call .
TRY.
* Using the reference of the statement call the respective methods to execute the query
lr_sql_statement->execute_query(
EXPORTING
statement = lv_where_clause_statement " SELECT Statement Being Executed
hold_cursor = space
RECEIVING
result_set = lr_sql_result_set ). " Database Cursor
* 10. Catch errors/exceptions (if any)
CATCH cx_parameter_invalid_type INTO lr_parameter_invalid_type.
lv_error_text = lr_parameter_invalid_type->get_text( ).
MESSAGE e000 WITH lv_error_text.
CATCH cx_parameter_invalid INTO lr_sql_parameter_invalid.
lv_error_text = lr_sql_parameter_invalid->get_text( ).
MESSAGE e001 WITH lv_error_text.
CATCH cx_sql_exception INTO lr_sql_exception.
lv_error_text = lr_sql_exception->get_text( ).
MESSAGE e001 WITH lv_error_text.
CATCH cx_salv_msg INTO lr_salv_exception.
lv_error_text = lr_salv_exception->get_text( ).
MESSAGE e001 WITH lv_error_text.
ENDTRY.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form ASSIGN_TARGET_RESULT
*&---------------------------------------------------------------------*
* Assign Target variable for result set
*----------------------------------------------------------------------*
FORM assign_target_result .
TRY.
* Get the result of the query in a table
GET REFERENCE OF lt_result INTO lr_data.
lr_sql_result_set->set_param_table(
EXPORTING
itab_ref = lr_data ). " Reference to Output Variable
* 10. Catch errors/exceptions (if any)
CATCH cx_parameter_invalid_type INTO lr_parameter_invalid_type.
lv_error_text = lr_parameter_invalid_type->get_text( ).
MESSAGE e000 WITH lv_error_text.
CATCH cx_parameter_invalid INTO lr_sql_parameter_invalid.
lv_error_text = lr_sql_parameter_invalid->get_text( ).
MESSAGE e001 WITH lv_error_text.
CATCH cx_sql_exception INTO lr_sql_exception.
lv_error_text = lr_sql_exception->get_text( ).
MESSAGE e001 WITH lv_error_text.
CATCH cx_salv_msg INTO lr_salv_exception.
lv_error_text = lr_salv_exception->get_text( ).
MESSAGE e001 WITH lv_error_text.
ENDTRY.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form RETRIEVE_COMPLETE_RESULT_SET
*&---------------------------------------------------------------------*
* Retrieve Result set
*----------------------------------------------------------------------*
FORM retrieve_complete_result_set .
TRY.
lr_sql_result_set->next_package( ).
CATCH cx_parameter_invalid_type INTO lr_parameter_invalid_type.
lv_error_text = lr_parameter_invalid_type->get_text( ).
MESSAGE e000 WITH lv_error_text.
CATCH cx_parameter_invalid INTO lr_sql_parameter_invalid.
lv_error_text = lr_sql_parameter_invalid->get_text( ).
MESSAGE e001 WITH lv_error_text.
CATCH cx_sql_exception INTO lr_sql_exception.
lv_error_text = lr_sql_exception->get_text( ).
MESSAGE e001 WITH lv_error_text.
CATCH cx_salv_msg INTO lr_salv_exception.
lv_error_text = lr_salv_exception->get_text( ).
MESSAGE e001 WITH lv_error_text.
ENDTRY.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form CLOSE_QUERY
*&---------------------------------------------------------------------*
* Close the query, release resources
*----------------------------------------------------------------------*
FORM close_query .
lr_sql_result_set->close( ).
ENDFORM.
*&---------------------------------------------------------------------*
*& Form CLOSE_DB_CONNECTION
*&---------------------------------------------------------------------*
* Close DB connection
*----------------------------------------------------------------------*
FORM close_db_connection .
lr_sql_connection->close( ).
ENDFORM.
*&---------------------------------------------------------------------*
*& Form DISPLAY_RESULT
*&---------------------------------------------------------------------*
* Display ALV
*----------------------------------------------------------------------*
FORM display_result .
* Display the data in an ALV
cl_salv_table=>factory(
IMPORTING
r_salv_table = lr_salv_alv " Basic Class Simple ALV Tables
CHANGING
t_table = lt_result ).
* Show the output
lr_salv_alv->display( ).
ENDFORM.
For other Database users, your first ADBC program is the same as above with little change. Native SQL is not platform independent. In order to make the native SQLcompatible with ORACLE database, just comment the code in between below two tags for HANA.
*——————————————————————–*
* Begin of script for HANA Database
*——————————————————————–*
*——————————————————————–*
* End of script for HANA Database
*——————————————————————–*
And uncomment the tags in between the below two tags for ORACLE database.
*——————————————————————–*
* Begin of script for ORACLE Database
*——————————————————————–*
*——————————————————————–*
* End of script for ORACLE Database
*——————————————————————–*Program to demonstrate ADBC using non-HANA (Oracle) database.
The code is in subroutine PREPARE_NATIVE_SQL_STRING in the above code snippet.
If the native SQL is not prepared correctly, we get errors like show here.
In debug mode we can verify that it is connected to ORACLE system.
Let us check the output for the same program with ORACLE database users.
DBACOCKPIT
If you are in HANA database, you can easily check the syntax of native SQL in SQL editor at HANA Studio. But if you do not have HANA database, you can check the native SQL of your database using t-code DBACOCKPIT. Just follow the path shown in below image. Execute or hit F8 and if there is any issue in the SQL, you can easily find them in the error/message log window at the bottom.
Check the native SQL for ORACLE database. The JOIN statement for ORACLE is different. There is no explicit JOIN command. Two tables to be joined are separated by comma. I had to waste few hours just to figure this out (as I have no ORACLE SQL experience) :). Also, check fields selected are separated by comma and there is no Tilda (as in open SQL joins).
Some frequently asked questions on ADBC.
1. If a table resides both at Data Dictionary and Database. Does it make sense to use native SQL and/or ADBC so that the table is encountered at the database level itself?
Answer: SAP/HANA experts say that if the table resides both at database and SAP data dictionary, Open SQL should always be the first choice. Open SQL is optimized for communication with the database. If someone tries to be adventurous by using native SQL or ADBC when it is not needed, then it might worsen the performance because of overhead (like connection, constructor calls, statement class, query etc) in the ADBC framework.
2. If a table resides only in the Database, what should be used? Native SQL by using EXEC SQL – ENDEXEC or by calling ADBC?
Answer: SAP/HANA experts say, ADBC should be the choice in this case (even though EXEC SQL – ENDEXEC would do the same). Not necessarily for any performance advantage but for the ease of programming, clean OOPs concept, better error handling and modern method.
3. Can we have secondary Database connection from more than one ABAP system to single HANA database?
Answer: Yes, we can connect to the same secondary HANA Database system from more than one ABAP system and use Open SQL to query the data. But if we need to make sure all the custom tables and extensions to the standard table is identical in all ABAP system and HANA database (i.e. ABAP-based system and DDIC information on DB tables is identical).
For example, a custom table YSAPSPOT is defined in ABAP system SPOT1 with 10 fields and the same table YSAPSPOT has two extra fields in ABAP system SPOT2. But the HANA database has been updated with only 10 fields. So, if someone does SELECT * from system SPOT2 (which as 2 extra fields), then there would be problem as the Database and ABAP system information are not same.
So if we want to connect to same HANA database from multiple ABAP systems, we need to take care of such subtle information. In order to make Open SQL work with secondary database connection, the table reference must exist in the ABAP Data Dictionary and must match exactly – names, data types etc
4. Is database connection from ABAP specific to HANA technology?
Answer: No. ADBC is not a HANA specific technology. It is supported for all ABAP supported database types/operating system combinations. It can be used for connecting to ORACLE/MSSQL (Microsoft SQL server) etc from ABAP as long as the ORACLE/MSSQL etc kernel files are loaded into the ABAP system.
5. What is the syntax to call specific database system?
Answer: lr_sql_connection ?= cl_sql_connection=>get_connection( ‘ORA’ ).
6. Can ADBC return more than one output tables?
Answer: No. The ADBC interface only allows one parameter table, so we cannot receive more than one table output from one ADBC call. We need to call ADBC multiple times to return multiple tables.
