Changing Data

Open SQL contains a set of statements that allow you to change data in the database. You can insert, change and delete entries in database tables. However, you must remember that Open SQL statements do not check authorization or the consistency of data in the database. The following statements are purely technical means of programming database updates. They are to be used with care, and, outside the SAP transaction concept, only to be used in exceptional cases. The SAP transaction concept addresses the question of database update programming in the R/3 System. It discusses the difference between a database LUW and an SAP LUW, and explains about SAP transactions and the R/3 locking concept.

Inserting Lines

Changing Lines

Deleting Lines

Inserting or Changing Lines

COMMIT WORK and ROLLBACK WORK

Reading Data

The Open SQL statement for reading data from database tables is:

SELECT
INTO
FROM
[WHERE ]
[GROUP BY ]
[HAVING ]
[ORDER BY ].

The SELECT statement is divided into a series of simple clauses, each of which has a different part to play in selecting, placing, and arranging the data from the database.

Clause	Description
SELECT	The SELECT clause defines the structure of the data you want to read, that is, whether one line or several, which columns you want to read, and whether identical entries are acceptable or not.
INTO	The INTO clause determines the target area into which the selected data is to be read.
FROM	The FROM clause specifies the database table or view from which the data is to be selected. It can also be placed before the INTO clause.
WHERE	The WHERE clause specifies which lines are to be read by specifying conditions for the selection.
GROUP BY	The GROUP-BY clause produces a single line of results from groups of several lines. A group is a set of lines with identical values for each column listed in .
HAVING	The HAVING clause sets logical conditions for the lines combined using GROUP BY.
ORDER BY	The ORDER-BY clause defines a sequence for the lines resulting from the selection.

The individual clauses and the ways in which they combine are all very important factors in the SELECT statement. Although it is a single statement like any other, beginning with the SELECT keyword and ending with a period, its division into clauses, and the ways in which they combine, make it more powerful than other statements. A single SELECT statement can perform functions ranging from simply reading a single line to executing a very complicated database query.

You can use SELECT statements in the WHERE and HAVING clauses. These are called subqueries.

You can decouple the INTO clause from the SELECT statement by reading from the database using a cursor.

On certain database systems, locking conflicts can also occur when only reading the data. These can be avoided by using database commits.

Open SQL

Open SQL consists of a set of ABAP statements that perform operations on the central database in the R/3 System. The results of the operations and any error messages are independent of the database system in use. Open SQL thus provides a uniform syntax and semantics for all of the database systems supported by SAP. ABAP programs that only use Open SQL statements will work in any R/3 System, regardless of the database system in use. Open SQL statements can only work with database tables that have been created in the ABAP Dictionary.

In the ABAP Dictionary, you can combine columns of different database tables to a database view (or view for short). In Open SQL statements, views are handled in exactly the same way as database tables. Any references to database tables in the following sections can equally apply to views.

Overview

Open SQL contains the following keywords:

Keyword	Function
SELECT	Reads data from database tables
INSERT	Adds lines to database tables
UPDATE	Changes the contents of lines of database tables
MODIFY	Inserts lines into database tables or changes the contents of existing lines
DELETE	Deletes lines from database tables
OPEN CURSOR, FETCH, CLOSE CURSOR	Reads lines of database tables using the cursor

Return Codes

All Open SQL statements fill the following two system fields with return codes:

• SY-SUBRC

After every Open SQL statement, the system field SY-SUBRC contains the value 0 if the operation was successful, a value other than 0 if not.

• SY-DBCNT

After an open SQL statement, the system field SY-DBCNT contains the number of database lines processed.

Client Handling

A single R/3 System can manage the application data for several separate areas of a business (for example, branches). Each of these commercially separate areas in the R/3 System is called a client, and has a number. When a user logs onto an R/3 System, they specify a client. The first column in the structure of every database table containing application data is the client field (MANDT, from the German word for client). It is also the first field of the table key. Only universal system tables are client-independent, and do not contain a client name.

