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.). Items that are not applicable are designated "n/a."

Data Type Ranges

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

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.

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(); 
BEGIN 
DECLARE :Counter INTEGER; 
SET :Counter = 12 / 4 * 7; 
PRINT :Counter; 
END 

CALL checkvalue 

Parentheses

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(); 
BEGIN 
DECLARE :Counter INTEGER; 
SET :Counter = 32 / (4 + 4); 
PRINT :Counter; 
END 

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:

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 data 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 and decimal data types is 64.

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:

Table A-3 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."

Truncation

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.