Pervasive PSQL Supported Data Types

Pervasive PSQL Supported Data Types

The following table shows information about the transactional and relational data types supported by Pervasive PSQL. The SRDE converts the relational data types to ODBC default types unless another data type conversion is specified when SQLGetData or SQLBindCol is called. (For a discussion of data type conversions, see the data types appendix in the Microsoft ODBC Programmer’s Reference.).

Table 126

Pervasive PSQL Data Types and Equivalent ODBC Data Types

Transactional Type (Size)

Relational Type

ODBC Type (code)1

Create/Add Parameters4

SQL_SMALLINT(5)

SQL_LONGVARBINARY(-4)

SQL_LONGVARCHAR(-1)

not null
case insensitive

SQL_TIMESTAMP(11)

precision
scale
not null

UNIQUEIDENTIFIER

SQL_TINYINT(-6)

SQL_SMALLINT(5)

precision
scale
not null

precision
scale
not null

precision
scale
not null

precision
scale
not null

precision
scale
not null

precision
scale
not null

precision
scale
not null

size
not null
case insensitive

size
not null
case insensitive

SQL_TIMESTAMP(11)

UNSIGNED(1) BINARY

SQL_TINYINT(-6)

UNSIGNED(2) BINARY

SQL_SMALLINT(5)

UNSIGNED(4) BINARY

UNSIGNED(8) BINARY

not supported in SQL

not supported in SQL

not supported in SQL

not supported in SQL

SQL_VARCHAR(12)

size
not null
case insensitive

SQL_SMALLINT(5)

1 SQL_FLOAT and SQL_VARBINARY are not supported by Pervasive PSQL

2 The Pervasive PSQL metadata type code

3 "n/a" stands for "not applicable"

4 The required parameters are precision and size. The optional parameters are case insensitive, not null, and scale.

1. Padded with spaces

2. Flag set in FIELD.DDF to tell SQL to use binary. See also COLUMNMAP Flags in Distributed Tuning Interface Guide and Column Flags in Distributed Tuning Objects Guide.

3. Padded with binary zeros

4. Cannot be used as variable or in stored procedures

6. Cannot be indexed

7. Transactional only

8. TRUEBITCREATE must be set to "on" (the default)

9. TRUEBITCREATE must be set to "off"

10. Type code 30 is not a transactional interface type code. It is the identifier for DATETIME within the relational interface metadata

Data Type Ranges

The following table lists the value range for the Pervasive PSQL data types.

Table 127

Pervasive PSQL Data Type Ranges

Relational Data Type

Valid Value Range

-1.70141172e+38 – +1.70141173e+38

The smallest value by which you can increment or decrement a BLOAT4 is 2.938736e-39

-1.70141173e+38 – +1.70141173e+38

The smallest value by which you can increment or decrement a BLOAT8 is 2.93873588e-39

-9223372036854775808 – +9223372036854775807

range not applicable

range not applicable

-922337203685477.5808 – +922337203685477.5807

01-01-0001 – 12-31-9999

Note: 00-00-0000 is not a valid value to enter for DATE. If you have legacy data that contains 00-00-0000 data in DATE, you can query it by specifying "is null" in the query.

January 1, 1753 through December 31, 9999, to an accuracy of one thousandth of a second

depends on the length and number of decimal places

-1.7976931348623157e+308 – +1.7976931348623157e+308

The smallest value by which you can increment or decrement a DOUBLE is 2.2250738585072014e-308

-1.7976931348623157E+308 – +1.7976931348623157E+308

The smallest value by which you can increment or decrement a FLOAT is 2.2250738585072014e-308

+1 – +2147483647

-2147483648 – +2147483647

range not applicable

range not applicable

range not applicable

-99999999999999999.99 – +99999999999999999.99

Depends on the length and number of decimal places. See Precision and Scale of Decimal Data Types.

Depends on the length and number of decimal places. See Precision and Scale of Decimal Data Types.

Depends on the length and number of decimal places. See Precision and Scale of Decimal Data Types.

Depends on the length and number of decimal places. See Precision and Scale of Decimal Data Types.

Depends on the length and number of decimal places. See Precision and Scale of Decimal Data Types.

Depends on the length and number of decimal places. See Precision and Scale of Decimal Data Types.

-3.4028234E+38 – +3.4028234e+38

The smallest value by which you can increment or decrement a REL is 1.4E-45

-32768 – +32767

range not applicable

00:00:00 – 23:59:59

0001-01-01 00:00:00.0000000 – 9999-12-31 23:59:59.9999999 UTC

0 – 18446744073709551615

0 – 4294967295

UNIQUEIDENTIFIER

range not applicable

range not applicable

Operator Precedence

An expression may have multiple operators. Operator precedence determines the sequence in which the operations are performed. Pervasive PSQL uses the following levels of precedence. An operator on a higher level is evaluated before an operator on a lower level. Level one is the highest, level nine the lowest.

1

+ (positive), - (negative), ~ (bitwise NOT)

2

* (multiply), / (divide), % (modulo)

3

+ (add), (+ concatenate), - (subtract), & (bitwise AND)

4

=, >, <, >=, <=, <>, != (these comparison operators mean the following, respectively: equals, greater than, less than, greater than equal to, less than equal to, not equal, not equal)

5

^ (bitwise Exclusive OR), | (bitwise OR)

6

