[bug_fixed] Default value read from RDB$DEFAULT_SOURCE

Code samples and contributions from users for ZeosLib's DBOs of version 6.x

Moderators: gto, cipto_kh, EgonHugeist, mdaems

sandeep_c24
Expert Boarder
Expert Boarder
Posts: 158
Joined: 06.11.2005, 01:43

[bug_fixed] Default value read from RDB$DEFAULT_SOURCE

Postby sandeep_c24 » 21.03.2008, 05:30

Hi Mark

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;


There are some other changes as well, but please ignore them and merge the source where DefaultValue is assigned if it looks fine.

Regards

Sandeep

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 22.03.2008, 14:47

I'll patch like this:

Code: Select all

Index: D:/Data/Delphi/ZEOSTesting/src/dbc/ZDbcInterbase6Metadata.pas
===================================================================
--- D:/Data/Delphi/ZEOSTesting/src/dbc/ZDbcInterbase6Metadata.pas   (revision 350)
+++ D:/Data/Delphi/ZEOSTesting/src/dbc/ZDbcInterbase6Metadata.pas   (working copy)
@@ -1672,8 +1672,8 @@
           DefaultValue := GetStringByName('RDB$DEFAULT_SOURCE_DOMAIN');
         if StartsWith(Trim(UpperCase(DefaultValue)), 'DEFAULT') then
         begin
-          DefaultValue := Trim(Copy(DefaultValue,
-            Length('DEFAULT') + 1, Length(DefaultValue)));
+          DefaultValue := Trim(StringReplace(DefaultValue, 'DEFAULT ', '',
+            [rfIgnoreCase]));
         end;
 
         Result.MoveToInsertRow;


Replacing all occurrences is wrong when you have string fields with a default value like 'not filled by user -> filled with default clause'.
Also added a space after DEFAULT.

SVN Rev.351

Mark
Image


Return to “User Contributions”

Who is online

Users browsing this forum: No registered users and 1 guest