Wednesday, February 17, 2010

Copying Tables (Part Deux)

In Copying Tables I discussed the different ways you can copy data from one table to another table using both SQL and the Advantage API. In this post I am going to highlight some differences between the two approaches.

First I want to mention one API I did not discuss in my first post was AdsCopyTableStructure. This function creates a new structure with the same structure as the given table. No data is copied from the original table with this function. You can accomplish the same thing with an SELECT INTO statement by setting a filter condition that returns no records.

Now back to the main topic of this post, the different functionality of the API and SQL. SQL offers some more flexibility because it allows for creating a new table with a different structure than the original table. This is done by specifying columns in the Select List of a SELECT . . . INTO statement. The new table will be created with the specified columns in the specified order. This allows you to reorganize your columns or simply copy some of the columns from a table.

You can also combine the results from several tables into a single table, think views. You can use all of your normal SQL syntax to create joins and have the results dumped into a physical table. This can be done "on the fly". You can get similar functionality with the API by using a view handle instead of a table handle. Additionally SQL allows you to create temporary tables whereas the API only creates physical tables.

// Create Table with records in a specific order
SELECT * INTO EmployeeByHireDate FROM Employee
// Create a table using a joined resultset
SELECT c.CustID, c.CustNum, c.LastName, c.FirstName, c.CustomerSince, 
       cm.Name as CompanyName, cm.SalesRep INTO CustomerList
FROM Customer c INNER JOIN Company cm ON c.CompanyID = cm.CompanyID
// Create a temporary table based  on a physical table 
SELECT * INTO #Dept102 FROM Employee
WHERE Department = 102

The ACE API includes one function that cannot be done with an SQL command, converting a table. AdsConvertTable converts the specified table between the supported Advantage formats: DBF/NTX, DBF/CDX, DBF/VFP and ADT. Like the other table copying APIs AdsConvertTable can respect or ignore filters. Allowing you to create a new table in the specified format with a portion of the records from the source table.

I use this function a lot when re-creating issues sent in by customers. I usually have data that I can use to test but often it is in ADT tables. By using AdsConvertTable I can quickly convert the data to DBF or VFP tables to help resolve the issue. As with the other API calls this function is fairly straightforward.

uint lRetVal = ACE.AE_SUCCESS;
IntPtr hConnect;
IntPtr hTable;
// connect to a directory of free tables
lRetVal = ACE.AdsConnect60("C:\\Data", ACE.ADS_REMOTE_SERVER, "", "",
                            ACE.ADS_DEFAULT, hConnect);
if (lRetVal != ACE.AE_SUCCESS)
    // Handle error here
// Open the table to be converted
lRetVal = ACE.AdsOpenTable(cnADS.ConnectionHandle, "C:\\Data\\MyTable.ADT",
                           sCurrentTable, ACE.ADS_ADT, ACE.ADS_ANSI,
                           ACE.ADS_DEFAULT, out hTable);
if (lRetVal != ACE.AE_SUCCESS)
    // Handle error
// Convert the table to a VFP table
lRetVal = ACE.AdsConvertTable(hTable, ACE.ADS_IGNOREFILTERS, 
                              "C:\\Data\\MyTable.DBF", ACE.ADS_VFP);
if (lRetVal != ACE.AE_SUCCESS)
    // Handle error

Although the sample code above is in C# the ACE API can be accessed from virtually every Advantage client. In fact, you can call AdsCopyTable, AdsCopyTableStructure, AdsCopyTableContents and AdsConvertTable directly from a TAdsTable component in Delphi. There is also a TAdsBatchMove component which can be used when moving large amounts of records between tables.

No comments: