HP c-tree-SQL ISQL and Tools manual @ISQL @iSQLTutorial3.sql

ISQL and Tools

2.3.2Define

In this case define consists of the CREATE TABLE statement. This is done in a single iSQL statement in which specific fields are defined. Upon successful creation of the table, the changes made to the database by this transaction are made permanent by executing the COMMIT WORK statement. The following SQL syntax provides the functionality for the define phase:

•CREATE TABLE - Create a table.

•COMMIT WORK - Make changes permanent. Below is the interactive SQL for DEFINE:

ISQL> CREATE TABLE CUSTMAST (

cm_custnum VARCHAR(5),

cm_custzip VARCHAR(10), cm_custstate VARCHAR(3), cm_custrating VARCHAR(2), cm_custname VARCHAR(48), cm_custaddrs VARCHAR(48), cm_custcity VARCHAR(48) );

ISQL> COMMIT WORK CUSTMAST;

2.3.3Manage

This step provides data management functionality for the applica- tion. In this example we will operate on the customer master table by first deleting all records, then adding, updating, and finally dis- playing the contents of the table. Deleting all records will ensure a clean starting point. Adding records will populate the table allowing subsequent record manipulation.

•INSERT INTO - This will add a record by inserting it into the table

•SELECT - Fetch records according to select criteria

•DELETE FROM - Delete records from a table.

•COMMIT WORK - Make changes permanent.

In order to run this tutorial you should execute:

@ISQL> @iSQL_Tutorial3.sql

in the first ISQL instance. When the script is finished, start the same tutorial in a second instance of ISQL.

The first execution will finish without committing the update. When the second process is launched, it will encounter a lock on a record it is trying to delete and will block.

2-10 FairCom Corporation