By default, Open SQL statements use automatic client handling. Statements that access client-dependent application tables only use the data from the current client. You cannot specify a condition for the client field in the WHERE clause of an Open SQL statement. If you do so, the system will either return an error during the syntax check or a runtime error will occur. You cannot overwrite the MANDT field of a database using Open SQL statements. If you specify a different client in a work area, the ABAP runtime environment automatically overwrites it with the current one before processing the Open SQL statement further.

Should you need to specify the client specifically in an Open SQL statement, use the addition

... CLIENT SPECIFIED ....

directly after the name of the database table. This addition disables the automatic client handling and you can use the field MANDT both in the WHERE clause and in a table work area.

Accessing the Database 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 and columns (fields). The smallest possible combination of fields that can uniquely identify each line of the table is called the key. Each table must have at least one key, and each table has one key that is defined as its primary key. Relationships between tables are represented by foreign keys.

Standard SQL

SQL (Structured Query Language) is a largely standardized language for accessing relational databases. It can be divided into three areas:

· Data Manipulation Language (DML)

Statements for reading and changing data in database tables.

· Data Definition Language (DDL)

Statements for creating and administering database tables.

· Data Control Language (DCL)

Statements for authorization and consistency checks.

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 a list of the SQL statements available and their correct syntax.

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. To do this, it uses a database-specific component that shields the differences between database systems from the rest of the database interface. You choose the appropriate layer when you install the R/3 System.

There are two ways of accessing the database from a program - with Open SQL or Native SQL.

Open SQL

Open SQL statements are a subset of Standard SQL that is fully integrated in ABAP. They allow you to access data irrespective of the database system that the R/3 installation is using. Open SQL consists of the Data Manipulation Language (DML) part of Standard SQL; in other words, it allows you to read (SELECT) and change (INSERT, UPDATE, DELETE) data.

Open SQL also goes beyond Standard SQL to provide statements that, in conjunction with other ABAP constructions, can simplify or speed up database access. It also allows you to buffer certain tables on the application server, saving excessive database access. In this case, the database interface is responsible for comparing the buffer with the database. Buffers are partly stored in the working memory of the current work process, and partly in the shared memory for all work processes on an application server. Where an R/3 System is distributed across more than one application server, the data in the various buffers is synchronized at set intervals by the buffer management. When buffering the database, you must remember that data in the buffer is not always up to date. For this reason, you should only use the buffer for data which does not often change. You specify whether a table can be buffered in its definition in the ABAP Dictionary.

Native SQL

Native SQL is only loosely integrated into ABAP, and allows access to all of the functions contained in the programming interface of the respective database system. Unlike Open SQL statements, Native SQL statements are not checked and converted, but instead are sent directly to the database system. When you use Native SQL, the function of the database-dependent layer is minimal. Programs that use Native SQL are specific to the database system for which they were written. When writing R/3 applications, you should avoid using Native SQL wherever possible. It is used, however, in some parts of the R/3 Basis System - for example, for creating or changing table definitions in the ABAP Dictionary.

The ABAP Dictionary

The ABAP Dictionary, part of the ABAP Workbench, allows you to create and administer database tables. Open SQL contains no statements from the DDL part of Standard SQL. Normal application programs should not create or change their own database tables.

The ABAP Dictionary uses the DDL part of Open SQL to create and change database tables. It also administers the ABAP Dictionary in the database. The ABAP Dictionary contains metadescriptions of all database tables in the R/3 System. Only database tables that you create using the ABAP Dictionary appear in the Dictionary. Open SQL statements can only access tables that exist in the ABAP Dictionary.

Authorization and Consistency Checks

The DCL part of Standard SQL is not used in R/3 programs. The work processes within the R/3 System are logged onto the database system as users with full rights. The authorizations of programs or users to read or change database tables is administered within the R/3 System using the R/3 authorization concept. Equally, transactions must ensure their own data consistency using the R/3 locking concept. For more information, refer to Authorization Concept and Programming Database Updates.

ABAP Database Access

This section describes how ABAP programs communicate with the central database in the R/3 System.

Accessing the Database in the R/3 System

Open SQL

Native SQL

Logical Databases

Contexts

Programming Database Changes

ABAP Database Access

This section describes how ABAP programs communicate with the central database in the R/3 System.

Accessing the Database in the R/3 System

Open SQL

Native SQL

Logical Databases

Contexts

Programming Database Changes