Open SQL & Native SQL in SAP ABAP

In this tutorial, we’ll be learning about the two types of SQL (Structured Query Language) used in SAP ABAP. Let us dive deep into the world of database enquiry!

What is SQL?

• SQL stands for ‘Structured Query Language’.
• It is a language used to access, query and manipulate data in databases.
• You can create, view, delete, modify or update data, tables, views, etc. in a database using SQL.
• ANSI (American National Standards Institute) has declared SQL to be a standard language, especially for relational databases.

SQL Types in ABAP

SQL has many different types, and in ABAP we use two types of SQL:

1. OpenSQL
2. Native SQL

Let us see both these types in detail.

OpenSQL in SAP ABAP

• OpenSQL allows access to the ABAP Data Dictionary without checking which database platform the R3 system uses.
• It uses ABAP statements to operate upon the R3 system’s central database.
• ABAP has a rule that says you cannot use more than one database system if you are using database-specific queries.
• Hence, if you want to use multiple database systems, OpenSQL is your best bet.
• Both the queries and results run independently of the database system in ABAP.
• However, OpenSQL only works with the database tables created by Data Dictionary.

Here are some keywords in OpenSQL:

OpenSQL statements also have two additional instructions that they perform, called system fields –

OpenSQL Example in ABAP –

TABLES SDATAFLAIR.

DATA C TYPE CURSOR,
WA LIKE SDATAFLAIR.

OPEN CURSOR C FOR SELECT * FROM SDATAFLAIR WHERE CARRID = 'LH '
AND CONNID = '0400'
AND DATE = '19950228'
ORDER BY PRIMARY KEY.

DO.
FETCH NEXT CURSOR C INTO WA.
IF SY-SUBRC <> 0.
CLOSE CURSOR C.
EXIT.
ENDIF.
WRITE: / WA-BOOKID, WA-CUSTOMID, WA-CUSTTYPE, WA-WUNIT,WA-INVOICE.
ENDDO.

The above code will return the list of all customers of ID = LH & 0400, from date 1995-02-28

Native SQL in SAP ABAP

• Native SQL uses ABAP-specific or database-specific queries in the code.
• This means that it can integrate all the data that is not part of the R3 system.
• So native SQL can manage data that is specific to a database i.e. the data that is not managed by the ABAP Data Dictionary.
• However, by association of this definition, if you want to use more than one platform, you will need to use openSQL instead.
• In native SQL, you need to begin the statements with ‘EXEC SQL’ and end it with ‘ENDEXEC’ statement.

Native SQL Example in ABAP –

REPORT ZS_DATAFLAIR. 
DATA: BEGIN OF df,  
      connid  TYPE DF1-connid,
      cityfrom TYPE DF1-cityfrom,
      cityto  TYPE DF1-cityto,  
      END OF df. 
    
DATA DF1 TYPE DF1-carrid VALUE 'LH'. 
EXEC SQL PERFORMING loop_output.
   SELECT connid, cityfrom, cityto  
   INTO : df 
   FROM DF1 
   WHERE carrid = : DF1 
ENDEXEC. 

FORM loop_output.  
   WRITE: / df-connid, df-cityfrom, df-cityto. 
ENDFORM.

The above code will output all customers containing carrid = DF1.

OpenSQL vs Native SQL

Summary

Thus, in this tutorial, we learnt about two types of SQL used in ABAP – OpenSQL and native SQL.

We learnt why SQL is used and especially in ABAP. We learnt how to use both kinds of SQL and the difference between them.