Solved: Re: Native SQL

Former Member · ‎2008 Mar 28

Hi Experts,

I want to know about Native SQL and

Open SQL.

Regards,

Nagaraju Tankala

Former Member · ‎2008 Mar 28

Hi,

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:

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.

Client Handling

A single R/3 System can manage the application data for several separate areas of a business (for example, branches). Each of these commercially separate areas in the R/3 System is called a client, and has a number. When a user logs onto an R/3 System, they specify a client. The first column in the structure of every database table containing application data is the client field (MANDT, from the German word for client). It is also the first field of the table key. Only universal system tables are client-independent, and do not contain a client name.

By default, Open SQL statements use automatic client handling. Statements that access client-dependent application tables only use the data from the current client. You cannot specify a condition for the client field in the WHERE clause of an Open SQL statement. If you do so, the system will either return an error during the syntax check or a runtime error will occur. You cannot overwrite the MANDT field of a database using Open SQL statements. If you specify a different client in a work area, the ABAP runtime environment automatically overwrites it with the current one before processing the Open SQL statement further.

Should you need to specify the client specifically in an Open SQL statement, use the addition

... CLIENT SPECIFIED ....

directly after the name of the database table. This addition disables the automatic client handling and you can use the field MANDT both in the WHERE clause and in a table work area.

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 )

The parameters are separated by commas. You must also specify whether the parameter is for input (IN), output (OUT) or input and output (INOUT). For further information, refer to SAP Note 44977.

EXEC SQL

EXECUTE PROCEDURE proc1 ( IN , OUT :y )

ENDEXEC.

Cursor Processing

Cursor processing in Native SQL is similar to that in Open SQL:

OPEN

ENDIF.

ENDDO.

EXEC SQL.

CLOSE c1

ENDEXEC.

This example opens a cursor, reads data line by line, and closes the cursor again. As in Open SQL, SY-SUBRC indicates whether a line could be read.

Data Types and Conversions

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 checks used in Open SQL. This places a larger degree of 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 the database column are identical.

If the database table is not defined in the ABAP Dictionary, you cannot refer directly to its data type. In this case, you should create a uniform type description in the ABAP Dictionary, which can then be used by all application programs.

If the table is defined in the ABAP Dictionary, you should remember that the sequence of fields in the ABAP Dictionary definition may not be the same as the actual sequence of fields in the database. Using the asterisk (*) in the SELECT clause to read all columns into a corresponding work area would lead to meaningless results. In the worst case, it would cause an error.

The Native SQL module of the database interface passes a description of the type, size, and memory location of the ABAP fields used to the database system. The relevant database system operations are usually used to access and convert the data. You can find details of these operations in the manuals for the programming interface of the relevant database system. In some cases, Native SQL also performs other compatibility checks.

The documentation from the various database manufacturers provides detailed lists of combinations of ABAP data types and database column types, both for storing ABAP field values in database tables (INSERT, UPDATE) and for reading database contents into ABAP fields (SELECT). You can also apply these descriptions for the input and output parameters of database procedures. Any combinations not listed there are undefined, and should not be used.

The following sections provide details of the data types and conversions for individual databases. Although they are database-specific, there are also some common features.

Recommended type combinations are underlined. Only for these combinations is behavior guaranteed from release to release. For any other combinations, you should assume that the description only applies to the specified release.

The results of conversions are listed in a results column:

"OK": The conversion can be performed without loss of data.

Operations that fail are indicated by their SQL error code. Errors of this kind always lead to program termination and an ABAP short dump.

In some cases, data is transferred without an SQL error occurring. However, the data is truncated, rounded, or otherwise unusable:

Right truncation.

"Left" or "right" applies to the normal way of writing a value. So, for example, if a number is truncated, its decimal places are affected.

: Left truncation

: Number is rounded up or down during conversion

: A number that was "too small" is rounded to 0 (underflow)

: The conversion result is undefined.

There are several possible results. The concrete result is either not known at all, or can only be described using a set of rules that is too complicated for practical use.

: The conversion returns the SQL value NULL.

: The conversion is performed without fields and unchecked.

The original data is converted, but without its format being checked. The result may therefore be a value invalid for the result type, which cannot be processed further. An example of this is a date field containing the value "99999999" or "abcdefgh" after conversion.

Combinations of ABAP data type and database column type can be divided into finer subcategories. Here, for example, using the transfer direction ABAP ® database (INSERT, UPDATE):

If the width of the ABAP field is greater than that of the database column, the ABAP field may contain values for which there is not enough space in the database column. This can produce other cases: The concrete data value in ABAP finds space in the database column, or not.

If the ABAP field is at most as long as the database column, there is always space for the ABAP value in the database column.

Some types, such as numeric columns, expect values in a particular format. This is particularly important in connection with character types, for example, when you want to write an ABAP character field (type C) into an integer column.

In SAP the database interface will convert the open sql into native sql.

Reward points if helpful.

Thanks and Regards.

Edited by: Ammavajjala Narayana on Mar 28, 2008 5:30 PM

Former Member · ‎2008 Mar 28

hi nagaraj,

open SQL means it's used for all data bases.

native SQL is one which is related to particular data base only,

data base interpriter is used to convert open sql to native sql of particular data base,

b/c of this reason R/3 is data base indipendent,

generaly we write open sql statements in our prg,or any where we requir ,database inter priter converts it into native sql formate to communicate with particular data base

regards,

chandu

Former Member · ‎2008 Mar 28