What is Open SQL ? Open SQL or Native SQL in SAP ABAP ?

Se el primero en calificar

In order to retrieve stored Data from SAP Tables, ABAP programs use Open SQL or Native SQL to read, update or delete entries in the persistance layer (SAP db). In this article, we will discuss What’s Open SQL, then What’s Native SQL illustrated by 2 samples.

Table of Contents

What’s 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:

All Open SQL statements fill the following two system fields with return codes:

Check the official definition on help.sap.com

HOLA, estas en Statologos la enciclopedia más grande de estadística aplicada en español, no olvides que tienes un potente buscador de información en el menú utilizalo sabiamente 😉

What is Native SQL (in SAP ABAP)

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.

Native SQL Vs Open SAP

All ABAP programs in SAP R/3 System have been written with Open SQL. ?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?s assume you have an SAP R/3 system that uses Oracle database instant ORC1. You have another 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.

Use of Native SQL

Using Native SQL, you can

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.

What does an EXEC SQL statement do in ABAP?

Syntax of EXEC SQL in SAP ABAP

What’s EXEC SQL’s Effect ?

These statements define an area in an ABAP program in which one or more Native SQL statements are to be carried out. The area between EXEC and ENDEXEC is not completely checked by the syntax check. The statements entered there are passed to the Native SQL interface and processed there as follows:

Almost all SQL statements that are valid for the addressed database system can be included between EXEC and ENDEXEC, in particular the DDL statements. These SQL statements are passed from the Native SQL interface to the database system largely unchanged. The syntax rules are specified by the database system, in particular the case sensitivity rules for database objects. If the syntax allows a separator character between individual statements, you can include several Native SQL statements between EXEC and ENDEXEC. Generally, the semicolon (;) is used as the separator character.

All Native SQL statements bypass SAP buffering. Automatic client handling is not performed.

System fields

The statement ENDEXEC sets the system fields sy-subrc and sy-dbcnt. When using the addition PERFORMING, note that implicit cursor processing is carried out and the system fields are set for every read process.

After implicit cursor processing with PERFORMING, sy-subrc always contains the value 4.

sy-subrc Meaning
0 The statements between EXEC and ENDEXEC were executed successfully.
4 The statements between EXEC and ENDEXEC were not executed.

The ENDEXEC statement sets sy-dbcnt to the number of table rows processed in the last Native SQL statement. So After implicit cursor processing with PERFORMING, sy-dbcnt contains the total number of lines read.

Native SQL Advantages and Disadvantages – EXEC SQL statement

Advantages of Native SQL

Native SQL Disadvantages

Sample of Native SQLI

Example 1

Inserting two rows in the database table SCARR. If neither of these rows exists, sy-subrc is set to 0 by ENDEXEC and sy-dbcnt to 1. Otherwise, an exception is raised and handled.

Example 2

Reading several rows from the database table SPFLI using cursor handling and host variables in native SQL. If rows were found, sy-subrc is set to 0 and sy-dbcnt is increased by one for each row read.

admin Administrator
No hay contenido aun.
Download WordPress Themes Free
Download Best WordPress Themes Free Download
Download Premium WordPress Themes Free
Free Download WordPress Themes
download udemy paid course for free
download xiomi firmware
Download WordPress Themes Free
download udemy paid course for free

Deja un comentario

Comenzando con SAP BW, aquí hay una breve descripción general de SAP BW para obtener una visión general de la…