HP 3000 Manuals

HP ALLBASE/4GL Developer Reference Manual Vol. 1

File/SQL Table Definition Screen 

This screen allows you to define HP ALLBASE/SQL tables, KSAM data files,
HP TurboIMAGE/iX data sets, and serial data files.

This screen uses three windows.  The first window (File Type "I") allows
you to enter the details for KSAM files, and the second window (File Type
"S") lets you enter details for HP ALLBASE/SQL base tables.  The third
window (File Type "M") lets you enter the details for HP TurboIMAGE/iX
detail master data sets.


NOTE  The term file, as used on this screen, refers to HP ALLBASE/SQL
      base tables, HP TurboIMAGE/iX data sets, and KSAM and serial files.


Screen Image 


            

Field Descriptions 

File Name   Enter the HP ALLBASE/4GL internal name of the file.  This is
the name that is used in all file references and in the FILE logic
command.

If you enter a file name, and a file with the same name in case shifted
form already exists, HP ALLBASE/4GL displays a warning message.

File Type   The permissible entries in this field are:

   I     KSAM (indexed) data file

   S     HP ALLBASE/SQL table

   F     Fixed length record serial file

   V     Variable length record serial file

   A     Automatic master HP TurboIMAGE/iX data set

   D     Detail HP TurboIMAGE/iX data set

   M     Manual master HP TurboIMAGE/iX data set

The entry in this field determines which window is displayed on this
screen.

Description   Documentation fields.  Enter a description of the file for
documentation purposes.  HP ALLBASE/4GL automatically updates the date
and time fields to show the date and time of the last modification.

SQL Table Definition Window 


            

Field Descriptions 

External Name   Enter the external name of the table as it is known in
the HP ALLBASE/SQL database.  Note that you cannot use a qualified name
in the form owner.table in this field.

The entry in this field defaults to the uppercase form of the HP
ALLBASE/4GL name for the table.  You can type over the default if you
require a different external name for the table.

The external name for an HP ALLBASE/SQL table can be up to 20 characters
in length.  It can be made up of any combination of uppercase letters (A
to Z), digits (0 to 9), \$, #, :, or underscores (_).  The first
character cannot be an underscore or a digit.  You cannot enter lowercase
letters in this field.

SQL DBEFileset   Enter the name of the HP ALLBASE/SQL DBEFileset that
will contain the HP ALLBASE/SQL table.  The entry in this field defaults
to SYSTEM.

HP ALLBASE/4GL creates the table as owner.table_name in the specified
fileset, where owner is the name of the HP ALLBASE/SQL owner group
specified on the application header screen in the HP ALLBASE/4GL
administrator.

HP ALLBASE/4GL creates the table in the database environment defined for
the application on the application definition screen by the system
administrator.

The DBEfileset must contain at least one DBEfile.  Both the DBEfileset
and the DBEfile must exist before you can create an HP ALLBASE/SQL table.

SQL Access Class   Enter 1, 2, or 3 to define the SQL access class for
the table as Public, Publicread, or Private respectively.  The entry in
this field defaults to 1.  The SQL access class determines the locking
mode and the initial access mode for the table.  Refer to the HP 
ALLBASE/SQL Reference Manual for more information.

Record Layout   Enter the name of an existing record layout to define the
columns of the table.

The record layout must not contain any field specifications that are
defined as having multiple occurrences.

HP ALLBASE/4GL uses the key fields (if any) on this record layout to
define the indexes for the table.

HP ALLBASE/SQL uses the names of the fields on the record layout to
define the columns of the table.  HP ALLBASE/SQL automatically converts
all alphabetic characters in the field specification names to uppercase.

HP TurboIMAGE/iX Data Set Definition Window 

This window allows you to define automatic master data sets, manual
master data sets, and detail data sets.  Refer to Additional Information
for a brief definition of these three types of data sets.


            

Field Descriptions 

Database Name   Enter the name of the database that will contain the data
set.  This must be the name of an existing database name defined by the
HP ALLBASE/4GL administrator in the administrator application.

Schema Data Set Name   This is the external HP TurboIMAGE/iX name of the
data set, up to 16 characters.  The data set should exist in the database
identified in the previous field.

The name may contain all characters valid in HP ALLBASE/4GL names except
the underscore character (_).  The name may also contain any of the
following characters, which are accepted within HP TurboIMAGE/iX:

                +, -, *, /, ?, ', #, %, &, and @.

This field defaults to the name in the File Name field, with any
underscore characters (_) converted to hyphens (-), and any lowercase
characters converted to uppercase.

Default Record   Enter the number of one of the 12 record layouts for the
file that is to be treated as the default record in a file record field
reference.  (Refer to chapter 3.)  If you don't enter a record number, HP
ALLBASE/4GL uses record number 1 as the default record.

Record Layout List   Enter the names of the record layouts for this file.
You can specify up to 12 different record layouts.

