Register Login

Difference between Open SQL and Native SQL

Updated May 18, 2018

Open SQL vs Native SQL

Open SQL

Open SQL allows developers to control SQL statements directly. Open SQL encapsulates the semantics for statement execution, parameter binding and results fetching provided by each database vendor in a vendor- independent interface. The operations performed with Open SQL translate directly to the primitive operations provided by each database, yet the API is consistent across all vendors.

To avoid incompatibilities between different database tables and also to make ABAP/4 programs independent of the database system in use, SAP has created a set of separate SQL statements called Open SQL. Open SQL contains a subset of standard SQL statements as well as some enhancements which are specific to SAP.

Open SQL contains the following keywords:

  • SELECT - Reads data from database tables.
  • INSERT - Adds lines to database tables.
  • UPDATE - Changes the contents of lines of database tables.
  • MODIFY - Inserts lines into database tables or changes the contents of existing lines.
  • DELETE - Delete lines from database tables.
  • OPEN CURSOR, FETCH, CLOSE CURSOR - Reads lines of database tables using the cursor.

All Open SQL statements fill the following two system fields with return codes:

  • SY-SUBRC
    After every Open SQL statement, the system field SY-SUBRC contains 0 if the operation was successful, a value other than 0 if not.
  • SY-DBCNT
    After an OPEN SQL statement, the system field SY-DBCNT contains the number of database lines processed.

Open SQL allows you to access all database tables known to the SAP System, regardless of the database manufacturer. Sometimes, however, we may want to use database-specific SQL statements called Native SQL in the ABAP/4 program.

A database interface translates SAP's Open SQL statements into SQL commands specific to the database in use. Native SQL statements access the database directly.

Native SQL

Native SQL allows you to use database-specific SQL statements in an ABAP program. This means that you can use database tables that are not administered by the ABAP Dictionary, and therefore integrate data that is not part of the R/3 System.

ABAP Native SQL allows you to include database-specific SQL statements in an ABAP program. Most ABAP programs containing database-specific SQL statements do not run with different databases. If different databases are involved, use Open SQL. To execute ABAP Native SQL in an ABAP program, use the statement EXEC. Open SQL (Subset of standard SQL statements), allows you to access all database tables available in the R/3 System, regardless of the manufacturer. To avoid conflicts between database tables and to keep ABAP programs independent from the database system used, SAP has generated its own set of SQL statements known as Open SQL.

If you create a table by using database tools, without ABAP Dictionary, you are not able to use Open SQL to reach this table. You just can use Native SQL to do that.

Native SQL statements bypass the R/3 database interface. There is no table logging, and no synchronization with the database buffer on the application server. For this reason, you should, wherever possible, use Open SQL to change database tables declared in the ABAP Dictionary. In particular, tables declared in the ABAP Dictionary that contain log columns with types LCHR and LRAW should only be addressed using Open SQL, since the columns contain extra, database-specific length information for the column. Native SQL does not take this information into account, and may therefore produce incorrect results. Furthermore, Native SQL does not support automatic client handling. Instead, you must treat client fields like any other.

To ensure that transactions in the R/3 System are consistent, you 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, you can

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

Native SQL works without the administrative data about database tables stored in the ABAP Dictionary. Consequently, it cannot perform all of the consistency check used in Open SQL. This places a larger degree responsibility on application developers to work with ABAP fields of the correct type. You should always ensure that the ABAP data type and the type of database column are identical.

Native SQL Advantages and Disadvantages - EXEC SQL statement

Advantages

  • Tables are not declared in ABAP Dictionary can be accessed. (e.g. Tables belonging to sys or system user of Oracle, etc.)
  • To use some of the special features supported by the database-specific SQL. (e.g. Passing hints to Oracle optimizer.)

Disadvantages

  • No syntax check is performed whatever is written between EXEC and ENDEXEC.
  • ABAP program containing database-specific SQL statements will not run under different database systems.
  • There is no automatic client handling for client dependent tables.
  • Care has to be taken during migration to higher versions.


Comments

  • 25 Jul 2017 11:20 am Shalesh Singh Visen Helpful Answer

    In SAP ABAP you can use both OPEN SQL and NATIVE SQL commands to access the database. if you are using NATIVE SQL commands then you will have to use database specific commands. this means that u must have a strong knowledge of the syntaxes being used for the commands.
    Open SQL allows you to access all database tables known to the SAP system, regardless of the database manufacturer. Sometimes, however, we may want to use database-specific SQL statements called Native SQL in the ABAP/4 program.

    ABAP Native SQL allows you to include database-specific SQL statements in an ABAP program. Most ABAP programs containing database-specific SQL statements do not run with different databases. If different databases are involved, use Open SQL. To execute ABAP Native SQL in an ABAP program, use the statement EXEC. Open SQL (Subset of standard SQL statements), allows you to access all database tables available in the R/3 System, regardless of the manufacturer.
    On the other hand the OPEN SQL commands are NOT database specific, which means that the OPEN SQL commands are automatically converted into the respective SQL statements with the help of database interface and are passed to the database. An ABAP Program using OPEN SQL commands is therefore are not database-specific and can be used in any R/3 system.  In addition, if u are using OPEN SQL commands, then you can easily buffer your tables on the application server. Buffering makes the faster access to the set of records. This means that the load on the server is also reduced.

    To avoid incompatibilities between different database tables and also to make ABAP/4 programs independent of the database system in use, SAP has created a set of separate SQL statements called Open SQL. Open SQL contains a subset of standard SQL statements as well as some enhancements which are specific to SAP.

    A database interface translates SAP's Open SQL statements into SQL commands specific to the database in use. Native SQL statements access the database directly.

    The OPEN SQL commands involves operations for data manipulation language only. OPEN SQL commands do not contain the operations for data definition language(DDL)  because these operations are not included in the ABAP Dictionary.

  • 25 Jul 2017 11:17 am Shalesh Singh Visen

    Open sql connect database through dbinterface.but native sql directly affected db(it is not recomended it usefull for basis only)

    or

    native sql we can access tables with specific database . 
    open sql we can access tables regard less of its database system.


×