SAP ABAP DATA BASE UPDATES COMPLETE

OVERVIEW: DATABASE UPDATES:


You can update databases either using ABAP's Open SQL commands, or with the database-specific commands of your database's Native SQL command set.

You can access ABAP cluster databases using special ABAP commands.

You can access the data in database tables using the Open SQL commands. The command set includes operations of the Data Manipulation Language (DML). The Data Definition Language (DDL) operations are not available in Open SQL, as these functions are performed by the ABAP Dictionary.

Native SQL commands allow you to carry out both DML and DDL operations.

The commands for ABAP cluster databases enable operations to be carried out on the data in the cluster databases. The tables themselves are created in the ABAP Dictionary as transparent tables.
For general information on cluster tables, refer to the course appendix.

For further information on Native and Open SQL, see the ABAP Editor Keyword documentation for the term SQL.

Each time you access the database using Open SQL, the database interface of each work process (application server) converts this to a database-specific command. For this reason, the ABAP programs themselves are independent of the database used and can be transferred to other system platforms (with a different database system) without additional programming requirements.

SAP database tables can be buffered at the application server level. The aims of buffering are to

Reduce the time needed to access data with read accesses. Data on the application server can be accessed more quickly than data on the database.

Reduce the load on the database. Reading the data from application server buffers reduces the number of database accesses.

The buffered tables are accessed exclusively via database interface mechanisms.

Database accesses with Native SQL enable database-specific commands to be used. This requires a detailed knowledge of the syntax in question. Programs that use Native SQL commands need additional programming after they are transported to different system environments (different database systems), since the syntax of the SQL commands generally varies from one database to the next.

The target quantity can be limited on the database using all the Open SQL commands discussed here.

One or more rows can be processed with a SQL command. Each command also provides the option of specifying the table name dynamically.

In addition to this, each type of operation has a syntax variant, which can be used to change individual fields in a row.

With masked field selections (WHERE LIKE ''), note that '_' masks an individual character and '%' masks a character string of any length (in line with the SQL standard).

For all Open SQL commands, you can edit data in the current client (standard). To do so, you do not specify any command additions and leave the client field non valuated.

If you want to edit data from other clients explicitly, use the SQL command with the addition CLIENT SPECIFIED and enter the number of the client in which the SQL operation is to be carried out in the WHERE clause of the command.

All Open SQL commands return confirmation of the success or failure of the database operation in the form of a return code. This is always returned by the database interface in the sy-subrc system field. The return code '0' (zero) always means that the operation has been completed successfully. All other values mean that errors have occurred. For further details, please refer to the keyword documentation for the command in question.

In addition, the sy-dbcnt system field displays the number of records for which the desired database operation was actually carried out.

Note that Open SQL commands do not perform any automatic authorization checks. You need to carry these out separately (see unit Authorization Checks).

To insert a new row in a database table, enter the command INSERT INTO VALUES . To do so, you must specify the data to be written to the database in the structure (key and non-key fields) before the command.

The structure must be typed according to the row structure of the database table to be updated (DATA TYPE ).

Rows can also be inserted for views. However, there are two restrictions here: The view may only contain fields from one table and must be created in the ABAP Dictionary with maintenance status 'read and change'.

The INSERT command has the two return codes '0' (row could be inserted) and '4' (row could not be inserted, as a row with the same key already exists).

The following ABAP short forms exist:

Short form 1: INSERT [CLIENT SPECIFIED] FROM .

Short form 2: INSERT [CLIENT SPECIFIED].

The second short form requires that the data, which is to be added to the database, be available in a table work area called . This table work area must be declared in the program with TABLES: .

The second short form is forbidden using ABAP Objects.
a database table. The internal table contains the data in the rows that are to be inserted.

The internal table must be typed to row type .

If the operation can be carried on all rows, the return code sy-subrc returns the value zero. If even one data record cannot be created, a runtime error is triggered. This means that no data record is inserted by the command.

You can prevent the runtime error occurring with the addition ACCEPTING DUPLICATE KEYS.

In the event of an error, the addition sets return code 4 instead of the runtime error. The data records that were successfully inserted are not rejected (no DB ROLLBACK)

The sy-dbcnt system field contains the number of rows that were successfully inserted in the database.

The command UPDATE SET = ... = WHERE allows you to change data in one row in a database table. After the SET command, you specify the fields in the rows whose values you want to change and the key of the database row in the WHERE clause. The key must be specified completely; each individual field must be specified with the relational operator '='.

For numeric fields, the data following the SET command may be specified in the form of a "calculation rule" carried out on the database: f = g, f = f + g, f = f - g.
The command has the two return codes 0 (row could be changed) and 4 (row could not be changed).

