Understanding ALLBASE/SQL Operations

• Accessing data using data manipulation language (DML).

• Creating ALLBASE/SQL objects using data definition language (DDL).

• Managing security with data control language (DCL).

• Handling transactions.

• Handling errors.

• Performing dynamic processing.

Using DML to Manipulate Data

You use DML to examine or modify the rows in the tables in a database. The chief DML command for creating queries is SELECT. For other kinds of data manipulation you use DELETE, INSERT, or UPDATE. Whether for OLTP (online transaction processing) or report-writer applications, DML commands are the heart of database access. Therefore, much of the programmer's time is spent using DML to create the most efficient queries and updates. Most of this manual is devoted to the task of coding effective DML statements.

The following example of a simple data manipulation statement reads a row of data into some host variables (host variables are described below, under "Creating Source Files").

   EXEC SQL SELECT PartNumber, PartName, SalesPrice

        INTO :PartNumber, :PartName, :SalesPrice

        FROM PurchDB.Parts;

Using DDL to Define Database Objects

You use DDL to create database objects, including tables, views, indexes, and authorization groups. For these tasks, you use the SQL CREATE commands, including CREATE TABLE, CREATE VIEW, CREATE INDEX, and CREATE GROUP. DDL is not always used in application programs, since database objects frequently exist before coding starts. However, it is sometimes useful to define objects at run time. Or you might wish to create special applications to tailor the creation of objects for the specific needs of your system. For detailed information about DDL functions, refer to the ALLBASE/SQL Database Administration Guide.

The following example of a data definition statement shows the creation of an index on one column of the Parts table:

   EXEC SQL CREATE UNIQUE INDEX PartNumIndex

        ON PurchDB.Parts (PartNumber);

Data definition also includes the DROP commands for database objects such as the index created in the previous example, and the ALTER TABLE command for making changes to these objects. It also includes SQL statements for creating files and filesets for storage of database information.

Using DCL to Manage Security

You use DCL to create a security scheme for your databases. Control statements are used to initiate access to a DBEnvironment and to provide security on specific database objects.

You may find it useful to assign permissions for newly created objects when the default permissions are not sufficient for the needs of your application. You can control the ownership of objects in ALLBASE/SQL DBEnvironments at creation time or through the TRANSFER OWNERSHIP command. Similarly, you can assign authorization for specific objects as you create them or through the GRANT and REVOKE commands.

Data control statements let you create groups, assign members to them, then grant all the authorities as needed. You can also grant and revoke authorities to individual users.

The following example of a data control statement assigns CONNECT authority to everyone for the current DBEnvironment:

   EXEC SQL GRANT CONNECT TO ALL;

Handling Transactions

Transactions are the units of work in an ALLBASE/SQL application. Transactions are usually delimited by BEGIN WORK and COMMIT WORK or ROLLBACK WORK statements. During a transaction, ALLBASE/SQL obtains locks on data and index pages; this can cause others to wait for data access. ALLBASE/SQL lets you regulate the kinds of locking your transactions use so as to achieve the greatest concurrency possible while protecting data from the actions of other users. The ALLBASE/SQL Reference Manual contains information about managing the transactions in your applications.

Handling Errors

You use error handling to detect error conditions that arise while you are connected to an ALLBASE/SQL DBEnvironment. Error handling can be either implicit or explicit.

In implicit error handling, you use the WHENEVER SQLERROR and WHENEVER SQLWARNING statements to branch to a specific location following any error or warning. In this kind of processing, you do not test for a specific error condition.

In explicit error handling, it the programmer's responsibility to examine the specific error code returned by ALLBASE/SQL. For example, if the error number returned is -14024, ALLBASE/SQL has detected a deadlock and has rolled back a transaction. This means you can try the transaction again. On the other hand, an error of -2206 means that a table or view was not found. In this case, it does not make sense to reattempt the transaction.

Both implicit and explicit error handling techniques usually employ an error routine that incorporates the ALLBASE/SQL message command, SQLEXPLAIN. For example, a loop may be used to display to the user the text of each error or warning message related to a given SQL command.

Refer to Chapter 4 for complete details about error handling, including several examples.

Dynamic and Non-Dynamic Operations

Whether you are using DML, DDL, or DCL operations, you can structure embedded SQL statements as either dynamic or non-dynamic. Commands are non-dynamic when the syntax of the entire command is known and preprocessed prior to run time. For many non-dynamic commands, ALLBASE/SQL can speed up database access by storing runtime instructions in the DBEnvironment at preprocessing time. The following is a non-dynamic query.

   printf("Enter the Part Number: ");

   gets(PartNumber);



   EXEC SQL SELECT PartName, SalesPrice

        INTO :PartName, :SalesPrice

        WHERE PartNumber = :PartNumber;

Commands are dynamic when they are preprocessed at run time. ALLBASE/SQL converts these commands into executable instructions at run time rather than at preprocessing time. For example, you might know which table and which columns you want to query with a SELECT, but you may not know until run time the name of the DBEnvironment itself. In such a case, the preprocessor cannot store instructions in the DBEnvironment prior to run time.

The following is an example of a dynamic CONNECT in which the user enters the name of the DBEnvironment at run time:

   printf("Enter the DBEnvironment Name: ");

   gets(DBEName);

   sprintf(DynamicCommand,"CONNECT TO '%s';",DBEName);

   EXEC SQL EXECUTE IMMEDIATE :DynamicCommand;

There are many techniques for dynamic processing of queries and non-queries. The topic is discussed fully in Chapter 8.