Skip to content

ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Write Accesses 

INSERT, UPDATE, MODIFY dbtab - LOB Handles

In the statements INSERT, UPDATE, or MODIFY, LOB handles can be created as writer streams and specified as locators.

Other versions: 7.31 | 7.40 | 7.54

Prerequisites

An LOB in a row to be inserted or changed can be supplied from an LOB handle component of a work area or an individual reference variable for an LOB handle (at UPDATE with SET). The static type of each LOB handle component must be one of the following system classes for streaming or locators:

  • CL_ABAP_DB_C_WRITER or CL_ABAP_DB_C_LOCATOR in the case of CLOBs.
  • CL_ABAP_DB_X_WRITER or CL_ABAP_DB_X_LOCATOR in the case of BLOBs.

The stream type or locator type must match the data type of the LOBs.

Rules

Unlike in reads, the behavior for streams does not match the behavior for locators.

Writes Using Writer Streams

If, in a write, an LOB is associated with a reference variable of the type CL_ABAP_DB_C_WRITER or CL_ABAP_DB_X_WRITER, a corresponding writer stream is usually created and the reference variable points to the object after execution. The LOB to be changed can be edited using the methods of the writer stream. When an LOB is associated with a writer stream referenced by an LOB handle component from the source work area, the following various platform-dependent behavior may occur:

  • On some database systems (MaxDB, Oracle), the components of the work area which are not LOB handle components are written directly to the database when executing the statement. If the rows cannot be added or changed, sy-subrc and sy-dbcnt respectively are set immediately and no writer stream is created for the LOB handle components. If the rows can be added or changed, writer streams are created and their content is passed to the database when closing the last writer stream of the ABAP SQL statement at the latest.
  • On other database systems, the components of the work area which are not LOB handle components are not written directly to the database when executing the statement. Instead, writer streams are always created for the LOB handle components. The contents of the remaining components are also delayed, that is, they are passed when closing the last writer stream of the ABAP SQL statement at the latest. Between executing the statement and actually writing the remaining components, the status is undefined since it is not yet determined whether the operation can be executed at all. In this case, sy-subrc is set to the value 2 and sy-dbcnt is set to -1 after the statement is executed. Before a writer stream is closed, a reference to an object from the class CL_ABAP_SQL_CHANGING_STMNT, a subclass of CL_ABAP_SQL_STATEMENT_HANDLE, from the interface IF_ABAP_DB_WRITER, can be obtained using the method GET_STATEMENT_HANDLE. The GET_STATE method provides information about the status of the ABAP SQL statement, and the GET_DB_COUNT method returns the number of added or changed rows after the last writer stream of the ABAP SQL statement was closed. The methods of the abstract superclass can be used to check the status of the ABAP SQL statement and all streams that are still open can be closed. If the ABAP SQL statement is not closed by the closing of the last writer stream, GET_DB_COUNT also returns the value -1. If the operation cannot be executed, for example due to duplicate key entries, the exception CX_STREAM_IO_EXCEPTION or CX_CLOSE_RESOURCE_ERROR is raised when the data is passed to the database, depending on exactly when the data is passed.

A writer stream created in this way exists until it is explicitly closed using its method CLOSE from the IF_ABAP_CLOSE_RESOURCE interface. As long as a writer stream for an ABAP SQL statement is still open, the corresponding database operation is not finished. The state of the ABAP SQL statement is described using an object of the CL_ABAP_SQL_STATEMENT_HANDLE class, for which the GET_STATEMENT_HANDLE method returns a reference for every writer stream.

When using writer streams, a non-handleable exception is raised in the following cases:

  • When accessing a writer stream that is already closed.


Notes

  • Exactly when the data of a writer stream is passed to the database is not defined. The data is passed at the latest when the last writer stream is closed but can also occur during writing in the stream, for example because a buffer is no longer large enough.

  • Always close a writer stream explicitly and as soon as possible using its method CLOSE. Also refer to Streaming and Locators.

Writes Using Locators

If, in a write, an LOB is associated with a reference variable of type CL_ABAP_DB_C_LOCATOR or CL_ABAP_DB_X_LOCATOR, this variable must point to an appropriate locator, created in a statement SELECT for a LOB in a different database table or the same table. If the statement can be executed (sy-subrc is 0), the LOB to be changed is replaced directly in the database system using the complete LOB of the locator.

Restrictions

The following restrictions apply:

  • A maximum of 16 data streams can be opened for an ABAP SQL statement.
  • In a database LUW there can be a maximum of 16 ABAP SQL statements whose data streams are open at the same time.
  • A maximum of 1000 LOB handles can be open in a database LUW.


Note

Always close a LOB handle as soon as possible using its method CLOSE. Also refer to Streaming and Locators.

Examples


Example

Components string1 and string2 in structure wa are declared as LOB handles for character-like writer streams. The statement MODIFY creates the writer streams. Character strings are passed to both writer streams via reference variable stmnt. Once the writer streams have been closed, the associated database fields STRING1 und STRING2 contain the chained content of the streams.

DATA wa TYPE demo_expressions WRITER FOR COLUMNS string1 string2. 

wa = VALUE #( id = 'X' ). 

MODIFY demo_expressions FROM wa. 

DATA(stmnt) = wa-string1->get_statement_handle( ). 
DO 10 TIMES. 
  wa-string1->write( CONV #( sy-index ) ). 
ENDDO. 
wa-string1->close( ). 

stmnt = wa-string2->get_statement_handle( ). 
DO 26 TIMES. 
  wa-string2->write( substring( val = sy-abcde 
                               off = sy-index - 1 
                               len = 1 ) ). 
ENDDO. 
wa-string2->close( ). 

SELECT SINGLE string1, string2 
       FROM demo_expressions 
       WHERE id = 'X' 
       INTO @DATA(result). 

cl_demo_output=>display( result ).

Example

In the statement UPDATE, the column STRING2 is modified with a locator, which was created in a SELECT statement for the column STRING1 of the same database table. Afterwards STRING1 and STRING2 have the same content.

DELETE FROM demo_expressions. 
INSERT demo_expressions FROM @( VALUE #( id = 'X' string1 = sy-abcde ) ). 

DATA locator TYPE REF TO cl_abap_db_c_locator. 

SELECT SINGLE string1 
       FROM   demo_expressions 
       WHERE  id = 'X' 
       INTO   @locator. 

UPDATE demo_expressions SET string2 = @locator WHERE id = 'X'. 

SELECT SINGLE string1, string2 
       FROM demo_expressions 
       WHERE id = 'X' 
       INTO @DATA(result). 

cl_demo_output=>display( result ).

Executable Examples