Tuesday, July 7, 2009

Tip 42 – Adding Tables to a Dictionary

You can add an existing table to a data dictionary using the sp_AddTableToDatabase system procedure or using the AdsDDAddTable API call.

To use the sp_AddTableToDatabase you must specify the following; Table Name, Table Path, Table Type, Character Type, Index File(s) and Comments. The table path can be a UNC path or relative path to the dictionary, however, the table must be on the same drive as the database otherwise a 7041 error will occur. The table type is specified as an integer value: 1 DBF/NTX, 2 DBF/CDX, 3 ADT and 4 VFP. Likewise the character types are 1 ANSI and 2 OEM, however this setting will be overridden if a dynamic collation has been specified.

The following statement adds the Department table to the data dictionary specified on the current connection.

EXECUTE PROCEDURE sp_AddTableToDatabase( 'Department',  'C:\Data\Department.adt', 3, 1, NULL, 'Test of sp_AddTableToDatabase' )

The AdsDDAddTable API takes a connection handle to a data dictionary and then the same parameters as the system procedure.

   1: // Connect to the dictionary
   2: ADSHANDLE hConn;
   3: AdsConnect60( "C:\\Data\SampleDB\\SampleDB.ADD", ADS_REMOTE_SERVER, "ADSSYS",
   4:               NULL, ADS_DEFAULT, &hConn );
   6: // Add the department table
   7: AdsDDAddTable( hConn, "Department", "C:\\Data\\Department.adt", ADS_ADT,
   8:                ADS_ANSI, NULL, "Test of sp_AddTableToDatabase" );
  10: // Disconnect
  11: AdsDisconnect( hConn );
You must connect to the data dictionary with a user who has the right to Create tables in the dictionary in order to use either of these functions.

No comments: