Native SQL

 We all know that the ABAP programs use Open SQL, which is translated by the SAP Database Interface into the native SQL of the underlying database during execution. What if I told you that the folks at SAP were prodigies and even before the concept of code pushdown or the existence of SAP HANA by itself, ABAP already supported a way to call and execute native database code directly from the application layer using native SQL!

EXEC SQL.
  <native SQL code>
ENDEXEC.

This block allowed developers to write native SQL statements specific to the underlying DB (e.g., Oracle, SQL Server, etc.) directly inside ABAP code. It bypassed the abstraction layer of Open SQL and talked straight to the database.

Example:

• Let's say I want to apply some conditions and then retrieve data. These conditions can be based either on default values or on inputs provided by the user. In native SQL, we handle them as follows:

1. User-provided values are treated as variables and are prefixed with a colon (:).
2. Default values are passed directly within single quotes (').

    

DATA: lv_matnr TYPE mara-matnr VALUE 'MAT123',
      lv_mtart TYPE mara-mtart.

EXEC SQL.
  SELECT mtart INTO :lv_mtart
  FROM mara
  WHERE matnr = :lv_matnr      -- User-provided value (variable)
    AND spras = 'E'            -- Default value (hardcoded in quotes)
ENDEXEC.

WRITE: / 'Material Type:', lv_mtart.

• Let’s say I want to select multiple lines of data, this is where the concept of a cursor becomes handy in native SQL. When you're expecting more than one result row, you can use a cursor to fetch rows one by one inside a loop. This gives you control over how data is read and processed, especially in native SQL where direct internal table usage isn't supported like in Open SQL.

DATA: lv_matnr TYPE mara-matnr,
      lv_mtart TYPE mara-mtart.

EXEC SQL.
  OPEN my_cursor FOR
    SELECT matnr, mtart
    FROM mara
    WHERE mtart = 'FERT'
ENDEXEC.

DO.
  EXEC SQL.
    FETCH my_cursor INTO :lv_matnr, :lv_mtart
  ENDEXEC.

  IF sy-subrc = 0.
    WRITE: / 'Material:', lv_matnr, 'Type:', lv_mtart.
  ELSE.
    EXIT.
  ENDIF.

  
ENDDO.

EXEC SQL.
  CLOSE my_cursor
ENDEXEC.

SAP allows you to invoke stored procedures written in the underlying database (like Oracle, HANA, etc.) from ABAP via native SQL, using the CALL statement inside an EXEC SQL block.
Let's assume you have a database procedure like:

CREATE PROCEDURE get_material_details (
  IN p_matnr VARCHAR(18),
  OUT p_mtart VARCHAR(4)
)

You can call it from ABAP like this:

DATA: lv_matnr TYPE mara-matnr VALUE 'MAT123',
      lv_mtart TYPE mara-mtart.

EXEC SQL.
  CALL get_material_details (:lv_matnr, :lv_mtart)
ENDEXEC.

WRITE: / 'Material Type from procedure:', lv_mtart.

Fetching data from secondary databases?? - SAP Gotcha. So, you thought SAP only plays within its main DB?
Not quite.

SAP provides a mechanism called DBCONNECT and secondary database connections, allowing you to connect to external databases (non-SAP or other SAP DBs) and fetch data directly from ABAP. We can define a secondary DB connection in transaction DBCO.

Give it a logical name (e.g., ZSECONDDB)

Point it to your external DB with credentials.

Use Native SQL with the CONNECTION clause:
EXEC SQL.
  CONNECT TO zseconddb AS con
ENDEXEC.


Data Type Compatibility Between ABAP and SAP HANA
When working with Native SQL, it is important to note that the data types used in the SQL statements are compatible with ABAP Layer, if not it will cause errors or unexpected behaviour in run time.
Below is the simplified overview of the mappings:
Numeric Type Mapping

  

    

      
ABAP Type
      
SAP HANA Type
    
  
  

    
b, s
SMALLINT
    
i
INTEGER
    
int8
BIGINT
    
p
DECIMAL (2*len - 1, with decimal places)
    
decfloat16
VARBINARY (length 8)
    
decfloat34
VARBINARY (length 16)
    
f
DOUBLE
  
Character-Like Type Mapping

  

    

      
ABAP Type
      
SAP HANA Type
    
  
  

    
c, n
NVARCHAR (same length)
    
string
NCLOB
    
d (date)
NVARCHAR (length 8)
    
t (time)
NVARCHAR (length 6)
  

Byte-Like Type Mapping

  

    

      
ABAP Type
      
SAP HANA Type
    
  
  

    
x
VARBINARY (same length)
    
xstring
BLOB