Muftasoft TM

# Database Access in ABAP

Database access is a fundamental aspect of ABAP (Advanced Business Application Programming) development, as it allows you to interact with the underlying database to retrieve, store, update, and delete data. In this section, we'll explore database access in ABAP, focusing on Open SQL, Select, Insert, Update, and Delete operations, and the use of database locks and transactions.

## Open SQL in ABAP

Open SQL is a subset of the SQL (Structured Query Language) standard that is supported by ABAP for database access. It provides a standardized way to perform database operations in SAP systems. Open SQL is portable across different database systems supported by SAP, making it a powerful and versatile tool for interacting with databases.

Here's how you can use Open SQL in ABAP:

```abap DATA: lt_resultset TYPE TABLE OF z_employee,

    lv_employee_id TYPE i,
    lv_employee_name TYPE string.

“ Select statement SELECT employee_id, employee_name

FROM z_employee
INTO TABLE @lt_resultset
WHERE employee_id = @lv_employee_id
  AND employee_name = @lv_employee_name.

” Insert statement INSERT INTO z_employee (employee_id, employee_name)

VALUES @lv_employee_id, @lv_employee_name.

“ Update statement UPDATE z_employee

SET employee_name = @lv_employee_name
WHERE employee_id = @lv_employee_id.

” Delete statement DELETE FROM z_employee

WHERE employee_id = @lv_employee_id.

```

Key points to note about Open SQL in ABAP:

- It supports SELECT, INSERT, UPDATE, and DELETE operations. - It uses INTO TABLE to retrieve data into internal tables. - It uses VALUES to specify values to insert. - It allows you to use variables and data structures directly in SQL statements using the @ symbol.

## Database Locks and Transactions

Database locks and transactions are crucial for ensuring data integrity in a multi-user environment. They control access to data to prevent conflicts when multiple users or processes attempt to access or modify the same data simultaneously.

### Database Locks

In ABAP, you can use the following lock types:

1. Shared Lock (`FOR SHARE`):

1. Allows multiple users to read the same data simultaneously.
2. Prevents data from being changed while locked.

2. Exclusive Lock (`FOR UPDATE`):

1. Grants exclusive access to a single user for both reading and updating data.
2. Blocks other users from reading or updating the same data.

3. Lock Objects:

1. You can define custom lock objects in the ABAP Dictionary to implement application-specific locking logic.
2. Lock objects provide more control over locking than SQL statements alone.

### Transactions

Transactions in ABAP ensure that a series of database operations are treated as a single unit of work. Either all the operations within a transaction are executed successfully, or none of them are. This helps maintain data consistency and integrity.

Here's how you can work with transactions in ABAP:

```abap DATA: lv_employee_id TYPE i,

    lv_employee_name TYPE string.

START-OF-SELECTION.

TRY.

" Start a database transaction
START TRANSACTION.

" Insert operation within the transaction
INSERT INTO z_employee (employee_id, employee_name)
  VALUES @lv_employee_id, @lv_employee_name.

" Additional operations within the transaction
" ...

" Commit the transaction to save changes
COMMIT WORK.

CATCH cx_root INTO DATA(ex).

" Handle exceptions and roll back the transaction if necessary
ROLLBACK WORK.

ENDTRY. ```

In the code above:

- `START TRANSACTION` initiates a transaction. - `COMMIT WORK` commits the transaction, making all the changes permanent. - `ROLLBACK WORK` rolls back the transaction, undoing any changes made within it if an exception occurs.

Database locks and transactions ensure data consistency and prevent data corruption in SAP systems, particularly in scenarios where multiple users or processes access and modify data concurrently. They are essential for maintaining the integrity of SAP applications and databases.