Manipulating Data

You can use ISQL to manipulate and access the data in your database by using the following SQL and ISQL commands:

   INSERT      An SQL statement.

   INPUT       An ISQL command.

   SELECT      An SQL statement.

   DELETE      An SQL statement.

   UPDATE      An SQL statement.

   INFO        An ISQL command.

The SQL statements in this list are collectively referred to as data manipulation language (DML) statements. INPUT and INFO are ISQL commands which support the data manipulation function.

A brief description of each command is presented in the next sections. Refer to the ALLBASE/SQL Reference Manual for complete information on using the SQL statements. Refer to the "ISQL Commands" chapter of this manual for complete information on using INPUT and INFO.

Using the INSERT Statement

The basic SQL statement for entering data into a table is the INSERT statement, as shown in the following example:

   isql=> INSERT INTO PurchDB.Parts VALUES

   > ('2010-S-01','Synthesizer, monophonic',499.00);

   isql=>

A single row is inserted into the Parts table.

Using the INPUT Command

As an alternative to the SQL INSERT statement, you can use the ISQL INPUT command shown here.



   isql=> INPUT;



   Table name> PurchDB.Parts;

   Column name> (PartNumber, PartName);

   1> ('2010-S-01','Synthesizer, monophonic');

   2> ('2010-S-02','Synthesizer, stereophonic');

   3> ('2010-S-03',null);

   4> END;

   Number of rows processed is 3

   COMMIT WORK to save to DBEnvironment.



   isql=>

ISQL provides the following options for committing rows during INPUT command execution:

When the AUTOCOMMIT option of the SET command is ON, ISQL automatically processes a COMMIT WORK statement every time the number of rows specified in the AUTOSAVE option of the SET command has been entered and when you terminate the INPUT command.
You can specify the COMMIT WORK option at anytime during INPUT command processing to commit rows entered since the last COMMIT WORK was processed. For example:
4> ('CharValue',NumericValue); 5> COMMIT WORK; 6>
You can specify an option called ROLLBACK WORK to back out rows entered since the last COMMIT WORK was processed.

Using the SELECT Statement

When you enter a SELECT statement through ISQL, you can browse through the query result at the terminal, print the query result, and/or edit the query result. The files used for these activities are illustrated in Figure 3-1.

Figure 3-1 Files Used for SELECT Command

ISQL puts the query result into the file named in the SET command with the OUTPUT option. A file named isqlout is used if a SET OUTPUT command has not been issued to name another file. The file isqlout is purged when the terminal display of the query result is terminated. If you want to save a query result, change the name of the output file before you enter the SELECT statement:

   isql=> SET OUTPUT KeepFile;

   isql=>

Ensure that you have write permission in the directory where isqlout or the newly named output file resides. The following describes how three additional SET options affect the query result:

The FRACTION option determines the number of decimal digits displayed in the query result for data of types FLOAT, REAL, and DECIMAL. The value of this option is initially 2.
The NULL option determines the character that ISQL displays when a null value occurs. The value of this option is initially a blank.
The PAGEWIDTH option determines the maximum length of each row ISQL puts into the output file. The initial setting of this option is its maximum value: 32767 bytes. Any rows in the query result that are longer than the PAGEWIDTH length are truncated.

Use the following commands to set options and issue a SELECT statement:

   isql=> SET FRACTION 0; SET NULL ?;SET EDITOR /usr/bin/vi;

   isql=> SELECT PARTNUMBER,UNITPRICE,DELIVERYDAYS

   >FROM PURCHDB.SUPPLYPRICE;

Once the query result is in the output file, it is displayed on the terminal as follows:

   SELECT PARTNUMBER,UNITPRICE,DELIVERYDAYS FROM PURCHDB.SUPPLYPRICE;

   ----------------+--------------+------------

   PARTNUMBER      |UNITPRICE     |DELIVERYDAYS

   ----------------+--------------+------------

   1123-P-01       |           450|          30

   1123-P-01       |           475|          15

   1123-P-01       |           550|          15

   1123-P-01       |           475|          30

   1123-P-01       |           500|          20

   1123-P-01       |           525|          15

   1133-P-01       |           180|          30

   1133-P-01       |           200|          15

   1133-P-01       |           220|          15

   1133-P-01       |           195|          20

   1143-P-01       |           180|          30

   1143-P-01       |           185|          15

   1143-P-01       |           175|          30

   1143-P-01       |           180|          20

   1153-P-01       |           210|          30

   1153-P-01       |           220|          15

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

   First 16 rows have been selected.

   U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]>    b

ISQL displays 16 selected rows at a time. You can browse through the query result by responding to the prompt beneath the query result. The following are valid responses:

UP and DOWN scroll backward and forward, respectively, by half the number of displayed rows. You can also press the Return key for downward scrolling. The row count that ISQL displays beneath the query result is incremented each time you scroll DOWN.
TOP and BOTTOM display the first and the last group of rows, respectively, of the query result. The row count that ISQL displays beneath the query result following a BOTTOM command reflects the total number of rows that qualify for the query.
LEFT and RIGHT scroll the display left and right, respectively, by 40 screen columns at a time.
PRINT n send n copies of the query result to the system printer; the default is one copy (n equals 1).

