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:

STATEMENT	MEANING
SELECT	this statement accesses, reads and displays data from the table
INSERT	this statement adds information into the tables (writes)
UPDATE	this statement modifies or changes the data within lines of tables
MODIFY	this statement either adds lines or changes line content of tables
DELETE	this statement deletes data from tables
OPEN CURSOR, FETCH, CLOSE CURSOR	this statement uses the cursor to read through the database

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

FIELD	MEANING
SY-SUBRC	This system field returns 0 if the operation was successful, and a nonzero value if it was not, to indicate type of error code
SY-DBCNT	This system field contains the number of lines from the database that have been processed

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

OpenSQL in ABAP	Native SQL in ABAP
OpenSQL statements, much like regular ABAP statements, need a period (.) after the end of each statement.	In native SQL, there is no need for a period after the statement. Additionally, it does not follow typical ABAP rules.
OpenSQL allows data from multiple database platforms	Native SQL does not allow data from multiple database platforms
Tables that belong to other systems cannot be accessed	Tables that belong to other systems can be accessed
Syntax check is performed in between the EXEC and ENDEXEC statements	No syntax check is performed

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.