Johnson Zhu wrote:
How are data stored in external file for the data type like smallint, integer, char, varchar, datetime? If we want to generate external file ourselves, what shall we do? Our goal is to generate the file related to the external table file. Any suggestion?
Ann W. Harrison answers:
External tables use the same data types as internal tables. The actual representation of data other than char depends on the endianness and alignment rules of the processor, and on the ODS version of Firebird. These are the rules (I think) for ODS 11. Earlier ODS's used different alignment rules for dates (I think) but the general rules are still true.
Char(n) turns in to characters in the specific character set.
Varchar(n) turns into a two byte integer followed by characters. The two bytes (when viewed in the correct order) indicate the number of significant characters. Varchar fields always start on two-byte boundaries. Thus table t1 (f2 char (5), f2 varchar(10)) will be stored as 'abcde 0x30x0abc' on a little-endian machine.
The various numeric types (decimal, int, etc.) turn into the appropriate sized binary number for the precision of the number. (<5 two bytes, <10 four bytes, <19 eight bytes). The scale of decimal and numeric values is carried in the field definition, not in the store value, so decimal (9,0) value 55, decimal (9,1) value 5.5, and decimal (9,2) value .55 are all stored as the same value. Two byte integers are stored on two byte boundaries. Four byte integers are stored on four byte boundaries. Eight byte integers are stored on eight byte boundaries. To make the boundaries work, Firebird will put in pad bytes which must be ignored.
Floating point numbers are stored in ISO format and aligned on four (for float) or eight (for double) byte boundaries. In earlier ODS's, on some machines, doubles were stored on four byte boundaries.
Dates are stored as a pair of four byte integers, aligned (I think) on four byte boundaries.
Remember that date, floating point, and integer values use the processor rules for endianness.
Blobs don't work in external files. When written to an external file a blob turns into a meaningless eight byte value.
Unlike internal tables, external tables do not have a built-in representation of null. Internal table include a bit array for each record indicating the null state for each field.
As Alexandre said, the easiest way to deal with external files is to turn everything into character strings. They're transportable, you can read and edit them, and they don't have pad characters. Remember to add a two character field at the end of each record for an end of line.