Monday, February 15, 2010

Copying Tables

There are a few ways to quickly copy tables using Advantage. You can copy the contents of one table to another table using SQL or using the Advantage API. These methods are very efficient since the operations are performed entirely on the server. You can use either method to create a new table and copy the data. You can also copy data from one table to another with or without a filter.

SQL is probably the simplest way to copy the contents of one table to another. There is no real programming involved you can simply use the SQL window in Advantage Data Architect (ARC). The following examples demonstrate copying the contents of a table to a new table and an existing table.

// Copy the contents from a table into a new table 
SELECT * INTO Employee_b FROM Employee
// Copy the contents from a table into an existing table
INSERT INTO Employee_c SELECT * FROM Employee
// Copy a portion of the records using a WHERE clause
SELECT * INTO Finance_Employees FROM Employee
  WHERE Department = 102
INSERT INTO Finance_Employees SELECT * FROM Employee
  WHERE Department = 102

There are two Advantage API calls that copy tables; AdsCopyTable and AdsCopyTableContents. AdsCopyTable creates a new table with the same structure and data as the original. AdsCopyTableContents copies the records from the first table to another table with the same structure.

You can limit the records that are copied by specifying a filter, scope or both on the table to be copied. You must then set the filter option to respect filters or respect scopes. ADS_IGNOREFILTERS ignores all filters and scopes, ADS_RESPECTSCOPES respects scopes and ignores filters and ADS_RESPECTFILTERS respects filters and scopes.

The following code is a C# example of using these functions. There are C examples and Delphi examples available in the help file. I left out the error checking for brevity, however, you should always check the return value of any ACE call. To get access to the ACE namespace add using AdvantageClientEngine at the top of your cs file.

uint lRetVal = ACE.AE_SUCCESS;
IntPtr hConnect;
IntPtr hSourceTable;
IntPtr hDestinationTable;
// 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)
    // Error occurred
    // use AdsGetLastError to retrieve the error message
// Open the source table
lRetVal = ACE.AdsOpenTable(hConnect, "C:\\Data\\Table1.adt", "", ACE.ADS_ADT, ACE.ADS_ANSI,
                           ACE.ADS_DEFAULT, out hSourceTable);
// Create a new table with the same structure as the source table
lRetVal = ACE.AdsCopyTable(hSourceTable, ACE.ADS_RESPECTFILTERS, "C:\\Data\\Backup\\TableCopy.adt");
// Open the destination table
lRetVal = ACE.AdsOpenTable(hConnect, "C:\\Data\Backup\\Table2", "", ACE.ADS_ADT, ACE.ADS_ANSI,
                           ACE.ADS_DEFAULT, out hDestinationTable);
// Copy the contents from source to destination
lRetVal = ACE.AdsCopyTableContents(hSourceTable, hDestinationTable, ACE.ADS_RESPECTFILTERS);

Here is a simple function that will return the error message for a given error code.

private string GetAdsError(uint uiErrCode)
    char[] ucBuf;
    ushort usBufLen = ACE.ADS_MAX_ERROR_LEN;
    ucBuf = new char[usBufLen];
    ACE.AdsGetErrorString(uiErrCode, ucBuf, ref usBufLen);
    return new string(ucBuf, 0, (int)usBufLen);


Unknown said...

What's the most efficient way to copy an entire database?

For example, I want to copy a client's entire database and take back to my office server, for testing/development.

In the past, I've tried backup and restore with varying result. Seemed like I always had to jump through hoops.


Chris Franz said...

The most efficient way to copy a database is to copy it with Windows Explorer or my personal favorite Robocopy. This only works when the database is not in use though.
The online backup will copy the entire database when it is in use. Then you can restore it when you get back to your development environment.
If you are having trouble with the online backup/restore process I would like to discuss the problems you experienced. Please send the details to Advantage support and I would be happy to assist you.

Unknown said...

Using xHarbour, ADS 10.1

I have an SQL Cursor from server connection.. I issue AdsCopyTable to 'D:', this works perfectly.

But if I destination to 'C:', it doesn't copy.

All partition are local drives to client.

I hope you can help me out.