EXEC

Basic form

EXEC SQL.

Addition:

... PERFORMING form

Effect

Executes the Native SQL statements enclosed between EXEC SQL and ENDEXEC statements. Unlike Open SQL, the addressed database tables must not be declared in the ABAP Dictionary.

Example

Creating the Table AVERI_CLNT:

EXEC SQL.
  CREATE TABLE AVERI_CLNT (
         CLIENT   CHAR(3)  NOT NULL,
         ARG1     CHAR(3)  NOT NULL,
         ARG2     CHAR(3)  NOT NULL,
         FUNCTION CHAR(10) NOT NULL,
         PRIMARY KEY (CLIENT, ARG1, ARG2)
                          )
ENDEXEC.

In a Native SQL statement, data is passed between the ABAP program and the database using host variables. A host variable is an ABAP variable that is identified as such in the Native SQL statement by a preceding colon (:).

Example

Displaying an extract from the table AVERI_CLNT:

DATA: F1(3), F2(3), F3(3).
F3 = ' 1 '.
EXEC SQL.
  SELECT CLIENT, ARG1 INTO :F1, :F2 FROM AVERI_CLNT
         WHERE ARG2 = :F3
ENDEXEC.
WRITE: / F1, F2.

To simplify the form of the INTO lists in the SELECT statement, you can, as in Open SQL, specify a single structure as the target area.

Example

Displaying an Extract from the Table AVERI_CLNT:

DATA: BEGIN OF WA,
        CLIENT(3), ARG1(3), ARG2(3),
      END OF WA.
DATA  F3(3).
F3 = ' 1 '.
EXEC SQL.
  SELECT CLIENT, ARG1 INTO :WA FROM AVERI_CLNT
         WHERE ARG2 = :F3
ENDEXEC.
WRITE: / WA-CLIENT, WA-ARG1.

Native SQL supports the directly-executable commands of your underlying database system. There are other special commands that you can use after the EXEC SQL statement for cursor handling, stored procedures (procedures stored in the database), and connections to other databases.

Cursor Processing

Cursor processing in Native SQL is similar to that in Open SQL. Use the OPEN c FOR SELECT ... command to open a cursor, and the FETCH NEXT c INTO ... to read a line into a given target area. The CLOSE c command closes the cursor. As in the SELECT statement, the target area can be either a list of variables or a structure. FETCH NEXT sets SY-SUBRC to 4 if there are no more records to read. You can also specify the name of the cursor as a host variable.

Example

Cursor Processing with Table AVERI_CLNT:

DATA: F1(3), F2(3), F3(3).
F3 = ' 1 '.
EXEC SQL.
  OPEN C FOR
    SELECT CLIENT, ARG1 FROM AVERI_CLNT
        WHERE ARG2 = :F3
ENDEXEC.
DO.
  EXEC SQL.
    FETCH NEXT C INTO :F1, :F2
  ENDEXEC.
  IF SY-SUBRC <> 0.
    EXIT.
  ENDIF.
  WRITE: / F1, F2.
ENDDO.
EXEC SQL.
  CLOSE C
ENDEXEC.

At the end of a database transaction (during database commit), all the cursors are closed. Using the command OPEN WITH HOLD c FOR SELECT ..., you can open a cursor that remains open after a database commit and can continue to be used.

Stored Procedures

The command EXECUTE PROCEDURE proc allows you to call a procedure stored in the database. When you call it, you can pass a list of host variables as parameters. When yuo call a procedure, you must specify for each parameter whether it is an input parameter ( IN), output parameter (OUT) or changing parameter (INOUT).

Example

Calling a Procedure:

DATA Y TYPE I VALUE 300.
DATA Z TYPE I.

EXEC SQL.
  INSERT INTO AVERI_CLNT (CLIENT, ARG1, ARG2, ARG3)
         VALUES ('000', 9, 2, 47)
ENDEXEC.

EXEC SQL.
  CREATE OR REPLACE PROCEDURE PROC1 (X IN NUMBER) IS
    BEGIN
      UPDATE AVERI_CLNT SET ARG3 = ARG3 + X;
    END;
ENDEXEC.
EXEC SQL.
  CREATE OR REPLACE PROCEDURE PROC2 (X IN NUMBER, Y OUT NUMBER) IS
    BEGIN
      SELECT ARG3 INTO Y
        FROM AVERI_CLNT
        WHERE CLIENT = '000' AND ARG1 = 9 AND ARG2 = 2;
      UPDATE AVERI_CLNT SET ARG3 = ARG3 - X;
    END;
