A database is a collection of tables and a session may contain several different databases. A database object is required before any table or data operations may take place. The following are typical operations performed on a database:

• Create a database object
• Connect to a database by calling CTDatabase::Connect()
• Perform table, index, field and record operations
• When you are done with the database, disconnect by calling CTDatabase::Disconnect()
• Destroy the database object if it was created with the new operator

Creating a Database object

A valid database object is required to perform any database operation. You need to pass a valid CTSession object to the CTDatabase constructor.

// create a CTDatabase object
CTDatabase ADatabase(ASession);
try
{
// connect to database "MyDatabase"
ADatabase.Connect("MyDatabase");
}
catch (CTException &err)
{
printf("Database connect failed with error %d\n", err.GetErrorCode);
}

If you create a dynamic CTDatabase object with the new operator, you are required to destroy the object with the delete operator.

// create a dynamic CTDatabase object
CTDatabase* pDatabase = new CTDatabase(ASession);
 
if (!pDatabase)
{
   printf("CTDatabase creating failed\n");
}  
... other operations ..
// destroy the CTDatabase object
delete pDatabase;

If you destroy an active database handle, all open tables associated with the database will be closed and the database will be disconnected from the session.

Connecting to a database

Before performing any operations with a database, a database object must be connected to a session by calling CTDatabase::Connect() method. The database should already have been created or added to the session.

// connect to a database
CTDatabase ADatabase(ASession);
 
try
{
ADatabase.Connect("MyDatabase");
}
catch (CTException &err)
{
   printf("Connect to database failed with error %d\n", err.GetErrorCode());
}

When a database is connected to the session, it is considered "active". Use CTDatabase::IsActive() to check if a particular database is active.

When the database connection is no longer needed, it must be disconnected from the session by calling CTDatabase::Disconnect() method.

// disconnect from a database
try
{
   ADatabase.Disconnect();
}
catch (CTException &err)
{
   printf("Disconnect from database failed with error %d\n",
          err.GetErrorCode());
}

Database properties

The default database properties are suitable for most FairCom DB API applications. Advanced developers may need to tune FairCom DB API to meet application requirements.

Database name

Once the database object connects, retrieve the database name with CTDatabase::GetName().

// display the database name
printf("Database: %s\n", ADatabase.GetName().c_str());

Database path

The database path, by default, is the server directory for client/server applications, or the application directory for non-server applications. To set a different database path, when the database is being created, just insert the appropriate path as the second parameter of CTSession::CreateDatabase(). With this information stored in the Session dictionary, the user does not need to know where it is located, since it will be automatically retrieved given the database name.

Once the database is successfully connected to a session, obtain the database path with CTDatabase::GetPath().

// display database properties
void DisplayDatabaseProperties(CTDatabase &ADatabase)
{
   printf("Database: %s\n", ADatabase.GetName().c_str());
   printf("Path: %s\n", ADatabase.GetPath().c_str());
   printf("Number of tables: %d\n", ADatabase.GetTableCount());
}

Table count

CTDatabase::GetTableCount() retrieves the number of tables associated with a database. CTDatabase::GetTableCount() return -1 (minus 1) if the database is not connected or if the database object is invalid.

// display the number of tables in database
printf("Number of tables: %d\n", ADatabase.GetTableCount());

Managing Tables

Once the user performs a successful database connect, the database handle can be used to operate on tables.

Every time a new table is created, or an existing table is added to a database, some of the table properties such as table name, path, data file extension, index file extension, etc, are placed in an entry of the database dictionary file.

Every time a user activates a table by opening it with CTTable::Open() method, the table is placed in a list of active (opened) tables within the database. When a table is deactivated, or closed by calling CTTable::Close() method, the table is removed from the list of active tables in the database. A user may query the active table list by locating the first active table, the next active table and finding a specific active table.

Adding an existing table

An existing table may be added or imported to a database by calling the CTDatabase::AddTable() method. CTDatabase:AddTable() takes as parameters the table name and the table path.

