I'm using TZMetadata on a Firebird 2.0 database to receive the tables, views, procedures. etc.
mdTables works fine for TABLE objects, but not for VIEWs.
Examples:
Some VIEWs are returned correnct: one row, containing the VIEW name and the type "VIEW". This VIEWs have a VIEW_CONTEXT = 0.
Some VIEWs are returned in multiple rows - this happends if a VIEW returns columns from different tables (VIEW_CONTEXT is 0(?), 1, 2, 3, 4...)
For all these VIEWs, TZMetadata returns "TABLE" as type.
Some VIEWs can not be received from TZMetadata: If mdTables is set and TableName := 'MySpecialView', an access violation is thrown. This happends if a VIEW have multiple VIEW_CONTEXTs greater than 0.
So, here is the old SQL statement to receive all tables and views:
Code: Select all
SQL := 'SELECT DISTINCT a.RDB$RELATION_NAME, b.RDB$SYSTEM_FLAG,'
+ ' b.RDB$VIEW_CONTEXT, a.RDB$VIEW_SOURCE FROM RDB$RELATIONS a'
+ ' JOIN RDB$RELATION_FIELDS b ON a.RDB$RELATION_NAME'
+ '=b.RDB$RELATION_NAME';
The access violation comes from:
Code: Select all
if ViewContext = 0 then
BLR := GetBlobByName('RDB$VIEW_SOURCE');
if BLR.IsEmpty then
This code is located in ZDbcInterbase6Metadata.pas.
Here is a corrected version of the UncachedGetTables method:
Code: Select all
function TZInterbase6DatabaseMetadata.UncachedGetTables(const Catalog: string;
const SchemaPattern: string; const TableNamePattern: string;
const Types: TStringDynArray): IZResultSet;
var
SQL, TableType: string;
LTableNamePattern: string;
BLR: IZBlob;
I, SystemFlag, ViewContext: Integer;
begin
Result := ConstructVirtualResultSet(TableColumnsDynArray);
LTableNamePattern := ConstructNameCondition(TableNamePattern,
'a.RDB$RELATION_NAME');
// SQL := 'SELECT DISTINCT a.RDB$RELATION_NAME, b.RDB$SYSTEM_FLAG,'
// + ' b.RDB$VIEW_CONTEXT, a.RDB$VIEW_SOURCE, a.RDB$DESCRIPTION FROM RDB$RELATIONS a'
// + ' JOIN RDB$RELATION_FIELDS b ON a.RDB$RELATION_NAME'
// + '=b.RDB$RELATION_NAME';
SQL := 'SELECT DISTINCT a.RDB$RELATION_NAME, a.RDB$SYSTEM_FLAG, '
+ ' a.RDB$VIEW_SOURCE, a.RDB$DESCRIPTION FROM RDB$RELATIONS a';
if LTableNamePattern <> '' then
SQL := SQL + ' WHERE ' + LTableNamePattern;
with GetConnection.CreateStatement.ExecuteQuery(SQL) do
begin
while Next do
begin
SystemFlag := GetIntByName('RDB$SYSTEM_FLAG');
// ViewContext := GetIntByName('RDB$VIEW_CONTEXT');
// if SystemFlag = 0 then
// begin
// if ViewContext = 0 then
// BLR := GetBlobByName('RDB$VIEW_SOURCE');
// if BLR.IsEmpty then
// TableType := 'TABLE'
// else TableType := 'VIEW';
// end else
// TableType := 'SYSTEM TABLE';
if SystemFlag = 0 then
begin
if IsNull(FindColumn('RDB$VIEW_SOURCE')) then
TableType := 'TABLE'
else
TableType := 'VIEW';
end else
TableType := 'SYSTEM TABLE';
if Length(Types) = 0 then
begin
Result.MoveToInsertRow;
Result.UpdateNull(1);
Result.UpdateNull(2);
Result.UpdateString(3, GetStringByName('RDB$RELATION_NAME'));
Result.UpdateString(4, TableType);
Result.UpdateString(5, Copy(GetStringByName('RDB$DESCRIPTION'),1,255));
Result.InsertRow;
end
else begin
for I := 0 to High(Types) do
begin
if Types[I] = TableType then
begin
Result.MoveToInsertRow;
Result.UpdateNull(1);
Result.UpdateNull(2);
Result.UpdateString(3, GetStringByName('RDB$RELATION_NAME'));
Result.UpdateString(4, TableType);
Result.UpdateString(5, Copy(GetStringByName('RDB$DESCRIPTION'),1,255));
Result.InsertRow;
end;
end;
end;
end;
Close;
end;
end;
ViewContext isn't used anymore.
To determine if a relation is a table or a view, RDB$VIEW_SOURCE is checked for null (this field contains the VIEW-body for VIEWs and NULL for tables).
I don't know if this is the corrent way to get all views from a firebird database. But it's working for me.
Any firebird developers around to verify these changes?
Edit: used version is 6.6.4 stable
