Zeoslib Portal

Posted: **16.02.2009, 13:31**

Hi,

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 distinct doesn't make sense, because VIEW_CONTEXT can have different values for each field in the view.

The access violation comes from:

Code: Select all

          if ViewContext = 0 then
          BLR := GetBlobByName('RDB$VIEW_SOURCE');
          if BLR.IsEmpty then

BLR is nil if ViewContext is not 0.

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;

This version doesn't perform a join to the RDB$RELATION_FIELDS, so it is guaranteed that only on row per view or table is returned.

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

Posted: **25.02.2009, 10:03**

SVN Rev. 596.

I hope I can include this in 6.6.5, but I'm waiting for feedback from some other FB users...

Mark

Zeoslib Portal

TZMetadata Access Violation and duplicated rows

TZMetadata Access Violation and duplicated rows