7

8

ALL, ANY, BETWEEN, IN, LIKE, OR, SOME

9

Two operators in an expression that have the same precedence level are evaluated left to right based on their position within the expression.

For example, in the SET statement in the following procedure, the division operator is evaluated before the multiplication operator. The procedure returns 21.

CREATE PROCEDURE checkvalue();

DECLARE :Counter INTEGER;

SET :Counter = 12 / 4 * 7;

PRINT :Counter;

CALL checkvalue

You may use parentheses to override the defined precedence of the operators in an expression. Everything within the parentheses is evaluated first to yield a single value. The value may then be used by an operator outside of the parentheses.

For example, in the SET statement in the following procedure, the division operator would ordinarily be evaluated before the addition operator. The result would be 12 (that is, 8 + 4). However, the addition is performed first because of the parentheses, so the procedure returns a result of 4.

CREATE PROCEDURE checkvalue1();

DECLARE :Counter INTEGER;

SET :Counter = 32 / (4 + 4);

PRINT :Counter;

CALL checkvalue1

If an expression has nested parentheses, the most deeply nested expression is evaluated first, followed by the next most deeply nested expression, and so forth.

For example, in the following SET statement, the addition is performed first (most deeply nested), then the multiplication, then the subtraction, and finally the division. The result is that the variable evaluates to 5.

SET :Counter = 100 / (40 - (2 * (5 + 5)));

Data Type Precedence

Data type precedence determines which data type results when two expressions of different data types are combined by an operator. The data type with the lower precedence is converted to the data type with the higher precedence.

Note: Pervasive PSQL returns an error if you perform an operation on incompatible data types. For example, you try to add an INTEGER to a CHAR.

Numeric Data Types

Pervasive PSQL supports the following precedence for the relational numeric data types:

•

DOUBLE, FLOAT, BFLOAT8 (highest)

•

•

DECIMAL, NUMERIC, NUMERICSA, NUMERICSTS

•

NUMERICSLS, NUMERICSTB, NUMERICSLB

•

CURRENCY, MONEY

•

BIGINT, UBIGINT

•

INTEGER, UINTEGER, IDENTITY

•

SMALLINT, USMALLINT, SMALLIDENTITY

•

TINYINT, UTINYINT

•

Character Data Types

The precedence for the relational character data types is:

•

•

If you concatenate a CHAR or VARCHAR with a LONGVARCHAR, the result is a LONGVARCHAR.

If you concatenate a CHAR with a VARCHAR, the result is the type of the first data type in the concatenation (moving left to right). For example, if c1 is a CHAR and c2 is a VARCHAR, the result of (c1 + c2) is a CHAR; the result of (c2 + c1) is a VARCHAR.

Time and Date Data Types

The precedence for the time and date data types is:

•

•

•

•

Data Types To Which Precedence Does Not Apply

The BINARY, LONGVARBINARY, and UNIQUEIDENTIFIER data types do not have a precedence because operations to combine them are not allowed.

Precision and Scale of Decimal Data Types

Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. The number 909.777 has a precision of 6 and a scale of 3, for instance.

The default maximum precision of numeric, numericsa, and decimal data types is 64. The default maximum precision of NUMERICSTS and NUMERICSLS is 63 because it reserves one byte for the plus or minus sign.

Precision and scale are fixed for all numeric data types except DECIMAL. An arithmetic operation on two expressions of the same data type results in the same data type, with the precision and scale for that type. If the operation involves expressions with different data types, the precedence rules determine the data type of the result. The result has the precision and scale defined for its data type.

The result is a DECIMAL for the following conditions:

•

Both expressions are DECIMAL.

•

One expression is DECIMAL and the other is a data type with a precedence lower than DECIMAL.

Table 128 defines how precision and scale are derived when the result of an operation is of data type DECIMAL. “Exp” stands for “expression,” “s” stands for “scale,” and “p” stands for “precision.”

Table 128

Calculation of Precision and Scale for DECIMAL Operation

Addition (exp1 + exp2)

max(s1, s2) + max(p1 - s1, p2 - s2) +1

Subtraction (exp1 - exp2)

max(s1, s2) + max(p1 - s1, p2 - s2) +1

Multiplication (exp1 * exp2)

Division (exp1 / exp2)

p1 - s1 + s2 + max(6, s1 + p2 +1)

max(6, s1 + p2 +1)

UNION (exp1 UNION exp2)

max(s1, s2) + max(p1 - s1, p2 - s2) +1

For example, if you add or subtract two fields defined as DECIMAL(8,2) and DECIMAL(7,4), the resulting field is DECIMAL(11,4).

If your application runs against different SQL DBMS products, you may encounter the following issues pertaining to truncation.

In certain situations, some SQL DBMS products prevent insertion of data because of truncation, while Pervasive PSQL allows the insertion of that same data. Additionally, Pervasive PSQL’s reporting of SQL_SUCCESS_WITH_INFO and the information being truncated differs with some SQL DMBS products in some scenarios regarding when the message is reported.

Numeric string data and true numeric data are always truncated by Pervasive PSQL. Some SQL DBMS products round this data when applicable. So if you have a numeric string or true numeric value of 123.457 and you insert it into a 6 bytes string column or precision 2 numeric column, Pervasive PSQL always inserts 123.45. Other DBMS products, by comparison, may insert a value of 123.46.