Open SQL and Native SQL

[Former Member]

Hi All,

Can any one describe me Open SQL and Native SQL with examples.

Thanks in advance.

[Former Member]

Hi,

WELCOME TO SDN

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

reward if helpful

raam

[Former Member]

hi,

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.

Hope this helps, Do reward.

[Former Member]

Native SQL statements are directly update the database...open SQL statements are converted and then update the database.

Regards,

Dara.

[Former Member]

\[removed by moderator as it was just a copy and paste answer of someone else's work without giving a source\]

Edited by: Jan Stallkamp on Aug 19, 2008 4:26 PM

[Former Member]

Hi, Kodandarami and Runal.

Thanks for the link and the matter.

The general ABAP select query is of Open SQL and Other commands like EXEC - ENDEXEC, etc. are used for other than R/3 databases and those are of Native SQL.

Am I right?

[Former Member]

hi,

You can access any database with open sql.

The statement EXEC and END EXEC is provided to access the data bases directly bypassing the application server to access the that databse specific features. Its not recommended to use it.

Same databse can be accessd through open sql and native sql.

Its the business requirement and developers choice to use either of them. Well to use SAP 's best capabilities its always advisable to use open sql.

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. 

Hope this answers.

Edited by: Runal Singh on May 2, 2008 12:37 PM

[Former Member]

Thanks Kannaiah and Runal.

Your answers were very helpful for me.

I think these threads will help others too.

I got the answer. but if you have ur own actual codes or examples showing use of Native SQL including non R/3 databases, then it will be welcomed.

Thanks and regards,

Ezal.