SQL Data Type Mapping and Defining SQL Columns as Date Columns

Table Procedure Examples, Design, and Use › SQL Data Type Mapping and Defining SQL Columns as Date Columns

SQL Data Type Mapping and Defining SQL Columns as Date Columns

The following table gives the mapping of the SQL data types in the generated SQL DDL syntax files. IDMS SQL syntax is generated in the .SQ1 files, Ingres 1.x SQL is generated in the .SQ2 files and Ingres 2.x SQL is generated in the .SQ3 files.

Type	IDMS SQL	Ingres 1.x SQL	Ingres 2.x SQL
B	BINARY(length	BYTE(length)	BYTE(length)
C	CHAR(length)	CHAR(length)	CHAR(length)
DT	DATE	DATE	DATE
D	DECIMAL(precision,scale)	FLOAT8	DECIMAL(precision,scale)
UD	UNSIGNED DECIMAL(precision, scale)	FLOAT8	DECIMAL(precision,scale)
DP	DOUBLE PRECISION	FLOAT8	FLOAT8
F	FLOAT	FLOAT4	FLOAT4
G	GRAPHIC	CHAR	CHAR
I	INTEGER	INTEGER4	INTEGER4
LI	LONGINT	FLOAT8	DECIMAL(19,0)
SI	SMALLINT	SMALLINT	SMALLINT
N	NUMERIC(precision,scale)	FLOAT8	DECIMAL(precision,scale)
UN	UNSIGNED NUMERIC(precision,scale)	FLOAT8	DECIMAL(precision,scale)
R	REAL	FLOAT4	FLOAT4
T	TIME	CHAR(8)	CHAR(8)
TS	TIMESTAMP	CHAR(26)	CHAR(26)

Type represents the data type to be used for the corresponding SQL Column in the generated table and table procedure definitions. The type of a column is determined by Quick Bridge from the element definition in the nonSQL schema. Because date fields cannot be defined as such in a nonSQL schema, one can overwrite the type as derived from the nonSQL schema definition with "DT" to define an SQL column as a date.

The data type can be set to"DT in the same dialogs that allow you to edit the SQL Column Names. To change the data type of a column, make the Type column visible by dragging the SQL Column Names column to the left in the container.

There is no date, time or timestamp data type in a nonSQL schema definition. Dates are represented by fixed length elements that have various formats. In many cases, structures have separate elements for the year, month, day and optional separators are used.

To be able to map to a date SQL column, you must use a nonSQL schema and subschema in which the group element that represents the date is redefined as an elementary field such as PIC 9(6) or PIC 9(8). Quick Bridge will display the date field as a single record field name to be included. Setting the type to DT will result in generated tables and table procedure definitions with the appropriate DATE data types for the date fields. The generated COBOL program will contain comments that indicate where any necessary date conversion statements need to be added.