Thursday, 6 September 2007

Accessing Interbase/Firebird Metadata in Delphi

WARNING: Interbase and Firebird system tables are not for the faint hearted. It is not recommended that you alter anything in these tables if you ever want to use them again - and keep your job. Major stuff-ups can occur.

I was looking at some old Delphi 7 code of mine when I was attempting to get in behind the scenes of Interbase and Firebird tables and fields to learn a little more about them. I came across these little pieces of information I'd like to share.

The following will list all the tables in one grid and, for each table, list all the fields in another grid. Drop on your TIBDatabase and direct it at your Interbase or Firebird server.

Now drop a TIBTransaction, TIBQuery, TDataSource, and a TDBGrid and connect them all.

Enter the following query into the IBQuery1...

  SELECT DISTINCT RDB$RELATION_NAME as MyTable
FROM RDB$RELATION_FIELDS
WHERE RDB$SYSTEM_FLAG=0
AND RDB$VIEW_CONTEXT IS NULL
ORDER BY RDB$RELATION_NAME


and make IBQuery1 live. Now drop on another TDBGrid, TIBQuery and TDataSource and connect them. This time, select IBQuery2 and add TDataSource1 in the "DataSource" Property. This will ensure that the second query will look to the first query to fill in the parameters. That parameter will be the field MyTable.

Enter the following query string into the SQL property of IBQuery2...

  SELECT RDB$FIELD_NAME AS FIELDS
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME = :MyTable
ORDER BY RDB$FIELD_POSITION


and make that query live. When you run that program, selecting tables in the first grid will show all the field information for that table in the second grid.

So far I have the following types in the field RDB$FIELD_TYPE. These can translate to...


  • 8 = Integer
  • 10 = Float
  • 12 = Date
  • 13 = Time
  • 14 = Char
  • 35 = TimeStamp
  • 37 = VarChar
  • 261 = Blob


There'll be a definitive list somewhere but that's all I have needed so far. I got that list from the internet somewhere but it was so long ago that I have forgotten where (thanks to whoever that was).

That will get you started, but here are some other queries that will return more metadata information...

Return indexes for a table

  SELECT RDB$INDEX_NAME 
FROM RDB$INDICES
WHERE RDB$RELATION_NAME = :MyTable
AND RDB$UNIQUE_FLAG IS NULL
AND RDB$FOREIGN_KEY IS NULL


Return all Generators

  SELECT RDB$GENERATOR_NAME 
FROM RDB$GENERATORS
WHERE RDB$SYSTEM_FLAG IS NULL


Return all Triggers

  SELECT * FROM 
RDB$TRIGGERS
WHERE RDB$SYSTEM_FLAG IS NULL


The shifters are coming in the morning so I'll post this now, please excuse any typing errors. Enjoy your day.

Remember: A positive attitude may not solve all your problems, but it will annoy enough people to make it worth the effort. Herm Albright (1876 - 1944)

9 comments:

  1. What an excellent reference Lorenzo. That is worth a link on here. Thanks for that.

    Steve

    ReplyDelete
  2. Very good article! But how can it be done on a query ? How can the program find the name/fieldtype on a (complex) query ? Thanks.
    Eddy

    ReplyDelete
  3. Must be 'WHERE RDB$SYSTEM_FLAG =0' for triggers query.
    And the same for RDB$UNIQUE_FLAG, values are 0 or 1, and one never has NULL value.
    But in the RDB$generators field RDB$SYSTEM_FLAG contain NULL for not system records, however these values documented as value = 0.

    ReplyDelete
  4. here's another DELPHI link:
    http://www.felix-colibri.com/papers/db/interbase/using_interbase_system_tables/using_interbase_system_tables.html

    ReplyDelete
  5. I am not sure where you're getting your info, but great
    topic. I needs to spend some time learning more or understanding more.

    Thanks for great info I was looking for this information for my mission.



    Also visit my web-site :: online download games

    ReplyDelete
  6. When I originally commented I clicked the "Notify me when new comments are added" checkbox
    and now each time a comment is added I get four emails with the same comment.
    Is there any way you can remove me from that service? Cheers!



    Here is my web-site; Tablet PC Testsieger - http://www.ccheonline.org/,

    ReplyDelete
  7. Hi. This uses the standard Google Blog so I'll check on what settings are on there when I get home in a couple of days.

    Sorry about the hassle

    Steve

    ReplyDelete
  8. I’m not that much of a online reader to be honest but
    your sites really nice, keep it up! I'll go ahead and bookmark your site to come back later on.
    Cheers

    Here is my website: sharp convection microwave reviews

    ReplyDelete

Note: only a member of this blog may post a comment.