ABAP on SAP HANA. Part XI. Are Native SQL and Open SQL Competitors?

Are Native and Open SQL competitors? The answer is simple. No. They have their own identity and usage.

Native Syntax in a nutshell:

ABAPers would not like if someone tells them that they are not the real SQL developer. After all, ABAPer rarely deal with Native SQL. Native SQL is considered the real SQL for the database in use.

If you see any code between EXEC SQL — ENDEXEC. It is Native SQL syntax.

What are the possible reasons for adopting Native SQL Approach?
i) Access tables that are not available on DDIC layer. So, we have not choice but to use Native SQL.
ii) To use some of the special features supported by DB-Specific SQL, like passing hints to Oracle Optimizer (for the index which boosts performance) etc.

What are the Pitfalls of Native SQL?
i) One of the not so good properties of Native SQL is that they are loosely integrated into ABAP.
ii) No syntax check at compile time for Native SQL. Statements are directly sent to the database system. Handle exception CX_SQL_EXCEPTION
iii) No automatic client handling, no table buffering.
iv) All tables, in all schemas, can be accessed.

The above drawbacks mean, Developers are responsible for client handling and accessing correct schema. Developers need to take care of releasing DB resources, proper locking and handle the COMMIT efficiently.

Open SQL in a nutshell:

SAP says:

Open SQL consists of a set of ABAP statements that perform operations on the central database in the SAP Web AS ABAP. It is called Open because it is database independent. Open = Platform independent.

Open SQL is the only DB abstraction layer with an SQL-like syntax that defines a common semantic for all SAP-supported databases. Behind the scene, the Kernel programs are busy converting the Open SQL statement to Native SQL statements for the database in use.

Open SQL can only work with database tables that have been created in the ABAP Dictionary.

Open SQL supports more standard SQL features (SQL92)
i) Some limitations of Open SQL removed starting with ABAP 7.4 SP05.
ii) For SAP HANA and other database platforms.

Open SQL supports Code Push down
i) Push down data intense computations and calculations to the HANA DB layer
ii) Avoid bringing all the data to the ABAP layer.

According to SAP, “Code Pushdown” begins with Open SQL
i) Use aggregate functions where relevant instead of doing the aggregations in the ABAP layer
ii) Use arithmetic and string expressions within Open SQL statements
iii) Use computed columns in order to push down computations that would otherwise be done in long loops.
iv) Use CASE and/or IF..ELSE expressions within the Open SQL.

If you have already read the above points somewhere else, then please ignore it. Check the below tables for a quick comparison of Native and Open SQL. I am sure, you have not seen such handy tables elsewhere.

Difference between Native SQL and Open SQL

Seq No Parameters  Native SQL  Open SQL 
Compilation at ABAP layer No Yes 
Database dependency  Yes  No
Table buffering possible  No  Yes 
All Schema Access  Yes  No
Access ABAP Dictionary  No  Yes 
Access to ABAP Core Data Services views  No  Yes 
Conversion of SQL statements to new syntax without any side effect  No  Yes 
Possibility of limiting the result set using ‘UP TO’ statement  No  Yes 
“Keep unnecessary load away from DB  No  Yes 
10  Possibility of Secondary Index  No  Yes 
11  Comparatively faster Aggregation and Calculation  Yes  No 
12  Strict Syntax check  No  Yes 
13  Consumption of parameterized CDS views  No  Yes 
14  Mandatory use of EXEC SQL statement  Yes No

Similarity between Native SQL and Open SQL

Seq No Parameters  Native SQL  Open SQL  Comments
All JOINs availability Yes Yes  Left, Right, Inner & Outer Joins
String operations availability Yes Yes   
Arithmetic Expressions Yes Yes   
Case Expressions Yes  Yes  
Usage of UNION and UNION ALL Yes Yes   
Supports Aggregation, Joins And Sub-Queries Yes Yes   
Code Pushdown Yes- via Database Procedures Yes – via AMDP   
Usage of computed columns to avoid loops – eg: Aggregation and Summation Yes Yes   
Recommendation of using specific fields than using SELECT* statement. Yes Yes   

If you have never written a Native SQL code before, please refer to the Native SQL Example Code Snippet below. Please do not ask, why did we not use Open SQL. This is just an example, my friend. Ideally, we should not be writing Native SQL for EKPO table which is available in DDIC layer.

Till I get a real database table example, be happy with this EXEC SQL — ENDEXEC statement.

TYPES: BEGIN OF ty_ekpo,
         ebeln TYPE ebeln,
         ebelp TYPE ebelp,
         werks TYPE werks_d,
       END OF ty_ekpo.
DATA: wa_ekpo TYPE ty_ekpo,
      it_ekpo TYPE STANDARD TABLE OF ekpo.
PARAMETERS p_werks TYPE werks_d.
* Native SQL Begin
EXEC SQL PERFORMING loop_and_write_output.
  INTO   :wa_ekpo
  FROM   ekpo
  WHERE  werks = :p_werks
* Native SQL End
* Subroutine from Native SQL
FORM loop_and_write_output.
  WRITE: / wa_ekpo-ebeln, wa_ekpo-ebelp, wa_ekpo-werks.

Let us see some output.

Try to put some wrong syntax between EXEC SQL and ENDEXEC. Syntax checker would not catch it and your program would activate successfully, but your program might dump. Do it yourself and have fun.