Numeric Data Types
The gateways limit numeric data types as described in the following sections.
Numeric Literals
The precision of numeric literals is not manipulated or validated before the numeric literal is passed to the host DBMS. Since no manipulation or validation is performed, numeric literals are subject to host DBMS rules. To illustrate how Ingres and the gateways handle numeric literals differently, consider the following SELECT statement example:
select 0.999999999999999999999999
Ingres returns DECIMAL(25, 24).
MSSQL Server returns DECIMAL(24, 24).
Oracle returns FLOAT8.
Terminal Monitor provides an easy way to demonstrate the different display format of floats compared with decimals and integers:
sql -f4F10.3 -f8F79.38 DBNAME
Note: Code that depends on the DBMS to handle truncation or rounding produces different behavior depending on the database. For example, the following code:
CREATE TABLE dec_round (
notes varchar(20),
decid decimal(18, 1)
);
commit;
\p\g
insert into dec_round(notes, decid) values ('-1017.77', -1017.77); \p\g
insert into dec_round(notes, decid) values ('1017.77', 1017.77); \p\g
select * from dec_round order by decid;
commit;
\p\g
produces output the same as for Ingres:
+--------------------+--------------------+
|NOTES |DECID |
+--------------------+--------------------+
|-1017.77 | -1017.7|
|1017.77 | 1017.7|
+--------------------+--------------------+
However, in Oracle and Microsoft SQL Server, it produces this output:
+--------------------+--------------------+
|notes |decid |
+--------------------+--------------------+
|-1017.77 | -1017.8|
|1017.77 | 1017.8|
+--------------------+--------------------+
This behavior also affects bind parameters.
Oracle
The Oracle gateway limits numeric data types as described in the following sections.
Oracle Select Statements Return Numeric Literals
SELECT statements that contain a numeric literal sent to the Oracle gateway will return a floating point number. However, if you typecast the statement to an integer, it will return a numeric literal—and be consistent across multiple gateways.
This behavior also affects most functions that return numeric data, for example SUM(), and MOD().
The OPENSQL_ORACLE_NUMBER_MAPPING setting, defined at gateway catalog creation time for number mapping, has no effect. The behavior detailed in this section results from the way Oracle handles numeric data.
For example, assuming the following table and data:
create table inttest (col1 int, col2 varchar (20));
insert into inttest (col1,col2) values (1, '1');
insert into inttest (col1,col2) values (2, '2');
Then the following SELECT statements return floating point from Oracle:
select sum(col1) from inttest;
select 1 from iidbconstants;
Examples
The following statement selects a numeric literal through the Oracle gateway, which returns a floating point:
* select 1 from iidbconstants;\p\g
/* SQL Startup File */
select 1 from iidbconstants;
Executing . . .
+-----------+
|1 |
+-----------+
| 1.000|
+-----------+
(1 row)
The following statement selects an integer4 as an explicit typecast, and the Oracle gateway returns a numeric literal:
* select int4(1) as explict_cast from iidbconstants;\p\g
select int4(1) as explict_cast from iidbconstants;
Executing . . .
+-------------+
|EXPLICT_CAST |
+-------------+
| 1|
+-------------+
(1 row)
This statement selects an integer8 as an explicit typecast, and the Oracle gateway returns a numeric literal:
* select int8(1) as explict_cast from iidbconstants;\p\g
select int8(1) as explict_cast from iidbconstants;
Executing . . .
+----------------------+
|EXPLICT_CAST |
+----------------------+
| 1|
+----------------------+
(1 row)
Oracle NUMBER Mapping
Oracle's integer support is based upon the NUMBER() data type. NUMBER is similar to the Ingres DECIMAL data type, which means that the underlying data type may hold both very large numbers (larger than a 4-byte integer) and also numbers with a fractional component.
The Oracle gateway and its catalog build utility (iigwcatora) provide a flexible mapping for the Oracle NUMBER data type. In previous releases, the following mapping was used:
• Oracle NUMBER(1)–NUMBER(3) attributes were mapped to an OpenSQL INTEGER1.
• Oracle NUMBER(4)–NUMBER(5) attributes were mapped to an OpenSQL INTEGER2.
• Oracle NUMBER(6)–NUMBER(38) attributes were mapped to an OpenSQL INTEGER4.
• Oracle NUMBER(0) was mapped to OpenSQL INTEGER4.
This mapping remains the default mapping for the Oracle NUMBER data type. This mapping is used only when the scale of the NUMBER data type is zero.
The Oracle gateway supports four mappings for the Oracle NUMBER data type. To support this mapping, the Oracle gateway catalog build utility (iigwcatora) now queries the user for an Oracle NUMBER mapping (OPENSQL_ORACLE_NUMBER_MAPPING). The default setting for OPENSQL_ORACLE_NUMBER_MAPPING is OPENSQL. When the Oracle catalogs are built with an OPENSQL_ORACLE_NUMBER_MAPPING of OPENSQL, the Oracle gateway maps the Oracle NUMBER type as it has in previous releases.
The other three settings for OPENSQL_ORACLE_NUMBER_MAPPING now are:
INTEGER
The Oracle gateway maps:
• NUMBER(0) to INTEGER4
• NUMBER(1–3) to INTEGER1
• NUMBER(4–5) to INTEGER2
• NUMBER(6–10) to INTEGER4
• NUMBER(11+) to BIGINT
FLOAT
The Oracle gateway maps NUMBER(0–38) to FLOAT.
DECIMAL
The Oracle gateway maps NUMBER(0–38) to DECIMAL(38,0).
The current value of OPENSQL_ORACLE_NUMBER_MAPPING may be retrieved from the system catalog iidbcapabilities. The current value of SQL_MAX_DECIMAL_PRECISION may be retrieved from the system catalog iidbcapabilities (see
How to Determine Decimal Precision).
Users may also pass the OPENSQL_ORACLE_NUMBER_MAPPING to the gateway catalog build utility with a command line parameter. The OPENSQL_ORACLE_NUMBER_MAPPING command line parameter is preceded with the -n flag. The following sample command line executes the Oracle gateway catalog build utility with an OPENSQL_ORACLE_NUMBER_MAPPING of INTEGER against the previously defined alias oracledb:
iigwcatora oracledb -n integer
The choice of mapping is installation-wide and is determined when creating the gateway system catalogs with the tool iigwcatora. You can change the mapping choice by simply re-running iigwacatora; a menu displays offering the choices previously described. This mapping is used only when the scale of the NUMBER data type is zero.
This feature affects CREATE TABLE AS SELECT where the source tables have one or more decimal data type columns.
Microsoft SQL Server
The MS SQL Server gateway limits numeric data types as follows:
• Integer type casts of strings containing fractional numbers such as float will return an error from MSSQL. For example, select int2('0.22').
• Valid TINYINT data type range is 0 - 255. Storage size is 1 byte. Unlike other DBMSs, TINYINT is an unsigned 1-byte integer in MSSQL.
Money Data Type
Enterprise Access supports the native Ingres money data type. The gateway catalog build utility provides a flexible mapping for the Ingres money data type. In its default behavior, the Ingres money data type maps to the host DBMS float data type.
See the chapter “Gateway Catalog Setup” in the Administrator Guide for possible money data type mappings for each DBMS.
If you are using II_MONEY_FORMAT and II_MONEY_PREC, mapping the Ingres money data type to the host DBMS money data type or its equivalent is required.
Before you decide to modify the default mappings of the Ingres money data type, review the following sections to ensure that you are aware of the limitations. It is recommended that you not change the default money data type mapping unless you have a need to support II_MONEY_FORMAT and II_MONEY_PREC.
If the Ingres money data type maps to the host DBMS float data type, II_MONEY_FORMAT and II_MONEY_PREC are not supported.
The gateways limit money data types as described in the following sections.
Microsoft SQL Server
Microsoft SQL Server gateway is implemented using ODBC functions. ODBC SQL does not have a money data type. When you select the money data type from the Microsoft SQL database, the returned data type is SQL_DECIMAL with precision 19 and scale 4 for money, and precision 10 and scale 4 for smallmoney. The Microsoft SQL gateway uses SQL_DECIMAL with precision 19 and scale 4 for output money or precision 10 and scale 4 for smallmoney.
When the money data type is mapped to Microsoft SQL money data type, existing columns of type decimal(19,4), decimal(10,4), money and smallmoney will be mapped to the Ingres money data type whether or not this was the original intention. If a user creates a new column of type decimal(19,4), decimal(10,4), money or smallmoney, it will be returned from the gateway as a money data type.
If Ingres money data type is mapped to decimal(19,4), values 1,000,000,000,000.00 and larger can be inserted into money data type columns but cannot be selected as money data type. They can be selected if they are converted to a data type that can accept precisions greater than 14 through a scalar function. Casting to decimal(19,4) is not a workaround to selecting data with a precision greater than 14, because decimal(19,4) will be mapped to money data type.
Oracle
Oracle does not have a native money data type. When the money data type is mapped to number(19,4), existing columns of type number(19,4) will be mapped to the Ingres money data type whether or not this was the original intention. If a user creates a new column of type number(19,4), it will be returned from the gateway as a money data type. Note that because Oracle does not provide support for a native DBMS MONEY data type, arithmetic expressions that operate only on MONEY data type are not guaranteed to yield MONEY data type.
If Ingres money data type is mapped to number(19,4), values 1,000,000,000,000.00 and larger can be inserted into money data type columns but cannot be selected as money data type. They can be selected if they are converted to a data type that can accept precision greater than 14 through a scalar function. Casting to number(19,4) is not a workaround to selecting data with a precision greater than 14, because number (19,4) will be mapped to money data type.
ODBC
ODBC hosts generally do not have a native money data type. When money data type is mapped to decimal(19,4), existing columns of type decimal(19,4) will be mapped to Ingres money data type whether or not this was the original intention. If a user creates a new column of type decimal(19,4), it will be returned from the gateway as a money data type. Note that because ODBC does not provide support for a native DBMS MONEY data type, arithmetic expressions that operate only on MONEY data type are not guaranteed to yield MONEY data type.
If Ingres money data type is mapped to decimal(19,4), values 1,000,000,000,000.00 and larger can be inserted into money data type columns but cannot be selected as money data type. They can be selected if they are converted to a data type that can accept precision greater than 14 through a scalar function. Casting to decimal(19,4) is not a workaround to selecting data with a precision greater than 14, because decimal(19,4) will be mapped to money data type.
Display Formats
This section explores the following display formats and limitations:
• Display formats implemented using II_MONEY_FORMAT, and II_MONEY_PREC
• Limitations when using II_MONEY_FORMAT, II_MONEY_PREC with scalar function money
For more information, see the Ingres OpenSQL Reference Guide.
II_MONEY_FORMAT and II_MONEY_PREC
The Ingres environment variable II_MONEY_FORMAT specifies the character displayed as the currency symbol. The default currency symbol is the dollar sign ($). It is set installation-wide.
II_MONEY_PREC specifies the number of digits of precision to be used in the default representation of money data. Valid settings are 0, 1, and 2. The default is 2 digits.
The gateways limit the money data types, II_ MONEY_FORMAT and II_MONEY_PREC, as described in the following sections.
Microsoft SQL Server
When you implement support for II_MONEY_FORMAT and II_MONEY_PREC by changing the value of OpenSQL Money during catalog build setup, you are accepting that the Ingres money data type will be mapped to decimal(19,4) for money and decimal(10,4) for smallmoney.
Oracle
When you implement support for II_MONEY_FORMAT and II_MONEY_PREC by changing the value of OpenSQL Money during catalog build setup, you are accepting that the Ingres money data type will be mapped to the Oracle number(19, 4) data type. The Oracle number(19, 4) is used for money data type.
ODBC
When you implement support for II_MONEY_FORMAT and II_MONEY_PREC by changing the value of OpenSQL Money during catalog build setup, you are accepting that the Ingres money data type will be mapped to decimal(19,4).
II_MONEY_FORMAT, II_MONEY_PREC, and Scalar Function Money
Support for II_MONEY_FORMAT and II_MONEY_PREC affects the behavior of typecasting scalar function MONEY as shown in the following example:
Ingres
drop table my_money\g;
create table my_money (col1 money)\g;
insert into my_money (col1) values (1)\g;
select varchar(col1) from my_money;
col1
$1.00
select varchar(money(23)) from iidbconstants\p\g;
col1
$23.00
Gateway
drop table my_money\g;
create table my_money (col1 money)\g;
insert into my_money (col1) values (1)\g;
select varchar(col1) from my_money;
col1
1
select varchar(money(23)) from iidbconstants\p\g;
col1
23
The gateway returns money as a numeric string without a dollar sign attached.
Note: When casting money data type to varchar and char, the output format will differ between gateways.
Decimal Data Type
The decimal data type is an exact numeric data type defined in terms of its precision (total number of digits) and scale (number of digits to the right of the decimal point).
The minimum precision for a decimal value is 1 and the maximum is 39. The scale of a decimal value cannot exceed its precision. Scale can be 0 (no digits to the right of the decimal point).
Display Formats
This section explores the following display formats and limitations:
• Display formats implemented using II_DECIMAL
• Limitations when using II_DECIMAL with the scalar function CHAR
For more information, see the Ingres OpenSQL Reference Guide.
II_DECIMAL
The gateways use the II_DECIMAL setting as the decimal point symbol for input and output data. The one exception is for the output data of the scalar function char. For information, see
II_DECIMAL and Scalar Function CHAR.
The Ingres environment variable II_DECIMAL specifies the character displayed as the decimal point for numeric literal values, that is, the one character used to separate fractional and non-fractional parts of a number. Default value is the period (.), as in 12.34. Alternatively, the comma (,) can be used, as in 12,34. Only (.) and (,) are allowed. II_DECIMAL, which is set by the client, can be used to change the decimal point character to a comma (,).
II_DECIMAL affects decimal, float, float4, and money data types.
If II_DECIMAL is set to comma, be sure that when SQL syntax requires a comma (such as a list of table columns or SQL functions with several parameters), that the comma is followed by a space.
Example:
select col1, 12,34, left(col4, 22) from t1;
Note: For information about II_DECIMAL in OpenSQL statements, see the Ingres OpenSQL Guide.
Microsoft SQL Server
In order to use II_DECIMAL with Microsoft SQL Server, the locale and Regional settings must be defined properly. See the Administrator Guide for more information about using II_DECIMAL with Microsoft SQL Server.
For the Microsoft SQL gateway, the II_DECIMAL setting and the Microsoft SQL decimal point setting are independent. The value for the II_DECIMAL setting is the input and output data decimal point symbol for Microsoft SQL gateway.
If II_DECIMAL is set to "," and Microsoft SQL uses default setting "." for decimal point, Microsoft SQL gateway uses "," as decimal point for input and output data.
Microsoft SQL Server can use "," as the decimal point for the output data.
When selecting data, Microsoft SQL gateway gets data with the output decimal point as "," from Microsoft SQL Server. Microsoft SQL gateway converts "," to the II_DECIMAL setting.
If II_DECIMAL uses the default setting "." and Microsoft SQL is set to use "," for the output data decimal point, Microsoft SQL gateway uses "." as decimal point for input and output data.
Scalar function CHAR's output data decimal point symbol is an exception. See the Scalar Function CHAR section for more information.
Oracle
For Oracle gateway, the II_DECIMAL setting and the Oracle decimal point setting are independent. The II_DECIMAL setting is the Oracle gateway's input and output data's decimal point symbol.
If II_DECIMAL is set to "," and Oracle uses default setting "." for the decimal point, the Oracle gateway uses "," as the decimal point for input and output data.
If II_DECIMAL uses the default setting "." and Oracle is set to use "," as the decimal point, the Oracle Gateway uses "." as the decimal point for input and output data.
Note: The new II_DECIMAL support does not allow numbers with fractional components to be brought back as strings with a comma, regardless of the II_DECIMAL and Oracle decimal point settings. Instead, numbers that are converted to string will always contain a period as the fractional indicator because the gateway internally sets the NLS_NUMERIC_CHARACTERS and always uses “.” as the decimal point when data is passed to and from the database. For example, if the original Oracle decimal point is set to a comma, then the following statement returns '10.99':
select char( decimal(10,99 , 10 , 2))
II_DECIMAL has no effect on DIRECT EXECUTE IMMEDIATE statements and the default decimal point, “.”, is always used. It does not allow numbers with comma as the fractional indicator to be used in DIRECT EXECUTE IMMEDIATE statements. To send numeric literals using DIRECT EXECUTE IMMEDIATE regardless of the II_DECIMAL setting, the default period decimal point, “.”, must be used.
For example, the following statement fails:
DIRECT EXECUTE IMMEDIATE 'insert into mytable (mydeccol) values (10,99)'
The following statement works:
DIRECT EXECUTE IMMEDIATE 'insert into mytable (mydeccol) values (10.99)'
II_DECIMAL and Scalar Function CHAR
If the scalar function CHAR's argument is numeric data type, the output data’s decimal point character is dependent on the host DBMS’s decimal point setting.
Example:
create table mydec (col1 decimal(10,3));
insert into mydec (col1) values (12345);
update mydec set col1 = col1 / 1000;
SELECT CHAR (col1) from mydec;
The output is 12.345, if host DBMS decimal point setting is "." or default. The output is 12,345, if host DBMS decimal point setting is ",".
Decimal Precision
Decimal precision is to 39 places. You may use decimal scale up to that provided by the DBMS with the following elements:
• DDL (data definition language)
• Results (for example, select)
• Scalar functions or type casts
• Bind parameters
• Database procedures (input, byref, row-returning)
DDL of decimal types is subject to DBMS-specific errors and varies, depending on the database in use.
How to Determine Decimal Precision
To discover the maximum supported decimal precision for a back end, issue a regular SQL query against iidbcapabilities.
Note: SQL_MAX_DECIMAL_PRECISION capability does not exist in the iidbcapabilities system catalog prior to Enterprise Access 2.7 or Ingres 10.
For example:
select * from iidbcapabilities
where cap_capability = 'SQL_MAX_DECIMAL_PRECISION';
For more information about querying iidbcapabilities, see the OpenSQL Reference Guide.