|
|
|
Defines a list of fields to be retrieved by an &NDBGET statement, or to be added or updated by the &NDBADD or &NDBUPD statements.
&NDBFMT dbname { [ DEFINE ]
FORMAT=formatname
[ FSCOPE={ PROCESS | GLOBAL } ]
[ USAGE={ OUTPUT | INPUT } ]
{ START | [ DATA ] format-text } |
[ DATA ] format-text | END | CANCEL |
DELETE FORMAT= { formatname | * }
[ FSCOPE={ PROCESS | GLOBAL } ] }
format-text for an INPUT format is:
{ NO-FIELDS | ALL-FIELDS | KEY-FIELDS |
FIELDS [ field-entry ] [ field-entry ] ... }
field-entry for an INPUT format is:
{ { name1 [ = { name2 | .RID } ] | pfix1* [ = pfix2* ] } |
( { name1 [ = { name2 | .RID } ] | pfix1* [ = pfix2* ] }
[ LENGTH= { 0 | length } ]
[ PAD= { ' ' | c | 'c' } ]
[ JUSTIFY= { LEFT | RIGHT | CENTER } ]
[ TRUNCATE ]
[ NULLFIELD={ DELETE | NULLVALUE | PAD | NORETURN } ] ) |
.LINK ( FROM=fieldname TO=keyedfieldname )
[ ID=fmtid ]
[ FROMID=fromfmtid ]
[ NOFIND= { WARNING | n | IGNORE } ] )
format-text }
format-text for an OUTPUT format is:
{ ALL-FIELDS | FIELDS [ field-entry ] [ field-entry ] ... }
field-entry for an OUTPUT format is:
[ ( ] { name1 [ =name2 ] | pfix1* [ =pfix2* ] }
[ [ TRUNCATE ] ) ]
The &NDBFMT statement allows an NCL procedure to predefine a list of fields to be returned when using &NDBGET. The advantage of predefinition is that it reduces the overhead of parsing and interpreting the format list on every &NDBGET. The parsing and interpretation is done, and the field list validated, just once, when the &NDBFMT statement is executed. The &NDBGET statement can then use the FORMAT=format name syntax to use the predefined format—the overheads is greatly reduced when reading a large number of records.
Operands:
The name of the NDB that the format is to apply to is a required operand. This NDB must have been previously opened by an &NDBOPEN statement.
An optional operand, indicating a new format definition follows, or the start of a multi-statement format definition follows.
Controls whether the format is PROCESS-level (this is the default) or GLOBAL (to the database).
A GLOBAL format has separate name space from any process. This means that global formats can have the same name as formats defined by any number of processes. The only way to create, reference, or delete a global format is by use of the FSCOPE=GLOBAL operand of the &NDBFMT, &NDBGET, &NDBADD, &NDBUPD or &NDBINFO verbs.
Controls the usage of the format. INPUT is the default and means that the format will be used for input from the NDB (that is, the &NDBGET verb).
USAGE=OUTPUT means that this format is to be used for output operations to the NDB (that is, the &NDBADD and &NDBUPD verbs).
A required operand for a single-statement definition, or the start of a multi-statement format definition, providing the name of the new format. formatname must be a 1- to 12-character name, the first character alphabetic or national, and the rest alphanumeric or national. formatname must not be already defined for the nominated database, but the same format name is defined on several databases.
Indicates the start of a multi-statement &NDBFMT. The statement must end after the START keyword.
Indicates that free-form text follows. This operand is optional, but it is recommended, as it prevents any ambiguous meaning of a name1 or name2 value of DATA, START, END, or CANCEL.
Indicates the end of a multi-statement &NDBFMT. This statement will call the database management system, passing the concatenated &NDBFMT format information.
Indicates an active &NDBFMT START/END set is to be canceled. If there is no active &NDBFMT START/END for this database, the statement is ignored.
Indicates that the named format (formatname), or all formats (*) within the scope specified or defaulted, are to be deleted. Following execution of this statement, the relevant formats are no longer defined.
Free-form text describing the desired format. The first keyword in this text indicates the specific type of format, which is one of the following:
Indicates that no data is wanted. An &NDBGET will only set &NDBRC to indicate the successful retrieval or otherwise, of the requested record.
Indicates that all fields defined in the database are to be returned, with fields not present in a given record being set to null. The fields will be returned in NCL variables of the same name.
Indicates that all fields defined in the database as being keyed (KEY=YES,UNIQUE, or SEQUENCE in the field definition) are to be returned, with key fields not present in a given record being set to null. The fields will be returned in NCL variables of the same name.
Indicates that a field list follows. The list will indicate the desired database fields to be returned, with optional renaming of the returned data.
Note: The field list is null, meaning the same as NO-FIELDS.
When using the FIELDS option, each field-entry denotes an operation to be performed during an &NDBGET. The operation may be to assign values from the database records to NCL variables, or it may be to link to other records in this NDB using field values in the previous records as keys.
If only name1 is present, it is both the name of the database field and the name of the NCL variable that will be set to its value. If both name1 and name2 are present, name1 is the name of the NCL variable that will hold the returned value, and name2 is the name of the field in the database.
If only pfix1* is present, all of the fields whose names begin with pfix1 will be assigned to NCL variables of the same name. If both pfix1* and pfix2* are present, pfix1 is the NCL variable prefix that will be used for the variables containing the returned values from all of the NDB fields which begin with pfix2. pfix1 is null, which means that the NCL variable names will be equivalent to the NDB field names, but without the prefix pfix2. In this case, if there is a NDB field called pfix2, it will not be assigned.
Indicates that the NDB record ID of the retrieved record will be assigned to the NCL variable name1.
Indicates the length of the NCL variable returned. Specifying 0 (the default) means that the variable will be set to the length of the corresponding NDB field. NDB fields shorter than that specified on the LENGTH option will be padded and justified as per the operands explained below. Longer fields will always be truncated on the right.
Indicates the pad character to use when the length for this NCL variable is greater than the length of the NDB field. The default is space.
Indicates how the data from the NDB field will be justified to the NCL variable when the length of the NCL variable is greater than the NDB field.
LEFT, the default, indicates that the data will start in the first character position of the variable, the rightmost portion of the variable containing the specified pad character.
RIGHT indicates that the data will finish in the right most character position of the NCL variable, the leftmost part of the variable being filled with the specified pad character.
CENTER indicates that the data will be centered and the same amount of the specified pad character will be used on either side.
If generic prefixes are specified, then it is possible for NCL variables to exceed 12 characters. However, this will normally cause an error. If TRUNCATE is specified, variable names will be truncated to a maximum of 12 characters. No checking on duplicates will be performed.
Determines the action to take on a receiving NCL variable when the NDB field is not present.
DELETE, the default, specifies that the variable is to be deleted.
NULLVALUE indicates that the variable will be assigned the correct null value for that field type - 0 for numeric fields, space for character fields, and 00/00/00 for date fields.
PAD indicates the variable will be assigned the value of the specified pad character.
NORETURN indicates that no action is to be performed on the variable if the corresponding field is null. This means that if the variable had a previous value in it, that value would remain there after the GET. NORETURN is very dangerous unless it is used with MODFLD=YES in the &NDBGET statement as it cannot be determined whether a field was modified or not by the &NDBGET.
Gives the user the facility to access up to 21 NDB records with a single &NDBGET statement via a linked get. The maximum number of .LINK requests on an &NDBGET statement is 20 (one .LINK retrieves one record).
Specifies the name of the field that will contain the source data for the linked get. This field is the field in the first (not .LINKed) record retrieved by the &NDBGET (if no FROMID specified), or in a previously linked record (if FROMID specified).
Specifies the key field to use in the search for the linked get. The search is always like a GET OPT=KEQ. If more than one record is found matching the given key, the one with the lowest RID is returned.
This is an optional 1- to 12-character name used to identify this particular link. This name is used in a subsequent link entry to identify this record, as opposed to the original record, as the source of the linked get.
This is an optional 1- to 12-character name which must have appeared as the ID operand of a previous linked entry. If omitted, the key value specified in the FROM operand is taken from the primary record. Otherwise it is taken from the record obtained in the link operation identified by from format ID
Specifies the action to take should there be no matching record when a linked get is performed.
WARNING, the default, means that the operation will terminate with a response code of 10, and all subsequent link operations are ignored.
Specifying n will cause a response code of n to be generated, n being within the range 10 to 19. Subsequent link operations will be ignored.
Specifying IGNORE will return with a response code of 0 and subsequent link operations will be attempted if possible.
Indicates that all fields defined in the database are to be added or updated. The fields will be retrieved from NCL variables of the same name.
Indicates that a field list follows. The list will indicate the desired database fields to be added or updated.
If only name1 is present, it the name of both the NCL variables and the name of the database field that will be set to its value. If both name1 and name2 are present, name1 is the name of the field in the database, and name2 is the name of the NCL variable containing its value.
If only pfix1* is present, then all of the database fields whose names begin with pfix1 will be set to values retrieved from NCL variables of the same name. If both pfix1* and pfix2* are present, pfix2 is the NCL variable prefix that will be used for the variables to set values to the NDB fields which begin with pfix1.
Referring to the same NDB field name twice in an OUTPUT format will cause an error.
If an NCL variable name that is greater than 12 characters long is generated from the combination of the NCL generic prefix and the suffix from a generically found NDB field name, the NCL variable name will be truncated to 12 characters.
Examples: &NDBFMT
The following example defines a format for MYNDB, called F1, to return all fields in the database when used on an &NDBGET. Any fields not in a retrieved record cause the appropriate NCL variable to be set no null.
&NDBFMT MYNDB DEFINE FORMAT=F1 DATA ALL-FIELDS
The next example defines a new format for MYNDB, called F2, to return 3 fields when used on &NDBGET, and returns the value in the database field 'SURNAME' in the NCL variable &SNAM, and 'FIRSTNAME' in &FNAM.
&NDBFMT MYNDB DEFINE FORMAT=F2 START
&NDBFMT MYNDB DATA FIELDS -* indicate field list
&NDBFMT MYNDB DATA SNAM = SURNAME -* get surname back in
-* &SNAM
&NDBFMT MYNDB DATA DOB -* get B back in &DOB
&NDBFMT MYNDB DATA (FNAM=FIRSTNAME) -* get firstname back
-* in &FNAM
&NDBFMT MYNDB END
The next example defines a new format for MYNDB, called F3, to return all the fields in the record prefixed by the letters 'ORD', and then to use the value in the field 'ORDCUST#' to perform a GET OPT=KEQ using the CUST# field as the key. If this get is successful, then all of the fields prefixed by CUST are returned from the second record.
&NDBFMT MYNDB DEFINE FORMAT=F3 DATA FIELDS ORD* +
.LINK (FROM=ORDCUST# TO=CUST#) FIELDS CUST*
The next example defines a new format for MYNDB, called F4, to return the record number of the found record in the NCL variable &RECRD, and then to use the value in the field 'ORDCUST#' to perform a GET OPT=KEQ using the CUST# field as the key. If this get is successful, then all of the fields prefixed by CUST are returned from the second record. It then uses the value in the field 'ORDSTAT' from the original record to perform a GET OPT=KEQ using ORDSTATKEY as the key. If successful, &ORDSTATREC is set from the record found using ORDSTATKEY.
&NDBFMT MYNDB DEFINE FORMAT=F4 DATA FIELDS RECRD = .RID +
.LINK (FROM=ORDCUST# TO=CUST#) FIELDS CUST* +
.LINK (FROM=ORDSTAT TO=ORDSTATKEY) FIELDS ORDSTATREC
The next example defines a new format for MYNDB, called F5, to return all the fields in the record prefixed by the letters 'OLINE' to corresponding variable names beginning with 'L'.
&NDBFMT MYNDB DEFINE FORMAT=F5 DATA FIELDS L* = OLINE* +
.LINK (FROM=OLINEITEM# TO=ITEM# ID=ITEM) +FIELDS ITEM* +
.LINK (FROM=ITEMSUPP# TO=SUPPLIER# FROMID=ITEM) +FIELDS SUPP*
For example, OLINE1 goes to L1, OLINE2 goes to L2, and so on. Then the value in the field 'OLINEITEM#' is used to perform a GET OPT=KEQ using the ITEM# field as the key. If this get is successful, then all of the fields prefixed by ITEM are returned from the second record. It then uses the value in the field 'ITEMSUPP#' in the second record to perform a GET OPT=KEQ using the SUPPLIER# field as the key. If this get is successful, all of the fields prefixed by SUPP are returned from the third record.
Notes:
Errors encountered while processing the &NDBFMT statement may cause the procedure to terminate, or may just be reflected in the &NDBRC system variable, depending on the setting of &NDBCTL ERROR option.
All defined PROCESS-level formats for a given database are deleted when an &NDBCLOSE for that database is executed, and, as all open databases are implicitly closed when the highest-level procedure terminates, all defined PROCESS-level formats are deleted too.
The &NDBGET statement may specify a format description itself, but the START/DATA/END option is not available, thus the format description must fit onto a single statement.
Format names are private to an NCL procedure. Any number of users of an NDB may use the same format name, with no interference. If a format is specified on an &NDBGET for a key field histogram, it is ignored.
| Copyright © 2009 CA. All rights reserved. |
|