Varad
To put Native Syntax briefly:
Telling an ABAPer that they are not the true SQL developer would not sit well with them.ABAPers don’t often work with Native SQL, after all.For the database that’s being used, native SQL is regarded as the actual SQL.
If any code appears between ENDEXEC and EXEC SQL. The syntax is Native SQL.
What could be the motivations behind using the Native SQL Approach?
The first option is to access tables that are not present in the DDIC layer. Thus, we are forced to use Native SQL.
ii) To make use of some of the unique functions provided by DB-Specific SQL, such as providing Oracle Optimizer with hints (for the index that improves performance), etc.
What Drawbacks Does Native SQL Have?
Answer: i) The fact that Native SQL is only tangentially connected with ABAP is one of its drawbacks.
ii) Native SQL does not have a syntax check at compilation time. Direct transmission of statements to the database system occurs. Manage the exception CX_SQL_EXCEPTION
iii) No table buffering, no automatic client handling.
iv) Access is available to every table across all schemas.
Because of the aforementioned issues, developers are in charge of client handling and correct schema access.It is the responsibility of developers to release database resources, lock files correctly, and manage commits effectively.
In summary, Open SQL:
According to SAP, Open SQL is made up of a number of ABAP statements that operate on the SAP Web AS ABAP’s central database.Because it is database independent, it goes by the name Open.Open equates to cross-platform.
The only database abstraction layer that offers a consistent semantic for all SAP-supported databases is Open SQL, and it has a syntax similar to SQL. The Open SQL statement is being transformed into Native SQL statements for the database in use by the kernel routines working behind the scenes.
Only database tables created in the ABAP Dictionary are compatible with Open SQL.
More common SQL functionalities are supported by Open SQL (SQL92).
i) Beginning with ABAP 7.4 SP05, some Open SQL constraints were eliminated.
ii) For database platforms such as SAP HANA.
Code is supported by Open SQL. Push down: i) Transfer computations and calculations involving large amounts of data to the HANA DB layer; ii) Refrain from moving all data to the ABAP layer.
Open SQL is where “Code Pushdown,” as SAP puts it, begins.
i) Use aggregate functions instead of doing the aggregations in the ABAP layer where applicable.
ii) Make use of mathematical and string expressions in Open SQL statements
iii) Use computed columns to push down computations that would otherwise be carried out in protracted loops.
iv) Use IF..ELSE and/or CASE expressions in Open SQL.
Please refer to the Native SQL Example Code Snippet below if you have never written Native SQL code before. Do not ask why we did not utilize Open SQL. My friend, this is only an example. Ideally, since the EKPO table is available on the DDIC layer, we shouldn’t be writing Native SQL for it.
Enjoy this EXEC SQL — ENDEXEC command as I wait for a real database table example.
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. SELECT EBELN, EBELP, WERKS INTO :wa_ekpo FROM ekpo WHERE werks = :p_werks ENDEXEC. *--------------------------------------------------------------------* * Native SQL End *--------------------------------------------------------------------* * Subroutine from Native SQL FORM loop_and_write_output. WRITE: / wa_ekpo-ebeln, wa_ekpo-ebelp, wa_ekpo-werks. ENDFORM.
Try inserting some incorrect syntax between ENDEXEC and EXEC SQL.Your program might spew if the syntax checker missed it, but it would activate successfully otherwise. Enjoy yourself and do it yourself.
SAP HANA ABAP. Section XII.Which Code to Data Technique–Open SQL, CDS, or AMDP?
What more would you like to add to this article? Have you encountered any problems with Open or Native SQL? Would you like to share any actual project specifications or solutions?Do not hesitate to speak up.Kindly share your opinions in the space provided for comments.
I sincerely appreciate your time!
