Frequently asked Questions about Code Tables
Q: Are code table real database files?A: No, code tables are abstract or conceptual definitions only. You define to the Framework the columns that are in the code table and indicate which ones define the unique key for a row in the code table. The table data is frequently stored in a real database file, though it may share the database file with other tables.
Q: Where does the data in a code table come from?A: It can come from anywhere. By default the Framework is shipped with an RDML function that will store code table data inside a single database table. However you can supply your own data storage function that can sources the code table data from anywhere that you want. This type of function is referred to as a Table Data Handler Function.
Q: How do I create a Data Storage function?A: A Table Data Handler function is a normal LANSA RDML function that communicates with the Framework using a pre-defined protocol. If you want to create your own Table Data Handler function, see process UF_SYSBR functions UFU0010 – UFU0015 for examples.
Q: Can Data Storage Functions interact with the end-user?A: No. Data storage functions are designed to act as data retrieval and update routines that can work in many different contexts. For example they can be invoked as a remote procedure by a Windows based Framework application or on a remote server as part of browser based application. This means that they need to be able to operate in contexts where no user interface is available to them.
Q: What are the benefits in using code tables?A: The main benefits in using the Framework code table system are simply in improved productivity and consistency. By using a standard shipped architecture for code table maintenance your can develop and maintain applications more rapidly and avoid the cost and complexity of developing your own code table system.
Q: How do I interface an external LANSA function (or LANSA for the web function) with the Framework generic table data file (FPTAB)?
A: The data in FPTAB is unusual in that it contains one record for every non-key cell in the table.
For example, in the Australian States table there are the fields
CODE (a key)
DESCRIPTN
MYSEQ
Each state will be represented as two records in FPTAB: One record for DESCRIPTN and one record for MYSEQ. Both records will contain all the key data (CODE in this case).
| Akey1 (FP_EKEY1) | Nkey1(FP_EKEYN1) | Other keys2 - 5 | Property Name(FP_EPTNAM) | Alpha property value (FP_EPTVAL) | Numeric property value (FP_EPTNV) |
|
NSW |
|
|
DESCRIPTN |
New South Wales |
|
|
NSW |
|
|
MYSEQ |
|
10 |
|
QLD |
|
|
DESCRPTN |
Queensland |
|
|
QLD |
|
|
MYSEQ |
|
20 |
|
... |
|
|
|
|
|
|
|
|
|
|
|
|
ExamplesI want to check that a currency code entered by a user is valid
*Use the logical view (kya) keyed by:
*Table name, AKey1, Nkey1, AKey2, Nkey 2, Akey3, Nkey3 ..., field name
CHECK_FOR in_file(FPTABkya) with_key('VF_CURRENCY' #MyCurrencyCodeField)
IF_STATUS *EQUALKEY
ENDIF
I want to display the description of a currency code that I have read from somewhere
*Use the logical view (nma) keyed by:
*Table name, field name, AKey1, Nkey1, AKey2, Nkey 2, Akey3, Nkey3 ...
FETCH fields(#FP_EPTVAL) from_file(FPTABnma) with_key('VF_CURRENCY' 'DESCRIPTN' #MyCurrencyCodeField)
Change #MyDescriptionField #FP_EPTVAL
I want to read through the currency codes and report on all transactions for each currency
*Use the logical view (nma) keyed by:
*Table name, field name, AKey1, Nkey1, AKey2, Nkey 2, Akey3, Nkey3 ...
SELECT *ALL from_file(FPTABnma) with_key('VF_CURRENCY' 'DESCRIPTN')
(assuming that there is always a DESCRIPTN for a CURRENCY)
ENDSELECT
I want to read through the currency codes and report on all transactions for each currency, and I need to know both the exchange rate and the description
*Use the logical view (nma) keyed by:
*Table name, field name, AKey1, Nkey1, AKey2, Nkey 2, Akey3, Nkey3 ...
SELECT *ALL from_file(FPTABnma) with_key('VF_CURRENCY' 'DESCRIPTN')
Change #MyDescriptionField #FP_EPTVAL
* Get a numeric cell value
* Use a second logical view to avoid confusing the pointer
FETCH fields(#FP_EPTNV) from_file(FPTABn2a) with_key('VF_CURRENCY' 'EXCHRATE' #FP_EKEY1)
Change #MyExchangeRateField #FP_EPTNV
ENDSELECT
*Use the logical view (nma) keyed by:
*Table name, field name, AKey1, Nkey1, AKey2, Nkey 2, Akey3, Nkey3 ...
SELECT *ALL from_file(FPTABnma) with_key('VF_CURRENCY' 'DESCRIPTN')
Change #MyCurrencyCodeField #FP_EKEY1
Change #MyCurrencyDescriptionField #FP_EPTVAL
* Now read through all the departments
* Use another logical view (n2a) to avoid confusing the pointer
SELECT *ALL from_file(FPTABn2a) with_key('VF_DEPTAB' 'DESCRIPTN')
Change #MyDepartmentCodeField #FP_EKEY1
Change #MyDepartmentDescriptionField #FP_EPTVAL
ENDSELECT
ENDSELECT
Note: It helps coding if for every table type there is a non-key field (e.g. Description) that must exist for every table entry.
I want to decode from a table with a numeric keySay there is a table VF_POSTCODE keyed by #POSTCODE (numeric)
*Use the logical view (nmn) keyed by:
*Table name, field name, NKey1, Akey1, NKey2, Akey 2, Nkey3, Akey3 ...
FETCH fields(#FP_EPTVAL) from_file(FPTABnmn) with_key('VF_POSTCODE' 'DESCRIPTN' #MyPostCodeField)
Change #MyDescriptionField #FP_EPTVAL
I want to decode from a table with a mixed key
Say there is a table VF_POSTCODE keyed by #POSTCODE (numeric) and #COUNTRY (alpha)
*Use the logical view (nmn) keyed by:
*Table name, field name, NKey1, Akey1, NKey2, Akey 2, Nkey3, Akey3 ...
FETCH fields(#FP_EPTVAL) from_file(FPTABLnmn) with_key('VF_POSTCODE' 'DESCRIPTN' #MyPostCodeField *blanks 0 #MyCountryCodeField)
Change #MyDescriptionField #FP_EPTVAL
I want to read departments in description order
*Use the logical view (val) keyed by:
*Table name, field name, Numeric Property Value, Alpha Property Value.
SELECT *ALL from_file(FPTABval) with_key('VF_DEPTAB' 'DESCRIPTN')
(assuming that there is always a DESCRIPTN for a department)
ENDSELECT