ABAP on SAP HANA. Part VIII. ADBC – ABAP DataBase Connectivity

4
10
ADBC

ADBC – ABAP DataBase Connectivity 

In our earlier post, we learned about Bottom Up Approach in SAP HANA.  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).

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
1. Native SQL allows us to use database-specific SQL statements in an ABAP program
2. No Syntax check of the SQL statement is performed. If there is an issue, we come to know only at runtime.
3. 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:

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

Read more about New Age Open SQL ABAP 740

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

1. 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).

2. Hashed and Sorted tables are not allowed as the target. So, the standard table is still the king.

3. If you are using ADBC, do not forget to handle the client/mandt explicitly in your code.

4. 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

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())

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.

1. CL_SQL_CONNECTION
2. CL_SQL_STATEMENT
3. 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.

ADBC

If you are in HANA, it is a good practice to test the native SQL in SQL editor at HANA Studio.

Also Read: Know about SAP HANA Studio icons and buttons

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.

Let us check the output for HANA database users.

HANA for ABAPer

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. ADBC Usage for Oracle DB

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.

ADBC

In debug mode we can verify that it is connected to ORACLE system.

Database Connectivity in SAP

Let us check the output for the same program with ORACLE database users.

CL_SQL_RESULT_SET

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.

ADBC in ABAP

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).

ADBC in SAP

Have questions about HANA? Check SAP HANA from Space Level.

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 YSAPYARD is defined in ABAP system YARD1 with 10 fields and the same table YSAPYARD has two extra fields in ABAP system YARD2. But the HANA database has been updated with only 10 fields. So, if someone does SELECT * from system YARD2 (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.

In the next post, we would look into AMDP which is the most preferred method and it obeys Top Down Approach of Code to Data Paradigm.

If you want to get such useful articles directly to your inbox, please SUBSCRIBE. We respect your privacy and take protecting it seriously.

If you liked this post, please hit the share buttons and like us on facebook.

Do you have anything to add to this article? Have you faced any issue using ADBC? Do you want to share any real project requirement or solutions? Please do not hold back. Please leave your thoughts in the comment section.

Thank you very much for your time!!

Useful Tutorials in SAPYard

1. ABAP for SAP HANA Tutorials
2. ABAP Web Dynpro Tutorials
3. GOS Tutorial
4. OOPs ABAP Tutorial
5. HANA Tutorial
6. SAP Netweaver and OData Tutorial
7. SAP Adobe Form Tutorial
8. SAP Fiori Tutorial
9. SAPUI5 Tutorial

Call for Guest Authors and Contributors to write SAP Articles on our page and get noticed and also receive cool Gifts.

Do you have any tips or tricks to share? Do you want to write some articles at SAPYard? Please REGISTER and start posting and sharing your knowledge to the SAP world and get connected to your readers. Please check our simple guidelines for contributing your articles and receiving the gifts.

4 COMMENTS

    • Dear Jiten,
      Thank you very much for your feedback.

      Unfortunately, at this point of time we do not have HANA system to share. But if you are looking for paid service (nominal fee) regarding HANA system, we can direct to a genuine vendor whose system we have used in past on monthly paid plan.

      Regards,
      Team SAPYard.

Leave a Reply to SAP Yard Cancel reply

Please enter your comment!
Please enter your name here