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>'

See also Limitations on LONGVARCHAR and LONGVARBINARY .

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.SQL 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] 

See also Limitations on LONGVARCHAR and LONGVARBINARY .

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.SQL 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); 
} 

Representation of Infinity

When Pervasive.SQL 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-3 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