HP ALLBASE/4GL checks each record layout to ensure that it complies with
the following rules.  If a record layout does not comply with these
rules, it cannot be used in a HP TurboIMAGE/iX data set, and HP
ALLBASE/4GL will display an error message when you press the  Commit Data 
 function key.

   *   Record layouts must not contain a key field with repeated
       occurrences.

   *   Record layouts for master data sets cannot contain keys that allow
       duplicate entries.

   *   Record layouts for automatic master data sets must contain one key
       field, and no other fields.

   *   Record layouts for manual master data sets must contain one key
       field, and may contain other non-key fields.

   *   Record layouts for detail data sets do not need to contain any key
       fields.

HP ALLBASE/4GL allocates a file record buffer for each record layout that
you include in this list.

Each field specification contained in a record layout used in this list
should match a data item in the physical HP TurboIMAGE/iX database.  If a
field specification does not match an HP TurboIMAGE/iX data item, an
error will occur when the application is run.

KSAM/Serial File Definition Window 


            

Field Descriptions 

External Name   Enter the name of the file as it is known to the MPE/iX
operating system, that is, the name of the file which resides on disk.
It can be either a simple or a fully qualified file name.

For KSAM files, the external file name (excluding any group and account
name specifier) cannot be longer than seven characters, and must be an
acceptable MPE/iX file name.

When you create this file, the HP ALLBASE/4GL KSAM data manager adds the
suffixes K, and S, to the external name you specify here to determine the
names of the physical files for the KSAM data file.

Normally, HP ALLBASE/4GL appends the current value of the MPE/iX variable
HP4DATAPATH to the file name.  However, if you specify a fully qualified
file name, HP ALLBASE/4GL builds the file in the group and account
specified.  Refer to appendix D for details of the MPE/iX variables and
group and account structure used by HP ALLBASE/4GL. If HP4DATAPATH is
undefined, HP ALLBASE/4GL builds the file in the user's current group and
account.

The external file name need not be unique.  More than one HP ALLBASE/4GL
file name may refer to the same external file.

For serial files, HP ALLBASE/4GL allows you to specify the name of a file
dynamically when the application is being run.  To do this, you must
specify the external name of the file as *FILENAME on this screen.  The
application logic must move the required file name into the *FILENAME
communication area field before you access the file.  If the serial file
does not already exist, HP ALLBASE/4GL creates it when it is first
accessed.

By changing the name in *FILENAME, you can use the same HP ALLBASE/4GL
file definition to refer to a number of different external files.
However, you must make sure that the HP ALLBASE/4GL file is closed before
you attempt to access a different external file.  HP ALLBASE/4GL
automatically closes all data files at the start of a process.  You can
also close a file explicitly with the FILE *CLOSE command.

Normally, HP ALLBASE/4GL appends the current value of the MPE/iX variable
HP4DATAPATH to the file name.  If the value in *FILENAME is a fully
qualified file name, HP ALLBASE/4GL performs the file operation on the
file specified by the value in *FILENAME. If HP4DATAPATH is undefined, HP
ALLBASE/4GL assumes the file is in the user's current group and account.

HP ALLBASE/4GL also allows you to define a serial file as a temporary
file.  To define a file as temporary, enter *TEMP in the External Name 
field.  HP ALLBASE/4GL creates a temporary file when the application
first attempts to write to the file.  These files are created in the
temporary domain of the group and account specified by the value of the
MPE/iX variable HP4DATAPATH. If HP4DATAPATH is undefined, the temporary
domain of the user's current group and account is used.  HP ALLBASE/4GL
deletes temporary files automatically when they are closed or when a
process terminates.

Record Layout List   Enter the names of the record layouts for this file.
You can specify up to 12 different record layouts.

HP ALLBASE/4GL creates the file with a physical record layout determined
by the default record.  The physical file only contains one record type,
even if you include more than one record layout name in this list.

HP ALLBASE/4GL allocates a file record buffer for each record layout that
you include in this list.

Default Record   Enter the number of one of the 12 record layouts for the
file that is to be treated as the default record in a file record field
reference.  (Refer to chapter 3.)  If you don't enter a record number, HP
ALLBASE/4GL uses record number 1 as the default record.

For KSAM files and fixed length record serial files, HP ALLBASE/4GL uses
the default record layout to determine the length and layout of the
physical records for the file.  If you are creating a file that uses
multiple record layouts, the default record layout should be the longest
record layout for the file.  If any record layouts are longer than the
default record layout, the additional data is truncated when the record
is written to the file.

When HP ALLBASE/4GL writes to a variable length record serial file, the
number of characters written is determined by the length of the current
record buffer rather than the default record layout.  For serial files
with variable length records, HP ALLBASE/4GL writes the number of
characters in the current buffer.  HP ALLBASE/4GL strips any trailing
spaces.  Note that under the MPE/iX operating system, a variable length
file cannot use a record that is longer than the default record layout
for the file.  The maximum record length for a variable length record
serial file is determined by the length of the default record for the
file.

HP ALLBASE/4GL uses the key fields on the default record layout to build
the KSAM file's index.

Function Keys 

f1       Field Specs.    Cancels the current screen and displays the
        field specifications screen.

f2       Record Header  Cancels the current screen and displays the
        record layout header screen.

