Entering content frame

Native SQL for Informix Locate the document in its SAP Library structure

The principal new features in the Native SQL interface from Release 4.0 are:

         Ability to connect to several databases in parallel (including non-SAP databases)

         Use of the cursor for stored procedures

         Access to non-SAP tables

         Ability to use (almost) all Native SQL statements for Informix The Native SQL statements that are not supported are listed below.

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 describes the direction of storing ABAP variable values in database tables (which do not have to be declared in the ABAP Dictionary) using Native SQL (INSERT and UPDATE). The second section deals with how to read from external database tables into ABAP variables using Native SQL (SELECT).

INSERT

 ABAP Field Type:

       Character C(n)

       Numeric Text N(n)

       Packed Number P(m,d)

       Integer I

       Float F

       Date D

       Time T

       Hexadecimal X(n)

SELECT

 DB Column Type

       char

       varchar

       nchar

       text

       byte

       date

       datetime

       interval

       decimal

       numeric

       money

       smallint

       integer

       smallfloat

       float

       double precision

 

Using Informix Native SQL in SAP 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.

 

ABAP Data Type C

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  [Comments]

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

other

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 valid DB datetime 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

 

ABAP Data Type N

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

 

 

ABAP Data Type P

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

 

ABAP Data Type I

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

 

ABAP Data Type F

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

 

ABAP Data Type D

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.

 

ABAP Data Type T

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.

 

ABAP Data Type X

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 ABAP database 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
[left-justified, filled out with trailing blanks]

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
[right-justified, filled out with leading zeros]

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)
[left-justified, filled out with trailing blanks]

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)
[left-justified, filled out with trailing blanks]

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
[left-justified, 1st and 2nd characters lost]

ok

 

 

Database Field Type: Text

ABAP

Data Type

Test case [notes]

Result

Character C

Database data value width > ABAP field width

[rtrunc]

Database data value width < ABAP field width
[left-justified, filled out with trailing blanks]

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

 

Database Column Type: Byte

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
[left-justified, filled out with trailing blanks]

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

 

Database Column Type: Date

ABAP

Data Type

Test case [notes]

Result

Character C

ABAP data value width < 10

[rtrunc]

ABAP field width > 10
[left-justified, filled out with trailing blanks]

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: datetime / 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
[may not be left-justified, filled out with trailing blanks]

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 money

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 precision

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 SAP Release 4.

You cannot use the following Native SQL statements in ABAP. They come under the following categories:

         NA
not applicable. These statements are non-executable and generate an error.

         SP
Special syntax. These statements require a special Native SQL syntax; see the keyword documentation for EXEC SQL

         NR
Not recommended. These statements should not be used with a database that is managed using the ABAP Dictionary.

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

 

 

Leaving content frame