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
Message was edited by:
Nils Petter Liadal
Corrected 'recird' to 'recid'
recid is a function, not a field. I.e., recid(_file) = field.file-recid
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.
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.