Tech Tip: Create a field in a table dynamically with SQL
PRODUCT: 4D | VERSION: 14.x | PLATFORM: Mac & Win
Published On: August 21, 2015
Here is a method to create a field dynamically in a table already created:
// ------------------------------------------------------------------------ // Name: CREATE_FIELD_IN_TABLE // Description: Method will create a field in an exsisting table. // // Parameters: // $1 (Pointer) - Table pointer // $2 (TEXT) - Name of the field // $3 (LONGINT) - Data type: 1-Alpha, 2-Text, 3-Date, 4-Time, 5-Boolean, // 6-Integer 16, 7-Integer 32, 8-Integer 64, 9-Real, 10-Float // 11-Blob, 12-Picture // ------------------------------------------------------------------------ C_POINTER($1;$tbl_pointer) C_TEXT($2;$field_name;$execute_text;$data_type_choice) C_LONGINT($3;$data_type) If (Count parameters=3) $tbl_pointer:=$1 $field_name:=$2 $data_type:=$3 Case of : ($data_type=1) $data_type_choice:=" UUID;" // Alpha : ($data_type=2) $data_type_choice:=" TEXT;" // Text : ($data_type=3) $data_type_choice:=" TIMESTAMP;" // Date : ($data_type=4) $data_type_choice:=" DURATION;" // Time : ($data_type=5) $data_type_choice:=" BOOLEAN;" // Boolean : ($data_type=6) $data_type_choice:=" INT16;" // Integer 16 : ($data_type=7) $data_type_choice:=" INT;" // Integer 32 : ($data_type=8) $data_type_choice:=" INT64;" // Integer 64 : ($data_type=9) $data_type_choice:=" REAL;" // Real : ($data_type=10) $data_type_choice:=" FLOAT;" // Float : ($data_type=11) $data_type_choice:=" BLOB;" // Blob : ($data_type=12) $data_type_choice:=" PICTURE;" // Picture Else $data_type_choice:=" TEXT;" End case $execute_text:="ALTER TABLE "+Table name($tbl_pointer)+"\nAdd "+$field_name+$data_type_choice Begin SQL EXECUTE IMMEDIATE: $execute_text End SQL end if |
Here is a sample code of create a text field using the CREATE_FIELD_IN_TABLE method:
CREATE_FIELD_IN_TABLE (->[Table_1];"Field_Text";2) |
Table that has the field created by the method in red:
![](https://kb.4d.com:443/kbase/assets/images/77350-1.jpg)