Metadata: mdTables

[Roaster]

Hi,

I'm currently trying to fetch the tables metadata of a Firebird 2.x database. Especially I'm interested in the description of each table I've added before.

When using a tool like FlameRobin or SQl Manager 2005 Lite to look at the description of each table I can see the correct text.

However when using TZSQLMetadata, setting MetadataType to mdTables, it returns all available tables and a column REMARKS which is empty in any case.

How can I access this column and read the description? Of course I could directly read the system table rdb$relations and use column rdb$description but it would be more convenient to use TZSQLMetadata instead.

It may be worth noting that I do get the remarks/description of MetadataType mdColumns.

So I guess it is just an issue with type mdTables.

[mdaems]

Hi Roaster...
Have a look at the code below. It seems this code only fills 2 fields on the result dataset.

I think with a little effort you can complete these?
Please make sure the columns you use from the system tables are available in Interbase and older firebird versions too... If not, could you tell what's only available in which version, so the queries can be made driver dependent?
If you can send me a reasonable patch, it will be applied to the standard zeos distribution.

Form ZDbcMetadata.pas:

Code: Select all

  TableColumns: array[1..TableColumnCount]
    of TZMetadataColumnDef =(
    (Name: 'TABLE_CAT'; SQLType: stString; Length: 255),
    (Name: 'TABLE_SCHEM'; SQLType: stString; Length: 255),
    (Name: 'TABLE_NAME'; SQLType: stString; Length: 255),
    (Name: 'TABLE_TYPE'; SQLType: stString; Length: 255),
    (Name: 'REMARKS'; SQLType: stString; Length: 255)
  );

From ZDbcInterbase6.pas :

Code: Select all

function TZInterbase6DatabaseMetadata.GetTables(const Catalog: string;
  const SchemaPattern: string; const TableNamePattern: string;
  const Types: TStringDynArray): IZResultSet;
var
  Key: string;
  SQL, TableType: string;
  LTableNamePattern: string;
  BLR: IZBlob;
  I, SystemFlag, ViewContext: Integer;
begin
  Key := GetTablesMetaDataCacheKey(Catalog,SchemaPattern,TableNamePattern,Types);
  Result := GetResultSetFromCache(Key);
  if Result = nil then
  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 FROM RDB$RELATIONS a'
      + ' JOIN RDB$RELATION_FIELDS b ON a.RDB$RELATION_NAME'
      + '=b.RDB$RELATION_NAME';

    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 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.UpdateNull(5);
          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.UpdateNull(5);
              Result.InsertRow;
            end;
          end;
        end;
            
      end;
      Close;
    end;

    AddResultSetToCache(Key, Result);
  end;
end;

[Roaster]

Thanks mdaems,

I appreciate your help very much and I already found those lines but needed confirmation about possible changes.

Anyway it seems to be unit ZDbcInterbase6Metadata.pas and changed three lines commented in the code below.

I cannot speak for Interbase databases though, for this reason someone who is able to test should jump in and test those changes. OTH when looking at function GetColumns I noticed that at least Interbase 5 knows about RDB$DESCRIPTION, too.

Code: Select all

function TZInterbase6DatabaseMetadata.GetTables(const Catalog: string;
  const SchemaPattern: string; const TableNamePattern: string;
  const Types: TStringDynArray): IZResultSet;
var
  Key: string;
  SQL, TableType: string;
  LTableNamePattern: string;
  BLR: IZBlob;
  I, SystemFlag, ViewContext: Integer;
begin
  Key := GetTablesMetaDataCacheKey(Catalog,SchemaPattern,TableNamePattern,Types);
  Result := GetResultSetFromCache(Key);
  if Result = nil then
  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 FROM RDB$RELATIONS a'
// Added RDB$DESCRIPTION in result set //
      + ' 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';

    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 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.UpdateNull(5);
// Changed this line
          Result.UpdateString(5, GetStringByName('RDB$DESCRIPTION'));
          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.UpdateNull(5);
// Changed this line
              Result.UpdateString(5, GetStringByName('RDB$DESCRIPTION'));
              Result.InsertRow;
            end;
          end;
        end;
            
      end;
      Close;
    end;

    AddResultSetToCache(Key, Result);
  end;
end;

Also I notice that RDB$DESCRIPTION is actually defined as type blob in the Firebird database but the result set field REMARKS is just able to hold up to 255 characters:

Code: Select all

TableColumns: array[1..TableColumnCount] 
    of TZMetadataColumnDef =( 
    (Name: 'TABLE_CAT'; SQLType: stString; Length: 255), 
    (Name: 'TABLE_SCHEM'; SQLType: stString; Length: 255), 
    (Name: 'TABLE_NAME'; SQLType: stString; Length: 255), 
    (Name: 'TABLE_TYPE'; SQLType: stString; Length: 255), 
    (Name: 'REMARKS'; SQLType: stString; Length: 255)  <<< Possible conflict
  ); 

So there might by a conflict later. This seems to be also true for fetching the descriptions when using type mdColumns, too.

cu,
Michael

[Roaster]

Hmm,

something strange happens. I added those changes mentioned above. Now when manually creating a TZSQLMetadata object it does not return anything from the REMARKS field.

Code: Select all

