What is the difference between open sql & native sql ?
- 3 Answers
- 12961 Views
- Accenture, SAP Labs, I also Faced
- E-Mail Answers
Answer Posted / sameera
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.)
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.
| Is This Answer Correct ? | 1 Yes | 1 No |
Post New Answer View All Answers
What is sap abap data classes?
What is delivery class? : abap data dictionary
Define alv programming in abap?
What happens if a function module runs in an update task? : abap modularization
What is the project you are currently working and work that you are doing
In se11 -->Tech. settings --> Data class. If I save table as Mater data OR Transaction data, what effect will it has for 'Storage' in Database after activation. or In Database how it will store in both the cases.
There is a delivery that is being created through the ABAP code and in the middle it says delivery created but someone is modifying , how would you rectify this issue
How many types of data classes are there in sap?
What is modularization and its benefits?
What is a database view? : abap data dictionary
What are the advantages of abap query tool?
How can we use multiple transactions by using bdc_insert? : abap bdc
A field containing quantity amounts (data type quan) must be assigned to a referencetable and a reference field. Explain?
What is database utility?
What is the difference between data elements and domains?
