DML Statements (SAP Library

DML Statements

Use

The DML (Data Manipulation Language) statements INSERT, UPDATE and DELETE are used to insert, update or delete data on the database respectively.

Activities

Inserting rows

The INSERT statement is used to add rows to a table. Host variables may appear in the VALUES clause. For more information, see Open SQL Grammar.

Example

String empl_name = "Miller";

int empl_id = 911;

#sql [ctx] { INSERT INTO employee (name, id)

VALUES (:empl_name, :empl_id) };

Insert. One a new entry is inserted into the employee table.

Updating rows

The UPDATE statement is used to modify rows in a table. Host variables can appear in the SET and WHERE clause. For more information, see Open SQL Grammar.

Example

String empl_name = "Smith";

int empl_id = 911;

#sql [ctx] { UPDATE employee

SET name = :empl_name

WHERE id = :empl_id }

Update. The name of the employee with the id 911 is update to the new value 'Smith'.

Deleting rows

The DELETE statement is used to remove rows from a table. Host variables can appear in the WHERE clause. For more information, see Open SQL Grammar.

Example

int empl_id = 1234;

#sql [ctx] { DELETE FROM employee

WHERE id = :empl_id };

Delete. The entry for the employee with the id 1234 is deleted.

Update Count

In contrast to queries, DML statements do not produce a result set. Instead, DML statements return an update count – that is, the number of changed rows. In SQLJ, the update count of the last executed statement can be retrieved by the getUpdateCount()method of the ExecutionContext object this statement has been executed on.

Example

#sql [ctx] { DELETE FROM employee WHERE id > 500) };

int updateCount = ctx.getExecutionContext().getUpdateCount();

System.out.println("" + updateCount + " rows have been deleted.");

Update Count.A DELETE statement is executed on the default execution context of the connection context ctx. The number of rows deleted is retrieved using the getUpdateCount() method of this execution context.