Listing Tables and Columns

Forum related to SQLite

Moderators: gto, cipto_kh, EgonHugeist

mac_srv
Fresh Boarder
Fresh Boarder
Posts: 21
Joined: 23.01.2015, 00:04

Listing Tables and Columns

Postby mac_srv » 07.10.2015, 15:11

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
Gold Boarder
Gold Boarder
Posts: 671
Joined: 17.01.2011, 14:17

Re: Listing Tables and Columns

Postby marsupilami » 09.10.2015, 14:33

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
Fresh Boarder
Fresh Boarder
Posts: 21
Joined: 23.01.2015, 00:04

Re: Listing Tables and Columns

Postby mac_srv » 09.10.2015, 23:34

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
Gold Boarder
Gold Boarder
Posts: 671
Joined: 17.01.2011, 14:17

Re: Listing Tables and Columns

Postby marsupilami » 11.10.2015, 00:28

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
Fresh Boarder
Fresh Boarder
Posts: 21
Joined: 23.01.2015, 00:04

Re: Listing Tables and Columns

Postby mac_srv » 12.10.2015, 20:59

Hi Jan,

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

So, following a similar thread:

viewtopic.php?f=24&t=3363&hilit=MetadaType+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
Gold Boarder
Gold Boarder
Posts: 671
Joined: 17.01.2011, 14:17

Re: Listing Tables and Columns

Postby marsupilami » 13.10.2015, 18:50

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
Fresh Boarder
Fresh Boarder
Posts: 21
Joined: 23.01.2015, 00:04

Re: Listing Tables and Columns

Postby mac_srv » 13.10.2015, 20:29

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.


Return to “SQLite”

Who is online

Users browsing this forum: No registered users and 0 guests