KNOWLEDGE BASE
Log In    |    Knowledge Base    |    4D Home
Tech Tip: 4D Data Types in SQL System Tables
PRODUCT: 4D Developer | VERSION: 11.1 | PLATFORM: Mac & Win
Published On: March 12, 2008
Your current browser may not allow you to download the asset. We recommend Mozilla Firefox or Google Chrome.
Log In

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