ARTICLE
Open SQL - Strings
Open SQL can be used to store character strings and binary data
as strings in database columns. There are two kinds of strings in the
database, short strings and long strings (
LOB s ), which differ in the way the data is
stored in the database. Whether a string column is a short or a long
string, is specified in ABAP Dictionary.
Note the following when using strings in database tables:
The structure of a database table with strings is deep and cannot be
specified in positions in which only flat structures are possible, for
example in the TABLES statement, which is
obsolete for database access, or for typing the obsolete
table parameters of procedures.
Database tables with strings cannot be used in
views .
Short Strings
Short strings are only available for character strings (DDIC type
SSTRING ). They are normally implemented as VARCHAR fields in
the database and stored in the data record. Short strings must always
have a length restriction in ABAP Dictionary which cannot exceed 1333
characters. Trailing blanks are ignored by the database.
Short strings can be used as key fields of database tables. This can
produce significant memory and performance gains in comparison with
using long fields of type CHAR .
In Open SQL statements, you can use short strings wherever you
can use CHAR fields.
Note
When you use short strings as key fields, note that their trailing
blanks are ignored by the database. An exception is raised if a row is
inserted whose trailing blanks are the only thing that differentiate it
from an existing row in a key field with the type SSTRING .
Long Strings
Long strings ( LOB s ) are provided as
CLOB s for character strings (ABAP
Dictionary type STRING ) or as BLOB s
for binary data (ABAP Dictionary type RAWSTRING ). These
strings are generally designed so that only one LOB locator is
saved in the data record, and the actual string data is saved outside
the data record. A length restriction can be defined for long strings in
ABAP Dictionary. For columns of the type STRING , trailing spaces
are retained.
Long strings are subject to the following restrictions:
They must not be used in key fields.
They must not be used in relational expressions of the
WHERE clause and HAVING clause
as well as in the ON conditions of the
FROM clause .
They must not be used in aggregate
functions .
They must not be used in database indexes .
They must not be used in the SELECT clause
in combination with the addition
DISTINCT .
They must not be used in a GROUP BY
clause .
They must not be used in an ORDER BY
clause .
If a string column is modified using UPDATE ... SET
, the primary key must be specified
in full.
Note
Long strings ( LOB s) can be accessed using
streaming and
locators .
Length Restriction
It is possible for long strings and mandatory for short strings to
define a length restriction for them in ABAP Dictionary. If this
restriction is violated when data is written to the database, the
system raises an exception of the class
CX_SY_OPEN_SQL_DB . Any truncation of the string when data is
read from the database into a target field is ignored. The value of
the length restriction can be queried using the function
dbmaxlen( ) .
Notes
RESET M2
Any database can choose to represent an empty string by a NULL
value.
If a database table dbtab or a work area wa contains
strings, the work area wa must be compatible with the row
structure of the database table dbtab for the following
statements:
- SELECT * FROM dbtab INTO wa
- INSERT dbtab FROM wa or INSERT INTO dbtab VALUES wa
- UPDATE dbtab FROM wa
- MODIFY dbtab FROM wa
- DELETE dbtab FROM wa
Similarly, if the following set operations are used, the row structure
of the internal table itab must be compatible with the row
structure of the database table dbtab if itab or dbtab
contains strings:
- SELECT * FROM dbtab INTO TABLE itab
- INSERT dbtab FROM TABLE itab
- UPDATE dbtab FROM TABLE itab
- MODIFY dbtab FROM TABLE itab
- DELETE dbtab FROM TABLE itab
Performance
Since the data of long strings is stored outside the data record, access
to long strings is slower than to other data types. This applies
particularly to set operations. This note is not applicable to short
strings.
Documentation extract taken from SAP system, � Copyright SAP AG. All rights reserved