Metadata and Stored Procedures

sandeep_c24 · Post by **sandeep_c24** » 03.02.2008, 11:20

I am trying to retrieve the source code for Stored procedures using Zeos metadata component but it does not look like it is fetched. Am I missing something or has it nor been implemented yet?

If it has not been implemented can anyone help me implement this?

Regards

Sandeep

Post by **mdaems** » 03.02.2008, 13:21

Database?

sandeep_c24 · Post by **sandeep_c24** » 03.02.2008, 20:27

I would like to do that for Firebird, Interbase, Postgres, SQLite, MySQL, MSSQL and others if possible.

Regards

Sandeep

sandeep_c24 · Post by **sandeep_c24** » 04.02.2008, 08:15

Types/Domains are also not working. Can anything be done about them as well?

Regards

Sandeep

Post by **mdaems** » 04.02.2008, 11:58

Hi Sandeep,

Adding the procedure source to the StoredProcedure metadata may be fairly easy as the framework is already available. You could even reuse one of the 3 reserved fields that are available already.
Main problem may be the datatype of the column. I have the impression that until now all metadata columns are limited to 255 char strings or numeric types.

Just have a look at the ZDbcMetaData.pas and ZDbc<database>Metadata.pas files.

Concerning the other metadatatypes you are asking for : adding/modifying should be piece of cake for the person who implements the procedure sources as the code is very modular. The more, I've seen the 'TypeInfo'.

As you might know by now : good and safe contributions can be quickly integrated when posted in the 'User patches', 'User contributions' and 'Feature Requests' forums. For bug fixes, please use the bug tracker. Please keep bug fixes separated from new features. These have a different priority.

Mark

sandeep_c24 · Post by **sandeep_c24** » 07.02.2008, 10:31

I have modified the code in ZDbcInterbase6Metadata.pas to display the TYPE_NAME and COLUMN_SIZE correctly. Please check the following and merge if it looks ok.

Please also look at FIELD_SUB_TYPE and suggest how to handle SUB_TYPE 0.

Please replace lines 1685 to 1690 with following lines.

Code: Select all

          Ord(ConvertInterbase6ToSqlType(TypeName, SubTypeName))); //DATA_TYPE
        // SC  - TYPE_NAME
        // !!!!!!!!!!!!!!!!!!!! Not Sure what FIELD_SUB_TYPE 0 is !!!!!!!!!!!!!!!!!!!!
        if  (TypeName = 16) then
        begin
          if (SubTypeName = 0) then
            Result.UpdateString(6, 'NULL');
          if (SubTypeName = 1) then
            Result.UpdateString(6, 'NUMERIC');
          if (SubTypeName = 2) then
            Result.UpdateString(6, 'DOUBLE');
        end
        else
        // SC
        Result.UpdateString(6,GetStringByName('RDB$TYPE_NAME'));    //TYPE_NAME
        
        // SC
        // !!!!!!!!!!!!!!!!!!!! Not Sure what FIELD_SUB_TYPE 0 is !!!!!!!!!!!!!!!!!!!!
        if  (TypeName = 16 )
        and (SubTypeName in [0, 1, 2]) then
          Result.UpdateInt(7, GetIntByName('RDB$FIELD_PRECISION')) //COLUMN_SIZE
        else
        // SC
        Result.UpdateInt(7, GetIntByName('RDB$FIELD_LENGTH'));    //COLUMN_SIZE
        
        Result.UpdateNull(8);    //BUFFER_LENGTH

Regards

Sandeep

Post by **mdaems** » 07.02.2008, 14:31

Thanks for the patch. I hope you can answer following questions so patching may happen quickly.

Did a quick check here
http://www.felix-colibri.com/papers/db/ ... ables.html

I have some questions left.
Why numeric/double instead of numeric/decimal as in the article.
Instead of Result.UpdateString(6, 'NULL') you better use Result.UpdateNull(6), I think.

Reading the article I wonder why you only treat Typename = 16 (and not 7 or 8 ) this way.

Can you demonstrate the difference between field_length, field_precision and field_scale using some samples?

Mark

sandeep_c24 · Post by **sandeep_c24** » 07.02.2008, 22:15

>Why numeric/double instead of numeric/decimal as in the article.
That should be decimal not double, not sure why I typed double.

>Instead of Result.UpdateString(6, 'NULL') you better use Result.UpdateNull(6), I think.
Ok, I'll do that.

>Reading the article I wonder why you only treat Typename = 16 (and not 7 or 8 ) this way.
I was just testing for 16, but I think you are right it should be done for 7 and 8 as well.

>Can you demonstrate the difference between field_length, field_precision and field_scale using some samples?
In Firebird 2.0 Field_Length is 8 (I think) for above types but what I would like to see is Field_Precision and Field_Scale for field of type numeric(14, 4). This is what I get when I use SQLite. I am at office right now and do not have acces to sql command that gets the metadata. I can post it later if you want me to.

Regards

Sandeep

Post by **mdaems** » 07.02.2008, 22:40

Looks reasonable... Can you post the full corrected function, next time? (Easier to patch

)

I'll ask cipto_kh, our unofficial Firebird specialist for a second opinion about the change. If he agrees and nobody else objects, your patch will be in.

Mark

sandeep_c24 · Post by **sandeep_c24** » 08.02.2008, 10:07

Hi Mark

I have made some more changes to the function. Please have a look and see if they look alright.

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 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
          DefaultValue := Trim(Copy(DefaultValue,
            Length('DEFAULT') + 1, Length(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;
          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.InsertRow;
      end;
      Close;
    end;

    AddResultSetToCache(Key, Result);
  end;
end;

Regards

Sandeep

sandeep_c24 · Post by **sandeep_c24** » 09.02.2008, 11:10

Hi Mark

I had a look at the unit ZDbcMetadata and all the reserved columns are of type string of size 255. Since the source of the procedure could be quite big how long should the length of one of the reserved column be set to?

Is it ok to use reserved column or should a new column be added?

Regards

Sandeep

Post by **mdaems** » 09.02.2008, 20:57

It would be OK to use a reserved one, but I think the datatype needs to be something like Text or BLOB. At least of unlimited length. Not sure if that's possible for this kind of datasets, however. I think that will be your biggest problem to solve. Other stuff is just copy/paste/imitate. If you need help you can ask using pm, skype , msn or yahoo. I haven't time to look into it right now, however.
Maybe a request from my side : can you add some option to the Metadata component to disable source fetching? It may be quite slow to get sources.

Mark

sandeep_c24 · Post by **sandeep_c24** » 11.02.2008, 07:03

I have added a boolean property called GetSource to TZSQLMetadat to control fetching of the source.

Is the property name ok?

Regards

Sandeep

Post by **mdaems** » 11.02.2008, 10:18

I would call it 'IncludeSources' as 'Get...' is more like a method name.

Mark

sandeep_c24 · Post by **sandeep_c24** » 11.02.2008, 10:31

Ok, I have done that.

Regards

Sandeep