Retreiving metadata: connect system tables _field to _file - Forum - OpenEdge RDBMS - Progress Community

Retreiving metadata: connect system tables _field to _file

 Forum

Retreiving metadata: connect system tables _field to _file

  • Hi

    I want to retrieve some metadata for the fields in our database (the datatype, number of decimals etc). From what I can see, it is possible to find this in the system table _field. But how can I decide which file each field belongs in? I can't find any plausible foreign key in _field that can connect to any key in _file.

    I was at first thinking about using the file.db-recid, but all records in that table has the same recid. Then there is file.file-number, but this field has a format (->>>>>9) that isn't found on any field in the other table. Anyone know how to "connect" these two tables?

  • Look here http://www.peg.com/techpapers/monographs/schema/schema.html

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • OK, but I still miss one little piece in the puzzle: I can't seem to find a field called recid in the table _File. I want to connect those two tables using SQL, but

    select recid from _File

    fails.

    Message was edited by:

    Nils Petter Liadal

    Edit:

    Corrected 'recird' to 'recid'

  • recid is a function, not a field. I.e., recid(_file) = field.file-recid

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • OK, but can I use this in an SQL-statement? RECID(_file) means, I guess, that the correct record must be active in _file, and I can't see that there is such a thing as an "active record" in an SQL-statement (unless I could write something like 'select RECID(select * from _file where _file-name='test') from _file', which it seems I can't)?

  • I've never used it myself, but RECID is a SQL reserved word with the same functionality as the ABL reserved word, so you should be able to make the join in the same way.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • If you are using SQL to attain the metadata information, there are a couple other options to consider.

    First, both JDBC and ODBC have metadata methods as part of their APIs.

    Second, there are OpenEdge SQL system views that you can use in a SELECT statement such as SYSPROGRESS.SYSCOLUMNS that return metadata info. The SQL documentation lists these.