HP 3000 Manuals

ALLBASE/SQL FORTRAN Application Programming Guide

Defining and Manipulating Data 

You embed data definition and data manipulation commands in a subprogram
unit.

Data Definition 

You can embed the following SQL commands to create objects or change
existing objects:

     ALTER TABLE                      DROP DBEFILE
     CREATE DBEFILE                   DROP DBEFILESET
     CREATE DBEFILESET                DROP GROUP
     CREATE GROUP                     DROP INDEX
     CREATE INDEX                     DROP MODULE
     CREATE TABLE                     DROP TABLE
     CREATE VIEW                      DROP VIEW

In a program, data definition commands are useful for such activities as
creating temporary tables or views to simplify data manipulation or
creating an index that improves the program's performance:

      EXEC SQL CREATE INDEX PartNameINDEX
     1                  ON PurchDB.Parts (PartName)

The index created with this command expedites data access operations
based on partial key values:

      EXEC SQL SELECT  PartName
     1           INTO :PartName
     2           FROM  PurchDB.Parts
     3          WHERE  PartName LIKE :partialkey

Data Manipulation 

SQL has four basic data manipulation commands:

   *   SELECT: retrieves data.

   *   INSERT: adds rows.

   *   DELETE: deletes rows.

   *   UPDATE: changes column values.

These four commands can be used for various types of data manipulation
operations:

   *   Simple data manipulation:  operations that retrieve single rows,
       insert single rows, or delete or update a limited number of rows.

   *   Processing with cursors:  operations that use a cursor to operate
       on a row at a time within a set of rows.  A cursor is a pointer
       the program advances through the set of selected rows.

   *   Dynamic operations:  operations specified by the user or program
       at runtime.

In all data manipulation operations, you use host variables to pass data
back and forth between your program and the DBEnvironment.  Host
variables can be used in the data manipulation commands wherever the
syntax explained in the ALLBASE/SQL Reference Manual allows them.

The SELECT command shown at 8 in Figure 3-1 retrieves the row from
PurchDB.Parts that contains a part number matching the value in the host
variable named in the WHERE clause (PartNumber).  The three values in the
row retrieved are stored in three host variables named in the INTO clause
(PartNumber, PartName, and SalesPrice).  An indicator variable
(SalesPriceInd) is also used in the INTO clause, to flag the existence of
a null value in column SalesPrice:

      EXEC SQL SELECT  PartNumber, PartName, SalesPrice
     1           INTO :PartNumber,
     2                :PartName
     3                :SalesPrice :SalesPriceInd
     4           FROM  PurchDB.Parts
     5          WHERE  PartNumber = :PartNumber

You can also use host variables in non-SQL statements; in this case, omit
the colon:

      SalesPrice = response

      EXEC SQL SELECT  COUNT(PartNumber)
     1           INTO :PART-COUNT
     2           FROM  PurchDB.Parts
     3          WHERE  SalesPrice > :SalesPrice

All host variables used in a program unit must be declared in the Type
Declaration Section in that program unit, as discussed earlier in this
chapter under "Declaring Host Variables".

MPE/iX 5.0 Documentation