Retreiving metadata: connect system tables _field to _file

Posted by Admin on 09-Sep-2008 06:55


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?

All Replies

Posted by Thomas Mercer-Hursh on 09-Sep-2008 07:48

Look here

Posted by Admin on 09-Sep-2008 08:07

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'

Posted by Thomas Mercer-Hursh on 09-Sep-2008 09:52

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

Posted by Admin on 10-Sep-2008 01:35

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)?

Posted by Thomas Mercer-Hursh on 11-Sep-2008 10:47

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.

Posted by bwerne on 19-Sep-2008 05:56

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.

This thread is closed