// add MyTable to the current database
try
{
   ADatabase.AddTable("MyTable", "");
}
catch (CTException &err)
{
   printf("Add table failed with error %d\n", err.GetErrorCode());
}

Adding an existing table under transaction control

An extra level of data integrity can be achieved when you add an existing table to a database under transaction control. When the transaction is committed, the database dictionary data for the table is committed to disk. If the transaction is aborted, the dictionary data for the table is automatically removed from the database dictionary.

The code fragment below shows how to add an existing table under transaction control.

// begin a transaction
ADatabase.Begin();
try
{
   // add MyTable to the current database
   ADatabase.AddTable("MyTable", "");
   // commit the transaction
   ADatabase.Commit();
}
catch (CTException &err)
{
   // abort the transaction
   ADatabase.Abort();
   printf("Add table failed with code %d\n", err.GetErrorCode());
}

Dropping a table

When you drop a table from a database, the table information is removed from the database dictionary, but the table data and index files are left untouched. The drop table operation can be reversed with an add table operation. Drop a table from a database by calling CTDatabase::DropTable() method.

// drop MyTable from current database
try
{
   ADatabase.DropTable("MyTable");
}
catch (CTException &err)
{
   printf("Drop table failed with code %d\n", err.GetErrorCode());
}

Dropping a table under transaction control

An extra level of data integrity can be achieved when you drop a table from a database under transaction control. When the transaction is committed, the changes to the database dictionary data for the table are committed to disk. If the transaction is aborted, the dictionary data for the table is automatically restored to the database dictionary.

The code fragment below shows how to drop an existing table under transaction control. No error checking is included in the sample code:

// start a transaction
ADatabase.Begin();
try
{
   // drop MyTable from current database
   ADatabase.DropTable("MyTable");
   // commit the transaction
   ADatabase.Commit();
}
catch (CTException &err)
{
   // abort the transaction
   ADatabase.Abort();
   printf("Drop table failed with code %d\n", err.GetErrorCode());
}

Deleting a table

When you delete a table from a database, the table information is removed from the database dictionary and the table data and index files are deleted from disk. The delete table operation can be reversed only when used under transaction control. Without transaction control, a delete table operation will delete the data and index files and the table data will be lost. Delete a table from a database by calling CTDatabase::DeleteTable() method. Example:

// delete MyTable from current database
try
{
CTString password;
ADatabase.DeleteTable("MyTable", password);
}
catch (CTException &err)
{
   printf("Delete table failed with code %d\n", err.GetErrorCode());
}

Note: The DeleteTable() method takes as parameters the table name and the table password. Set the password parameter to an empty CTString object if a table was created without passwords.

Deleting a table under transaction control

An extra level of data integrity can be achieved when you delete a table from a database under transaction control. When the transaction is committed, the changes to the database dictionary data for the table are committed to disk and the table and index files are deleted from disk. If the transaction is aborted, the dictionary data for the table is automatically restored to the database dictionary and the original data and index files are restored to their original state.

The code fragment below shows how to delete an existing table under transaction control. No error checking is included in the sample code:

// start a transaction
try
{
  // delete MyTable from current database
  CTString password;
  ADatabase.DeleteTable("MyTable", password);
  
  // commit the transaction
  ADatabase.Commit();
}
catch (CTException &err) 
{
   // abort the transaction
   ADatabase.Abort();
   printf("Delete table failed with code %d\n", err.GetErrorCode());
}

First Table

CTDatabase::FirstTable() retrieves the name and path of the first table in a session. If the session has no tables, CTDatabase::FirstTable() returns NO (false). See the example in "Next Table".

Next Table

CTDatabase::NextTable() retrieves the name and path of the next table in a database. CTDatabase::NextTable() returns NO (false) when no more tables exist for the current database.

