 |
» |
|
|
|
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
![[Files Used for SELECT Command]](img/i301.gif)
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 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 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). To specify a different printer, issue a file equation such as the following:
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=>
|
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. 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=>
|
|
