Tip #47 – Getting a List of Tables on a Connection

There are several ways to list the tables (files) on an Advantage Connection. When connected to a data dictionary you can use system.tables which returns a list of all the tables contained within the database. You can also use regular file operations to get the contents of the data folder. However, your user may not have rights to the directory where the data resides so a file operation may fail.

The AdsConnection object has a method GetTableNames which will return a string array of all the table names available on the current connection. This list will be returned regardless of the permissions that the user has to the directory. In fact it will even work when the user has no access to the folder at all.

When using the GetTableName function on a free table connection you can filter the results based on the file extension. You simply provide a mask of the file types you wish to display. For instance if you only wanted to see the DBFs available on the connection you would provide the "*.dbf" mask ( ex: cnAds.GetTableNames( "*.dbf" ) ). The default is to provide all of the table names for the table type specified in the connection string, ADT is the default table type.

When using GetTableNames on a dictionary connection you can see all of the tables in a linked dictionary by providing a Boolean value ( ex: cnAds.GetTableNames( true );  ). Linked tables will be shown in the following format <LinkName>::<TableName>. Linked tables are not listed by default.

Tip #46 – Getting Server Information from an AdsConnection Object

The AdsConnection object in the Advantage .NET Data Provider has many properties which provide information about the server it is connected to. With an active connection you can get the following information: Server Name, Server Type, Server Version, Server Time, Connection Handle, Connection Timeout and Date Format.

These properties can be easily accessed once the connection is opened and used within your application. For example you may wish to have your application verify that a specific version of Advantage is running you could do this with the following code.

   1: // Build the connection string

   2: string sConn = "Data Source=C:\data;ServerType=Remote";

   3:  

   4: // Connect to Advantage

   5: AdsConnection cnADS = new AdsConnection( sConn );

   6: cnAds.Open();

   7:  

   8: // Compare the server version

   9: string sVersion = "9.10.0.9";

  10: if ( sVersion.CompareTo(cnAds.ServerVersion) != 0 )

  11:   MessageBox.Show( "You must have Advantage version " + sVersion, "Invalid Version");

There is a sample application posted on CodeCentral which demonstrates retrieving all of the server properties through the AdsConnection object. The server information is shown below:

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.

Tip #44 – Altering Table Structure

Tables can be altered using the ALTER TABLE statement. This is a very powerful SQL statement because it allows for changing so many things about the table. You can ADD, ALTER and DROP columns (fields) and constraints defined in the table. Another overlooked feature of this command is the ability to change the position of various fields in the table.

The following statement alters the original structure of the Department table pictured below:

 

ALTER TABLE Department
  ALTER COLUMN Name FullName char(50) CONSTRAINT NOT NULL
  ALTER COLUMN Budget Budget money CONSTRAINT NOT NULL CONSTRAINT MINIMUM '0'
  ALTER COLUMN Administrator Administrator integer POSITION 4

The altered table structure is shown below. Notice that the Name field is now FullName, the Budget field has a Minimum Value and NULL Valid is now No. Finally the Administrator and StartDate fields have changed positions.

Tip 43 – Removing Tables from a Dictionary

There are several ways to remove a table from a data dictionary. Tables can be removed using the DROP TABLE SQL statement and using the AdsDDRemoveTable API.

The DROP TABLE statement removes the table from the dictionary and deletes all of the table files, including memos and indexes. You can use the FROM DATABASE combined with the NO_DELETE option to remove the table from the data dictionary without deleting the files. This is the default behavior in ARC.

   1: // Remove the table from the dictionary and delete the files

   2: DROP TABLE Person

   3:  

   4: // Remove the table from the dictionary but keep the files

   5: DROP TABLE Person FROM DATABASE NO_DELETE

The AdsDDRemoveTable API works in much the same way. There are three parameters; Connection Handle, Table Name and a delete files flag.

   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: // Remove the department table and its files

   7: AdsDDRemoveTable( hConn, "Department", TRUE )

   8:  

   9: // Disconnect

  10: AdsDisconnect( hConn );

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

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 );

   5:  

   6: // Add the department table

   7: AdsDDAddTable( hConn, "Department", "C:\\Data\\Department.adt", ADS_ADT,

   8:                ADS_ANSI, NULL, "Test of sp_AddTableToDatabase" );

   9:  

  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.

Tip 41 – Creating Tables with SQL

There are several two ways to create tables using SQL commands. The CREATE TABLE statement is the most commonly used You can also create a table based on one or more tables using the SELECT … INTO syntax.

The CREATE TABLE statement specifies a table name and the columns (fields) to be added. There are also may additional options depending on whether or not you are connected to a data dictionary. When creating a table in a dictionary you can specify table and column constraints. If you are creating a Visual FoxPro 9 (VFP) table type you can specify an additional vfp-option. These include NULL, NOT NULL and NOCPTTRANS. See the examples below:

   1: // Create a table with five fields

   2: CREATE TABLE Person

   3:   ( PersonID autoinc,

   4:     LastName char(50),

   5:     FirstName char(50),

   6:     HireDate date ,

   7:     EnrollmentDate date);

   8:  

   9: // Create a dictionary bound table with constraints

  10: CREATE TABLE Department

  11:   ( DepartmentID integer PRIMARY KEY CONSTRAINT NOT NULL,

  12:     Name char(50) CONSTRAINT NOT NULL,

  13:     Budget money CONSTRAINT NOT NULL CONSTRAINT MINIMUM '0',

  14:     StartDate date CONSTRAINT NOT NULL,

  15:     Administrator integer);

The SELECT … INTO syntax creates a table based on the field specified in the field list. You can use this to create a new table based on one or more tables. You can also use any views you have defined in your dictionary to create the new table. This can be very useful for creating tables filtered based on specific criteria or creating a table with a specific order.

   1: SELECT c.CompanyName,  COUNT(i.OrderID) AS TotalOrders, 

   2:        SUM(i.SubTotal) AS TotalSales 

   3:   FROM CustomerDetails c LEFT OUTER JOIN Invoice i 

   4:   ON c.CustID = i.CustID

   5:   WHERE OrderDate BETWEEN '2009-04-01' AND '2009-06-30'

   6:   GROUP BY c.CompanyName

   7:   ORDER BY TotalSales DESC

It is important to note that a SELECT … INTO statement will always create a free table. The CREATE TABLE statement will create the table in the dictionary, if the user has the proper permissions, on a dictionary connection by default. You can create a free table by adding the AS FREE TABLE option to the end of your CREATE TABLE statement when connected to a dictionary.