Creating a Database on the Fly

[needaname16]

Hi,

Previously I have only added or subtracted records from a database. I how have a need to create a new database on the fly which I can add tables and records to as needed by the program. Can someone tell me how I can achieve this? or point me to the right direction in MSDN.

Thanks a mill.

Needaname16

[parksie]

What type of database, are you using API, MFC, .NET....?

[needaname16]

I have to use either a dBase or Access Database, as for the method either MFC or API is fine.

[CornedBee]

Access using DAO as made available by MFC:

You need a CDaoDatabase object. Call Create and pass the filename for the database. Then create a CDaoTableDef object for each table you want. Fill CDaoFieldInfo and CDaoIndexInfo structures with data as described in the reference (look up the names of these structures) and call CDaoTableDef::CreateField/Index for each field. (index only for special fields, such as the primary key)
When you have all fields, call CDaoTableDef::Append for each tabledef. This will write the table structure to the file and you can fill it with data.

Here is the code I use to create my database:

void CCleanpkDoc::CreateDatabase(const CString& strFileName)
{
    if(!m_pDB)
        m_pDB = new CDaoDatabase;
    if(m_pDB->IsOpen())
        m_pDB->Close();
    m_pDB->Create(strFileName);
    CreateTables();
}

void CCleanpkDoc::CreateTables()
{
    // this function creates the tables for the database
    ASSERT(m_pDB && m_pDB->IsOpen());
    // Create the table "Struktur"
    CDaoTableDef tdStructure(m_pDB);
    tdStructure.Create(_T("Struktur"));
    // Create the field "ID"
    tdStructure.CreateField(_T("ID"), dbLong, 0, dbAutoIncrField);
    // Create the field "Title"
    CDaoFieldInfo dfi;
    dfi.m_strName = _T("Title");
    dfi.m_nType = dbText;
    dfi.m_lSize = 50;
    dfi.m_lAttributes = dbFixedField | dbUpdatableField;
    dfi.m_nOrdinalPosition = 0;
    dfi.m_bRequired = FALSE;
    dfi.m_bAllowZeroLength = FALSE;
    dfi.m_lCollatingOrder = 0;
    dfi.m_strForeignName = _T("");
    dfi.m_strSourceField = _T("");
    dfi.m_strSourceTable = _T("");
    dfi.m_strValidationRule = _T("");
    dfi.m_strValidationText = _T("");
    dfi.m_strDefaultValue = _T("Unnamed Topic");
    tdStructure.CreateField(dfi);
    // Create the field "ParentID"
    dfi.m_strName = _T("ParentID");
    dfi.m_nType = dbLong;
    dfi.m_lSize = 0;
    dfi.m_bRequired = TRUE;
    dfi.m_strDefaultValue = _T("0");
    tdStructure.CreateField(dfi);
    // Create the field "XPos"
    dfi.m_strName = _T("XPos");
    tdStructure.CreateField(dfi);
    // Create the field "YPos"
    dfi.m_strName = _T("YPos");
    tdStructure.CreateField(dfi);
    // Create the field "Width"
    dfi.m_strName = _T("Width");
    dfi.m_strDefaultValue = _T("100");
    tdStructure.CreateField(dfi);
    // Create the field "Height"
    dfi.m_strName = _T("Height");
    tdStructure.CreateField(dfi);
    // Create indices
    // for "ID"
    CDaoIndexInfo dii;
    dii.m_strName = _T("ID");
    CDaoIndexFieldInfo difi;
    difi.m_strName = _T("ID");
    difi.m_bDescending = FALSE;
    dii.m_pFieldInfos = &difi;
    dii.m_nFields = 1;
    dii.m_bPrimary = TRUE;
    dii.m_bUnique = TRUE;
    dii.m_bClustered = FALSE;
    dii.m_bIgnoreNulls = TRUE;    // doesn't matter
    dii.m_bRequired = TRUE;
    dii.m_bForeign = FALSE;
    dii.m_lDistinctCount = 0;    // ignored
    tdStructure.CreateIndex(dii);
    // for "ParentID"
    difi.m_strName = _T("ParentID");
    dii.m_strName = _T("ParentID");
    dii.m_bPrimary = FALSE;
    dii.m_bUnique = FALSE;
    dii.m_bForeign = TRUE;
    tdStructure.CreateIndex(dii);
    // add to database
    tdStructure.Append();
    // done
    tdStructure.Close();
    // ****************************************************
    // Create the table "Memos"
    CDaoTableDef tdMemos(m_pDB);
    tdMemos.Create(_T("Memos"));
    // Create the field "ID"
    tdMemos.CreateField(_T("ID"), dbLong, 0, dbAutoIncrField);
    // Create the field "OwnerID"
    dfi.m_strName = _T("OwnerID");
    dfi.m_nType = dbLong;
    dfi.m_lSize = 0;
    dfi.m_lAttributes = dbFixedField | dbUpdatableField;
    dfi.m_nOrdinalPosition = 0;
    dfi.m_bRequired = TRUE;
    dfi.m_bAllowZeroLength = FALSE;
    dfi.m_lCollatingOrder = 0;
    dfi.m_strForeignName = _T("");
    dfi.m_strSourceField = _T("");
    dfi.m_strSourceTable = _T("");
    dfi.m_strValidationRule = _T("");
    dfi.m_strValidationText = _T("");
    dfi.m_strDefaultValue = _T("");
    tdMemos.CreateField(dfi);
    // Create the field "Data"
    dfi.m_strName = _T("Data");
    dfi.m_nType = dbMemo;
    tdMemos.CreateField(dfi);
    // Create the field "XPos"
    dfi.m_strName = _T("XPos");
    dfi.m_nType = dbLong;
    dfi.m_lSize = 0;
    dfi.m_bRequired = TRUE;
    dfi.m_strDefaultValue = _T("0");
    tdMemos.CreateField(dfi);
    // Create the field "YPos"
    dfi.m_strName = _T("YPos");
    tdMemos.CreateField(dfi);
    // Create the field "Width"
    dfi.m_strName = _T("Width");
    dfi.m_strDefaultValue = _T("100");
    tdMemos.CreateField(dfi);
    // Create the field "Height"
    dfi.m_strName = _T("Height");
    tdMemos.CreateField(dfi);
    // Create the field "Primary"
    dfi.m_strName = _T("Primary");
    dfi.m_strDefaultValue = _T("False");
    dfi.m_nType = dbBoolean;
    tdMemos.CreateField(dfi);
    // Indices (ID and OwnerID)
    dii.m_strName = _T("ID");
    difi.m_strName = _T("ID");
    difi.m_bDescending = FALSE;
    dii.m_bPrimary = TRUE;
    dii.m_bUnique = TRUE;
    dii.m_bClustered = FALSE;
    dii.m_bIgnoreNulls = TRUE;    // doesn't matter
    dii.m_bRequired = TRUE;
    dii.m_bForeign = FALSE;
    dii.m_lDistinctCount = 0;    // ignored
    tdMemos.CreateIndex(dii);
    difi.m_strName = _T("OwnerID");
    dii.m_strName = _T("OwnerID");
    dii.m_bPrimary = FALSE;
    dii.m_bUnique = FALSE;
    dii.m_bForeign = TRUE;
    tdMemos.CreateIndex(dii);
    tdMemos.Append();
    tdMemos.Close();
} 

