sp_ModifyTableProperty

Advantage SQL Engine

  Previous topic Next topic  

Sets one table property in the data dictionary.

Syntax

sp_ModifyTableProperty(

TableName,CHARACTER,200,

Property,CHARACTER,200,

Value,MEMO,

ValidationOption,CHARACTER,25,

FailTable,CHARACTER,515 )

sp_ModifyTableProperty(

TableName,CHARACTER,200,

Property,CHARACTER,200,

Value,NMEMO,

ValidationOption,CHARACTER,25,

FailTable,CHARACTER,515 )

Parameters

TableName (I)

Name of the table in the database to set the property.

Property (I)

Name of table property to set. See Remarks for possible values.

Value (I)

Value to store in the data dictionary.

ValidationOption (I)

If the property is a constraint on records in the table, the ValidationOption parameter specifies the type of validation to perform on existing records in the table. See Remarks for possible values.

FailTable (I)

If the property specified is a constraint on records in the table, and validation is to be performed on existing records in the table, this parameter specifies the name of the file to save the records that are deleted from the table due to the new constraint. If this parameter is NULL, the records will be saved in a table named "savefail.adt" in the same directory as the table. If a path is specified, it must be a fully qualified UNC path or a relative path from the data dictionary.

Special Return Codes

AE_INVALID_PROPERTY_ID

Either Property is not a valid table property index, or the specified property cannot be modified.

AE_INVALID_OBJECT_NAME

The table specified by TableName cannot be located in the data dictionary.

Remarks

sp_ModifyTableProperty sets one property for the specified table in the database. The new property overwrites the existing property in the data dictionary. The following are the valid property names.

Property

Description

COMMENT

Changes the table description.

TABLE_VALIDATION_EXPR

Sets a new record level validation expression for the table. To remove an existing record level validation expression for the table, use NULL or an empty string as the value. The ValidationOption can be used to indicate how the existing records in the table should be validated. Setting this property requires the table to be opened and validated.

TABLE_VALIDATION_MSG

Sets an error message that will be returned when a record in the table is being modified in such a manner that the record will fail to pass the record level constraint.

TABLE_PRIMARY_KEY

Sets the table’s primary key. Value is the name of one of the indexes associated with the table. To remove an existing primary key for the table, use NULL or an empty string. The primary key of the table can be used as the default ordering of the records in the table or used to support referential integrity within the database.

TABLE_ENCRYPTION

Encrypt/decrypt the database table. Pass in ‘TRUE’ to encrypt the table and ‘FALSE’ to decrypt the table. If the table is an ADT table, the field header of the table will also be encrypted.

TABLE_DEFAULT_INDEX

Sets the default index to be used by the table. For optimized performance the default index is initially not set, indicating no default index should be used. The Advantage Client Engine does not use the default index directly. Client front-ends must implement a call to AdsDDGetTableProperty to retrieve the index name, and then take the appropriate actions to implement default index functionality.

TABLE_PERMISSION_LEVEL

Sets the table permission verification level. The table permission level determines how column permissions are enforced on this table. The value is expected to be an integer representing the permission level.

Permission Level 1: If the user does not have read permission to a column in the table, reading the value of the column by the user will not cause an error. Instead, a NULL value is returned on the column that the user does not have read permission to. Filtering the table (using a scope, filter, or SQL WHERE clause) on a column that the user does not have permission to is allowed. This permission level is most compatible with other shared file access databases such as Paradox. Pass in ‘1’ as the value to the system procedure.

Permission Level 2: This is the default table permission level in Advantage. Reading of a column in the table that the user does not have read permission to does not cause an error condition. Instead, a NULL value is always returned on the column that user does not have read permission to. However, filtering a table (using a scope, filter, or SQL WHERE clause) on the column that the user does not have permission to will return an error. Pass in ‘2’ as the value to the system procedure.

Permission Level 3: This is the most restrictive table permission level. Direct access to the table is not allowed. The table can only be accessed using SQL statements. If the SQL statement contains a column that the user does not have the proper access to, an error will be returned. In other words, the user must have read permission to all columns in the SELECT, WHERE, HAVING, and ORDER BY clauses of an SQL statement. This level is most compatible with other client server database such as MS SQL Server. Pass in ‘3’ as the value to the system procedure.

TABLE_AUTO_CREATE

Changes whether or not the specified table and/or any associated indexes are automatically created. If the value is True and the table and/or associated indexes do not exist when the table is opened, the missing files will be automatically created. This property is False by default (turned off).

TABLE_MEMO_BLOCK_SIZE

Sets the size of memo blocks that auto-creation functionality should use when re-creating a missing table and its associated memo file. Passing in NULL will unset this property and Advantage will auto-create the table using the default memo block size for this particular table type.

TABLE_TXN_FREE

Changes whether or not the specified table is treated as a transaction-free table.  If the value is True, all updates to the table when a transaction is active will be excluded from the scope of the transaction.  This property is False by default.

 

TABLE_CACHING

This property is used to enable or disable caching of table data in the caching system.  Please read Table Data Caching before enabling this feature.  Value is expected to be 1 (CACHE_READS), 2 (CACHE_WRITES), or 0 (CACHE_NONE).

 

 

The ValidateOption parameter is used only when Property is TABLE_VALIDATION_EXPR. It specifies how the function should validate existing records in the table. Once the record level constraint is successfully set for the database table, the Advantage Database Server will always validate any new records added to the table and any modification to existing records in the table. The following are the allowed values for ValidateOption.

 

ValidateOption

Description

NO_VALIDATE

Do not perform any validation on existing records in the table. Only verify that the record level constraint expression is valid. CAUTION: Using this option can logically corrupt tables and is strongly discouraged. If an index is available on the field in question, validating constraints is a quick operation and should always be performed.

APPEND_FAIL

Perform validation on existing records in the table. Records failing to meet the new constraint will be permanently deleted from the table and appended to the table specified by FailTable or to the default fail table if FailTable is NULL.

WRITE_FAIL

Perform validation on existing records in the table. Records failing to meet the new constraint will be permanently deleted from the table. The fail table will be created to save all records that are removed from the original table.

NO_SAVE

Perform validation on existing records in the table. Records failing to meet the new constraint will be permanently deleted from the table. The deleted records are not saved and will be lost.

RETURN_ERROR

Perform validation on existing records in the table. If there are existing records in the table not meeting the new constraint, an error will be returned by the function. The table will not be modified. If the error is returned the new constraint will not be set.

Example

After making a connection to the database, change the record level constraint of the "Customer Information" table. The records not meeting the new record level constraint are saved in "deleted.adt" in the same directory as the data dictionary.

EXECUTE PROCEDURE sp_ModifyTableProperty(

‘Customer Information’,

‘TABLE_VALIDATION_EXPR’,

‘Not Empty( Customer ID )’,

‘VALIDATE_WRITE_FAIL’,

‘deleted.adt’ );

See Also

system.tables

sp_ModifyFieldProperty