In ABAP (Advanced Business Application Programming), data retrieval and database access are critical aspects of developing applications that interact with the underlying SAP database. ABAP provides several methods and constructs to retrieve data from databases. Here are key techniques for data retrieval and database access:
1. Open SQL:
Open SQL is a powerful and standardized way to access and manipulate database tables within ABAP programs. It provides a high-level, SQL-like syntax for database operations. Open SQL statements are portable across different database management systems (DBMS) supported by SAP.
SELECT Statement: The SELECT statement is used to retrieve data from database tables. You can filter and sort data using various clauses such as WHERE, ORDER BY, and GROUP BY.
DATA: lt_employees TYPE TABLE OF z_employee, lv_employee_id TYPE i VALUE 1001.
SELECT * FROM z_employee INTO TABLE lt_employees WHERE employee_id = lv_employee_id.
INSERT, UPDATE, and DELETE Statements:These statements are used to insert, update, and delete records in database tables.
INSERT INTO z_employee (employee_id, employee_name) VALUES (1002, 'John Doe'). UPDATE z_employee SET employee_name = 'Jane Smith' WHERE employee_id = 1002. DELETE FROM z_employee WHERE employee_id = 1002.
2. Native SQL:
Native SQL allows you to execute database-specific SQL statements directly. While it provides more flexibility, it may not be as portable across different DBMS as Open SQL.
EXEC SQL Statement: The EXEC SQL statement is used for executing native SQL statements.
EXEC SQL. SELECT employee_name INTO :lv_name FROM z_employee WHERE employee_id = :lv_employee_id. END-EXEC.
3. ABAP Database Connectivity (ADBC):
ADBC is a modern database interface in ABAP that provides enhanced database access capabilities. It is more flexible and efficient than traditional Open SQL when dealing with complex queries and database-specific features.
CL_SQL_CONNECTION:ADBC uses the CL_SQL_CONNECTION class to establish database connections.
DATA: lo_connection TYPE REF TO cl_sql_connection, lo_result_set TYPE REF TO cl_sql_result_set, lv_employee_id TYPE i VALUE 1001.
CREATE OBJECT lo_connection.
" Establish a database connection. lo_connection->connect( ... ).
" Prepare and execute a SQL statement. lo_connection->prepare( ... ). lo_connection->execute( ... ).
" Fetch data from the result set. lo_result_set = lo_connection->get_result( ).
4. ABAP Data Dictionary (DDIC):
ABAP programs can access data stored in SAP's Data Dictionary, which defines the structure of database tables in the SAP system.
TABLES Statement: The TABLES statement allows you to declare and access database tables in ABAP programs.
TABLES: z_employee.
SELECT * FROM z_employee INTO lt_employees WHERE employee_id = lv_employee_id.
5. Function Modules and BAPIs:
SAP provides pre-defined function modules and Business Application Programming Interfaces (BAPIs) that encapsulate complex business logic and data retrieval operations. These can be called from ABAP programs.
CALL FUNCTION Statement:Use the CALL FUNCTION statement to call function modules.
