🚀 go-pugleaf

I hope you can get what you want from the following code :-

This is for an Access database.

void __fastcall TwSelAppFieldsGen::bContinueClick(TObject *Sender)
{
   String appCode, projDir, appSourceDir;
   String docDirName, docDbName, conStringPart1, conStringPassword;
   String appDatabaseDir, appDatabaseName, docConStringPassword;
   String fullName, conString, tableName, fieldName, dataType;
   String strQuery, dbName, indexFields, testChar;
   String divider, primeKey, ascDesc, indexText;
   int numTables, numFields, fieldPos, fieldSize, typeOfData, pBarProgress;
   int approxFields, numIndexFields, strLength, approxFieldsPerTable;
   TStrings *ListOfTables = new TStringList;
   TStrings *IndexList = new TStringList;
   TADOConnection *dbConnection = new TADOConnection(NULL);
   TADOConnection *docDbConnection = new TADOConnection(NULL);
   TADOTable *tWorkTable = new TADOTable(NULL);
   TFieldDefList *pDefList = tWorkTable->FieldDefList;
   TIndexOptions indexOptions;
   bool primary, ascending;

   // Get Selected Application parameters
   if (!GetSelectedApplicationParameters(wSelAppFieldsGen->lAppSel))
   {
      MessageSound("Information");
      MessageDlg("No Documentation for DLL Folder",
      mtInformation, TMsgDlgButtons() << mbOK,0);
      return;
   }

   appCode = appArray[1];
   projDir = appArray[2];
   appSourceDir = appArray[3];
   docDirName = appArray[10];
   docDbName = appArray[11];
   conStringPart1 = appArray[12];
   conStringPassword = appArray[13];
   appDatabaseDir = appArray[14];
   appDatabaseName = appArray[15];
   approxFieldsPerTable = StrToInt(appArray[16]);

   dbName = appDatabaseDir + "\\" + appDatabaseName;
   docConStringPassword = "";
   divider = ";";

   // Make a connection for storing Field Definitions
   docDbConnection->LoginPrompt = false;
   docDbConnection->Connected = false;
   fullName = docDirName + "\\" + docDbName;
   conString = conStringPart1 + fullName + ";" + docConStringPassword;
   docDbConnection->ConnectionString = conString;
   docDbConnection->Connected = true;

   // Connect Documentation Query and Table to Database
   wDataModule->qFieldDef->Active = false;
   wDataModule->qFieldDef->Connection = docDbConnection;
   wDataModule->qFieldDef->Active = true;
   wDataModule->tFieldDef->Active = false;
   wDataModule->tFieldDef->TableName = "FieldDefinitions";
   wDataModule->tFieldDef->Connection = docDbConnection;
   wDataModule->tFieldDef->Active = true;

   // Delete all records from FieldDefinitions table
   // for this Application
   strQuery = "DELETE FROM FieldDefinitions ";
   strQuery += Format("WHERE ApplCode = '%s'",
       OPENARRAY(TVarRec, (appCode)));
   wDataModule->cGeneralCommand->Connection = docDbConnection;
   wDataModule->cGeneralCommand->CommandText = strQuery;
   wDataModule->cGeneralCommand->Execute();      // Run Query

   wDataModule->tFieldDef->Close();
   wDataModule->tFieldDef->Open();

   // Make a connection for retrieving Field Definitions
   dbConnection->LoginPrompt = false;
   dbConnection->Connected = false;
   conString = conStringPart1 + dbName + ";" + conStringPassword;
   dbConnection->ConnectionString = conString;
   dbConnection->Connected = true;

   // Get all table names
   dbConnection->GetTableNames(ListOfTables, false);

   numTables = ListOfTables->Count;
   approxFields = numTables * approxFieldsPerTable;
   wSelAppFieldsGen->pBar->Min = 0;
   wSelAppFieldsGen->pBar->Max = approxFields;
   wSelAppFieldsGen->pBar->Visible = true;
   pBarProgress = 1;
   wSelAppFieldsGen->pBar->Position = pBarProgress;

   // Loop for all tables
   for (int tblIndex = 0; tblIndex < numTables; tblIndex++)
   {
      tableName = ListOfTables->Strings[tblIndex];
      tWorkTable->Active = false;
      tWorkTable->TableName = tableName;
      tWorkTable->Connection = dbConnection;
      tWorkTable->Active = true;
      // Get Field Definition List
      pDefList->Update();
      numFields = pDefList->Count;
      fieldPos = 0;

      // Check if Primary Index exists
      tWorkTable->GetIndexNames(IndexList);
      indexText = IndexList->Text;
      strLength = indexText.Length();
      if (strLength == 0)    // Check for No Index
      {
         numIndexFields = 0;
      }
      else
      // Get Primary Index Details
      {
//         String indexName = tWorkTable->IndexDefs->Items[0]->Name;
//         String descFields = tWorkTable->IndexDefs->Items[0]->DescFields;
         indexFields = tWorkTable->IndexDefs->Items[0]->Fields;
         indexOptions = tWorkTable->IndexDefs->Items[0]->Options;
         if (indexOptions.Contains(ixPrimary)) {  primary = true;  }
         else                                  {  primary = false; }
         // The following test is not working - always Ascending
         if (indexOptions.Contains(ixDescending)) {  ascending = false; }
         else                                     {  ascending = true;  }
         strLength = indexFields.Length();
         numIndexFields = 1;
         for (int ix = 1; ix < strLength +1; ix++)
         {
            testChar = indexFields.SubString(ix,1);
            if (testChar == divider)
            {
               numIndexFields ++;
            }
         }
      }

      // Loop for all fields
      for (int iFields = 0; iFields < numFields; iFields++)
      {
         fieldName = pDefList->FieldDefs[iFields]->Name;
         typeOfData = pDefList->FieldDefs[iFields]->DataType;
         fieldSize = pDefList->FieldDefs[iFields]->Size;
         fieldPos ++;
         // Set Primary Index Info - if there is an index
         if (numIndexFields > 0)
         {
            if (iFields <= numIndexFields - 1)
            {
               if (primary)     {  primeKey = "Yes";  }
               else             {  primeKey = "";     }
               if (ascending)   {  ascDesc  = "Ascending";  }
               else             {  ascDesc  = "";           }
//               primeKey = "Yes";
//               ascDesc = "Ascending";
            }
            else
            {
               primeKey = "";
               ascDesc = "";
            }
         }
         else
         {
            primeKey = "";
            ascDesc = "";
         }

         // Convert DataType to text - Set FieldSize where required
         switch(typeOfData)
         {
            case 2  : dataType = "Integer";
                      fieldSize = 2;                break;
            case 3  : dataType = "Long Integer";
                      fieldSize = 4;                break;
            case 5  : dataType = "Yes/No";
                      fieldSize = 1;                break;
            case 8  : dataType = "Currency";
                      fieldSize = 8;                break;
            case 11 : dataType = "Date/Time";
                      fieldSize = 8;                break;
            case 14 : dataType = "AutoNumber";
                      fieldSize = 4;                break;
            case 15 : dataType = "OLE Object";
                      fieldSize = 0;                break;
            case 24 : dataType = "Text";            break;
            case 39 : dataType = "Memo";
                      fieldSize = 0;                break;
            default : dataType = "Text";            break;
         }

         // Add record to FieldDefinition table
         wDataModule->qFieldDef->Append();
         wDataModule->qFieldDef->FieldValues["ApplCode"] = appCode;
         wDataModule->qFieldDef->FieldValues["TableName"] = tableName;
         wDataModule->qFieldDef->FieldValues["FieldPos"] = fieldPos;
         wDataModule->qFieldDef->FieldValues["FieldName"] = fieldName;
         wDataModule->qFieldDef->FieldValues["DataType"] = dataType;
         wDataModule->qFieldDef->FieldValues["FieldSize"] = fieldSize;
         wDataModule->qFieldDef->FieldValues["PrimaryKey"] = primeKey;
         wDataModule->qFieldDef->FieldValues["AscendDescend"] = ascDesc;
         wDataModule->qFieldDef->Post();
         pBarProgress ++;
         wSelAppFieldsGen->pBar->Position = pBarProgress;
      }
   }

   wSelAppFieldsGen->pBar->Visible = false;

   // Tidy up
   docDbConnection->Connected = false;
   delete docDbConnection;
   dbConnection->Connected = false;
   delete dbConnection;
   delete ListOfTables;
   delete IndexList;
   delete tWorkTable;

   wMainMenu->Visible = false;
   wSelAppFieldsGen->Close();
   wMainMenu->Visible = true;       // Force Form Show Event
}
//---------------------------------------------------------------------------