ENDEXEC.
EXEC SQL.
  EXECUTE PROCEDURE PROC1 ( IN :Y )
ENDEXEC.
EXEC SQL.
  EXECUTE PROCEDURE PROC2 ( IN :Y, OUT :Z )
ENDEXEC.
IF SY-SUBRC <> 0 OR Z <> 347.
  WRITE: / 'Wrong result for EXECUTE PROCEDURE:', Z.
ENDIF.
EXEC SQL.
  DROP PROCEDURE PROC1
ENDEXEC.
EXEC SQL.
  DROP PROCEDURE PROC2
ENDEXEC.

Multi-Connect

When you start the R/3 System, an initial connection is opened to the R/3 database. The multi-connect allows you to open other connections to other databases of the same, or a different, type. The only condition is that the database must be supported by SAP. When you activate a database connection, the subsequent Native SQL statements are executed by this database system. Each connection that you want to address using multi connect must be described by a record in the table DBCON.

You open a connection with the command CONNECT TO dbs, where dbs is the name of the connection as specified in the table DBCON. You can specify the name either as a host variable or a literal. You can use the AS alias addition to specify an alias name for the connection. This is necessary if you want to open more than one connection to the same database. To make connection dbs the current connection, use the statement SET CONNECTION dbs. All subsequent Native SQL statements are then processed by this connection. SET CONNECTION DEFAULT resets the initial connection. To close the current connection, use the DISCONNECT command. Use GET CONNECTION to specify the current connection.

Notes

Notes

  1. Unlike in Open SQL, a client field in Native SQL is treated like any other field, and must therefore be listed explicitly in statements.


  2. You cannot perform reliable authorization checks using EXEC SQL. You should implement them at program level instead.


  3. When you start the R/3 System, an automatic CONNECT to the current database occurs. You do not need to execute this CONNECT explicitly.


  4. You can (but do not have to) end a Native SQL statement with a semicolon (";"). You must never conclude a Native SQL statement with a period (".").


  5. Some database systems allow you to differentiate between upper and lowercase in table and field names. If you want to use this, you must ensure that you write the names correctly. To use lowercase letters in names in the ABAP Editor, you must set the "Upper-/lowercase" attribute in the program attributes.


  6. Since there are no arrays in ABAP, you cannot use array operations in Native SQL. If the result of a SELECT command is a table, you can read it into your program either using the Native SQL FETCH command, or the ... PERFORMING form addition.


  7. The " character in Native SQL does not introduce a comment to the end of the line as it does in an ABAP program.


  8. Native SQL allows you to use database commits to avoid locking conflicts with read access.


  9. If you want to select into a work area in a SELECT statement, and not into a list of host variables, the host variable must be a structure. If you cannot determine statically (at compilation time) whether the target area is a structure at runtime (for example, in the case of a non-typed field symbol or a non-typed formal parameter), then there is a runtime error, depending on the database system. Or only the first selected column is placed into the target area. In this case, one can be certain, using INTO STRUCTURE (instead of only INTO), that at runtime a structure really is available as target area.



Addition

... PERFORMING form

This addition is not allowed in an ABAP Objects context. See Subroutine Calls Not Allowed in EXEC SQL.

Effect

If the result of a SELECT command is a table, the system reads the result set line by line in a loop. For each line, the system calls the subroutine form . You can terminate the loop using the EXIT FROM SQL statement in the subroutine form. If the result of the selection is a single record, form is only called once.

Example

Displaying a Selection From Table AVERI_CLNT:

DATA: F1(3), F2(3), F3(3).

F3 = ' 1 '.
EXEC SQL PERFORMING WRITE_AVERI_CLNT.
  SELECT CLIENT, ARG1 INTO :F1, :F2 FROM AVERI_CLNT
         WHERE ARG2 = :F3
ENDEXEC.

FORM WRITE_AVERI_CLNT.
  WRITE: / F1, F2.
ENDFORM.

Note

You can only use this addition in a SELECT command.

Exceptions

Catchable Exceptions

CX_SY_NATIVE_SQL_ERROR

Non-Catchable Exceptions

Related

SELECT, INSERT, UPDATE, MODIFY, DELETE, OPEN CURSOR, FETCH, CLOSE CURSOR, COMMIT WORK und ROLLBACK WORK.



Additional help

Native SQL