dii open sql and native sql

[Former Member]

difference between open sql and native sql

[Former Member]

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 your ABAP/4 program.

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

open sql is not confined to any particular type of database

while a native sql is confined to a particular one.

native sql : are not checked and converted they are sent

directly to the database system.allows to use database

specific sqql statements in an abap program.

Please check this link

http://help.sap.com/saphelp_46c/helpdata/EN/fc/eb3b8b358411d1829f0000e829fbfe/content.htm

Native SQL Advantages and Disadvantages -

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.)

Disadvanteges

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 clien handling for client dependent tables.

Care has to be taken during migration to higher versions.

Open SQL

Open SQL statements are a fully integrated subset of Standard SQL within ABAP. They enable the ABAP programs to access data irrespective of the database system installed. Open SQL consists of the Data Manipulation Language (DML) part of Standard SQL; in other words, it allows you to read (SELECT) and change (INSERT, UPDATE, DELETE) data.

Open SQL also goes beyond Standard SQL to provide statements that, in conjunction with other ABAP constructions, can simplify or speed up database access. It also allows you to buffer certain tables on the application server, saving excessive database access. In this case, the database interface is responsible for comparing the buffer with the database. Buffers are partly stored in the working memory of the current work process, and partly in the shared memory for all work processes on an application server. In SAP systems that are distributed across more than one application server, the data in the various buffers is synchronized at set intervals by buffer management. When buffering the database, you must remember that data in the buffer is not always up to date. For this reason, you should only use the buffer for data which does not often change. You specify whether a table can be buffered in its definition in the ABAP Dictionary.

Native SQL

Native SQL is only loosely integrated into ABAP, and allows access to all of the functions contained in the programming interface of the respective database system. Unlike Open SQL statements, Native SQL statements are not checked and converted, but instead are sent directly to the database system. When you use Native SQL, the function of the database-dependent layer is minimal. Programs that use Native SQL are specific to the database system for which they were written. When developing generally valid ABAP applications, you should – as far as possible – avoid using Native SQL. In some components of the SAP System, Native SQL is used – for example, in the ABAP Dictionary for creating or changing tables.

In ABAP/4 programming language, there are two types of SQL being used. One is the NATIVE SQL and the other is OPEN SQL.

Open SQL

Open SQL allows you to access the database tables declared in the ABAP dictionary regardless of the database platform that the R/3 system is using.

Open SQL consists of a set of ABAP statements that perform operations on the central database in the R/3 system. The results of the operations and any error messages are independent of the database system in use. Open SQL thus provides a uniform syntax and semantics for all of the database systems supported by SAP. ABAP programs that only use Open SQL statements will work in any R/3 system, regardless of the database system in use. Open SQL statements can only work with database tables that have been been created in the ABAP dictionary.

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 the value 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.

In Open SQL SELECT statement functions as a LOOP. You can process and access the records selected during the query with in the SELECT-ENDSELECT block.

Native SQL

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

To use Native SQL statement, you must precede it with the EXEC SQL statement, and follow it with the ENDEXEC statement.

The syntax is as follows:

EXEC SQL PERFORMING <form>. <Native SQL statement> ENDEXEC.

There is no period after Native SQL statements. Furthermore, using inverted commas (“) or an asterisk (*) at the beginning of a line in a native SQL statement does not introduce a comment as it would in normal ABAP syntax. You need to know whether table and field names are case-sensitive in your chosen database.

In Native SQL statements, the data is transported between the database table and the ABAP program using host variables. These are declared in the ABAP program, and preceded in the Native SQL statement by a colon (:). You can use elementary structures as host variables. Exceptionally, structures in an INTO clause are treated as though all of their fields were listed individually.

As in Open SQL, after the ENDEXEC statement, SY-DBCNT contains the number of lines processed. In nearly all cases, SY-SUBRC contains the value 0 after the ENDEXEC statement.

reward if helpful

[Former Member]

Reward points..

[Former Member]

HI,

We all know that SAP can run on various databases and for that it has DATABASE INTERFACE.

OPEN SQL : This is an sql code which is independent of any database. i.e this open sql code can work on any database in the sap system.

The DATABASE INTERFACE converts the syntax of the open sql to native sql and pass it to the database.

EG: We write select stmt in ABAP program as

select * from mara.

incase of some databases like ORACLE ,MSSQL, MYSQL, the statments may be same but the statement terminator may be a '.' , ' ;', no terminator etc. the sql statement which we write will be translated into particular native sql and passed to the database.

NATIVE SQL: THis is database dependent. the code written for one database will not work for other.

OPEN VS NATIVE SQL

Open SQL is SAPs version of SQL. Open SQL is written to isolate the code from a specific RDBMS; thereby, creating database independence. The Native SQL interface passes all SQL statements to the database without modifying them. In some cases, Open SQL does not allow you to take advantage of database specific features.

Disadvantages of Native SQL

Native SQL bypasses SAP's internal database interface, thereby, creating the following problems:

No table logging on SAP level during Native SQL operations

Synchronous match codes are not updated by Native SQL operations

No synchronization of the SAP table buffer

SQL statements become database dependent. This may cause numerous porting problems between database versions

Advantages of Native SQL

With the new SQL capabilities of SAP Release 4.0 Native SQL is only of interest in the following cases:

Use of set operations (UNION, INTERSECT, MINUS)

Use of database specific features

If Native SQL must be used and has been approved, adhere to the following guidelines:

Don't use Native SQL for operations that make database structure modifications

Isolate EXEC SQL statements in their own INCLUDE files

Document the reason for using Native SQL with detailed comments inside the INCLUDE file

Be careful when reading long fields and raw fields! Length information is additionally stored within the field, invisible for Open SQL.

For further reference go to :

OPEN SQL [http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3969358411d1829f0000e829fbfe/content.htm]

NATIVE SQL :[http://help.sap.com/saphelp_47x200/helpdata/en/fc/eb3b8b358411d1829f0000e829fbfe/content.htm]

reward points if helpful.

Thanks and Regards,

Narayana.