// Display all tables in a database
void DisplayTables(CTDatabase &ADatabase)
{
CTString Name;
CTString Path;
 
if (ADatabase.FirstTable(Name, Path))
do
{
   printf("Table: %s Path: %s\n", Name.c_str(), Path.c_str());
}
while (ADatabase.NextTable(Name, Path);
}

Find Table

CTDatabase::FindTable() locates a specific table given the table name and, if the table exists, retrieves the table path. If a table cannot be found, CTDatabase::FindTable() returns NO (false).

// return YES if table exist or NO if table does not exit
CTBOOL TableExist(CTDatabase &ADatabase, CTString& tblName)
{
   CTString tblPath;
 
   return ADatabase.FindTable(TblName, tblPath);
}

First Active Table

CTDatabase::GetFirstActive() retrieves the table object pointer of the first active table. If the database contains no active tables, CTDatabase::GetFirstActive() returns NULL.

Next Active Table

CTDatabase::GetNextActive() retrieves the table object pointer of the next active table. When no more active tables exist, CTDatabase::GetNextActive() returns NULL.

// Display all active tables
void DisplayActiveTables(CTDatabase &ADatabase)
{
   VRLEN hScan;
   CTTable *pTable;
 
   if ((pTable = ADatabase.GetFirstActive(&hScan)) != NULL)
   {
      do
      {
         printf("Table: %s Path: %s\n", pTable->GetName().c_str(),
         pTable->GetPath().c_str());
         pTable = ADatabase.GetNextActive(&hScan);
      }
      while (pTable != NULL;
   }
}

Find Active Table

CTDatabase::FindActive() locates a specific active table and returns the table object pointer. If the table is not active, CTDatabase::FindActive() returns NULL.

// Check if table is active
CTBOOL IsTableActive(CTDatabase& ADatabase, CTString& tblName)
{
   return (ADatabase.FindActive(tblName) != NULL) ? YES : NO;
}

The function above is shown for example purposes only as the FairCom DB API method CTTable::IsActive() provides a more efficient way to check if a table is active.

Table UID (Unique IDentifier)

When a table is created or added to a database, an automatic and unique identifier (UID) is associated with the table. A table UID is unique within the database that the table is associated with.

A table UID is an unsigned long value that can be used as an alternative method to operate on tables once the table is created or added to a database.

Find Table by UID

The overloaded method CTDatabase::FindTable() locates a table in the database given the table UID and retrieves the table name and path. The following example shows how to implement a table open function using the table UID instead of the table name.

// open table using UID
CTTable* OpenByUID(CTDatabase& ADatabase, ULONG uid, CTOPEN_MODE OpenMode)
{
   CTString tblName;
   CTString tblPath;
   CTTable* Retval;
 
   // locate the table in the database by uid
   if (ADatabase.FindTable(uid, tblName, tblPath))
   {
      Retval = new CTTable(ADatabase);
   
      if (!Retval)
         throw CTException(CTDBRET_NOMEMORY);
   
      try
      {
         Retval->Open(tblName, OpenMode);
      }
      catch (CTException& err)
      {
         delete Retval;
         throw;
      }
   }
   else
   {
      // table not found
      throw CTException(INOT_ERR);
   }
   return Retval;
}

Find active Table by UID

The overloaded method CTDatabase::FindActive() locates an active table given its UID number and returns the active table object pointer. The following example shows how to check if a table is active using its UID number.

// check if atable is active, by UID
CTBOOL IsTableActive(CTDatabase& ADatabase, ULONG uid)
{
return (ADatabase. FindActive(uid) != NULL) ? YES : NO;
}

Database Dictionary

A database must have a database dictionary; this file has the database name with extension .fdd. It is created with CTDatabase::CreateDatabase(), and must exist for the user to connect to the database. Each database dictionary maintains information about the tables associated with the database. When a database is connected to the session, it is considered "active".

Database Dictionary Layout

This information is included for general information on the database dictionary structure. FairCom DB API has appropriate functions to retrieve any needed information from the database dictionary, such as the table UID or path.

A database dictionary file has several different record types:

• Table records (the field Type set to 2) holds information about a data file (table).
• Index records (the field Type set to 3) holds information about an index file.
• A special type four record which maintained the COUNTER field has been been deprecated as of FairCom DB V9.0.