Universal Field Types

[sir.wally.lewis]

Does UNIDAC have a global way ( for all providers ) to list fields of a table and their datatypes and sizes?

ie. TUNIConnection.GetFieldInfo( TableName : String ; var FieldDataList );

Kind Regards,

Robert.

[ViktorV]

There are several variants of decision:

You can retrieve the information about table fields using the TUniTable component as shown below:

Code: Select all

  UniTable.TableName := TableName;
  UniTable.FieldDefs.Update;
  for i := 0 to UniTable.FieldDefs.Count - 1 do
  ...

You can also retrieve the information about the fields using the TUniMetaData component as shown below:

Code: Select all

  UniMetaData.MetaDataKind := 'Columns';
  UniMetaData.Restrictions.Values['Table_Name'] := TableName;
  UniMetaData.Open;
  while not UniMetaData.Eof do begin
    Format('Table name: %s | FieldName: %s | Fied Type: %s' | Fied Length: %s',     [UniMetaData.FieldByName('TABLE_NAME').AsString, UniMetaData.FieldByName('COLUMN_NAME').AsString, UniMetaData.FieldByName('DATA_TYPE').AsString, UniMetaData.FieldByName('DATA_LENGTH').AsString]);
    UniMetaData.Next;
  end;

[sir.wally.lewis]

Thanks for that, that worked!

on further investigation,

I also need "Primary Key" Column List on a specified table.

Is this possible?

Kind Regards,

Robert.

[ViktorV]

You can retrieve the information about the Primary Key fields using the TUniMetaData component as shown below:

Code: Select all

  UniMetaData.MetaDataKind := 'IndexColumns';
  UniMetaData.Restrictions.Values['Table_Name'] := TableName;
  UniMetaData.Filter := 'INDEX_NAME = "PRIMARY"';
  UniMetaData.Filtered := True;
  UniMetaData.Open;
  while not UniMetaData.Eof do begin
    Format('Column name: %s', [UniMetaData.FieldByName('COLUMN_NAME').AsString]);
    UniMetaData.Next;
  end;

[sir.wally.lewis]

Sad to say,

that does not work, as the "INDEX_NAME" columns is database specific.

eg,

When Database is Firebird this field comes up with something like "RDB$24"

[ViktorV]

Try to use the following code:

Code: Select all

var
  Filter : String;
begin
  UniMetaData.Restrictions.Values['Table_Name'] := TableName;
  UniMetaData.MetaDataKind := 'Constraints';
  UniMetaData.Filter := 'CONSTRAINT_TYPE = "PRIMARY KEY"';
  UniMetaData.Filtered := True;
  UniMetaData.Open;
  if UniMetaData.IsEmpty then
    Exit
  else begin
    if (UpperCase(UniConnection.ProviderName) = 'INTERBASE') OR (UpperCase(UniConnection.ProviderName) = 'SQL Server') then
      Filter := 'INDEX_NAME = ' + QuotedStr(UniMetaData.FieldByName('INDEX_NAME').AsString)
    else
      Filter := 'INDEX_NAME = ' + QuotedStr(UniMetaData.FieldByName('CONSTRAINT_NAME').AsString);
  end;
  UniMetaData.MetaDataKind := 'IndexColumns';
  UniMetaData.Filter := Filter;
  UniMetaData.Open;
  while not UniMetaData.Eof do begin
    ShowMessage(Format('Column name: %s', [UniMetaData.FieldByName('COLUMN_NAME').AsString]));
    UniMetaData.Next;
  end;
end;