What is the difference between open sql & native sql ?
- 3 Answers
- 12268 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 are number ranges?
What is the use of abap data dictionary? : abap data dictionary
How to Modify Layout set for RVORDER01 for printing sales order confirmation. plz mention the detail coding Tahnks, Rahul
Is it possible to pass data to and from include programs explicitly? : abap modularization
What are the 2 other types of views, which are not allowed in release 3.0?
What is off cycle payroll run
Name the abap/4 modularization techniques?
Can multiple abap systems connect to abap database?
Define abap/4 layer?
Final class
In the MM module for finding out the standard cost what is the view one has to look at?
How to Solve a Particular Ticket in Realtime ?
What are aggregated objects : abap data dictionary
What is the Process to transfer IDOC from Outbound System to Inbound system.
What is the tcode to create indexes?
