OLE DB: How to copy one DBF table into another with the same structure effectively?

I've seen this Q before, and I think it must be a difficult one.

Has anyone suggested the simple expedient of just copying the DBF file (using, for instance SHFileOperation()
    SHFileOperation Function
    http://msdn.microsoft.com/en-us/library/bb762164(VS.85).aspx
(or other simple file duplication tool) and then open that copy as a separate database and delete all of the data (leaving the schema intact)? It might be simpler than any other sequence of operations.
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Another variation of the same idea: It may be possible to backup, then restore the database specifying a different filename (thus duplicating it). See:
    How to: Restore a Database to a New Location and Name (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms190447.aspx
for the SQL Server version of that. See also:
    Copying Databases with Backup and Restore
    http://msdn.microsoft.com/en-us/library/ms190436.aspx
(again for MsSqlServer, but similar steps might be applicable for your situation).

Dan, the copying of a sample database table is only a simplification. The goal is to store the result of more complex SELECT command. It should behave as SELECT INTO, but the destination table is in another format (extracting some data from Jet database and saving the result in free DBF table for importing into legacy application).

It seems that the last step that I need to implement (the core of the question) a dynamically allocated buffer that is to be bound by two accessors (dynamically). One accessor is expected to fill the buffer from the SELECT result, the other accessor is expected to be used for insertion of the data into the destination table.  The problem is that the destination table may not support exactly the same data type of a column.  But if I recall correctly, some transformations can be done by the accessor.

The problem is not related to Microsoft SQL server at all. Although later the result could be stored also in MS SQL server database. But this does not change the problem that I need to solve now.

Thanks,
    Petr

Sorry. Please ignore the second part of the original question (copy/paste error).

It seems to me like you just need to write some code that moves the data from the SELECT (source) recordset into the recordset for the INSERT. (target)  Unless you are trying to do something that is general purpose (i.e., work for any number of fields containing any type of data in any size table), it seems pretty straight forward.
Open source recordset (SELECT, read-only)
Open target recordset for update/insert
while ( ! src.eof()  )  {
    copy data from src to target, converting as needed
    insert the target
    src.MoveNext();
}
I use MsSqlServer the most, so I think in terms of its features and capabilities.  I've found that it will generally convert incoming textual data into the field in the target table automatically.  For instance, if you use a command like:
INSERT INTO destTbl (sName, tDate, nNumber)
   VALUES ("John", "2008-09-20",  "123456" )

Then SQL Server vill convert the second value to a timestamp, and the third to an integer (if that's what the target table's data types are).
-==--=-==--==--=--==--=-==--==--=-=
As an EE PageEditor, I can modify your question text:  Shall I truncate it after the first "Thanks for your help,  Petr"?

> Shall I truncate it after the first "Thanks for your help,  Petr"?

Yes, please. And thanks. Can I do it myself for my questions, somehow?


Regarding the problem... Yes, the code must be general. The SELECT command, source connection string, destination connection string, and some other necessary information are to  be read from a text file. This way, nothing can be hardwired in the utility code. I will harder this week and present the solution if I am able to solve it.

Just neet to study the documentation and think more about it...

Thanks,
   Petr

See the related 500 pts question http:Q_23678466.html "OLE DB: Can one buffer be shared by src and dest tables? (CManualAccessor)"

Well, here is a full sample with statically allocated buffer, with source table consisting of more integer columns. The destination table with a different structure is explicitly created as empty, and the result of a SELECT command is copied from source to destination table using the shared buffer.

Any critique, pointing to a possible flaw, enhancement suggestions,... are welcome.

// $Id$
 
/* Creating srcTable with more columns of simple types, filling with values
   and later copying to dstTable using CComand SELECT. The shared buffer 
   allocated on the stack.
*/
 
#define WIN32_LEAN_AND_MEAN // Exclude rarely-used stuff from Windows headers
 
#include <cassert>
#include <iostream>  // cin, cout, cerr
 
#include <atldbcli.h>
 
using namespace std;
 
int main()
{
    // Inicialize the COM.
    ::CoInitialize(NULL);
 
    // The data source represents a connection through a provider to a data source.
    CDataSource ds;
 
    CDBPropSet propSet(DBPROPSET_DBINIT);
    propSet.AddProperty(DBPROP_INIT_DATASOURCE, "d:/Data");  // modify the path if needed
    HRESULT hr = ds.Open("VFPOLEDB.1", &propSet, 1);
    assert(SUCCEEDED(hr));
 
    // Represents a single database access session.
    CSession session;
 
    hr = session.Open(ds);
    assert(SUCCEEDED(hr));
 
    // This part is not the core of the problem, just creation
    // of the source and destination tables for trying the concept
    // later.
    cout << "Creating the tables...\n";
    CCommand<CManualAccessor, CRowset> cmd;
    hr = cmd.Open(session, "CREATE TABLE srcTable (k1 Integer, k2 Integer, k3 Integer)");
    assert(SUCCEEDED(hr));
    cmd.Close();
    hr = cmd.Open(session, "CREATE TABLE dstTable (k1 Integer, k2 Integer, k3 Integer, sum Integer)");
    assert(SUCCEEDED(hr));
    cmd.Close();
 
    // Open the source table for the insertion. Make the IRowsetChange 
    // accessible.
    CTable<CManualAccessor, CRowset> srcTable;
    struct {
        int k1;            
        int k2;            
        int k3;            
        int sum;   // to be used only later in SELECT         
    } buf;         // This is the buffer to be shared later
 
    hr = srcTable.CreateAccessor(3, &buf, sizeof(buf));
    assert(SUCCEEDED(hr));
 
    srcTable.AddBindEntry(1, DBTYPE_I4, sizeof(buf.k1), &buf.k1, NULL, NULL);
    srcTable.AddBindEntry(2, DBTYPE_I4, sizeof(buf.k2), &buf.k2, NULL, NULL);
    srcTable.AddBindEntry(3, DBTYPE_I4, sizeof(buf.k3), &buf.k3, NULL, NULL);
 
    CDBPropSet ps(DBPROPSET_ROWSET);
    ps.AddProperty(DBPROP_IRowsetChange, true);
    ps.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_INSERT);
    hr = srcTable.Open(session, "srcTable", &ps, 1);
    assert(SUCCEEDED(hr));
 
    cout << "Inserting records into source table...\n";
    for (unsigned long n = 0; n < 10; ++n)
    {
        buf.k1 = n;
        buf.k2 = n * 2;
        buf.k3 = n * 3;
        hr = srcTable.Insert();
        assert(SUCCEEDED(hr));
    }
 
    // Display the source table records.
    cout << "src ------------------------------------------------\n";
    hr = srcTable.MoveFirst();
    while (SUCCEEDED(hr) && hr != DB_S_ENDOFROWSET)
    {
        cout << buf.k1 << ", " << buf.k2 << ", " << buf.k3 << "\n";
        hr = srcTable.MoveNext();
    }
    cout << "----------------------------------------------------\n\n";
    
    // Close the srcTab object. It will not be used from now on.
    // It also releases its accessors.
    srcTable.Close();
 
    // Create and bind the accessor to the result of the above cmd.
    hr = cmd.CreateAccessor(4, &buf, sizeof(buf));
    assert(SUCCEEDED(hr));
 
    cmd.AddBindEntry(1, DBTYPE_I4, sizeof(buf.k1), &buf.k1, NULL, NULL);
    cmd.AddBindEntry(2, DBTYPE_I4, sizeof(buf.k2), &buf.k2, NULL, NULL);
    cmd.AddBindEntry(3, DBTYPE_I4, sizeof(buf.k3), &buf.k3, NULL, NULL);
    cmd.AddBindEntry(4, DBTYPE_I4, sizeof(buf.sum), &buf.sum, NULL, NULL);
 
    // Use the above defined CCommand cmd object to execute the SELECT command.
    hr = cmd.Open(session, "SELECT *, (k1+k2+k3) AS sum FROM srcTable "
                           "WHERE k2 BETWEEN 4 and 14");
 
    // Create the destination table object and the accessor.
    CTable<CManualAccessor, CRowset> dstTable;
    hr = dstTable.CreateAccessor(4, &buf, sizeof(buf));
    assert(SUCCEEDED(hr));
 
    dstTable.AddBindEntry(1, DBTYPE_I4, sizeof(buf.k1), &buf.k1, NULL, NULL);
    dstTable.AddBindEntry(2, DBTYPE_I4, sizeof(buf.k2), &buf.k2, NULL, NULL);
    dstTable.AddBindEntry(3, DBTYPE_I4, sizeof(buf.k3), &buf.k3, NULL, NULL);
    dstTable.AddBindEntry(4, DBTYPE_I4, sizeof(buf.sum), &buf.sum, NULL, NULL);
 
    hr = dstTable.Open(session, "dstTable", &ps, 1);
    assert(SUCCEEDED(hr));
 
    // Move to the first record of the src table and start to copy
    // the records into the destination table.
    cout << "Copying records from the source to the destination table\n"
            "(the core of the solved problem; here for simple types of data)\n";
    hr = cmd.MoveFirst();
    while (SUCCEEDED(hr) && hr != DB_S_ENDOFROWSET)
    {
        hr = dstTable.Insert();
        assert(SUCCEEDED(hr));
        hr = cmd.MoveNext();
    }
 
    // Display the destination table records.
    cout << "dst ------------------------------------------------\n";
    hr = dstTable.MoveFirst();
    while (SUCCEEDED(hr) && hr != DB_S_ENDOFROWSET)
    {
        cout << buf.k1 << ", " << buf.k2 << ", " << buf.k3 << ", " << buf.sum << "\n";
        hr = dstTable.MoveNext();
    }
    cout << "----------------------------------------------------\n\n";
 
    // Close the objects.
    cmd.Close();
    cmd.ReleaseCommand();
 
    dstTable.Close();
 
    session.Close();
    ds.Close();
 
    // Unicialize the COM.
    ::CoUninitialize();
 
    return 0;
}

Select allOpen in new window

Here is the example that shows the important core of my final solution. The source database uses Jet provider, the destionation free DBF table is created using VFPOLEDB provider. It uses dynamically allocated shared buffer (as wanted at the question). Some conversion of db datatypes is done.

I am ready to split the points among DanRollins and anyone who finds the bugs or has otherwise valuable comments and inspiring questions.

There definitely are some not-so-smooth facets in the solution. For example, the SELECT command is executed twice; the first result is used only to get the column info, the data is collected after second usage of the same SELECT command. The destination table is created via textually constructed SQL command, not via direct OLE DB API part (see another question http:Q_23685207.html), etc.

Thanks,
   Petr

// $Id$
 
/* Export from a JET database SQL command to the newly created DBF table
   using OLE DB. The structure of the output table is determined from
   the results. The buffer is allocated dynamically as needed.
   The accessors are bound also dynamically as needed. 
   
   Short and long integers, currency, and double (R8) converted to Num(19,5).
   Memo columns converted to Char(10).
 
   The core functionality separated to a function. Called more times
   for exporting more tables.
 
   First working version..
*/
 
#define WIN32_LEAN_AND_MEAN // Exclude rarely-used stuff from Windows headers
 
#include <cassert>
#include <iostream>  // cin, cout, cerr
#include <sstream>
 
#include <atldbcli.h>
 
using namespace std;
 
void bufAlloc(DBORDINAL count, DBCOLUMNINFO * pColInfo,
              BYTE * & buf, DBLENGTH & bufsize);
DBLENGTH getBufSize(const DBCOLUMNINFO & colInfo);
void createAndBindAccessors(CManualAccessor & ma, 
                            DBORDINAL count, DBCOLUMNINFO * pColInfo,
                            BYTE * buf, DBLENGTH bufsize);
std::string getTabDefRepr(const std::string & destTabName, 
                          DBORDINAL count,
                          DBCOLUMNINFO * pColInfo);
void jet2dbfExport(const std::string & SELECT_command, 
                   const std::string & dstTableName, 
                   CSession & sessionJet, 
                   CSession & sessionDBF);
 
int main()
{
    // Inicialize the COM.
    HRESULT hr = ::CoInitialize(NULL);
    assert(SUCCEEDED(hr));
 
    // Jet database data source.
    CDataSource dsJet;
 
    CDBPropSet propSetJet(DBPROPSET_DBINIT);
    propSetJet.AddProperty(DBPROP_INIT_DATASOURCE, 
                           "D:\\some\\path\\to\\my.mdb");  // modify the path if needed
 
    hr = dsJet.Open("Microsoft.Jet.OLEDB.4.0", &propSetJet, 1);
    assert(SUCCEEDED(hr));
 
    // DBF database (VFPOLEDB) data source -- results
    CDataSource dsDBF;
 
    CDBPropSet propSetDBF(DBPROPSET_DBINIT);
    propSetDBF.AddProperty(DBPROP_INIT_DATASOURCE, "D:/Data");  // modify the path if needed
 
    hr = dsDBF.Open("VFPOLEDB.1", &propSetDBF, 1);
    assert(SUCCEEDED(hr));
 
    // Open sessions to both Jet and DBF databases.
    CSession sessionJet;
    hr = sessionJet.Open(dsJet);
    assert(SUCCEEDED(hr));
 
    CSession sessionDBF;
    hr = sessionDBF.Open(dsDBF);
    assert(SUCCEEDED(hr));
 
    // Export the results of the following SQL SELECT commands...
    jet2dbfExport("SELECT * FROM ad", "ad", sessionJet, sessionDBF);
 
    // Close the session and data source objects.
    sessionJet.Close();
    sessionDBF.Close();
 
    dsJet.Close();
    dsDBF.Close();
 
    // Unicialize the COM.
    ::CoUninitialize();
 
    return 0;
}
 
 
DBLENGTH getBufSize(const DBCOLUMNINFO & colInfo)
{
    DBLENGTH len = colInfo.ulColumnSize;
    switch (colInfo.wType)
    {
    case DBTYPE_STR:
        if (len > 254)
            len = 10;     // memo narrowed to 10 characters
        ++len;            // plus one '\0' sentinel char
        break;            // one char, one byte
 
    case DBTYPE_WSTR:
        if (len > 254)
            len = 10;     // memo narrowed to 10 characters
        ++len;            // plus one '\0' sentinel char
        len *= 2;         // one char, two bytes
        break;   
 
    // Expected types, simply pass the original, initial value.
    case DBTYPE_BOOL:
    case DBTYPE_DATE:
        break;
 
    // The following types will be converted to Num(19,5) later, but 
    // the conversion will be done on output (when calling .Insert()). 
    // The buffer for input will be ready for the natural size 
    // of the values. No correction here.
    case DBTYPE_I2:    
    case DBTYPE_I4:
    case DBTYPE_CY:
    case DBTYPE_R8:
        break;
 
    default:
        cout << "convert() unknown data type " << colInfo.wType << "\n";
    }
 
    assert(len > 0);
    return len;
}
 
 
std::string getTabDefRepr(const std::string & destTabName, 
                          DBORDINAL count,          // number of columns
                          DBCOLUMNINFO * pColInfo)
{
    USES_CONVERSION;
    ostringstream ostr;
 
    ostr << "CREATE TABLE " << destTabName << " (\n";
 
    for (DBORDINAL i = 0; i < count; ++i)
    {
        // Output the field def separator and the indentation.
        if (i > 0)
        {
            ostr << ",\n";
        }
        ostr << "  ";
 
 
        // Memo column narrowed to Char(10).
        DBLENGTH colSize = pColInfo[i].ulColumnSize;
        if (colSize > 254)
        {
            assert(pColInfo[i].wType == DBTYPE_STR || pColInfo[i].wType == DBTYPE_WSTR);
            colSize = 10;
        }
 
        ostr << OLE2A(pColInfo[i].pwszName) << " ";   // column id
 
        /* The following table describes the mapping of Visual FoxPro to OLE DB types:
 
           Indicator                        OLE DB type
           --------------------------------------------------------------------------
           C, Char, Character               DBTYPE_STR           
           C NOCPTRANS, Character (binary)  DBTYPE_BYTES
           D, Date                          DBTYPE_DATE
           T, DateTime                      DBTYPE_DBTIMESTAMP
           N, Num, Numeric                  DBTYPE_NUMERIC       (precision, scale) 
           F, Float                         DBTYPE_NUMERIC       (precision, scale)
           I, Int, Integer                  DBTYPE_I4
           L, Logical                       DBTYPE_BOOL
           B, Double                        DBTYPE_R8            (precision)
           Y, Currency                      DBTYPE_CY
           M, Memo                          DBTYPE_STR
           M NOCPTRANS, Memo (binary)       DBTYPE_BYTES
           G, General (blob)                DBTYPE_BYTES
 
        */
        switch (pColInfo[i].wType)
        {
        case DBTYPE_STR:   
        case DBTYPE_WSTR:   
            ostr << "Char(" << colSize << ")";  // possibly narrowed memo
            break;
 
        case DBTYPE_BOOL:
            ostr << "Logical";
            break;
 
        case DBTYPE_DATE:
            ostr << "Date";
            break;
 
        case DBTYPE_NUMERIC:
            ostr << "Num("
                 << static_cast<int>(pColInfo[i].bPrecision) << ", "
                 << static_cast<int>(pColInfo[i].bScale) << ")";
            break;
 
        // The following types will be converted to Num(19,5), but 
        // the conversion will be done on output (when calling .Insert()). 
        // The buffer for input will be ready for the natural size 
        // of the values.
        case DBTYPE_I2:    
        case DBTYPE_I4:
        case DBTYPE_CY:
        case DBTYPE_R8:
            ostr << "Num(19,5)";
            break;
 
        default:
            ostr << "??? [unknown data type " << pColInfo[i].wType << "]";
        }
 
        ostr << " NOT NULL";   // All columns forced as NOT NULL.
    }
    ostr << "\n)\n";
 
    return ostr.str();
}
 
 
void bufAlloc(DBORDINAL count, DBCOLUMNINFO * pColInfo,
              BYTE * & buf, DBLENGTH & bufsize)
{
    bufsize = 0;     // starting value -- no size accumulated
    for (DBORDINAL i = 0; i < count; ++i)
    {
        bufsize += getBufSize(pColInfo[i]);
    }
 
    buf = static_cast<BYTE *>(CoTaskMemAlloc(bufsize));
}
 
 
void createAndBindAccessors(CManualAccessor & ma, 
                            DBORDINAL count, 
                            DBCOLUMNINFO * pColInfo,
                            BYTE * buf, 
                            DBLENGTH bufsize)
{
    // Create and bind the accessor of the cmd object.
    HRESULT hr = ma.CreateAccessor(count, buf, bufsize);
    assert(SUCCEEDED(hr));
 
    DBLENGTH offset = 0;
    for (DBORDINAL i = 0; i < count; ++i)
    {
        // Length of the column in bytes after possible correction.
        DBLENGTH len = getBufSize(pColInfo[i]);
 
        ma.AddBindEntry(pColInfo[i].iOrdinal,
                        pColInfo[i].wType,
                        len,
                        buf+offset);
 
        // Increase the offset -- get ready for the next loop.
        offset += len;
    }
}
 
 
void jet2dbfExport(const std::string & SELECT_command, 
                   const std::string & dstTableName, 
                   CSession & sessionJet, 
                   CSession & sessionDBF)
{
    cout << "Exporting " << dstTableName << " ... ";
 
    // Create the command object (cmd) and the destination table object.
    CCommand<CManualAccessor, CRowset> cmd;
    CTable<CManualAccessor, CRowset> dstTable;
 
    // Use the above defined CCommand cmd object to execute the SELECT command.
    HRESULT hr = cmd.Open(sessionJet, SELECT_command.c_str());
    assert(SUCCEEDED(hr));
 
    // Get the information about the structure of the results.
    DBORDINAL colCount = 0;           // number of columns
    DBCOLUMNINFO * pColInfo = NULL;   // to be alocated and returned
    LPOLESTR pOLEString = NULL;
 
    hr = cmd.GetColumnInfo(&colCount, &pColInfo, &pOLEString);
    assert(SUCCEEDED(hr));
    cmd.Close();
 
    // Generate the CREATE TABLE command. Get the textual definition
    // first of the form of the CREATE TABLE ... command 
    // and execute that command.
    string CREATE_TABLE_command(getTabDefRepr(dstTableName,
                                              colCount,
                                              pColInfo));
    //??? cout << "\n" << CREATE_TABLE_command << "\n";          //??? for debugging only
    hr = cmd.Open(sessionDBF, CREATE_TABLE_command.c_str());
    assert(SUCCEEDED(hr));
    cmd.Close();
 
    // Allocate the buffer.
    BYTE * pBuf = NULL;
    DBLENGTH bufsize = 0;
    bufAlloc(colCount, pColInfo, pBuf, bufsize);
 
    // Create and bind the accessor of the cmd object.
    createAndBindAccessors(cmd, colCount, pColInfo, pBuf, bufsize);
 
    // Create the destination table accessor.
    createAndBindAccessors(dstTable, colCount, pColInfo, pBuf, bufsize);
 
    // Release the allocated columnt info structures.
    if (pColInfo != NULL)
    {
        CoTaskMemFree(pColInfo);
        pColInfo = NULL;
    }
 
    if (pOLEString != NULL)
    {
        CoTaskMemFree(pOLEString);
        pOLEString = NULL;
    }
 
    // Execute the SELECT command again to get the rowset (now data
    // is available via the accessor).
    hr = cmd.Open(sessionJet, SELECT_command.c_str());
 
    // Open the destination table object (also with accessor bound
    // to the shared buffer).
    CDBPropSet ps(DBPROPSET_ROWSET);
    ps.AddProperty(DBPROP_IRowsetChange, true);
    ps.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_INSERT);
 
    hr = dstTable.Open(sessionDBF, dstTableName.c_str(), &ps, 1);
    assert(SUCCEEDED(hr));
 
    // Move to the first record of the src table and start to copy
    // the records into the destination table.
    SecureZeroMemory(pBuf, bufsize);
    hr = cmd.MoveFirst();
    while (SUCCEEDED(hr) && hr != DB_S_ENDOFROWSET)
    {
        hr = dstTable.Insert();
        assert(SUCCEEDED(hr));
        SecureZeroMemory(pBuf, bufsize);
        hr = cmd.MoveNext();
    }
 
    // Close the objects.
    cmd.Close();
    cmd.ReleaseCommand();
 
    dstTable.Close();
 
    // Release the shared buffer.
    if (pBuf != NULL)
    {
        CoTaskMemFree(pBuf);
        pBuf = NULL;
    }
 
    cout << "done\n";
}

Select allOpen in new window

Thanks, Dan, for your help.