open sql and native sql

[Former Member]

1.i read on some documentation transparent table support open sql and native sql. but sap does not support the native sql then the above statement is correct or not? if it's correct the give simple explanation.

[Former Member]

Hi,

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

Native SQL Statements in ABAP Programs

To use a Native SQL statement, you must precede it with the EXEC SQL statement, and follow it with the ENDEXEC statement as follows:

EXEC SQL [PERFORMING <form>].

<Native SQL statement>

ENDEXEC.

There is no period after Native SQL statements. Furthermore, using inverted commas (") or an asterisk (*) at the beginning of a line in a native SQL statement does not introduce a comment as it would in normal ABAP syntax. You need to know whether table and field names are case-sensitive in your chosen database.

In Native SQL statements, the data is transported between the database table and the ABAP program using host variables. These are declared in the ABAP program, and preceded in the Native SQL statement by a colon (:). You can use elementary structures as host variables. Exceptionally, structures in an INTO clause are treated as though all of their fields were listed individually.

If the selection in a Native SQL SELECT statement is a table, you can pass it to ABAP line by line using the PERFORMING addition. The program calls a subroutine <form> for each line read. You can process the data further within the subroutine.

As in Open SQL, after the ENDEXEC statement, SY-DBCNT contains the number of lines processed. In nearly all cases, SY-SUBRC contains the value 0 after the ENDEXEC statement. Cursor operations form an exception: After FETCH, SY-SUBRC is 4 if no more records could be read. This also applies when you read a result set using EXEC SQL PERFORMING.

Open SQL

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

In the ABAP Dictionary, you can combine columns of different database tables to a database view (or view for short). In Open SQL statements, views are handled in exactly the same way as database tables. Any references to database tables in the following sections can equally apply to views.

Overview

Open SQL contains the following keywords:

Keyword

Function

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

Deletes lines from database tables

OPEN CURSOR,

FETCH,

CLOSE CURSOR

Reads lines of database tables using the cursor

Return Codes

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 the value 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.

Reward If Helpfull,

Naresh.

[Former Member]

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.

Open SQL allows you to access database tables declared in the ABAP Dictionary, regardless of the database platform you are 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 managed by the ABAP Dictionary, and therefore integrate data that is not part of the SAP Web AS ABAP 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

To use a Native SQL statement, you must precede it with the EXEC SQL statement, and follow it with the ENDEXEC statement as follows:

EXEC SQL PERFORMING form.

Native SQL Anweisung

ENDEXEC.

e.g.

REPORT demo_native_sql.

DATA: BEGIN OF wa,

connid TYPE spfli-connid,

cityfrom TYPE spfli-cityfrom,

cityto TYPE spfli-cityto,

END OF wa.

DATA c1 TYPE spfli-carrid VALUE 'LH'.

EXEC SQL PERFORMING loop_output.

SELECT connid, cityfrom, cityto

INTO :wa

FROM spfli

WHERE carrid = :c1

ENDEXEC.

FORM loop_output.

WRITE: / wa-connid, wa-cityfrom, wa-cityto.

ENDFORM.

The Open SQL statement for reading data from database tables is:

SELECT result

INTO target

FROM source

WHERE condition

GROUP BY fields

HAVING cond

ORDER BY fields.

Please go through the link:

http://help.sap.com/saphelp_nw2004s/helpdata/en/fc/eb3b8b358411d1829f0000e829fbfe/frameset.htm

http://help.sap.com/saphelp_nw2004s/helpdata/en/fc/eb3b8b358411d1829f0000e829fbfe/frameset.htm

Please reward the helpful entries.

Regards,

Shiva Kumar

[Former Member]

Hi,

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

Regards,

vineela.

[Former Member]

Hi..

Well i have not used native SQL in my program any time..

but i dont think it does'nt allow us to use..

Native SQL coding is just that it is database specific structure..

While in open SQL you are not in a need to bother about DATABASE no metter whether its oracle or SQL At Application server all set.. and we will get accourding result in presentation server.

regards.

Manish

[Former Member]

yes it's true

SAP can not do authorization check if it is using native SQL..

SAP can do authorization on open SQL and SAP always do authorization for security thats y using open not native...

[Former Member]

Hi.. chakri,

since i had not used native SQL in any way.

but i am sure that you cannot access it in ur program.

Native SQL coding is just that it is database specific structure..

While in open SQL you are not in a need to bother about DATABASE no metter whether its oracle or SQL..

At Application all set.. and we will get our accourding result.

regards.

Vipul