I have changed the way DEFAULT was removed from RDB$DEFAULT_SOURCE.
Code: Select all
function TZInterbase6DatabaseMetadata.GetColumns(const Catalog: string;
const SchemaPattern: string; const TableNamePattern: string;
const ColumnNamePattern: string): IZResultSet;
var
Key: string;
SQL, Where, ColumnName, DefaultValue: string;
TypeName, SubTypeName, FieldScale: integer;
LTableNamePattern, LColumnNamePattern: string;
begin
Key := Format('get-columns:%s:%s:%s:%s',
[Catalog, SchemaPattern, TableNamePattern, ColumnNamePattern]);
Result := GetResultSetFromCache(Key);
if Result = nil then
begin
Result := ConstructVirtualResultSet(TableColColumnsDynArray);
LTableNamePattern := ConstructNameCondition(TableNamePattern,
'a.RDB$RELATION_NAME');
LColumnNamePattern := ConstructNameCondition(ColumnNamePattern,
'a.RDB$FIELD_NAME');
if StrPos(PChar(ServerVersion), 'Interbase 5') <> nil then
begin
SQL := 'SELECT a.RDB$RELATION_NAME, a.RDB$FIELD_NAME, a.RDB$FIELD_POSITION,'
+ ' a.RDB$NULL_FLAG, b. RDB$FIELD_LENGTH, b.RDB$FIELD_SCALE,'
+ ' c.RDB$TYPE_NAME, b.RDB$FIELD_TYPE, b.RDB$FIELD_SUB_TYPE,'
+ ' b.RDB$DESCRIPTION, b.RDB$CHARACTER_LENGTH, b.RDB$FIELD_SCALE'
+ ' as RDB$FIELD_PRECISION, a.RDB$DEFAULT_SOURCE, b.RDB$DEFAULT_SOURCE'
+ ' as RDB$DEFAULT_SOURCE_DOMAIN FROM RDB$RELATION_FIELDS a'
+ ' JOIN RDB$FIELDS b ON (b.RDB$FIELD_NAME = a.RDB$FIELD_SOURCE)'
+ ' LEFT JOIN RDB$TYPES c ON b.RDB$FIELD_TYPE = c.RDB$TYPE'
+ ' and c.RDB$FIELD_NAME = ''RDB$FIELD_TYPE''';
Where := LTableNamePattern;
if LColumnNamePattern <> '' then
begin
if Where = '' then
Where := LColumnNamePattern
else Where := Where + ' AND ' + LColumnNamePattern;
end;
if Where <> '' then
Where := ' WHERE ' + Where;
SQL := SQL + Where + ' ORDER BY a.RDB$RELATION_NAME, a.RDB$FIELD_POSITION';
end
else
begin
SQL := ' SELECT a.RDB$RELATION_NAME, a.RDB$FIELD_NAME, a.RDB$FIELD_POSITION,'
+ ' a.RDB$NULL_FLAG, a.RDB$DEFAULT_VALUE, b. RDB$FIELD_LENGTH,'
+ ' b.RDB$FIELD_SCALE, c.RDB$TYPE_NAME, b.RDB$FIELD_TYPE,'
+ ' b.RDB$FIELD_SUB_TYPE, b.RDB$DESCRIPTION, b.RDB$CHARACTER_LENGTH,'
+ ' b.RDB$FIELD_PRECISION, a.RDB$DEFAULT_SOURCE, b.RDB$DEFAULT_SOURCE'
+ ' as RDB$DEFAULT_SOURCE_DOMAIN, a.RDB$FIELD_SOURCE,'
+ ' b.RDB$COMPUTED_SOURCE '
+ ' FROM RDB$RELATION_FIELDS a'
+ ' JOIN RDB$FIELDS b ON (b.RDB$FIELD_NAME = a.RDB$FIELD_SOURCE)'
+ ' LEFT JOIN RDB$TYPES c ON (b.RDB$FIELD_TYPE = c.RDB$TYPE'
+ ' and c.RDB$FIELD_NAME = ''RDB$FIELD_TYPE'')';
Where := LTableNamePattern;
if LColumnNamePattern <> '' then
begin
if Where = '' then
Where := LColumnNamePattern
else Where := Where + ' AND ' + LColumnNamePattern;
end;
if Where <> '' then
Where := ' WHERE ' + Where;
SQL := SQL + Where + ' ORDER BY a.RDB$RELATION_NAME, a.RDB$FIELD_POSITION';
end;
with GetConnection.CreateStatement.ExecuteQuery(SQL) do
begin
while Next do
begin
TypeName := GetIntByName('RDB$FIELD_TYPE');
SubTypeName := GetIntByName('RDB$FIELD_SUB_TYPE');
FieldScale := GetIntByName('RDB$FIELD_SCALE');
ColumnName := GetStringByName('RDB$FIELD_NAME');
DefaultValue := GetStringByName('RDB$DEFAULT_SOURCE');
if DefaultValue = '' then
DefaultValue := GetStringByName('RDB$DEFAULT_SOURCE_DOMAIN');
if StartsWith(Trim(UpperCase(DefaultValue)), 'DEFAULT') then
begin
// Following code stuffs up Default value for column for field
// PHONE_NO in table DEPARTMENT in EMPLOYEE database.
// It is better to replace DEFULT with ''.
// DefaultValue := Trim(Copy(DefaultValue,
// Length('DEFAULT') + 1, Length(DefaultValue)));
DefaultValue := StringReplace(DefaultValue, 'DEFAULT', '',
[rfReplaceAll, rfIgnoreCase]);
DefaultValue := Trim(DefaultValue);
end;
Result.MoveToInsertRow;
Result.UpdateNull(1); //TABLE_CAT
Result.UpdateNull(2); //TABLE_SCHEM
Result.UpdateString(3,
GetStringByName('RDB$RELATION_NAME')); //TABLE_NAME
Result.UpdateString(4, ColumnName); //COLUMN_NAME
Result.UpdateInt(5,
Ord(ConvertInterbase6ToSqlType(TypeName, SubTypeName))); //DATA_TYPE
// SC - Begin
// TYPE_NAME
case TypeName of
7 : Result.UpdateString(6, 'SMALLINT');
8 : Result.UpdateString(6, 'INTEGER' );
16 :
begin
if (SubTypeName = 0) then
Result.UpdateString(6, GetStringByName('RDB$TYPE_NAME'));
if (SubTypeName = 1) then
Result.UpdateString(6, 'NUMERIC');
if (SubTypeName = 2) then
Result.UpdateString(6, 'DECIMAL');
end;
14 : Result.UpdateString(6, 'CHAR');
27 : Result.UpdateString(6, 'DOUBLE PRECISION'); // Instead of DOUBLE.
37 : Result.UpdateString(6, 'VARCHAR'); // Instead of VARYING
else Result.UpdateString(6, GetStringByName('RDB$TYPE_NAME'));
end;
// Result.UpdateString(6,GetStringByName('RDB$TYPE_NAME')); //TYPE_NAME
// SC - End
// SC - Begin
// COLUMN_SIZE.
case TypeName of
7, 8 : Result.UpdateInt(7, 0);
16 : Result.UpdateInt(7, GetIntByName('RDB$FIELD_PRECISION'));
else Result.UpdateInt(7, GetIntByName('RDB$FIELD_LENGTH'));
end;
// Result.UpdateInt(7, GetIntByName('RDB$FIELD_LENGTH')); //COLUMN_SIZE
// SC - End
Result.UpdateNull(8); //BUFFER_LENGTH
if FieldScale < 0 then
Result.UpdateInt(9, -1 * FieldScale) //DECIMAL_DIGITS
else Result.UpdateInt(9, 0); //DECIMAL_DIGITS
Result.UpdateInt(10, 10); //NUM_PREC_RADIX
if GetIntByName('RDB$NULL_FLAG') <> 0 then
Result.UpdateInt(11, Ord(ntNoNulls)) //NULLABLE
else Result.UpdateInt(11, Ord(ntNullable));
Result.UpdateString(12,
Copy(GetStringByName('RDB$DESCRIPTION'),1,255)); //REMARKS
Result.UpdateString(13, DefaultValue); //COLUMN_DEF
Result.UpdateNull(14); //SQL_DATA_TYPE
Result.UpdateNull(15); //SQL_DATETIME_SUB
Result.UpdateInt(16,
GetInt(7)); //CHAR_OCTET_LENGTH
Result.UpdateInt(17,
GetIntByName('RDB$FIELD_POSITION') + 1); //ORDINAL_POSITION
if IsNullByName('RDB$NULL_FLAG') then
Result.UpdateString(18, 'YES') //IS_NULLABLE
else Result.UpdateString(18, 'NO'); //IS_NULLABLE
Result.UpdateNullByName('AUTO_INCREMENT');
if CompareStr(ColumnName, UpperCase(ColumnName)) = 0 then
Result.UpdateBooleanByName('CASE_SENSITIVE', False)
else
Result.UpdateBooleanByName('CASE_SENSITIVE', True);
Result.UpdateBooleanByName('SEARCHABLE', True);
Result.UpdateBooleanByName('WRITABLE', True);
Result.UpdateBooleanByName('DEFINITELYWRITABLE', True);
Result.UpdateBooleanByName('READONLY', False);
Result.UpdateStringByName('UDT', GetStringByName('RDB$FIELD_SOURCE'));
Result.UpdateStringByName('COMPUTED_SOURCE',
GetStringByName('RDB$COMPUTED_SOURCE'));
Result.InsertRow;
end;
Close;
end;
AddResultSetToCache(Key, Result);
end;
end;
Regards
Sandeep
