Listing Tables and Columns

[mac_srv]

Hi,

Using ZeosLib 7.2.0 Beta in C++Builder XE2 and SQLite 3.8.11.1, I wasn't able to list Columns using the TZSQLMetadata component. I've tested (MetadaType = mdTables) and it works fine, but (MetadaType = mdColumns) shows a empty ZMetaData object (via DBGrid visual component).

Here are the basic property settings.

Code: Select all

ZConnection->ControlsCodePage = cGET_ACP
ZConnection->Database = C:\MyApp\EssentialSQL.db
ZConnection->LibraryLocation = C:\MyApp\sqlite3.dll  //Version 3.8.11.1
ZConnection->Protocol = sqlite-3

ZMetaData->Connection = ZConnection
ZMetaData->MetadaType = mdColumns

DSource->DataSet = ZMetaData

DBGrid->DataSource = DSource

Am I missing something here? I can share the demo database I'm testing.

Thanks in advance.

Marcelo.
P.S. Please alert me if this subject is better on ZeosLib 7.2 Beta's forum.

[marsupilami]

Hello Marcelo,

I think you would need to add the following lines to your example code to make it work:

Code: Select all

ZMetaData->TableName = "YourTableName"
ZMetaData->Open

With best regards,

Jan

[mac_srv]

Hi Jan,

Nice hint! It is working.

The interesting part is that I've used TZSQLMetaData with Firebird and the (MetadaType = mdColumns) option list all tables and their columns, so you do not neet do set (TableName = "YourTableName"). I think this way is better, since I'm using TZSQLMetaData to construct a TreeView of tables and its fields.

Here is the basic code, which uses just one TZSQLMetaData object and just one loop:

Code: Select all

int
TDModTest::FillStructTreeView(TTreeView* ATView) const
{
  int Count = 0;
  // Prepare SQL MetaData.
  ZMetaData->Close();
  ZMetaData->MetadataType = mdColumns;
  ZMetaData->Open();
  // Auxiliary.
  AnsiString STableName, SColumnName, SOld;
  SOld = "";
  TTreeNode* NodeTable;
  TTreeNode* NodeColumn;
  // Add nodes to ATView.
  while ( !ZMetaData->Eof )
  { // Table name.
    STableName = ZMetaData->FieldByName("TABLE_NAME")->AsString;
    // Do not add system's tables.
    bool NameOK = ( !STableName.Pos("RDB$") &&
                    !STableName.Pos("MON$") );
    // Add node for TableName (if OK and different).
    if ( NameOK && (STableName != SOld) )
    {
      NodeTable  = ATView->Items->Add(ATView->Selected, STableName);
      SOld = STableName;
    }
    // Column name.
    SColumnName = ZMetaData->FieldByName("COLUMN_NAME")->AsString;
    // Add child node for ColumnName
    if ( NameOK && (SColumnName != "") )
      NodeColumn = ATView->Items->AddChild(NodeTable, SColumnName);
    // Next item.
    ZMetaData->Next();
    Count++;
  }
  return (Count);
}

Do you think this is a difference in ZeosLib's drivers behavior that deserves to be reported?

Thanks anyway.

Marcelo.

[marsupilami]

Hello Marcelo,

I think this doesn't need to be reported. I am not sure if there is any specification how TZSqlMetaData should behave in the case of an empty identifier (table name) field. I am sure however that you should make sure that wildcard characters are properly escaped if they are part of the table name. There should be some function that does so, but I am sure what its name is currently...
With best regards,

Jan

[mac_srv]

Hi Jan,

I've tested (TableName = %) without any luck.

So, following a similar thread:

http://zeoslib.sourceforge.net/viewtopi ... +mdColumns

I've decided to change the code setting up TableName properly with the result of a ZConnection->GetTableNames() call.

Here it is:

Code: Select all

int
TDModTest::FillStructTreeView(TTreeView* ATView) const
{
  int Count = 0;
  // Prepare SQL MetaData.
  ZMetaData->MetadataType = mdColumns;
  // Auxiliary.
  TStringList* SLTables = new TStringList();
  try
  {
    String STableName, SColumnName, SOld;
    SOld = "";
    TTreeNode* NodeTable;
    // Get Tables names.
    ZConnection->GetTableNames("", SLTables);
    // Add nodes to ATView.
    for (int t=0; t<SLTables->Count; t++)
    { // Counting table nodes.
      Count++;
      // Table name.
      STableName = SLTables->Strings[t];
      // Do not add system's tables.
      bool NameOK = ( !STableName.Pos("RDB$") &&
                      !STableName.Pos("MON$") );
      // Add node for TableName (if OK and different).
      if ( NameOK && (STableName != SOld) )
      {
        NodeTable  = ATView->Items->Add(ATView->Selected, STableName);
        SOld = STableName;
        // Add Columns nodes usign TZSQLMetadata.
        ZMetaData->Close();
        ZMetaData->TableName = STableName;
        ZMetaData->Open();
        while (!ZMetaData->Eof)
        { // Column name.
          SColumnName = ZMetaData->FieldByName("COLUMN_NAME")->AsString;
          // Add child node for ColumnName
          if ( SColumnName != "" )
            ATView->Items->AddChild(NodeTable, SColumnName);
          // Next item.
          ZMetaData->Next();
        }
      }
    }// for (int t...
  }
  __finally
  {
    if (SLTables) delete (SLTables);
  }
  return (Count);
}

Now it is working with Firebird and SQLite. I'll try another DBMS too.

Thanks for your help.

Best,

Marcelo.

[marsupilami]

Hello Marcelo,

hmmm - I have to check that. There were some things going on about searching for tables with wildcards some time ago. I did not yet have the time to correct that behaviour. I will have to check these things in the code... I hope to have the time to do so during the upcoming weekend..

Best regards,

Jan

[mac_srv]

Jan

While searching for information, I've read something about problems with wildchars. At a glance, I concluded that the char "_" plays as "%" and that was a source of problems with tables named as "TABLE_NAME".

On the other hand, if TZSQLMetadata"s property "TableName = %" works, so it will be another way to establish a common behavior in TZSQLMetadata class.

But no rush with this subject. I've shared the Structure+TTreeView code in order to help other readers.

Thanks,

Marcelo.