Page 1 of 1
Listing Tables and Columns
Posted: 07.10.2015, 15:11
by 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.
Re: Listing Tables and Columns
Posted: 09.10.2015, 14:33
by 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
Re: Listing Tables and Columns
Posted: 09.10.2015, 23:34
by 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.
Re: Listing Tables and Columns
Posted: 11.10.2015, 00:28
by 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
Re: Listing Tables and Columns
Posted: 12.10.2015, 20:59
by 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.
Re: Listing Tables and Columns
Posted: 13.10.2015, 18:50
by 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
Re: Listing Tables and Columns
Posted: 13.10.2015, 20:29
by 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.