HP 3000 Manuals

HP ALLBASE/4GL Developer Self-Paced Training Guide

HP ALLBASE/SQL Table Definition and Creation 

When you have completed this lesson you will have learned the two steps
necessary for creating an HP ALLBASE/SQL table:

   *   Completing the definition for a base table.

   *   Creating the table.


NOTE  If you intend to work through this section, make sure your HP
      ALLBASE/4GL administrator has already created an HP ALLBASE/SQL
      database that corresponds with the field specifications, record
      layouts, and file definitions that you have created, and those that
      you will create in later lessons.

      If that has not been done, you can still read through this section,
      view the screens, and make the field entries.  You will not,
      however, be able to commit the screen.


Task 1 - Defining an HP ALLBASE/SQL Table 

HP ALLBASE/4GL allows you to store data for an application in HP
ALLBASE/SQL tables.  You have already defined the fields that make up the
table records.  You have also defined the record layout that is to be
associated with the data file.

When you created the record layout in Lesson 2, you allocated a key
number to several fields on the records layout.  The HP ALLBASE/SQL data
manager uses the key fields to define the indexes for the file.  Key
number 1 is the primary key.

Using the Data File/SQL Table Definition Screen.   

This screen uses three different windows.  For an SQL table, this screen
allows you to specify the name of the table as it is known to HP
ALLBASE/4GL and the record layout to be used for the table.

This screen also allows you to specify the external name of the table,
the SQL DBEFileSet to hold the table, the SQL access class for the table,
and the name of the record layout for the table.

To access the screen from the main menu: 

   1.  Select the Dictionary option.

   2.  Choose Database Items.

   3.  Choose Data File/SQL Table Definition.


          Data File/SQL Table Definition Screen 

To enter the field values: 

        Field                 Entry                         Explanation 

---------------------------------------------------------------------------------------

File Name             product               The HP ALLBASE/4GL name for the SQL table.
                                            This is the name you use in references to
                                            the table in HP ALLBASE/4GL logic.  Note
                                            that the name can only be up to eight
                                            characters long.

File Type             S                     Indicates the type of file interface that
                                            is to be used in this application.  S 
                                            specifies an HP ALLBASE/SQL table.
                                            Automatically displays window.

          Table 2-0.  (cont.) 

        Field                 Entry                         Explanation 

---------------------------------------------------------------------------------------

Description           Enter text describing
                      table's purpose


          SQL Window 

To enter values in the SQL window: 

When you entered S in the File Type field and pressed Return, the SQL
window appeared.  Fill in that window with the values given below.

        Field                 Entry                         Explanation 

---------------------------------------------------------------------------------------

External Name         Accept the default.   The name of the table in the HP ALLBASE/SQL
                                            database environment for the application.

SQL DBEfileset        PRODFS                The name of the DBEfileset for the table.
                                            The DBEfileset must exist in the database
                                            environment before you can create the file.
                                            The entry in this field defaults to SYSTEM,
                                            but you can type over the default.

SQL Access Class      Accept the default.   Specifies the table locking mode as public,
                                            publicread, or private.  All transactions
                                            on this table are subject to the normal HP
                                            ALLBASE/SQL locking provision.

Record Layout         product_rcrd

To complete and commit the screen: 

   1.  When you have entered all the values for this screen, press the  
       Commit Data  function key to create the file definition.

       This is the logical file definition for use in HP ALLBASE/4GL.
       Your next step is to create the physical disk file.

   2.  Press the  Create File  function key, or use the  Previous Menu  
       function key and select Create File/SQL Table.

       The file/SQL creation screen should be displayed.

Task 2 - Creating an HP ALLBASE/SQL Table 

This is the second step in creating an HP ALLBASE/SQL table.  During this
procedure, HP ALLBASE/4GL creates the table, using the field
specifications in the table record layout to define the columns of the
table.

Using the Create File/SQL Table Screen.   

You use this screeen to enter the name of the table you have already
defined.  HP ALLBASE/4GL reads the table definition and displays the
details so you can confirm your selections.  You can then create the
table.

To access the screen: 

   1.  From the main menu select the Dictionary option.

   2.  Choose Database Items.

   3.  Choose Create File/SQL Table.


          Create File/SQL Table Screen 

To enter the field values: 

        Field                 Entry                         Explanation 

---------------------------------------------------------------------------------------

File Name             Accept the default.   The name you used when you created the
                                            table definition.

This completes the data entry required on this screen.  The remaining
fields display the data you entered on the record layout screen and the
file/SQL table definition screen.

To create the physical file: 

   1.  Press Return to display the details of this file on the screen.

       The MPE/iX external file name is displayed first, followed by the
       key fields from the default record.

       Note that the two keys, defined when you created the record
       definition, are listed, along with an indicator specifying whether
       duplicate values are allowed.  The description of the file is
       displayed in the lower portion of the screen.

   2.  To create the table, press the  Commit Data  function key.

       HP ALLBASE/4GL displays a message telling you that it is
       connecting to the application database, and then displays a
       message to confirm that the table has been created successfully.

Notes on SQL Tables.   

SQL Table Ownership 

HP ALLBASE/4GL creates tables as owner_group.table where owner_group is
the SQL owner group defined for the application.  The name of this owner
group is specified on the application definition screen in the
administrator application.

The product table is created as SQLGRP.PRODUCT in the application
database environment.

Table Format 

HP ALLBASE/4GL creates the table using the field specifications in the
table record layout to define the columns of the table.  HP ALLBASE/4GL
creates columns defined by numeric field specifications as decimal
columns, and columns defined by other field specification types as CHAR
columns.  All columns created permit null values.

HP ALLBASE/4GL uses the key fields in the record layout for a table to
create indexes for the table.  The index corresponding to key field
number 1 is a clustering index.

MPE/iX 5.0 Documentation