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 AnweisungENDEXEC.
There is no period (.) after Native SQL statements. Furthermore, using inverted commas (") in a Native SQL statement or an asterisk (*) at the beginning of a line 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 variables 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 SELECTstatement is a table, you can pass it to ABAP line by line using the PERFORMINGaddition. The program calls a subroutine form for each line read. You can process the data further within the subroutine.
As in Open SQL, sy-dbcnt contains the number of processed lines after the statement ENDEXEC. sy-subrc contains in almost all cases the value 0 after the statement ENDEXEC. Exceptions are cursor operations: after FETCH, sy-subrc is if not more records could be read. This also applies when you read a result set using EXEC SQL PERFORMING.
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 = :c1ENDEXEC.
FORM loop_output. WRITE: / wa-connid, wa-cityfrom, wa-cityto.ENDFORM.
The output is as follows:
The work area wa and the field c1 Native SQL statement SELECT are used. wa is the target area into which the selected data is written. As a structure, wa is handled in the INTO clause as if all subfields were listed individually: [..] INTO :wa-connid, :wa-cityfrom, :wa-cityto. c1 is used in the WHERE clause. The subroutine loop_output writes the data from wa to the list.
Native SQL allows you to execute (nearly) all statements provided by the database and its SQL programming interface (usually known as SQL Call Interface or similar) for executing SQL program code directly (using EXEC IMMEDIATE or a similar command). The following sections list the statements that are not supported.
Native SQL statements bypass the 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 long columns with the types LCHR or 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 are consistent, you should not use any transaction control statements (COMMIT, ROLLBACK, ...), or any statements that set transaction parameters (isolation level...) using Native SQL.
To standardize the specific syntax of different database products, ABAP has a uniform syntax:
EXECUTE PROCEDURE name ( parameterliste )
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 SAPnet note 44977.
EXEC SQL. EXECUTE PROCEDURE proc1 ( IN :x, OUT :y )ENDEXEC.
Cursor processing in Native SQL is similar to that in Open SQL:
EXEC SQL. OPEN c1 FOR SELECT client, arg1, arg2 FROM table_001 WHERE client = '000' AND arg2 = :arg2ENDEXEC.DO. EXEC SQL. FETCH NEXT c1 INTO :wa-client, :wa-arg1, :wa-arg2 ENDEXEC. IF sy-subrc <> 0. EXIT. ELSE. ... ENDIF.ENDDO.EXEC SQL. CLOSE c1ENDEXEC.
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.
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 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 - with regard to their structure - the sequence of fields in the ABAP Dictionary may not be the same as the actual sequence of fields in the database columns. 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, these would not necessarily be recognized as incorrect.
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:
Combinations of ABAP data type and database column type can be divided into finer subcategories - here, for example, using the transfer direction ABAP to the database (INSERT, UPDATE):