ABAP Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP Database Accesses →  Native SQL →  EXEC SQL - Embedded Native SQL 

EXEC SQL

Short Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax

EXEC SQL. 
  ... 
ENDEXEC. 

Effect

These statements define an area in an ABAP program in which one or more Native SQL statements can be specified statically. The area between EXEC and ENDEXEC is not checked completely 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, multiple Native SQL statements can be included between EXEC and ENDEXEC. Generally, the semicolon (;) is used as the separator character.
• SAP-specific Native-SQL language elements can also be included between EXEC and ENDEXEC. These statements are not passed directly from the Native SQL interface to the database, but are converted appropriately. These SAP-specific language elements are:
  

• Literals

• Host variables

• Statements for cursor processing

• Database procedure calls

• Statements for establishing database connections

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 obsolete addition PERFORMING, note that implicit cursor processing is carried out and the system fields are set for every read.

sy-subrc	Meaning
0	The statements between EXEC and ENDEXEC were executed successfully.
4	The statements between EXEC and ENDEXEC werenot successful. After implicit cursor processing with PERFORMING, sy-subrc always contains the value 4.

The statement ENDEXEC sets sy-dbcnt to the number of table rows processed in the last Native SQL statement. After implicit cursor processing with PERFORMING, sy-dbcnt contains the total number of rows read. If an overflow occurs because the number or rows is greater than 2,147,483,647, sy-dbcnt is set to -1.

Notes

• Programs with Native SQL statements are generally dependent on the database system used, so that they cannot be executed in all AS ABAP systems. This is especially true for the examples in this section, which were written for Informix database systems, unless otherwise stated.
• If insertions or modifications with Native SQL statements INSERT or UPDATE would cause double rows with regard to the primary table key, no exception is raised. Instead, sy-subrc is set to 4. However, if another operation, such as executing a Stored Procedure, would cause a double row, an exception would be raised.
• The client ID of a database table must be specified explicitly. Note that application programs should only use data from the current client. See also the associated security note and the programming guideline.
• The obsolete addition PERFORMING (not allowed in classes) executes implicit cursor processing and must no longer be used. The obsolete statement EXIT FROM SQL can be used to exit this type of processing.
• Native SQL statements used for transaction control (COMMIT, ROLLBACK) are detected by the database interface and the actions required at the end of a transaction are performed.
• The static embedding of Native SQL statements between EXEC SQL and ENDEXEC is replaced by dynamic passes to objects from ADBC classes. New features in the Native SQL in interface are now developed only in ADBC. Only ADBC should be used in new programs.
  

Example

The following example demonstrates how a simple embedded Native SQL statement can be replaced by ADBC. The use of the instance operator NEW removes the need for a helper variable of type CL_SQL_STATEMENT when creating objects.

"Static Native SQL 
EXEC SQL. 
  COMMIT WORK 
ENDEXEC. 

"Dynamic Native SQL 
NEW cl_sql_statement( )->execute_update( `COMMIT WORK` ). 

Example

See the executable example for static Native SQL.

Exceptions

Catchable Exceptions

CX_SY_NATIVE_SQL_ERROR

• Cause: Error when building up a secondary database connection.
  Runtime Error: DBIF_DSQL2_CONNECTERR
• Cause: Illegal interruption of a database selection. The cursor has been closed.
  Runtime Error: DBIF_DSQL2_INVALID_CURSOR:
• Cause: There is insufficient memory available for aNative SQL statement.
  Runtime Error: DBIF_DSQL2_NO_MEMORY
• Cause: Database object already exists in the database. An attempt was made to create a database object (for example, table, view, index) on the database, but this object already exists.
  Runtime Error: DBIF_DSQL2_OBJ_EXISTS
• Cause: The name of a table or view that does not exist on the database was used.
  Runtime Error: DBIF_DSQL2_OBJ_UNKNOWN
• Cause: An SQL error occurred while executing a native SQL command.
  Runtime Error: DBIF_DSQL2_SQL_ERROR
• Cause: The maximum number of database connections (connections) has been exceeded.
  Runtime Error: EXSQL_CONNECTION_LIMIT
• Cause: The maximum number of pointers (cursors) has been exceeded.
  Runtime Error: EXSQL_DSQL_CURSOR_LIMIT
  

Non-Catchable Exceptions

• Cause: The specified cursor does not exist. A CLOSE or SELECT command in Native SQL has a specified cursor, but this cursor is unknown to cursor administration.
  Runtime Error: EXSQL_DSQL_CURSOR_NOT_FOUND
• Cause: The specified cursor is already open. An OPEN command command in Native SQL has a specified cursor, which the cursor administration knows is already open.
  Runtime Error: EXSQL_DSQL_DUPLICATE_CURSOR
• Cause: There is insufficient roll memory available. When a Native SQL statement is processed, the internal memory is required to prepare the SQL Call.
  Runtime Error: EXSQL_DSQL_NOROLL
• Cause: An indicator variable has the wrong type. It must have the type INT2.
  Runtime Error: EXSQL_DSQL_WRONG_IVAR_TYPE
• Cause: The connection name has already been assigned. A CONNECT command in Native SQL has a specified connection name, which is already used for another connection.
  Runtime Error: EXSQL_ILLEGAL_CONNECTION
• Cause: The connection "DEFAULT" has been specified for a DISCONNECT command in Native SQL. This connection cannot be terminated with DISCONNECT.
  Runtime Error: EXSQL_ILLEGAL_DISCONNECTION
• Cause: A Native SQL command contains too many variables. Here, variables are all ABAP fields that are marked with a preceding colon(":"). If the INTO clause has the form INTO :wa for a work area wa, then all the fields of wa also count as variables.
  Runtime Error: EXSQL_TOO_MANY_VARS
• Cause: A SET CONNECTION or DISCONNECT command in Native SQL has a specified connection that is unknown to the connection administration.
  Runtime Error: EXSQL_UNKNOWN_CONNECTION
• Cause: Despite the assertion INTO STRUCTURE, the target option specified in the INTO- clause is not structured.
  Runtime Error: EXSQL_UNSTRUCTURED_INTO
• Cause: A LOB Variable has the wrong type. LOB variables must have the type string, xstring, c, x or n.
  Runtime Error: EXSQL_WRONG_TYPE_FOR_LOB
  

Continue

EXEC SQL - Literals

EXEC SQL - Host Variables

EXEC SQL - OPEN, CLOSE, FETCH

EXEC SQL - EXECUTE

EXEC SQL - CONNECT

ENDEXEC