topic What is the difference between open sql and native sql in Application Development and Automation Discussions

What is the difference between open sql and native sql

Former Member — Tue, 18 Dec 2007 10:17:01 GMT

what is the difference between open sql and native sql

thanks & regards

Gani

Re: What is the difference between open sql and native sql

Former Member — Tue, 18 Dec 2007 10:19:56 GMT

Hi,

Open SQL statements are a subset of Standard SQL that is fully integrated in ABAP. They allow you to access data irrespective of the database system that your installation is using. 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. The tasks of the Data Definition Language (DDL) and Data Control Language (DCL) parts of Standard SQL are performed in NetWeaver AS ABAP by the ABAP Dictionary and the authorization system. These provide a unified range of functions, irrespective of database, and also contain functions beyond those offered by the various database systems.

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 ABAP 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 ABAP application server. Where NetWeaver AS ABAP is distributed across more than one ABAP application server, the data in the various buffers is synchronized at set intervals by the 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.

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. In Native SQL, you can primarily use database-specific SQL statements. The Native SQL interface sends them as is to the database system where they are executed. You can use the full SQL language scope of the respective database which makes all programs using Native SQL specific for the database system installed. In addition, there is a small set of SAP-specific Native SQL statements which are handled in a special way by the Native SQL interface. ABAP applications contain as little Native SQL as possible. In fact, it is only used in a few components (for example, to create or change table definitions in the ABAP Dictionary).

<REMOVED BY MODERATOR>

Regards,

Sreenivas

Edited by: Alvaro Tejada Galindo on Apr 25, 2008 4:38 PM

Re: What is the difference between open sql and native sql

Former Member — Tue, 18 Dec 2007 10:20:27 GMT

Hi,

Open SQL - ABAP specific SQL

Native SQL - DB specific SQL

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

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.

Thanks.

Re: What is the difference between open sql and native sql

Former Member — Tue, 18 Dec 2007 10:21:09 GMT

Hi,

Native sql statements change according to the database, where as the open sql statements are abap statements and they goto the database utility and convert them to native sql statements and pass it to database.

<REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on Apr 25, 2008 4:39 PM

Re: What is the difference between open sql and native sql

Former Member — Tue, 18 Dec 2007 10:22:14 GMT

Native SQL Commands:

The Commandss that are specific to the Database(i.e. some SQL commands of 'Oracle' wont work with 'My SQL'.). Should be written in EXEC statement in ABAP.

Open SQL Commands:

The commands that are not specific for a particular Database( i.e. no matter whether Oracle or My SQL is the database the commands work directly in ABAP)

<REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on Apr 25, 2008 4:39 PM

Re: What is the difference between open sql and native sql

Former Member — Tue, 18 Dec 2007 10:25:15 GMT

ganesh

open sql in the these are the sql statements for ABAP irrespective of the database used in the SAP system

as well as SAP provides facility to write the sql statements regarding to the database used tht is these native sql statements may diffeer from one database to other

if u want to write native sql commands then u have write thos in between

exec.

native sql statements....

endexec.

<REMOVED BY MODERATOR>

and feel free to ask any quiries my mail id <REMOVED BY MODERATOR>

<REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on Apr 25, 2008 4:40 PM

Re: What is the difference between open sql and native sql

Former Member — Tue, 18 Dec 2007 10:26:48 GMT

Hello Gani,

The difference is:

OPEN SQL - is basic SQL language (SELECT,FROM,WHERE....) this SQL is what in common in all DB servers. That means you don't care what kind of DB server your sytem working.

NTIVE SQL - is DB server specific language, for Example ORACLE has some commands that MSSQL won't understand.That means you must know what kind of DB server your sytem working.

Best Regards

Eli Steklov

<REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on Apr 25, 2008 4:40 PM

Re: What is the difference between open sql and native sql

Former Member — Tue, 18 Dec 2007 10:27:36 GMT

hi,

open sql is the Sql statements we use in ABAP which are independent of the underlying database.native sql statements are the ones which are directly related to the underlying database and only work with it. So use of native sql statements is avoided for portability purposes.

<REMOVED BY MODERATOR>

regards,

pavan

Edited by: Alvaro Tejada Galindo on Apr 25, 2008 4:41 PM

Re: What is the difference between open sql and native sql

Former Member — Tue, 18 Dec 2007 10:29:06 GMT

Hi Ganesh,

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

Re: What is the difference between open sql and native sql

Former Member — Fri, 25 Apr 2008 20:27:19 GMT

Hi All,

I would like to know how can I update a table in MS SQL database.

TRY.

EXEC SQL.

UPDATE sap_sync

SET (orders = :wa-c_orders,

travel = :wa-c_travel)

ENDEXEC.

CATCH cx_sy_native_sql_error INTO oref .

ENDTRY.

Actually I'm getting an exception with this command.

Thanks in advance

Alexis