Native SQL Overview

Native SQL allows to code database-specific SQL statements in an ABAP program. The database-specific statement should code in between the EXEC SQL and ENDEXEC statement.

Using native SQL statements, we can use database tables that are not administered by the ABAP Dictionary and can integrate data that is not part of the R/3 system.

The database-specific SQL statements or native SQL statements will not run under different database systems. If program using more than one database platform, use only Open SQL statements.

Syntax –

EXEC SQL [PERFORMING <form>].
     ... 
  <Native SQL statement>
     ...
ENDEXEC.

The period should be coded at the end of the EXEC SQL and ENDEXEC statements and there is no period required after Native SQL statements.

SAP ABAP comment statements are not considered as comment if they are coded in between the EXEC SQL and ENDEXEC.
We may need to verify the table and field names are case-sensitive or non case-sensitive before using them in the program.

In Native SQL statements, the data is transferred in between the database table and the ABAP program using host variables. These host variables should declared in the ABAP program and preceded by a colon (:) in the Native SQL statement.

Elementary structures can be used as host variables. If the selection in a SELECT statement is a table, we can pass table row to ABAP line by line using the PERFORMING <form>. The program calls a subroutine <form> for each line read. The data can further process in the subroutine.

SY-DBCNT contains the number of lines processed and SY-SUBRC contains the value 0 if the native SQL statement is successful. If the separator is supported, many Native SQL statements can be coded in between the EXEC SQL and ENDEXEC.

The Native SQL statements can only run on single database. If the program using more than one database, then use Open SQL. Native SQL statements bypasses the R/3 database interface.

To ensure the transactions are consistent in the R/3 System, we should not use any transaction control statements (COMMIT, ROLLBACK WORK), or any statements that set transaction parameters (isolation level…) using Native SQL.

Using Native SQL, we can –

Transfer values from ABAP fields to the database.
Read data from the database and process it in ABAP programs.

Advantages –

Tables are not declared in ABAP Dictionary can be accessed.
Some of the specific features supported by the database-specific SQL.

Disadvantages –

No syntax check is performed on the query written in between EXEC and ENDEXEC.
ABAP program containing database-specific SQL statements will not run under different database systems.

Native SQL Architecture Overview –

Example –

Below example to display the LG product information from the table ZTC_TPRODUCT.

Table Data –

Code –

*&---------------------------------------------------------------------*
*& Report  Z_NATIVESQL
*&---------------------------------------------------------------------*
*& Written by TutorialsCampus
*&---------------------------------------------------------------------*

REPORT  Z_NATIVESQL.

* Declaring work area
DATA: WA LIKE ZTC_TPRODUCT.

* Declaring header of the output.
WRITE: /'PRODUCTID  | PRODUCT                                  '
	'|    PAMOUNT    |'.
ULINE.

* VAR declaration of type PRODUCT-ID with value 'LG%'
* Value 'LG%' to retrieve all products of LG
DATA VAR LIKE WA-PRODUCTID VALUE 'LG%'.

* Retrieving data from the table record by record
EXEC SQL PERFORMING write_table.
      SELECT * FROM ZTC_TPRODUCT
        INTO :WA
       WHERE PRODUCTID LIKE :VAR
ENDEXEC.
ULINE.

* Displaying the table fields.
FORM write_table.
      WRITE: / WA-PRODUCTID, '|', WA-PRODUCT,  '|',WA-PAMOUNT,  '|'.
ENDFORM.

SAP ABAP