view release on metacpan or  search on metacpan

lib/DBD/Informix/Summary.pm  view on Meta::CPAN

  DECIMAL(s,p)      - s-digit fixed-point number with p decimal places
  MONEY(s)          - s-digit fixed-point number with 2 decimal places
  MONEY(s,p)        - s-digit fixed-point number with p decimal places
  NUMERIC(s)        - synonym for DECIMAL(s)
  NUMERIC(s,p)      - synonym for DECIMAL(s,p)
  INT8              - signed 64-bit integer, excluding -2**63 (IDS/UDO)
  SERIAL8           - synonym for INT8 as far as scale is concerned

C<DBD::Informix> always returns all numbers as strings.  Thus the driver
puts no restriction on size of PRECISION or SCALE.


=head2 String Data Handling

Informix supports the following string data types:

  VARCHAR(size)
  NVARCHAR(size)
  CHAR
  CHAR(size)
  NCHAR
  NCHAR(size)
  CHARACTER VARYING(size)
  NATIONAL CHARACTER VARYING(size)
  NATIONAL CHARACTER(size)
  CHARACTER(size)
  VARCHAR(size,min)   -- and synonyms for this type
  NVARCHAR(size,min)  -- and synonyms for this type
  LVARCHAR            -- IDS/UDO only

Arguably, TEXT and BYTE blobs should also be listed here,
as they are automatically converted from/to strings.

CHAR types have a limit of 32767 bytes in OnLine and IDS and a slightly
smaller value (325xx) for SE. For VARCHAR types the limit is 255.
LVARCHAR columns are limited to 2 KB; when used to transfer other data
types, up to 32 KB.  C<DBD::Informix> 0.61 doesn't have fully operational
LVARCHAR support.

The CHAR and NCHAR types are fixed length and blank padded.

Handling of national character sets depends on the database version
(and is different for versions 5, for versions 6 and 7.1I<x>, and for
versions 7.2I<x> and later).  Details for version 8.I<x> vary depending on I<x>.
It depends on the locale, determined by a wide range of standard ( I<e.g.>,
C<LANG>, C<LC_COLLATE>) and non-standard ( I<e.g.>, C<DBNLS>, C<CLIENT_LOCALE> )
environment variables.  For details, read the relevant manual.
Unicode is not currently directly supported by Informix (as of
1999-02-28).

Strings can be concatenated using the C<||> operator.

=head2 Date Data Handling

There are two basic date/time handling types: DATE and DATETIME.
DATE supports dates in the range 01/01/0001 through 31/12/9999.
It is fairly flexible in its input and output formats.  Internally,
it is represented by the number of days since December 31 1899,
so January 1 1900 was day 1.  It does not understand the calendric
gyrations of 1752, 1582-4, or the early parts of the first millenium,
and imposes the calendar as of 1970-01-01 on these earlier times.

DATETIME has to be qualified by two components from the set:

  YEAR MONTH DAY HOUR MINUTE SECOND FRACTION FRACTION(n) for n = 1..5

These store a date using ISO 8601 format for the constants.
For example, DATE("29/02/2000") is equivalent to:

  DATETIME("2000-02-29") YEAR TO DAY,

and The Epoch for POSIX systems can be expressed as:

  DATETIME(1970-01-01 00:00:00) YEAR TO SECOND

There is no direct support for timezones.

The default date time format depends on the environment locale
settings and the version and the data type.  The DATETIME types are
rigidly ISO 8601 except for converting 1-digit or 2-digit years to a
4-digit equivalent, subject to version and environment.

Handling of two digit years depends on the version, the bugs fixed, and
the environment.  In general terms (for current software), if the
environment variable C<DBCENTURY> is unset or is set to C<'R'>, then the
current century is used.  If DBCENTURY is C<'F'>, the date will be in the
future; if DBCENTURY is C<'P'>, it will be in the past; if DBCENTURY is
C<'C'>, it will be the closest date (50 year window, based on current day,
month and year, with the time of day untested).

The current datetime is returned by the C<CURRENT> function, usually
qualified as CURRENT YEAR TO SECOND.

Informix provides no simple way to input or output dates and times in
other formats.  Whole chapters can be written on this subject.

Informix supports a draft version of the SQL2 INTERVAL data type:

  INTERVAL start[(p1)] [TO end[(p2)]]

(Where C<[]> indicates optional parts.)

The following interval qualifications are possible:

  YEAR, YEAR TO MONTH,
  MONTH,
  DAY, DAY TO HOUR, DAY TO MINUTE, DAY TO SECOND,
  HOUR, HOUR TO MINUTE, HOUR TO SECOND,
  MINUTE, MINUTE TO SECOND,
  SECOND, FRACTION

I<p1> specifies the number of digits specified in the most significant
unit of the value, with a maximum of 9 and a default of 2 (except YEAR
that defaults to 4). I<p2> specifies the number of digits in fractional
seconds, with a maximum of 5 and a default of 3.

Literal interval values may be specified using the following syntax:

  INTERVAL value start[(p1)] [TO end[(p2)]]

For example: