OPEN SQL IN SAP ABAP

Accessing the Data base in the R/3 System

In the R/3 System, Long life data is stored in relational database tables. In a relational database model, the real world is represented by tables. A table is a two-dimensional matrix, consisting of lines (rows) and columns (fields).

Standard SQL

Structured query language (SQL) is largely standardized language for accessing relational database. It can be divided into three areas;

1) Data Manipulation Language (DML).

Statements for reading data and changing data in database tables.

2) Data Definition Language (DDL).

Statements for creating and administering database tables.

3) Data Control Language (DCL).

Statements for authorization and consistency.

Note: The SQL is Database Dependent.

Each database has a programming interface that allows you to access the database tables using SQL statements; The SQL statements in these programming interfaces are not fully standardized. To access a specific database system, you must refer to the documentation of that system for list of the SQL statements available and their correct syntax (Since it is Database Dependent).

The Database Interface

To make the R/3 System independent of the database system with which you use it despite the differences in the SQL syntax between various databases, each work process on an application server has a database interface. The R/3 system communicates with the database by means of this interface. The database interface converts all of the database requests from the R/3 System into the correct standard SQL statements for the database system.

There are two ways of accessing the database from a program with OPEN SQL, NATIVE SQL.

Open SQL, Which is Database Independent and Integrated with ABAP Workbench.

Open (Database Independent) SQL is the combination of

a) Open DDL – is the Data Dictionary.

b) Open DML

c) Open DCL

Since Open DDL is the Data Dictionary, Now We Discuss DML and DCL Operations in Detail.

DML Operations in Details

DML – Data Manipulation Language and it can be

A) Reading Data(SELECT) From Database

This will be used in the REPORTS.

B) Changing Data in Database

INSERT, UPDATE, MODIFY& DELETE

Working with Database Changing Operations:

Always, All the Database Changing (INSERT, UPDATE, MODIFY, DELETE) Operations for Single Record is through WORK AREA and for Multiple Records through INTERNAL TABLE.

For All the Database Changing Operations, the Structure of the work area and must be according to the structure of the database table i.e. should have all the Fields form Database Table in the same Order.

INSERT – SYNTAX:

Single Record – INSERT FROM .

Multiple Records – INSERT FROM TABLE ACCEPTING DUPLICATE KEYS.

Accepting duplicate keys does not mean that it accepts and inserts duplicate records. Instead it ignores the records which already exist in database and inserts the rest of the records.

Whenever you want to insert more than one line into a database table, it is more efficient to work with an internal table than to insert the lines one by one.

If the record doesn’t exist in Database, Insertion takes place and else No Insertion.

UPDATE – SYNTAX:

Column Update: UPDATE SET = = Where .

Row Update: UPDATE FROM . (Single Record)

UPDATE FROM TABLE . (Multiple Records)

NOTE: Updates (Overwrites) if record exists else Ignores.

MODIFY Lines:

MODIFY – SYNTAX

INSERT - When Record Doesn’t Exist.

UPDATE – When Record Exist.

MODIFY SINGLE RECORD – MODIFY FROM .

MODIFY MULTIPLE RECORDS – MODIFY FROM TABLE .

NOTE: Modify Never Fails i.e. either it Inserts the Records or it updates.

DELETEING LINES:

DELETE -Syntax

Single Record – DELETE FROM (OR) DELETE FROM WHERE .

Multiple Records – DELETE FROM TABLE .

NOTE: Deletes If records exists else ignores.

DCL (DATA CONTROL LANGUAGE)

Committing Database Changelings

COMMIT WORK. (To Save the Changes Permanently in DB)

ROLLBACK WORK to Undo the Changes which are not Yet Committed.

NOTE: The Default COMMIT WORK, is Executed at the end of the program and Which Commits all the Database Changes in the program But SAP recommends the Explicit COMMIT WORK after each Database Changing Operation.

System Variables:

SY-SUBRC:

0 is For Successful Execution Open SQL Statement and <> 0 For UN Successful Execution.

SY-DBCNT:

No of database Records Successfully processed.

Result of Database Changing Operations:

Insert is Successful (SY-SUBRC = 0), When All the Records are Successfully Inserted.

Update is Successful (SY-SUBRC=0), When All the Records are Successfully Updated.

MODIFY’S is always set to 0. Because it either Overwrites the Record if it finds a Match with Primary Key Otherwise it Inserts a New Record. So it is Always Successful.