The SQL catalogue of 4D includes six system tables, which can be accessed by any SQL user having read access rights. The table most relevant to this Tech Tip is the _USER_COLUMNS table. This table contains information regarding the fields in a 4D database. It can be accessed within 4D with one SQL statement:

Begin SQL       SELECT * FROM _USER_COLUMNS INTO <<ListBox1>> End SQL

In this case the data is returned into a List Box object to be displayed. Two particular columns from that table are of interest in this tech tip: the DATA_TYPE and DATA_LENGTH columns. Each of 4D's possible data types correspond to an integer value stored in the DATA_TYPE column, and the DATA_LENGTH column is particularly important for Alpha fields, which offer variable sizes. The data types are as follows:

Field type in 4D	DATA_TYPE
Boolean	1
Integer	3
Long Integer	4
Integer 64 Bits	5
Real	6
Float	7
Date	8
Time	9
Alpha	10 *
Text	10 *
Picture	12
BLOB	18

*These two 4D data types have the same value. To differentiate them the DATA_LENGTH column can be used. The data length for an Alpha field is ((alpha field size)*(2))+4. The data length for a Text field is 0.

For more information on the System Tables see the "4D v11 SQL Reference", in the "Principles for Integrating 4D and the 4D SQL engine" section. The documentation can be found at:

http://www.4d.com/support/documentation.html