Native SQL for Informix
The principal new features in the Native SQL interface from Release 4.0 are:
Isolation Levels
All SET ISOLATION LEVEL statements last not only within EXEC SQL - ENDEXEC, but also in subsequent statements, for example, in Open SQL for the same database connection. This can adversely affect the lock mechanism. To prevent this, you should reset the isolation level to DIRTY READ.
Furthermore, do not use the above statements in stored procedures, since they cannot be compared with the DBSL.
Data types
When you use Native SQL and host variables to access SAP tables in Informix, you should use work areas or the LIKE statement.
When accessing non-SAP tables, you must ensure that the ABAP variable type and the database field type are compatible, since the Native SQL module works without information from the ABAP Dictionary. The Native SQL module opens the type and memory area reserved in ABAP to the DBMS. This allows you to read and write directly to and from it. This means that the module behaves, with a few exceptions, exactly as though you were using ESQL/C. In other words, some conversions are not allowed and trigger error messages, and rounding errors and truncation are also possible. The principal data type descriptions and conversions for Informix databases are described in the Programmers Manual.
For example, if you use Native SQL to attempt to convert a DATE value into an ABAP variable with type I, the system will be unable to perform the conversion.
Below is a description of the various type compatibilities between ABAP variables and Informix database field types.
It lists all of the type conversions that are permitted and supported by the Native SQL module. Preferred type combinations are shown in bold type.
For illegal type combinations, the error procedure of the ABAP Workbench is given, along with the SQL error code of any resulting ABAP short dump. Remember, however, that this code can change from release to release.
There is no formal description of how other type combinations not listed here behave.
The first section deals with using Native SQL (INSERT and UPDATE) to save ABAP variables in non-SAP database tables. The second section deals with how to read from external database tables into ABAP variables using Native SQL (SELECT).
INSERT
ABAP field type:
SELECT
Database Column Type
Using Informix Native SQL in R/3 Release 4
INSERT and UPDATE
Saving values from ABAP variables using Native SQL.
Each of the following tables represents a single ABAP data type. This is the type of the ABAP variable whose value you want to save.
In the left-hand column is the SQL data type of the database field of the external database table.
On the right is the reaction of the ABAP Workbench.
The description of the SQL data type DECIMAL applies also to the types MONEY and NUMERIC.
The ABAP data type C can be reproduced in most databases. However, where there are non-CHAR fields, you must take care with length, format, and permitted value ranges, since rounding, truncation, or format errors can easily occur. Truncation and rounding errors are not returned as SQL errors.
DB Data Type |
Test case [notes] |
Result |
char |
ABAP data value width > DB column width |
[rtrunc] |
ABAP data value width <= DB column width |
OK | |
varchar |
ABAP data value width > DB column width |
[rtrunc] |
ABAP data value width <= DB column width |
OK | |
nchar |
ABAP data value width > DB column width |
[rtrunc] |
ABAP data value width <= DB column width |
OK | |
text |
Occurs in a DELETE statement, or text field occurs in the WHERE clause |
-615 |
otherwise |
OK | |
byte |
|
-608 |
date |
ABAP data value does not have a valid DB date format |
[undef] |
ABAP date value has a valid DB date format, but wrong value range |
-608 | |
ABAP data value has a valid DB date format |
OK | |
datetime |
ABAP data value does not have a valid DB date time format |
-1262 |
ABAP date value has a valid DB date time format, but wrong value range |
-1218 | |
ABAP data value has have a valid DB date time format |
OK | |
interval |
ABAP data value does not have a valid DB interval format |
-1264 |
ABAP data value does not have a valid DB interval format |
-1263 | |
ABAP data value has a valid DB interval format |
OK | |
decimal |
ABAP data value is non-numeric |
-1213 |
Loss of significant figures in conversion |
-1226 | |
Loss of non-significant figures |
-1226 | |
No loss in conversion |
OK | |
integer |
ABAP data value is non-numeric |
-1213 |
Loss of significant figures in conversion |
-1215 | |
No loss in conversion |
OK | |
smallint |
ABAP data value is non-numeric |
-1213 |
Loss of significant figures in conversion |
-1215 | |
No loss in conversion |
OK | |
float |
ABAP data value is non-numeric |
-1213 |
Loss of significant figures in conversion |
[round] | |
Loss of non-significant figures |
[round] | |
No loss in conversion |
OK | |
smallfloat |
ABAP data value is non-numeric |
-1213 |
Loss of significant figures in conversion |
[round] | |
Loss of non-significant figures |
[round] | |
No loss in conversion |
OK | |
double precision |
ABAP data value is non-numeric |
-1213 |
Loss of significant figures in conversion |
[round] | |
Loss of non-significant figures |
[round] | |
No loss in conversion |
OK |
DB Data Type |
Test case [notes] |
Result |
char |
ABAP field width > DB column width |
[rtrunc] |
ABAP field width <= DB column width |
ok
| |
varchar |
ABAP field width > DB column width |
[rtrunc] |
ABAP field width <= DB column width |
ok
| |
nchar |
ABAP field width > DB column width |
[rtrunc] |
ABAP field width <= DB column width |
ok
| |
text |
|
-608 |
byte |
|
-608 |
date |
|
-1218 |
datetime, interval |
DB column type consists of a single element (for example, datetime hour to hour) and ABAP value range is valid |
ok
|
otherwise |
-1218,-1261 | |
decimal |
Loss of figures in conversion |
-1226 |
No loss in conversion |
ok
| |
integer |
Loss of figures in conversion |
-1215 |
No loss in conversion |
ok
| |
smallint |
Loss of figures in conversion |
-1215 |
No loss in conversion |
ok
| |
float |
Loss of figures in conversion |
[round] |
No loss in conversion |
ok
| |
smallfloat |
Loss of significant figures in conversion |
[round] |
No loss in conversion |
ok
| |
double precision |
Loss of significant figures in conversion |
[round] |
No loss in conversion |
ok
|
DB Data Type |
Test case [notes] |
Result |
char |
ABAP data value width > DB column width |
[rtrunc] |
ABAP data value width <= DB column width |
ok
| |
varchar |
ABAP data value width > DB column width |
[rtrunc] |
ABAP data value width <= DB column width |
ok
| |
nchar |
ABAP data value width > DB column width |
[rtrunc] |
ABAP data value width <= DB column width |
ok
| |
text |
|
-608 |
byte |
|
-608 |
date |
|
-1218 |
datetime, interval |
DB column type consists of a single element (for example, datetime hour to hour) and ABAP value range is valid |
ok
|
otherwise |
-1218,-1261 | |
decimal |
Loss of figures in conversion |
-1226 |
No loss in conversion |
ok
| |
integer |
Loss of figures in conversion |
-1215 |
No loss in conversion |
ok
| |
smallint |
Loss of figures in conversion |
-1215 |
No loss in conversion |
ok
| |
float |
Loss of figures in conversion |
[round] |
No loss in conversion |
ok
| |
smallfloat |
Loss of figures in conversion |
[round] |
No loss in conversion |
ok
| |
double precision |
Loss of figures in conversion |
[round] |
No loss in conversion |
ok
|
DB Data Type |
Test case [notes] |
Result |
char |
Loss of figures in conversion |
-1207 |
No loss in conversion |
ok
| |
varchar |
Loss of figures in conversion |
-1207 |
No loss in conversion |
ok
| |
nchar |
Loss of figures in conversion |
-1207 |
No loss in conversion |
ok
| |
text |
|
-608 |
byte |
|
-608 |
date |
|
-1218 |
datetime, interval |
|
-1260 |
decimal |
Loss of figures in conversion |
-1226 |
No loss in conversion |
ok
| |
integer |
No loss in conversion |
ok
|
smallint |
Loss of figures in conversion |
-1214 |
No loss in conversion |
ok
| |
float |
Loss of figures in conversion |
[round] |
No loss in conversion |
ok
| |
smallfloat |
Loss of significant figures in conversion |
[round] |
No loss in conversion |
ok
| |
double precision |
Loss of significant figures in conversion |
[round] |
No loss in conversion |
ok
|
DB Data Type |
Test case [notes] |
Result |
char |
Loss of figures in conversion |
-1207 |
No loss in conversion |
ok
| |
varchar |
Loss of figures in conversion |
-1207 |
No loss in conversion |
ok
| |
nchar |
Loss of figures in conversion |
-1207 |
No loss in conversion |
ok
| |
text |
|
-608 |
byte |
|
-608 |
date |
|
-1218 |
datetime, interval |
|
-1260 |
decimal |
Loss of figures in conversion |
-1226 |
No loss in conversion |
ok
| |
integer |
Loss of figures in conversion |
-1215 |
No loss in conversion |
ok
| |
smallint |
Loss of figures in conversion |
-1214 |
No loss in conversion |
ok
| |
float |
No loss in conversion |
ok
|
smallfloat |
Loss of significant figures in conversion |
[round] |
No loss in conversion |
ok
| |
double precision |
No loss in conversion |
ok
|
DB Data Type |
Test case [notes] |
Result |
char |
ABAP data value width (8) > DB column width |
[rtrunc] |
ABAP data value width (8) <= DB column width |
ok
| |
varchar |
ABAP data value width (8) > DB column width |
[rtrunc] |
ABAP data value width (8) <= DB column width |
ok
| |
nchar |
ABAP data value width (8) > DB column width |
[rtrunc] |
ABAP data value width (8) <= DB column width |
ok
| |
date |
|
-1205 |
other types |
See ABAP data type C |
(see above) |
You cannot map the ABAP data type D to the Informix data type date because they have different formats. Format conversion is not possible because of the different display variants.
DB Data Type |
Test case [notes] |
Result |
char |
ABAP data value width (6) > DB column width |
[rtrunc] |
ABAP data value width (6) <= DB column width |
ok
| |
varchar |
ABAP data value width (6) > DB column width |
[rtrunc] |
ABAP data value width (6) <= DB column width |
ok
| |
nchar |
ABAP data value width (6) > DB column width |
[rtrunc] |
ABAP data value width (6) <= DB column width |
ok
| |
datetime hour to second |
|
-1261 |
other types |
See ABAP data type C |
(see above) |
You cannot map the ABAP data type T to the Informix data type datetime because they have different formats. There is no reformatting.
DB Data Type |
Test case [notes] |
Result |
char |
ABAP data value width >= 256 |
-609 |
ABAP data value width < 256 |
ok
| |
varchar |
ABAP data value width >= 256 |
-609 |
ABAP data value width < 256 |
ok
| |
nchar |
ABAP data value width >= 256 |
-609 |
ABAP data value width < 256 |
ok
| |
text |
ABAP data value width >= 256 |
ok
|
byte |
ABAP data value width < 256 |
ok
|
date |
|
[undef] |
datetime |
|
[undef] |
interval |
|
[undef] |
decimal |
|
[undef] |
integer |
|
[undef] |
smallint |
|
[undef] |
float |
|
[undef] |
smallfloat |
|
[undef] |
double precision |
|
[undef] |
Since the DBSL for R/3 tables in Informix allows you to store hexadecimal fields shorter than 256 characters in CHAR format, the native SQL module allows you to access these fields. If the ABAP variable is shorter than 256 characters, the database field is interpreted as CHAR. The first two characters in the database field contain the length information.
This also means that the ABAP variable must be larger than 256 characters when you access a byte or text field.
SELECT
Reading database fields using Native SQL.
Each of the following tables refers to a database field type, which is also the type of the value you want to read from the database table. In some cases, several database data types behave in the same way. Where this occurs, the data types have been included in a single table to save space.
In the left-hand column is the ABAP data type of the target variable in the ABAP program.
On the right is the reaction of the ABAP Workbench.
Database Column Type char, varchar, or nchar
ABAP Data Type |
Test case [notes] |
Result |
Character C |
Database data value width > ABAP field width |
[rtrunc] |
Database data value width = ABAP field width |
ok
| |
Database data value width < ABAP field width |
ok
| |
Numeric N |
Database data value width > ABAP field width |
[rtrunc] |
Database data value width = ABAP field width |
ok
| |
Database data value width < ABAP field width |
ok
| |
Packed P |
Database data value width > ABAP field width |
[rtrunc] |
Database data value width <= ABAP field width |
ok
| |
Integer I |
Database data value is non-numeric |
-1213 |
Loss of significant figures in conversion |
-1215 | |
No loss in conversion |
ok
| |
Float F |
Database data value is non-numeric |
-1213 |
Loss of significant figures in conversion |
[round] | |
Loss of non-significant figures in conversion |
[round] | |
No loss in conversion |
ok
| |
Date D |
Database data value width > ABAP field width (8) |
[rtrunc] |
Database data value width = ABAP field width (8) |
ok
| |
Database data value width < ABAP field width (8) |
ok
| |
Time T |
Database data value width > ABAP field width (6) |
[rtrunc] |
Database data value width = ABAP field width (6) |
ok
| |
Database data value width < ABAP field width (6) |
ok
| |
Hexadecimal X |
Database data value width > 256 > ABAP field width |
[rtrunc] |
ABAP data value width >= 256 |
-1269 | |
ABAP field width < 256 and database column width < 256 |
ok
|
ABAP Data Type |
Test case [notes] |
Result |
Character C |
Database data value width > ABAP field width |
[rtrunc] |
Database data value width < ABAP field width |
ok
| |
Database data value width = ABAP field width |
ok
| |
Numeric N |
|
-1269 |
Packed P |
|
-1269 |
Integer I |
|
-1269 |
Float F |
|
-1269 |
Date D |
|
-1269 |
Time T |
|
-1269 |
Hexadecimal X |
ABAP data value width < 256 |
-1269 |
ABAP data value width >= 256 |
ok
|
ABAP Data Type |
Test case [notes] |
Result |
Character C |
ABAP data value width < 256 |
-1269 |
ABAP data value width >= 256 Database data value width > ABAP field width Database data value width < ABAP field width Database data value width = ABAP field width |
[rtrunc] ok ok | |
Numeric N |
|
-1269 |
Packed P |
|
-1269 |
Integer I |
|
-1269 |
Float F |
|
-1269 |
Date D |
|
-1269 |
Time T |
|
-1269 |
Hexadecimal X |
ABAP data value width < 256 |
-1269 |
ABAP data value width >= 256 |
ok
|
ABAP Data Type |
Test case [notes] |
Result |
Character C |
ABAP data value width < 10 |
[rtrunc] |
ABAP field width > 10 |
ok
| |
ABAP field width = 10 |
ok
| |
Numeric N |
|
[undef] |
Packed P |
|
[undef] |
Integer I |
|
[undef] |
Float F |
|
[undef] |
Date D |
|
[undef] |
Time T |
|
[undef] |
Hexadecimal X |
|
-1269 |
Database Column Type: Date-Time / Interval
ABAP Data Type |
Test case [notes] |
Result |
Character C |
Database data value width > ABAP field width |
[rtrunc] |
Database data value width < ABAP field width |
ok
| |
Numeric N |
|
[undef] |
Packed P |
|
[undef] |
Integer I |
|
-1260 |
Float F |
|
-1260 |
Date D |
|
[undef] |
Time T |
|
[undef] |
Hexadecimal X |
|
-1269 |
Database Column Type: Decimal, Numeric, or Currency
ABAP Data Type |
Test case [notes] |
Result |
Character C |
Loss of figures in conversion |
[ rtrunc] |
No loss in conversion |
ok
| |
Numeric N |
Loss of figures in conversion |
[ rtrunc] |
No loss in conversion |
ok
| |
Packed P |
Loss of figures in conversion |
[ rtrunc] |
No loss in conversion |
ok
| |
Integer I |
Loss of figures in conversion |
-1215 |
No loss in conversion |
ok
| |
Float F |
Loss of figures in conversion |
[ round] |
No loss in conversion |
ok
| |
Date D |
|
[undef] |
Time T |
|
[undef] |
Hexadecimal X |
|
[undef] |
When you read the data type DECIMAL into an ABAP field with type C, you must allow space for thousand separators as well as for the decimal point.
Database Column Type integer or smallint
ABAP Data Type |
Test case [notes] |
Result |
Character C |
Loss of significant figures in conversion |
[rtrunc] |
No loss in conversion |
ok
| |
Numeric N |
Loss of significant figures in conversion |
[ rtrunc] |
No loss in conversion |
ok
| |
Packed P |
Loss of significant figures in conversion |
[ rtrunc] |
No loss in conversion |
ok
| |
Integer I |
No loss in conversion |
ok
|
Float F |
|
ok
|
Date D |
|
[undef] |
Time T |
|
[undef] |
Hexadecimal X |
|
[undef] |
Database Column Type: Smallfloat, Float, or Double
ABAP Data Type |
Test case [notes] |
Result |
Character C |
Loss of significant figures in conversion |
[ rtrunc] |
No loss in conversion |
ok
| |
Numeric N |
Loss of significant figures in conversion |
[ rtrunc] |
No loss in conversion |
ok
| |
Packed P |
Loss of significant figures in conversion |
[ rtrunc] |
No loss in conversion |
ok
| |
Integer I |
Loss of significant figures in conversion |
-1215 |
No loss in conversion |
ok
| |
Float F |
|
ok
|
Date D |
|
[undef] |
Time T |
|
[undef] |
Hexadecimal X |
|
[undef] |
Using Informix Native SQL in R/3 Release 4
You cannot use the following Native SQL statements in ABAP. They come under the following categories:
This list is not intended to be exhaustive. It is merely intended as a programming guideline. Any Informix SQL statements that are not listed here should be executable without any problem.
Informix SQL statement |
Class |
ALLOCATE DESCRIPTOR |
NA |
ALTER FRAGMENT |
NR |
ALTER INDEX |
NR |
ALTER TABLE … MODIFY NEXT SIZE |
NR |
ALTER TABLE … LOCK MODE |
NR |
ALTER TABLE … ADD ROWIDS |
NR |
ALTER TABLE … DROP ROWIDS |
NR |
BEGIN WORK |
NA |
CHECK TABLE |
NR |
CLOSE DATABASE |
NR |
CONNECT |
SP |
CREATE AUDIT |
NR |
CREATE DATABASE |
NR |
DATABASE |
NR |
DEALLOCATE DESCRIPTOR |
NR |
DECLARE CURSOR |
SP |
DELETE ... WHERE CURRENT OF <cursid> |
NA |
DESCRIBE |
NA |
DISCONNECT |
SP |
DROP AUDIT |
NR |
DROP DATABASE |
NR |
EXECUTE |
NA |
EXECUTE IMMEDIATE |
NA |
EXECUTE PROCEDURE |
SP |
FETCH |
SP |
FLUSH |
NA |
FREE |
NA |
GET DESCRIPTOR |
NA |
GET DIAGNOSTICS |
NA |
GRANT FRAGMENT |
NR |
INFO |
NA |
LOAD |
NA |
OPEN CURSOR |
SP |
OUTPUT |
NA |
PREPARE |
NA |
PUT |
NA |
RECOVER TABLE |
NR |
RENAME DATABASE |
NR |
REVOKE FRAGMENT |
NR |
ROLLFORWARD DATABASE |
NR |
SELECT ... INTO TEMP <tab> |
NA |
SELECT ... FOR UPDATE OF <column> |
NA |
SET CONNECTION |
SP |
SET DATASKIP |
NR |
SET DEBUG FILE |
NR |
SET DESCRIPTOR |
NR |
SET TRANSACTION |
NR |
START DATABASE |
NR |
UNLOAD |
NA |
UPDATE STATISTICS |
NR |
UPDATE ... WHERE CURRENT OF <cursid> |
NA |
WHENEVER |
NA |