Rows can also be changed in views. However, there are two restrictions here: The view may only contain fields from one table and must be created in the ABAP Dictionary with maintenance status 'read and change'.

The following g short forms exist:

Short form 1: UPDATE FROM .

Short form 2: UPDATE dbtab.

With short form 1, the entire data record must have been written to the structure (key and non-key fields) before it is called up. The structure must be typed to the row type of the database table (DATA: TYPE . The short form is not field-specific, but sends the entire structure to the database interface.

The second short form requires that the data, which is to be updated in the database, be available in a table work area called . This table work area must be declared in the program with TABLES: .

The second short form is forbidden using ABAP Objects.

If identical changes are to be made to several rows in a table, use the syntax specified on the slide.

Using the WHERE clause, specify the rows for which the change is to be carried out.

The following "calculations" are also possible here for the numerical fields to be changed:

f = g, f = f + g, f = f - g.

The command has the two return codes 0 (at least one row has been changed) and 4 (no rows could be updated).

The sy-dbcnt field contains the number of updated rows in the database table.

There is a short form UPDATE SET = ... = . This requires that a table work area has been created with TABLES and changes the fields specified after SET for all rows in the current client.

The short form is forbidden using ABAP Objects.

If changes are to be made to several rows in a database table, whereby the changes for each row is determined via an internal table, use the syntax UPDATE FROM TABLE .

Here, the internal table contains the data of the rows to be changed (key and non-key fields). The internal table must have the row type .

The command has the two return codes 0 (all rows have been updated) and 4 (at least one row of the internal table was not used to update the database; the remaining rows have been updated).

The system field sy-dbcnt contains the number of rows that have been updated in the database.

The MODIFY command is SAP-specific. It includes the operations of the two commands
INSERT ... and UPDATE...:

In other words, MODIFY FROM inserts a new data record if the structure specifies a data record that does not yet exist in the database.

If the structure specifies an existing data record; the command updates the row in question.

Using the different syntax variants, you can make changes to individual rows, make similar changes to several rows, and carry out operations on sets of records.

All variants of the MODIFY... syntax have the two return codes 0 (all rows were inserted or updated) and 4 (at least one line was not inserted or updated).

The operation can also be carried out on views. However, there are two restrictions here: The view may only contain fields from one table and must be created in the ABAP Dictionary with maintenance status 'read and change'.

The field sy-dbcnt contains the number of rows that have been changed or inserted in the database.

The command DELETE FROM WHERE enables one row to be deleted from a database table. In the WHERE clause, specify all the key fields with the relational operator '='.

The command has the two return codes 0 (row has been deleted) and 4 (row has not been deleted).

A row can also be deleted from views. However, there are two restrictions here: The view may only contain fields from one table and must be created in the ABAP Dictionary with maintenance status 'read and change'.

The following short forms exist:

Short form 1: DELETE [CLIENT SPECIFIED] FROM ,

Short form 2: DELETE [CLIENT SPECIFIED].

Short form 1 requires that the structure has been filled with the key fields of the row to be deleted before it is called up. The structure must have the row type .

Short form 2 requires that the key fields of the row to be deleted be available in a table work area called . This table work area must be declared in the program with TABLES: .

The second short form is forbidden using ABAP Objects.

The command DELETE FROM WHERE enables several rows to be deleted from a database table. Here, you can specify the rows that are to be deleted with the WHERE clause.

The command has the two return codes 0 (at least one row was deleted) and 4 (no rows were deleted).

The system field sy-dbcnt contains the number of rows that have been updated on the database.

To delete several specific rows from a database using a database operation, use the statement DELETE FROM TABLE . The internal table here contains the key fields for the rows that are to be deleted. The internal table must have the row type .

The command has the two return codes 0 (all rows have been deleted) and 4 (at least one row could not be deleted, the rest have been deleted).

There are two ways of deleting all the rows from a table in the current client:

Either DELETE FROM WHERE IN with a blank internal table

or DELETE FROM WHERE LIKE '%'.

The number of rows deleted from the database is shown in the system field sy-dbcnt.

If you receive a return code other than zero from the database interface in response to an Open SQL statement for changing data in the database, you should make sure that the database is reset to the status it had before the change attempt was made. You can do this by means of a database rollback.
The database rollback undoes any changes made to the current database LUW (see the next unit).

For return codes from DB change statements (Open SQL), the most suitable means of triggering a database rollback is to send a termination dialog message (A message or X message). This triggers a database rollback and terminates the associated program.

All other message types (E,W, I) also involve a dialog but do not trigger a database rollback.

You can also trigger a database rollback using the ABAP statement ROLLBACK WORK (without terminating the program at the same time). You should not use the ROLLBACK WORK statement directly, unless you do not want to reset the program context (unlike a termination dialog message)