Notes on Data Types

This section covers various behaviors and key information regarding the available data types.

CHAR, VARCHAR, and LONGVARCHAR

CHAR columns are padded with blanks to "fill" the columns

VARCHAR/LONGVARCHAR are not padded with blanks to "fill" the columns. The significant data is terminated with a NULL character.

In all cases the trailing blanks are NOT significant in comparison operations (LIKE and =). However, in the LIKE case, if a space is explicitly entered in the query (like 'abc %'), the space before the wildcard does matter. In this example you are looking for 'abc<space><any other character>'

BINARY and LONGVARBINARY

BINARY columns are padded with binary zeros to "fill" the columns

LONGVARBINARY are NOT padded with blanks to "fill" the columns.

The current engine does not compare LONGVARBINARY. The current engine is able to compare fixed length BINARY data.

Pervasive PSQL now supports multiple LONGVARCHAR and LONGVARBINARY columns per table. The data is stored according to the offset in the variable length portion of the record. The variable length portion of data can vary from the column order of the data depending on how the data is manipulated. Consider the following example.

CREATE TABLE BlobDataTest 
( 
Nbr   UINT,          // Fixed record (Type 14) 
Clob1 LONGVARCHAR,   // Fixed record (Type 21) 
Clob2 LONGVARCHAR,   // Fixed record (Type 21) 
Blob1 LONGVARBINARY, // Fixed record (Type 21) 

)

On disk, the physical record would normally look like this:

[Fixed Data (Nbr, Clob1header, Clob2header, 
Blob1header)][ClobData1][ClobData2][BlobData1]

Now alter column Nbr to a LONGVARCHAR column:

ALTER TABLE BlobDataTest ALTER Nbr LONGVARCHAR

On disk, the physical record now looks like this:

[Fixed Data (Nbrheader, Clob1header, Clob2header, 
Blob1header)][ClobData1][ClobData2][BlobData1]
[NbrClobData]

As you can see, the variable length portion of the data is not in the column order for the existing data.

For newly inserted records, however, the variable length portion of the data is in the column order for the existing data.

[Fixed Data (Nbrheader, Clob1header, Clob2header, 
Blob1header)][NbrClobData][ClobData1][ClobData2]
[BlobData1]

Limitations on LONGVARCHAR and LONGVARBINARY

The following limitations apply to the LONGVARCHAR and LONGVARBINARY data types:

The LIKE predicate operates on the first 65500 characters of the column data.

All other predicates operate on the first 256 characters of the column data.

SELECT statements with GROUP BY, DISTINCT, and ORDER BY return all the data but only order on the first 256 characters of the column data.

In a single call to SQLGetData, the maximum number of characters returned by Pervasive PSQL for a LONGVARCHAR or LONGVARBINARY columns is 65500. Multiple calls must be made to SQLGetData to retrieve column data over 65500 characters.

Though the maximum amount of data that can be inserted into a LONGVARCHAR/LONGVARBINARY column is 2GB, using a literal in an INSERT statement reduces this amount to 15000 characters. You can insert more than 15000 characters by using a parameterized insert.

Comparison of Floats

Pervasive ODBC Engine Interface compares floating point numbers in comparison predicates using an almost equals algorithm. For example, 12.203 = 12.20300000000001, and 12.203 is >= 12.20300000000001. The epsilon value defined as dbl epsilon is (.2204460492503131e-016). This feature works for large numbers, but > and < will not be detected for small numbers; small numbers will be detected as equal.

Note
If you require precision to many decimal places, use the Decimal data type instead of the Real or Float data type.

Here is the comparison routine that Pervasive ODBC Engine Interface uses for the sql_double data type (which maps to the C double type). For the sql_real data type (which maps to the C float type), Pervasive ODBC Engine Interface uses flt_epsilon, which is (.2204460492503131e-016).

SHORT sCnvDblCmp( 
DOUBLE  d1, 
DOUBLE  d2) 
{ 
if (d1 == d2) 
   return 0; 
if (d1 > d2) 
   { 
   if (d1 > d2 + DBL_EPSILON) 
      return(1); 
   } 
else 
   { 
   if (d2 > d1 + DBL_EPSILON) 
      return(-1); 
   } 
return(0); 
}

DATETIME

The DATETIME data type represents a date and time value. The data type is stored internally as two 4-byte integers. The first four bytes store the number of days before or after the base date of January 1, 1900. The other four bytes store the time of day represented as the number of milliseconds after midnight.

The DATETIME data type can be indexed. The accuracy of DATETIME is one three-hundredth of a second.

DATETIME is a relational data type only. No corresponding transactional data type (Btrieve type) is available.

Format of DATETIME

The only permissible format for DATETIME is YYYY-MM-DD HH:MM:SS.mmm. (The CONVERT function contains an optional parameter that allows you to truncate the milliseconds portion of DATETIME. See the "Convert" function under Conversion Functions .)

The following table indicates the data components and their valid values for DATETIME.

Table A-4 DATETIME Components and Valid Values
Component	Valid Values
YEAR (YYYY)	1753 to 9999
MONTH (MM)	01 to 12
DAY (DD)	01 to 31
HOUR (HH)	00 to 23
MINUTE (MM)	00 to 59
SECOND (SS)	00 to 59
MILLISECOND (mmm)	000 to 999

Millisecond Rounding

The following table explains the rounding used for milliseconds.

