is there any difference between native sql and standard sql statements

[Former Member]

Hi

i have cleared with open sql and native sql statements.

Is there any difference between native sql and standard sql statements

Thanks&Regards,

S.Gangi Reddy.

[Former Member]

Standard SQL - SQL Standard statements

Native SQL - DB specific SQL

The language SQL is standardized by international standard bodies such as ISO and ANSI. By using standard SQL it should be easier to move applications between different database systems without the need to rewrite a substantial amount of code. Using standard SQL does not give any warranty though as all vendors does not implement all features in the standard.

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.

As a rule, an ABAP program containing database-specific SQL statements will not run under different database systems. If your program will be used on more than one database platform, only use Open SQL statements.

Reward points if useful.

[Former Member]

Hi,

Check this Link

http://help.sap.com/saphelp_nw70/helpdata/en/fc/eb3976358411d1829f0000e829fbfe/content.htm

Open SQL

Open SQL consists of a set of ABAP statements that perform operation on 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 database systems supported by SAP. ABAP programs that only use Open SQL statements will work in any SAP R/3 System, regardless of the database system in use. Open SQL statements can work with database tables that have been created in the ABAP Dictionary.

The method actually is simple that when a programmer writes an ABAP program with Open SQL statements, the kernel SAP programs convert Open SQL statements to real / native SQL statements for database in use. So like that write once, run for all databases and even for all operating systems. Like Java’s “Write Once. Run Anywhere“. Think about Java, even the Java uses the same principal that is Java Virtual Machine which looks like SAP’s kernel programs. Right? Can we say SAP did “Write Once. Run Anywhere” before Java?

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.

Native SQL

Native SQL is real SQL for database in use. It means beside OPEN SQL, if you need you can use the native SQL for databases. 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.

As a rule, an ABAP program containing database-specific SQL statements will not run under different database systems. If your program will be used on more than one database platform, only use Open SQL statements.

I have never used Native SQL in my experiences more than 6 years for ABAP programming. I tried it, you can be sure it works. All ABAP programs in SAP R/3 System have been written with Open SQL. But I sometimes encountered Native SQL statements in original ABAP programs. I think if you have a different database instant in the same database, you can use Native SQL statement to connect and do operation on this database instant. Let me clarify this a little bit. Let’s assume you have an SAP R/3 system that uses Oracle database instant ORC1. You have an other application, even it uses the same database Oracle, but as normally different database instant ORC2. So like data inside ABAP program, you can use Native SQL statements to connect ORC2, non-SAP database instant, to integrate SAP R/3 and non-SAP system. It is kind of an integration activity.

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

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.

-

---

This will be very useful to u... from SDN

-

---

Hope this helps u..

Reward points if useful

Jgds

Message was edited by:

Jegadesh Balan