c-treeDB’s alter table function can be used to alter the schema of an existing table by adding new fields or modifying existing fields of the specified table. During an alter table operation, when a new field is added to the table, or when an existing field type is changed, an optional default field value can be specified for these fields.
The default value of a field is used during an alter table operation when a full table rebuild is performed. During a full alter table rebuild, and after the old record buffer data is moved to the new record buffer, the new record buffer is scanned and, if a NULL field is found and that NULL field has a default value, the default value is copied to the field buffer. Typically the default field value is applied for new fields added to the table and to existing fields that have their types changed and the field value is NULL.
The field default value is kept as a string representation of the data. It is recommended that numeric data should be converted to string using one of the rich set of c-treeDB data conversion functions. Binary data can also be used by passing the pointer to data and the appropriate length.
The default value is set by calling the CTField::SetFieldDefaultValue() method.
Example:
// set the default value of country field */
try
{
CTField hField = hTable.GetField("country"));
hField.SetFieldDefaultValue("USA");
}
catch (CTException &err)
{
printf("SetFieldDefaultValue failed\n");
}
Use GetDefaultfieldValue() to retrieve the current field default value.
Example
// check if default field value is 'USA'
try
{
CTString value;
CTField hField = hTable.GetFielld("country");
hField = ctdbGetField(hTable, 5);
if (hField.GetFieldDefaultValue(value) > 0)
{
if (value == "USA")
printf("Default value is USA\n");
else
printf("Default value is not USA\n");
}
else
printf("No default value set\n");
}
catch (CTException &err)
{
printf("GetFieldDefaultValue failed\n");
}
You can check if a default value is set by calling the IsFieldDefaultValueSet() method.
Example
// check if default field value is set
CTField hField = hTable.GetField("country");
if (hField.IsFieldDefaultValueSet())
printf("Default field value is set\n");
else
printf("No default field value\n");
Once set, a default field value will remain in place until the table handle is closed. The ClearFieldDefaultValue() method clears the default value associated with a field. The default date and time types are also reset to their default values of CTDATE_MDCY and CTTIME_HMS respectively.
Example
// clear the default field value
try
{
CTField hField = hTable.GetField("country");
hField.ClearFieldDefaultValue();
}
catch (CTException &err)
{
printf("ClearFieldDefaultValue failed\n");
}
You can clear the default values for all fields in a table by calling the ClearAllFieldDefaultValue() method.
Example
// clear all default field values
try
{
hTable.ClearAllFieldDefaultValue();
}
catch (CTException &err)
{
printf("ClearAllFieldDefaultValue failed\n");
}
The default date and time types used for conversions to and from strings can be changed by calling the SetFieldDefaultDateTimeType() method.
When setting the default field values with date, time or timestamp data, the data must be first converted to string. By default the date type is CTDATE_MDCY while the default time type is CTTIME_HMS.
The possible date formats for string conversion are:
c-treeDB |
c-treeDB .NET |
|
CTDATE_MDCY |
MDCY_DATE |
Date is mm/dd/ccyy |
CTDATE_MDY |
MDY_DATE |
Date is mm/dd/yy |
CTDATE_DMCY |
DMCY_DATE |
Date is dd/mm/ccyy |
CTDATE_DMY |
DMY_DATE |
Date is dd/mm/yy |
CTDATE_CYMD |
CYMD_DATE |
Date is ccyymmdd |
CTDATE_YMD |
YMD_DATE |
Date is yymmdd |
Time Types can be one of the following string time formats:
c-treeDB |
c-treeDB .NET |
|
CTTIME_HMSP |
HMSP_TIME |
Time is hh:mm:ss am|pm |
CTTIME_HMP |
HMP_TIME |
Time is hh:mm am|pm |
CTTIME_HMS |
HMS_TIME |
Time is hh:mm:ss (24 hour) |
CTTIME_HM |
HM_TIME |
Time is hh:mm (24 hour) |
CTTIME_MIL |
MIL_TIME |
Time is hhmm (military) |
CTTIME_HHMST |
|
Time is hh:mm:ss.ttt (24 hour) |
Example
// set the field default date and time types
try
{
CTField hField = hTable.GetField("country");
hField.SetFieldDefaultDateTimeType();
}
catch (CTException &err)
{
printf("SetFieldDefaultDateTimeType failed\n");
}
The default date type value can be retrieved by calling the GetFieldDefaultDateType() method.
Example
// check the default date type
try
{
CTField hField = hTable.GetField("country");
if (hField.GetFieldDefaultDateType() == CTDATE_MDCY)
printf("Field default date type is OK\n");
}
catch (CTException &err)
{
printf("GetFieldDefaultDateType() failed\n");
}
The default time type value can be retrieved by calling the GetFieldDefaultTimeType() method.
Example
// check the default date type
try
{
CTField hField = hTable.GetField("country");
if (hField.GetFieldDefaultTimeType() == CTTIME_HMS)
printf("Field default time type is OK\n");
}
catch (CTException &err)
{
printf("GetFieldDefaultTimeType() failed\n");
}