[parksie]

Isn't DAO deprecated?

[CornedBee]

Yeah, I know, DAO doesn't support the newest MSJet database format, but the MFC wrapper is actually the ONLY object-oriented way to do it where the documentation is for C++, not for Automation clients.
Or do you have any idea where to get ADO docs for C++? (The SDK contains the ADO docs two times - both for Automation)

[parksie]

Not sure what you mean by Automation, but I found a set of examples like this:

// BeginExecuteCpp
#include <ole2.h>
#include <stdio.h>

#import "c:\Program Files\Common Files\System\ADO\msado15.dll" \
    no_namespace rename("EOF", "EndOfFile")

// Function declarations
inline void TESTHR(HRESULT x) {if FAILED(x) _com_issue_error(x);};
void ExecuteX(void);
void ExecuteCommand(_CommandPtr pCmdTemp, _RecordsetPtr pRstTemp);
void PrintOutput(_RecordsetPtr pRstTemp);
void PrintProviderError(_ConnectionPtr pConnection);
void PrintComError(_com_error &e);

////////////////////////////////
//      Main Function         //
////////////////////////////////

void main()
{
    if(FAILED(::CoInitialize(NULL)))
        return;

    ExecuteX();

    ::CoUninitialize();
}

///////////////////////////////////
//      ExecuteX Function        //
///////////////////////////////////

void ExecuteX(void) 
{
   HRESULT    hr = S_OK;

    // Define string variables.
   _bstr_t strSQLChange("UPDATE Titles SET Type = "
            "'self_help' WHERE Type = 'psychology'");
   _bstr_t strSQLRestore("UPDATE Titles SET Type = "
            "'psychology' WHERE Type = 'self_help'");
   _bstr_t strCnn("Provider=sqloledb;Data Source=MyServer;"
            "Initial Catalog=pubs;User Id=sa;Password=;");

    // Define ADO object pointers.
    // Initialize pointers on define.
    // These are in the ADODB::  namespace.
    _ConnectionPtr  pConnection = NULL;
    _CommandPtr     pCmdChange  = NULL;
    _RecordsetPtr   pRstTitles  = NULL;

    try
    {
        // Open connection.
        TESTHR(pConnection.CreateInstance(__uuidof(Connection)));
        pConnection->Open (strCnn, "", "", adConnectUnspecified);

        // Create command object.
        TESTHR(pCmdChange.CreateInstance(__uuidof(Command)));
        pCmdChange->ActiveConnection = pConnection;
        pCmdChange->CommandText = strSQLChange;

        // Open titles table, casting Connection pointer to an 
        // IDispatch type so converted to correct type of variant.
        TESTHR(pRstTitles.CreateInstance(__uuidof(Recordset)));
        pRstTitles->Open ("Titles", _variant_t((IDispatch *) pConnection, 
            true), adOpenStatic, adLockOptimistic, adCmdTable);

        // Print report of original data.
        printf(
            "\n\nData in Titles table before executing the query: \n");

        // Call function to print loop recordset contents.
        PrintOutput(pRstTitles);

        // Clear extraneous errors from the Errors collection.
        pConnection->Errors->Clear();

        // Call ExecuteCommand subroutine to execute pCmdChange command.
        ExecuteCommand(pCmdChange, pRstTitles);

        // Print report of new data.
        printf(
            "\n\n\tData in Titles table after executing the query: \n");
        PrintOutput(pRstTitles);

        // Use the Connection object's execute method to
        // execute SQL statement to restore data.
        pConnection->Execute(strSQLRestore, NULL, adExecuteNoRecords);

        // Retrieve the current data by requerying the recordset.
        pRstTitles->Requery(adCmdUnknown);

        // Print report of restored data.
        printf(
            "\n\n\tData after exec. query to restore original info: \n");
        PrintOutput(pRstTitles);

        // Clean up objects before exit.
        pRstTitles->Close();
        pConnection->Close();
    }

    catch (_com_error &e)
    {
        PrintProviderError(pConnection);
        PrintComError(e);
    }
}

