Tip #45 – Modifying Table Properties

A dictionary bound table has many additional properties that can be configured. These include:  Auto Create, Primary Key, Default Index, Permission Level, Memo Block Size and Validation Expression. These properties can be set using either the sp_ModifyTableProperty system procedure or AdsDDSetTableProperty API call.

The sp_ModifyTableProperty takes five parameters: Table Name, Property, Value, Validation Option and Fail Table. I recommend using the RETURN_ERROR validation option, this ensures that you do not loose any data if the modification fails. You can also use APPEND_FAIL which will write any records that do not meet the new criteria to the specified fail table. This option is useful when modifying the validation expression.

One of the most useful table properties is the Auto Create option. When set to true the table will be created when it is first opened if it does not exist. This can make deploying an empty data dictionary very simple since you only have to send the dictionary files. The auto create option also creates all indexes associated with the table. This can be very handy if you somehow loose your CDX or ADI file.

The following statement sets the auto create flag to true for the department table. An example SQL script which will change the auto create flag on all the tables is included in this tech-tip.

EXECUTE PROCEDURE sp_ModifyTableProperty( 'Department', 'TABLE_AUTO_CREATE', 'TRUE', 'RETURN_ERROR', '' )

The AdsDDSetTableProperty API call requires the following parameters: Connection Handle, Table Name, Property ID, Property Value, Property Length, Validate Option and Fail Table. Although similar to the sp_ModifyTableProperty call there are two additional parameters a connection handle and a property length. The property length is the length of the property value to assign.

The following code sets the auto create property to true.

   1: // Connect to the dictionary

   2: ADSHANDLE hConn;

   3: AdsConnect60( "C:\\Data\SampleDB\\SampleDB.ADD", ADS_REMOTE_SERVER, "ADSSYS",

   4:               NULL, ADS_DEFAULT, &hConn );

   5:  

   6: // Set the auto create property to true

   7: AdsDDSetTableProperty( hConn, "Department", ADS_DD_TABLE_AUTO_CREATE, "TRUE", 

   8:                        5, ADS_VALIDATE_RETURN_ERROR, NULL ); 

   9:  

  10: // Disconnect

  11: AdsDisconnect( hConn );

You must connect to the data dictionary with a user who has the right to alter tables in the dictionary in order to use either of these functions.