Open SQL Statements in SAP ABAP Using Open SQL statements to update, insert, modify, delete records in database tables in SAP Open SQL statements which are used to modify database table records in SAP ABAP programming OPEN SQL Statements are those statements which are used to update or modify database table data. The open SQL statements are.
INSERT Is used to insert data into a database table, data can be inserted into database table using two ways. Insert record from internal table Syntax: INSERT
FROM TABLE .
Insert record from work area Syntax: INSERT FROM .
UPDATE Is used to update data in database table, data can be inserted into database table using two ways. Update record from internal table Syntax: UPDATE FROM TABLE .
Update record from work area Syntax: UPDATE FROM .
MODIFY Is used to modify data in database table, data can be inserted into database table using two ways. Update record from internal table
Syntax: MODIFY FROM TABLE .
Update record from work area Syntax: MODIFY FROM .
DELETE Is used to delete data from database table, data can be inserted into database table using two ways. Update record from internal table Syntax: DELETE FROM TABLE .
Update record from work area Syntax: DELETE FROM .
Using Lock Objects on OPEN SQL statements Lock Objects Lock objects are used to synchronize the multiple set of users who are accessing the same set of data. There are three types of locks. 1. Exclusive Lock. 2. Shared Lock. 3. Exclusive but not cumulative lock. Exclusive Lock The locked data can be read or proceed one user only. A request for another exclusive lock for a shared lock is rejected. Shared Lock Several users can read the same data at the same time, but as same as a user edits the data,a second user can not longer access this data. Requests for further shared locks are accepted, but exclusive locks are rejected. Exclusive but not cumulative lock. Exclusive locks can be requested by the same user more than once and handled successfully, but an exclusive but not cumulative lock can only be requested once by a given user, all the other lock requests are rejected.
Use of lock objects Lock objects are used when ever we are modifying or updating or inserting or deleting the database table data using OPEN SQL statements. Lock objects are part of data dictionary objects, when ever we create a lock object using data dictionary, two function modules will be generated automatically in the back end. All custom objects in SAP starts with Y or Z except lock objects.Lock objects starts with EZ or EY. Name of the generated function modules are with following naming convention . ENQUE_. "USED TO PUT A LOCK ON DATABASE TABLE RECORD DEQUE_. "USED TO RELEASE LOCK ON DATABASE TABLE RECORD
*HERE IS THE LOCK OBJECT NAME WHICH IS CREATED IN DATA DICTIONARY
Creating Lock Objects in SAP Lock Objects are the part of data dictionary objects, lock objects can be created in SE11 T-code, lock objects always starts with E, (these are the only custom objects that dosen`t start with Z in SAP).
Creating a lock object in SAP Lock objects are data dictionary objects, can be created in SE11, follow below steps to create lock object. Example: Here I am creating a lock object to insert, delete, update for table ZSTUDENT table in SAP. Go to SE11, provide lock object name as EZSTUDENT, create.
Provide short description, select tables tab, provide table name and lock mode, save and activate.
When ever we create a lock object two function modules will be created, go to SE37, check FM`s DEQUEUE_EZSTUDENT and ENQUEUE_EZSTUDENT.
Using the lock object to update/insert table When ever we create a lock object, two function modules(ENQUEUE and DEQUEUE) will be created, we have to call those function modules before and after open SQL statements, refer program below. REPORT ZSAN_LOCKOBJECT.
DATA : IT_STUDENT TYPE TABLE OF ZSTUDENT, WA_STUDENT TYPE ZSTUDENT.
WA_STUDENT-STUDENTID = '09'. WA_STUDENT-NAME = 'SAPNuts'. WA_STUDENT-LASTNAME = 'SAPNuts'. WA_STUDENT-FATHER = 'SAP'. WA_STUDENT-DOB = '25/09/2013'. WA_STUDENT-GENDER = 'Male'. WA_STUDENT-COURSE = 'SAP ABAP'. WA_STUDENT-EMAIL = '[email protected]'. WA_STUDENT-FEE = '0000'.
CALL FUNCTION 'ENQUEUE_EZSTUDENT' "add lock EXPORTING
MODE_ZSTUDENT = 'E' MANDT
= SY-MANDT
STUDENTID
= WA_STUDENT-STUDENTID "record to lock, optional parameter
* X_STUDENTID = ' ' * _SCOPE * _WAIT
= '2' =''
* _COLLECT
=''
* EXCEPTIONS * FOREIGN_LOCK = 1 * SYSTEM_FAILURE * OTHERS
=2
=3
. IF SY-SUBRC = 0. MODIFY ZSTUDENT FROM WA_STUDENT. ENDIF.
CALL FUNCTION 'DEQUEUE_EZSTUDENT' "release lock EXPORTING MODE_ZSTUDENT = 'E' MANDT
= SY-MANDT
STUDENTID
= WA_STUDENT-STUDENTID "record to release lock, optional parameter
* X_STUDENTID = ' ' * _SCOPE * _SYNCHRON * _COLLECT .
= '3' ='' =''
Examples OPEN SQL Statements in SAP ABAP Below is the example program on INSERT, UPDATE, DELETE examples using lock objects.
REPORT ZSAN_LOCKOBJECT. DATA : IT_STUDENT TYPE TABLE OF ZSTUDENT, WA_STUDENT TYPE ZSTUDENT. WA_STUDENT-STUDENTID = '10'. WA_STUDENT-NAME = 'SAPNuts'. WA_STUDENT-LASTNAME = 'SAPNuts'. WA_STUDENT-FATHER = 'SAP'. WA_STUDENT-DOB = '25/09/2013'. WA_STUDENT-GENDER = 'Male'. WA_STUDENT-COURSE = 'SAP ABAP'. WA_STUDENT-EMAIL = '[email protected]'. WA_STUDENT-FEE = '0000'. CALL FUNCTION 'ENQUEUE_EZSTUDENT' "add lock EXPORTING MODE_ZSTUDENT = 'E' MANDT
= SY-MANDT
STUDENTID
= WA_STUDENT-STUDENTID.
IF SY-SUBRC = 0. INSERT ZSTUDENT FROM WA_STUDENT. "insert record IF SY-SUBRC = 0. WRITE:/ 'Record inserted'. ENDIF. UPDATE ZSTUDENT FROM WA_STUDENT. "update record IF SY-SUBRC = 0. WRITE:/ 'Record updated'. ENDIF. DELETE ZSTUDENT FROM WA_STUDENT. "delete record IF SY-SUBRC = 0.
WRITE:/ 'Record deleted'. ENDIF. ENDIF.
CALL FUNCTION 'DEQUEUE_EZSTUDENT' "release lock EXPORTING MODE_ZSTUDENT = 'E' MANDT STUDENTID
= SY-MANDT = WA_STUDENT-STUDENTID.
