Native SQL

An ABAP system interacts with its database using the following two layers:

• The database Interface (DBI) provides a database independent interface and additional services such as automatic client interface or table buffer.
• The database Specific Library (DBSL) connects to the respective database and translates Open SQL from Application into Native SQL. 

Sometimes, because Open SQL provides a common syntax across all supported databases, it can not support database specific SQL. As a result you need to use native SQL instead.

When using the native SQL, the syntax is not checked by compiler. the statements bypass Database Interface and are sent directly to the database. So there are no client handling implicitly, no table buffer.

There are two techniques to call the native sql in abap application layer:

1. EXEC SQL

Syntax
EXEC SQL.
  ...
ENDEXEC.

Effect
These statements define an area in an ABAP program in which one or more Native SQL statements can be specified statically. The area between EXEC and ENDEXEC is not checked completely by the syntax check. The statements entered there are passed to the Native SQL interface and processed there as follows:

• SAP-specific Native-SQL language elements can also be included between EXEC and ENDEXEC. These statements are not passed directly from the Native SQL interface to the database, but are converted appropriately. These SAP-specific language elements are:
• Literals
• Host variables
• Statements for cursor processing
• Database procedure calls
• Statements for establishing database connections
All Native SQL statements bypass SAP buffering. Automatic client handling is not performed.

System Fields
The statement ENDEXEC sets the system fields sy-subrc and sy-dbcnt. When using the obsolete addition PERFORMING, note that implicit cursor processing is carried out and the system fields are set for every read.


sy-subrc	Meaning
0	The statements between EXEC and ENDEXEC were executed successfully.
4	The statements between EXEC and ENDEXEC were not successful. After implicit cursor processing with PERFORMING, sy-subrc always contains the value 4.


The statement ENDEXEC sets sy-dbcnt to the number of table rows processed in the last Native SQL statement. After implicit cursor processing with PERFORMING, sy-dbcnt contains the total number of rows read. If an overflow occurs because the number or rows is greater than 2,147,483,647, sy-dbcnt is set to -1.

Notes

• Programs with Native SQL statements are generally dependent on the database system used, so that they cannot be executed in all AS ABAP systems. This is especially true for the examples in this section, which were written for Informix database systems, unless otherwise stated.
• If insertions or modifications with Native SQL statements INSERT or UPDATE would cause double rows with regard to the primary table key, no exception is raised. Instead, sy-subrc is set to 4. However, if another operation, such as executing a Stored Procedure, would cause a double row, an exception would be raised.
• The client ID of a database table must be specified explicitly. Note that application programs should only use data from the current client.
• The obsolete addition PERFORMING (not allowed in classes) executes implicit cursor processing and must no longer be used. The obsolete statement EXIT FROM SQL can be used to exit this type of processing.
• Native SQL statements used for transaction control (COMMIT, ROLLBACK) are detected by the database interface and the actions required at the end of a transaction are performed.
• The static embedding of Native SQL statements between EXEC SQL and ENDEXEC is replaced by dynamic passes to objects from ADBC classes. New features in the Native SQL in interface are now developed only in ADBC. Only ADBC should be used in new programs.
Example
The following example demonstrates how a simple embedded Native SQL statement can be replaced by ADBC. The use of the instance operator NEW removes the need for a helper variable of type CL_SQL_STATEMENT when creating objects.



"Static Native SQL
EXEC SQL.
  COMMIT WORK
ENDEXEC.

"Dynamic Native SQL
NEW cl_sql_statement( )->execute_update( `COMMIT WORK` ).

2. ABAP Database Connectivity (ADBC)

ADBC is recommended instead of older EXEC SQL statement. As an API, it supports executing the native SQL calls and exception handling. It is object-oriented and easy to use, as only three main classes (CL_SQL_CONNECTION, CL_SQL_STATEMENT, CL_SQL_RESULT_SET) are relevant in most cases.

Usage of ADBC:

•  Choose database connection (only when accessing secondary database):
• CL_SQL_CONNECTION -> get_connection()

•  Create a statement object: 
• Instantiation of class CL_SQL_STATEMENT

• Call native SQL: 
• CL_SQL_STATEMENT -> execute_query()
• Assign target variable for result set:
• Call method CL_SQL_RESULT_SET->set_param() or CL_SQL_RESULT_SET->set_param_table()
• Retrieve result set: 
• CL_SQL_RESULT_SET -> next_package()
• Close result set and release resources: 
• CL_SQL_RESULT_SET -> close()

Example:

DATA:
 LO_SQL TYPE REF TO CL_SQL_STATEMENT,
 LV_SQL TYPE STRING,
 LX_SQL TYPE REF TO CX_SQL_EXCEPTION,
 LO_RESULT TYPE REF TO CL_SQL_RESULT_SET,
 LR_FLIGHT TYPE REF TO DATA,
 LT_FLIGHT TYPE TABLE OF SFLIGHT.

TRY.
    CREATE OBJECT LO_SQL
      EXPORTING CON_REF = CL_SQL_CONNECTION=>GET_CONNECTION('HANADB').

    CONCATENATE 'SELECT * FROM SFLIGHT WHERE'
    'MANDT=`' SY-MANDT
    '` AND CARRID = `LH`' INTO LV_SQL.
   
    LO_RESULT = LO_SQL->EXECUTE_QUERY( LV_SQL ).
   
    GET REFERENCE OF LT_FLIGHT INTO LR_FLIGHT.
    LO_RESULT->SET_PARAM_TABLE( LR_FLIGHT ).
    LO_RESULT->NEXT_PACKAGE( ).
    LO_RESULT->CLOSE( ).
   
  CATCH CX_SQL_EXCEPTION INTO ...  
   
ENDTRY.

When using Native SQL, you should be aware of several pitfalls such as the following:

• The syntax of SQL is not checked by the compiler. So it is helpful to use CX_SQL_EXCEPTION to analyze messages returned by the database if the SQL string contains an error.
• Native SQL calls bypass the Database Interface. As a consequences, the following occurs:
• The ABAP table buffer is bypassed. This can make the native SQL calls slower than open SQL in some cases. In addition, changing data via native SQL is not recommended because the table buffer gets out of sync.
• Where conditions, Join conditions, and so on, must be included the client field explicitly.