Scanning An Application's Query And Table Objects For Refresh

Introduction

One of the appealing features of TTable and TQuery objects is the opportunity they provide to easily support multiple cursors into a database. But one of the problems with that approach lies in the possibility of the other cursors not showing updates made through a companion cursor. This article discusses how to overcome that problem.

A Simple Scan

The following loop scans every dataset in your application:

TDataSet *DataSet;

for (int SessionIndex = 0; SessionIndex < Sessions->Count; SessionIndex++)
{
   TSession *CurrentSession = Sessions->Sessions[SessionIndex];
   int DatabasesInSession = Sessions->Sessions[SessionIndex]->DatabaseCount;

   for (int DatabaseIndex = 0; DatabaseIndex < DatabasesInSession; DatabaseIndex++)
   {
      TDatabase *CurrentDatabase = CurrentSession->Databases[DatabaseIndex];
      int DataSetsInDatabase = CurrentDatabase->DataSetCount-1;

      for (int DataSetIndex = DataSetsInDatabase; DataSetIndex >= 0 ; DataSetIndex--)
      {
         DataSet = CurrentDatabase->DataSets[DataSetIndex];
      };
   };
};

Adapting The Simple Scan

When working with TTable, updating the cursor's view of data so that changes made via another cursor based on the same underlying table can be seen is just a matter of

TTable *Table = dynamic_cast<TTable *>(DataSet);

if (Table->TableName == DesiredTableName && Table->DatabaseName == DesiredDatabaseName) Table->Refresh;

Unfortunately, queries are much harder.

This is because it is difficult to find the tables used by the query. You can work around this by using a naming convention for the query that includes the table name(s) in the component name in some predictable fashion. If you need the perfectly generic case, however, you must access the BDE:


if(DbiQGetBaseDescs(StmtHandle,TemporaryCursor) == DBIERR_NONE)
{
    while
    (
      (
         Result =
            DbiGetNextRecord
            (
               TemporaryCursor,
               dbiNOLOCK,
               &StatementBaseTableDescriptor,
               NULL
            )

      ) != DBIERR_EOF
    )
      {
          if (Result != DBIERR_NONE)
          {
            char ErrorMessage[512];
            DbiGetErrorString (Result,ErrorMessage);

            Application->MessageBox
            (
                ErrorMessage,
                ("Error getting query table list for " + Name).c_str(),
                MB_ICONWARNING
            );

            DbiCloseCursor(TemporaryCursor);
            return;
          };

          String   DatabaseName = String(StatementBaseTableDescriptor.szDatabase).UpperCase();
          String   TableName = WithoutExtension(String(StatementBaseTableDescriptor.szTableName)).UpperCase();
          String   SearchTarget = TableName + "=" + DatabaseName;

          int TargetTableEntry = TableUsed->IndexOf(SearchTarget);

          if (TargetTableEntry WasNotFound)
          {
            TableUsed->Add(SearchTarget);
          };
      };

      DbiCloseCursor(TemporaryCursor);
    };
};

// Lookup fields

for (int Index = 0; Index < FieldCount; Index++)
{
   TField   *Field = Fields->Fields[Index];

   if (Field->FieldKind == fkLookup)
   {
      if (Field->LookupDataSet != NULL)
      {
          TTable   *LookupDataSet = dynamic_cast<TTable *>(Field->LookupDataSet);

          if (LookupDataSet != NULL)
          {
            String   DatabaseName = LookupDataSet->DatabaseName.UpperCase();
            String   TableName = WithoutExtension(LookupDataSet->TableName).UpperCase();
            String   SearchTarget = TableName + "=" + DatabaseName;

            int TargetTableEntry = TableUsed->IndexOf(SearchTarget);

            if (TargetTableEntry WasNotFound)
            {
               TableUsed->Add(SearchTarget);
            };
          };
      };
   };
};

Note that the WithoutExtension function is needed because of the presence of .dbf for dBase database table names. It is simple to implement, if you need it.

Also note that this will not work with a query that joins tables if the field list is "*". The field list must be "tablename.*" for each table used in the join, otherwise the BDE does not successfully identify the tables in the query.