Michael Corby


"Mike K" <nospam@nospam.net> wrote in message 
news:46d46ae6$1@newsgroups.borland.com...
> Sorry to need the handholding, but this code:
>
> TADODataSet *DS;
> Conn->OpenSchema(siColumns,NULL,NULL,DS);
>
> returns the error: "arguments are of the wrong type, are out of acceptable 
> range, or are in conflict with one another."
>
> I see that at design time I can open a dataset with a SELECT * query and 
> then have access to FieldDefs, which gives me the field data I need. How 
> do I get this data at runtime? I see GetIndexDefs() as a method, but 
> nothing for FieldDefs.
>
>
>
>
> Vassiliev V. V. wrote:
>> Look at TADOConnection::OpenSchema(). You need schema siColumns.
>>
>> //------------------------------------------
>> Regards,
>> Vassiliev V. V.
>> http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
>> ADO.Net
>> http://www.oledbdirect.com - The fastest way to access MS SQL Server,
>> MS Jet (Access) and Interbase (through OLEDB)
>>
>>
>> "Mike K" <nospam@nospam.net> ???????/???????? ? ???????? ?????????: 
>> news:46d3828b$1@newsgroups.borland.com...
>>> I'm building a database converter that needs to know the field data 
>>> types for every table.
>>>
>>> How is this done? I can use GetTableNames() and GetFieldNames() but I 
>>> still need the data types for each field.
>>>
>>> (Using BCB 5)
>>>
>>> thanks for any help...
>>>
>>