f3       SQL Sel Details  Cancels the current screen and displays the
        select list details screen.

f4       Create File  Cancels the current screen and displays the
        file/SQL table creation screen.

Additional Information 

Using Existing HP ALLBASE/SQL Tables.   

If you want to access an HP ALLBASE/SQL table directly using HP
ALLBASE/4GL, you can enter an HP ALLBASE/4GL definition for an HP
ALLBASE/SQL table that has the same name as the existing HP ALLBASE/SQL
table.  To do this, you must define a record layout with fields that
match the column definitions for the existing table.

Alternatively, you can use a SELECT command in an SQL block to access
existing tables.  Refer to chapter 11 for more information about SQL
logic blocks.

Using Existing HP TurboIMAGE/iX Data Set.   

If you want to access an existing HP TurboIMAGE/iX data set directly
using HP ALLBASE/4GL, you can use HP ALLBASE/4GL utilities to load the
data set definition into HP ALLBASE/4GL. The upload utility, HP4TUPLD,
uses information from the HP TurboIMAGE/iX database schema file to create
an ASCII text file containing the data item and data set definitions.
You then use HP4ATOS to load the definitions into HP ALLBASE/4GL. This
provides the HP TurboIMAGE/iX data item definitions and data set
definitions as field specifications, record layouts, and data sets in HP
ALLBASE/4GL. This is particularly useful if you wish to use a number of
existing data sets.  Refer to chapter 10 for details about this
procedure.

Alternatively, you can enter an HP ALLBASE/4GL definition for the data
set, and enter the name of the existing HP TurboIMAGE/iX data set in the
Schema Data Set Name field on the file/SQL table definition screen.

HP TurboIMAGE/iX Data Set Types.   

There are three types of HP TurboIMAGE/iX data sets:  automatic master
data sets, manual master data sets, and detail data sets.  A brief
definition of each type is included here, but for complete descriptions,
refer to the HP TurboIMAGE/iX Database Management System Reference 
Manual.

A detail data set contains records with similar types of data.  For
instance, it may contain details about each order that a customer
submits.  Detail data sets contain search items (key items), and each
data set can contain up to 16 search items.  All the records with common
key values are linked together in a logical chain.  In the example above,
the customer name or number might be the search item, and all records for
a single customer would be linked together in a logical chain.

Detail data sets are related to automatic master data sets and manual
master data sets by common key items.

An automatic master data set record contains one item, which is the key.
When a record is added to any detail data set related to an automatic
master data set, a record is automatically placed in the automatic master
data set.  Using the customer order example, the order date might be
placed in an automatic master data set.

An automatic master data set cannot be updated separately; a detail data
set record is required.  If all detail data set records related to an
automatic master record are deleted, the automatic master record is
deleted automatically.

A manual master data set contains a unique key item and may contain other
items.  A record cannot be added to any detail data set related to the
manual master data set unless a record containing a matching key item
exists in the manual master data set.  Using the customer order example,
a manual master may contain customer details.  You cannot add a customer
order record in the detail data set until the customer details exist in
the master data set.

A manual master data set cannot be automatically updated, and it may
contain records not currently related to a detail data set record.

Serial File Creation.     

If a serial file does not already exist, HP ALLBASE/4GL automatically
creates it when the application first attempts to access the file.

The process containing the FILE command that creates the file must
contain a MODE *WRITE command to specify that the process has write
access to the file.  If the process does not contain a MODE *WRITE
command, the file creation will fail.

HP ALLBASE/4GL creates fixed length serial files with a record length
equal to the length of the default record layout for the file.


NOTE  If you use multiple record layouts for a fixed length serial file
      under the MPE/iX operating system, make sure that the default
      record layout is the longest record layout for the file.  If you
      attempt to write to the file via a record layout that is longer
      than the default record layout, the additional characters will be
      truncated.


For variable length record serial files, HP ALLBASE/4GL also uses the
length of the longest record layout for the file to determine the maximum 
length of records for a variable length record serial file.

If required, you can use an MPE/iX file equation to equate the HP
ALLBASE/4GL serial file to an MPE/iX file with different default
characteristics.

HP ALLBASE/4GL creates temporary serial files in the temporary domain of
the group and account specified by the value of the system variable
HP4DATAPATH. If HP4DATAPATH is undefined, the temporary domain of the
user's current group and account is used.  Application users must have
appropriate access permissions for the group and account.

Using File Equations.     

If required, you can use an MPE/iX file equation to identify the external
name of an HP TurboIMAGE/iX data set, a KSAM file, or a serial file.

If you do use a file equation for an HP ALLBASE/4GL file, the formal
designator for the file must use the fully qualified name for the file.
For example, if the external name for a file is PRODUCT and the current
value for the MPE/iX variable HP4DATAPATH is REALDATA.HP4GL you could use
a file equation in the following format:

     FILE PRODUCT.REALDATA.HP4GL=PARTLIST.TESTDATA.HP4DEV

You cannot use file equations with HP ALLBASE/SQL tables.

MPE/iX 5.0 Documentation