The b response, shown in the previous example, displays the last group of rows in the query result as shown below.



   SELECT PARTNUMBER,UNITPRICE,DELIVERYDAYS FROM PURCHDB.SUPPLYPRICE;

   ----------------+--------------+------------

   PARTNUMBER      |UNITPRICE     |DELIVERYDAYS

   ----------------+--------------+------------

   1733-AD-01      |           255|          15

   1823-PT-01      |           435|          20

   1823-PT-01      |           450|          15

   1823-PT-01      |           450|          15

   1833-PT-01      |          1985|          20

   1833-PT-01      |          1990|          15

   1923-PA-01      |            70|          15

   1923-PA-01      |            80|          20

   1923-PA-01      |            70|          20

   1923-PA-01      |            75|          10

   1933-FD-01      |           565|          20

   1933-FD-01      |           585|          15

   1933-FD-01      |           600|          10

   1933-FD-01      |           590|          15

   1933-FD-01      |           585|          20

   1943-FD-01      |           575|????????????

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

   Number of rows selected is 69.

   U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd]>    e



   isql=>

The values in the UNITPRICE column are displayed without decimal digits because the FRACTION option of the SET command was 0 when the SELECT statement was executed. The last value in the DELIVERYDAYS column is displayed as several question marks, because the NULL option of the SET command was ? when the SELECT statement was executed and the value is null.

Because the OUTPUT option of the SET command was KeepFile when the SELECT statement was executed, the query result can be edited. You can use the ISQL EDIT command to access the query result. In the following example, the EDIT command invokes vi because the current EDITOR option of the SET command is /usr/bin/vi:



   isql=> EDIT KeepFile;



   SELECT PARTNUMBER,UNITPRICE,DELIVERYDAYS FROM PURCHDB.SUPPLYPRICE;

   ----------------+--------------+------------

   PARTNUMBER      |UNITPRICE     |DELIVERYDAYS

   ----------------+--------------+------------

   1123-P-01       |           450|          30

   1123-P-01       |           525|          15

   .

   .

   .

   :q



   isql=>

TID Function

Each row (tuple) in an ALLBASE/SQL table is stored at a database address on disk. This unique address is called the tuple identifier or TID. When using the TID function in a SELECT statement, specify it as you would a column name. For example:

   isql=> SELECT TID(), PARTNUMBER FROM PURCHDB.SUPPLYPRICE;

The TID and partnumber for each row in the PurchDB.SupplyPrice table are displayed on the terminal as follows:

   SELECT TID(), PARTNUMBER FROM PURCHDB.SUPPLYPRICE;

   -----------+----------------

   TID        |PARTNUMBER

   -----------+----------------

         1:1:0|1123-P-01

         1:1:1|1123-P-01

         1:1:2|1123-P-01

         1:1:3|1123-P-01

         1:1:4|1123-P-01

         1:1:5|1123-P-01

         1:1:6|1133-P-01

         1:1:7|1133-P-01

         1:1:8|1133-P-01

         1:1:9|1133-P-01

        1:1:10|1143-P-01

        1:1:11|1143-P-01

        1:1:12|1143-P-01

        1:1:13|1143-P-01

        1:1:14|1153-P-01

        1:1:15|1153-P-01

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

     First 16 rows have been selected.

     U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] >

A TID consists of eight bytes of binary data and has the following format:

Table 3-1 SQLTID Data Internal Format

Content	Byte Range
Version Number	1 through 2
File Number	3 through 4
Page Number	5 through 7
Slot Number	8

ISQL parses and displays the TID as three fields separated by colons. The version number is not displayed. In the following example, the file number is 1, the page number is 2, and the slot number is 133.

   1:2:133

When using the TID in a WHERE clause, the input parameter must be a constant. Only the equal and not equal comparison operators are supported. The version number field of the TID is optional, but if specified, must always be 0. The following example selects the row from PurchDB.Parts with a TID of 3:4:15.

   isql=> SELECT * FROM PURCHDB.PARTS WHERE TID() = 3:4:15;

Using the DELETE Statement

The SQL DELETE statement lets you delete groups of rows from a table. Here is an example:

   isql=> DELETE FROM PurchDB.Parts

   > WHERE SalesPrice > 500;

All rows with a SalesPrice greater than $500 are deleted.

Using the UPDATE Statement

The SQL UPDATE statement lets you modify groups of rows in a table, as in the following example:

   isql=> UPDATE PurchDB.Parts

   > SET SalesPrice = 1.2*SalesPrice;

All prices are increased by 20 percent.

Using the INFO Command

The INFO command allows you to display the definition of each column in a table or view.



   isql=> INFO PurchDB.Parts;



                                           Nulls                 Case

   Column Name    Data Type (length)       Allowed  Language     Sensitive

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

   PARTNUMBER     Char      (   16)          NO     n-computer     YES

   PARTNAME       Char      (   30)          YES    n-computer     YES

   SALESPRICE     Decimal   (   10,    2)    YES



   isql=>