HP 3000 Manuals HP 3000 Manuals
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.
![[]](../../pic3k/M012892/FFN83c50.gif)
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. 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=>
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[REV
BEG] option is its maximum value: 32767 bytes. Any rows in the
query[REV END] 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 TDP.PUB.SYS;
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).[REV BEG] To specify a
different printer, issue a file equation such as the following:
: FILE ISQLLP;DEV=PP
[REV END]
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 TDP.PUB.SYS because the current EDITOR option of
the SET command is TDP.PUB.SYS:
isql=> EDIT;
/t KEEPFILE
/l all
1 SELECT PARTNUMBER,UNITPRICE,DELIVERYDAYS FROM PURCHDB.SUPPLYPRICE;
2 ----------------+--------------+------------
3 PARTNUMBER |UNITPRICE |DELIVERYDAYS
4 ----------------+--------------+------------
5 1123-P-01 | 450| 30
6 1123-P-01 | 525| 15
:
/e
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 NATIVE-3000 YES
PARTNAME Char ( 30) YES NATIVE-3000 YES
SALESPRICE Decimal ( 10, 2) YES
isql=>