function TDBDescription.DBFetchTableMetaData(TableName : string) : string;
// Fetch the tables metadata from the database; returns the tables description
var
  ZMetadata : TZSQLMetadata;
begin
  Result := '';
  ZMetaData := TZSQLMetadata.Create(Nil);
  try
    ZMetadata.Connection := FConnection;
    ZMetadata.MetadataType := mdTables;
    ZMetadata.TableName := TableName;
    ZMetadata.Active := True;
    if (ZMetadata.RecordCount > 0) and (ZMetadata.FindField('REMARKS') <> Nil) then
    begin
      ZMetadata.First;
      Result := ZMetadata.FieldByName('REMARKS').AsString; <<< Is always empty
    end;
  finally
    ZMetadata.Active := False;
    FreeAndNil(ZMetaData);
  end;
end;

It even does not stop at any breakpoint in unit ZDbcInterbase6Metadata.pas where I made those changes, so what's the deal?

Btw I guess I've found a little bug in unit ZDbcInterbase6. The statement 'CRATE DATABSE...' has got two typos. I will file a Mantis report.

Code: Select all

procedure TZInterbase6Connection.Open;
var
  DPB: PChar;
  FDPBLength: Word;
  DBName: array[0..512] of Char;
begin
  if not Closed then Exit;

  if TransactIsolationLevel = tiReadUncommitted then
    raise EZSQLException.Create('Isolation level do not capable');

  DPB := GenerateDPB(Info, FDPBLength, FDialect);

  if HostName <> '' then
  begin
    if Port <> 3050 then
      StrPCopy(DBName, HostName + '/' + IntToStr(Port) + ':' + Database)
    else
      StrPCopy(DBName, HostName + ':' + Database)
  end else
  StrPCopy(DBName, Database);

  try
    { Create new db if needed }
    if Info.Values['createNewDatabase'] <> '' then
    begin
      CreateNewDatabase(Info.Values['createNewDatabase']);
      { Logging connection action }
      DriverManager.LogMessage(lcConnect, FPlainDriver.GetProtocol,
        Format('CRATE DATABSE "%s" AS USER "%s"', [Info.Values['createNewDatabase'], User]));
    end;

Done: Mantis Report 79

SOLVED: I had to recompile some more (ZEOS) units to retrieve the REMARKS content again.

[mdaems]

Thanks for the bug report! As it's just a typo in a log message, that won't take long to fix!

Concerning your patch above : that one will be applied as wel... What about adding Copy(...,1,255) ? (No LeftStr, because it's not available in D5)

My advice when changing things to Zeoslib : before testing build the changed package and all 'higher' packages before reinstalling the components.
Sequence : ZCore, ZParseSql, ZPlain, ZDbc, ZComponent and ZComponentDesign. This means : for a Zdbc change you have to compile ZDbc, ZComponent and ComponentDesign.
I suppose you figured this out already, but maybe this information helps somebody else.

[Roaster]

Concerning your patch above : that one will be applied as wel... What about adding Copy(...,1,255) ? (No LeftStr, because it's not available in D5)

Thanks for your answer and patch! when mentioning the string limitation of 255 characters I just meant that the Firebird database seems to be capable of holding more than those 255 characters in the description BLOB field.

I guess when using GetStringByName('RDB$DESCRIPTION') this will just fill the REMARKS field up to 255 characters and not more, simply because there is no more space left.
But what happens if RDB$DESCRIPTION has got more than 255 characters? At the moment I guess it will be cut off.

Is this intended?

[mdaems]

Intended... No idea.

I suppose this limit could be lifted. But that probably impacts the way these data are schown by components. Now the data is visible in a simple grid. Making it a blob field probably changes this behaviour. So it could be a feature request and eventually be added to a future release. I will not add it now as I'm busy creating the 6.6.2-rc version and we can't change behaviour now.

Concerning the copy(...,1,255) : you're probably right... I'm mainly programming in PL/SQL and there you get exeptions when putting a big string in a small variable. But you must admit : now it will always be clear that truncation occurs. (It's already in SVN, I will not remove it unless there are problems)

Mark

[Roaster]

Concerning the copy(...,1,255) : you're probably right... I'm mainly programming in PL/SQL and there you get exeptions when putting a big string in a small variable. But you must admit : now it will always be clear that truncation occurs. (It's already in SVN, I will not remove it unless there are problems)

Mark,
of course it's probably clearer to read and to understand, if using a truncating Copy but I took that idea from other coding where a limitation exists, too, and no Copy function was used either. So I liked the idea of being code-conform.

Thanks for adding this to SVN.

P.S when will you expect a 6.6.2-rc release?

[mdaems]

Actually, I'm writing the ant script now, starting from the SVN release scripts. Shouldn't take too long, but I do it at home when I have the time, so it all depends on wife, kids, family, hobbies, ... So in a few weeks should be a reasonable timing. Looking for a Christmas gift ?

There's also the dilemma about what to add. Your opinion?
- binaries (bpl's) -->not all Delphi versions available (and what if I would install 'not official' versions, would the result be legal?)
- outdated documentation
- doxygen class information
- dll's for the databases. Which are legal? What to include to avoid licence trouble?
- build scripts
- test suite

Mark