Table 1-5
Last Digit of Millisecond	Rounding Rule	Example
0, 1	1	2020-01-01 23:59:59.990 and 2020-01-01 23:59:59.991 round to 2020-01-01 23:59:59.991
2, 3, 4	3	2020-01-01 23:59:59.992, 2020-01-01 23:59:59.993, and 2020-01-01 23:59:59.993 round to 2020-01-01 23:59:59.993
5, 6, 7, 8	7	2020-01-01 23:59:59.995, 2020-01-01 23:59:59.996, 2020-01-01 23:59:59.997, and 2020-01-01 23:59:59.998 round to 2020-01-01 23:59:59.997
9	add 1 to last digit	2020-01-01 23:59:59.999 rounds to 2020-01-02 00:00:00.000

Compatibility of Data Types

The following table shows the resultant data type for addition and subtraction operations of DATE, TIME, TIMESTAMP, and DATETIME with other data types. Data types marked with an "X" are incompatible with DATE, TIME, TIMESTAMP, and DATETIME.

Table 1-6 Resultant Data Type for Addition and Subtraction of Operands Involving DATE, DATETIME, TIME, and TIMESTAMP
Operand 2 Æ Operand 1 Ø	DATE	DATETIME	TIMESTAMP
BFLOAT4		DATETIME	TIMESTAMP
BFLOAT8		DATETIME	TIMESTAMP
BIGINT		DATETIME
BINARY
BIT
CHAR
CURRENCY		DATETIME
DATE
DATETIME
DECIMAL		DATETIME
DOUBLE		DATETIME	TIMESTAMP
IDENTITY
INTEGER	DATE	DATETIME	TIMESTAMP
LONGVARBINARY
LONGVARCHAR
MONEY		DATETIME
NUMERIC		DATETIME
NUMERICSA
NUMERICSTS
REAL		DATETIME	TIMESTAMP
SMALLIDENTITY	DATE	DATETIME	TIMESTAMP
SMALLINT	DATE	DATETIME	TIMESTAMP
TIME
TIMESTAMP
TINYINT	DATE	DATETIME	TIMESTAMP
UBIGINT	DATE	DATETIME	TIMESTAMP
UINTEGER	DATE	DATETIME	TIMESTAMP
UNIQUEIDENTIFIER
USMALLINT	DATE	DATETIME	TIMESTAMP
UTINYINT	DATE	DATETIME	TIMESTAMP
VARCHAR

The CONVERT and CAST functions can be used with DATE, DATETIME, TIME, and TIMESTAMP as the following tables explain.

Table 1-7 Permitted CONVERT Operations
CONVERT From	Permitted Resultant Data Type (to)
DATE	DATE, DATETIME, TIMESTAMP, VARCHAR
DATETIME	Any of the supported SQL_xxxx data types except for SQL_GUID, SQL_BINARY, and SQL_LONGVARBINARY
TIME	TIME, DATETIME, TIMESTAMP, VARCHAR
TIMESTAMP	DATE, DATETIME, TIME, TIMESTAMP, VARCHAR
VARCHAR	DATE, DATETIME, TIME, TIMESTAMP

Note
The CONVERT function contains an optional parameter that allows you to truncate the milliseconds portion of DATETIME. See the "Convert" function under Conversion Functions .

Table 1-8 Permitted CAST Operations
CAST From	Permitted Resultant Data Type (to)
DATE	DATE, DATETIME, TIMESTAMP, VARCHAR
DATETIME	Any of the relational data types
TIME	TIME, DATETIME, TIMESTAMP, VARCHAR
TIMESTAMP	DATE, DATETIME, TIME, TIMESTAMP, VARCHAR
VARCHAR	DATE, DATETIME, TIME, TIMESTAMP

UNIQUEIDENTIFIER

The UNIQUEIDENTIFIER data type is a 16-byte binary value known as a globally unique identifier (GUID). A GUID is useful when a row must be unique among other rows.

UNIQUEIDENTIFIER requires a file format of 9.5 or greater .

You can initialize a column or local variable of UNIQUEIDENTIFIER the following ways:

By using the NEWID() scalar function. See NEWID ( ) .

By providing a quoted string in the form 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' in which each x is a hexadecimal digit in the range 0-9 or A-F. For example, '1129619D-772C-AAAB-B221-00FF00FF0099' is a valid UNIQUEIDENTIFIER value.

If you provide a quoted string, all 16 bytes are required. The database engine does not pad a partial string to ensure that it is 16 bytes.

You may use only the following comparison operators with UNIQUEIDENTIFIER values:


Operator	Meaning
=	Equals
<> or !=	Not equal to
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to
IS NULL	the value is NULL
IS NOT NULL	the value is not NULL

Note that ordering is not implemented by comparing the bit patterns of the two values.

Declaring Variables

You may declare variables of the UNIQUEIDENTIFIER data type and set the variable value with the SET statement.

DECLARE :Cust_ID UNIQUEIDENTIFIER DEFAULT NEWID() 
DECLARE :ISO_ID uniqueidentifier 
SET :ISO_ID = '1129619D-772C-AAAB-B221-00FF00FF0099'

Converting UNIQUEIDENTIFIER to Another Data Type

The UNIQUEIDENTIFER can be coverted with the CAST or CONVERT scalar functions to any of the following data types:

CHAR

LONGVARCHAR

VARCHAR

For conversion examples, see Conversion Functions .

Representation of Infinity

When Pervasive PSQL is required by an application to represent infinity, it can do so in either a 4-byte (C float type) or 8-byte (C double type) form, and in either a hexadecimal or character representation, as shown in the following table:

Table A-9 Infinity Representation
Value	Float Hexadecimal	Float Character	Double Hexadecimal	Double Character
Maximum Positive			0x7FEFFFFFFFFFFFFF
Maximum Negative			0xFFEFFFFFFFFFFFFF
Infinity Positive	0x7F800000	1E999	0x7FF0000000000000	1E999
Infinity Negative	0xFF800000	-1E999	0xFFF0000000000000	-1E999