//////////////////////////////////////////
//      ExecuteCommand Function         //
//////////////////////////////////////////

void ExecuteCommand(_CommandPtr pCmdTemp, _RecordsetPtr pRstTemp)
{
    try
    {
        // CommandText property already set before function was called.
        pCmdTemp->Execute(NULL, NULL, adCmdText);

        // Retrieve the current data by requerying the recordset.
        pRstTemp->Requery(adCmdUnknown);
    }

    catch(_com_error &e)
    {
        // Notify user of any errors that result from
        // executing the query.
        // Pass a connection pointer accessed from the Recordset.
        PrintProviderError(pRstTemp->GetActiveConnection());
        PrintComError(e);
    }
}

/////////////////////////////////////
//      PrintOutput Function       //
/////////////////////////////////////

void PrintOutput(_RecordsetPtr pRstTemp)
{
    // Ensure at top of recordset.
    pRstTemp->MoveFirst();

    // If EOF is true, then no data and skip print loop.
    if( pRstTemp->EndOfFile )
    {
        printf("\tRecordset empty\n");
    }
    else
    {
        // Define temporary strings for output conversions.
        // Initialize to first record's values.
        _bstr_t bstrTitle;
        _bstr_t bstrType;

        // Enumerate Recordset and print from each.
        while(!(pRstTemp->EndOfFile))
            {
            // Convert variant string to convertable string type.
            bstrTitle = pRstTemp->Fields->GetItem("Title")->Value;
            bstrType  = pRstTemp->Fields->GetItem("Type")->Value;
            printf("\t%s, %s \n", 
                (LPCSTR) bstrTitle,
                (LPCSTR) bstrType);
    
            pRstTemp->MoveNext();
        }
    }
}

///////////////////////////////////////////////
//      PrintProviderError Function          //
///////////////////////////////////////////////

void PrintProviderError(_ConnectionPtr pConnection)
{
    // Print Provider Errors from Connection object.
    // pErr is a record object in the Connection's Error collection.
    ErrorPtr  pErr = NULL;

    if( (pConnection->Errors->Count) > 0)
    {
        long nCount = pConnection->Errors->Count;
        // Collection ranges from 0 to nCount -1.
        for(long i = 0; i < nCount; i++)
        {
            pErr = pConnection->Errors->GetItem(i);
            printf("\t Error number: %x\t%s", pErr->Number,
                pErr->Description);
        }
    }
}

//////////////////////////////////////
//      PrintComError Function      //
//////////////////////////////////////

void PrintComError(_com_error &e)
{
    _bstr_t bstrSource(e.Source());
    _bstr_t bstrDescription(e.Description());
    
    // Print Com errors.
    printf("Error\n");
    printf("\tCode = %08lx\n", e.Error());
    printf("\tCode meaning = %s\n", e.ErrorMessage());
    printf("\tSource = %s\n", (LPCSTR) bstrSource);
    printf("\tDescription = %s\n", (LPCSTR) bstrDescription);
}
// EndExecuteCpp

[CornedBee]

I mean that all references are for VB, VBA, VBS etc.
Automation is a mechanism to make objects available to scripting engines and VB via a COM interface named IDispatch. Both DAO and ADO are dual interfaces which means you can use either IDispatch and Automation to access methods or IDispatch-derived interfaces to do it in a more COM-like way, but with sucking VARIANT arguments. And no documentation.

[parksie]

Well the way I see it COM isn't the world's best tool if you write C++. For VB it's dead easy, but it's far too heavyweight for C++.

Anyway, most people who write systems in C++ tend to do it all themselves (or use slightly more open libraries - CORBA springs to mind).

[CornedBee]

But pure DB API isn't anything like C++ and I like the features I get with DAO (the recordset types etc.), things you don't really have when using plain SQL statements...