Product Documentation

SQL Operations Guide

Previous Topic

Next Topic

Mapping Unsigned Integers with FairCom DB SQL

A command line option is available to ctsqlimp, -p (note lower case), to map an unsigned integer (CT_CHARU, CT_INT2U and CT_INT4U) not to a corresponding FairCom DB SQL type but to the next larger integer type, thus “promoting” it.

The related -P option (note upper case) also promotes unsigned types to a greater signed type, however it includes a CHECK clause on the column to avoid SQL values rolling over due to the smaller range of available signed values.

While the FairCom DB SQL engine will use the larger integer type, nothing at the c-tree ISAM storage level changes. This allows proper unsigned (positive) values for those values that when mapped into a signed field would become negative.

Without the -p option the mapping from c-tree to FairCom DB SQL is the following:

  • CT_CHARU - TINYINT
  • CT_INT2U - SMALLINT
  • CT_INT4U - INTEGER

With the -p option the mapping becomes:

  • CT_CHARU - SMALLINT
  • CT_INT2U - INTEGER
  • CT_INT4U - BIGINT

When using the -p switch, a SELECT * from table shows the proper values for unsigned integer fields instead of a negative value.

When doing an insert, as long as the inserted value fits in the original type range, the value seen at the ISAM level is the proper one. If the value inserted is outside the original type range, only the lower significant part is stored.

TOCIndex