HP 3000 Manuals HP 3000 Manuals
Adding Data to the Database
The next step is to add data to the database. Because no applications
exist for this database, your choice for adding data is with the
interactive tools that come with the product. For standard access to
TurboIMAGE/XL, your tool is QUERY. For SQL access to IMAGE/SQL, your tool
is ISQL. You can add data using the SQL INSERT statement or ISQL LOAD
command.
In Chapter 4, "A Database Example", the concept of referential integrity
was presented. That concept is further demonstrated here. If you refer
to the database design in Chapter 4 , selection table entries are
dependent upon corresponding values in the Composers and Albums tables.
To maintain referential integrity, you will add data in the following
order:
1. Composers and Albums
2. Selections
Using the INSERT Statement
You can use ISQL to CONNECT and then INSERT data into a table. The
following example adds rows to the COMPOSERS table with the INSERT
statement, then displays the table by reading all the data with the
SELECT statement:
:RUN ISQL.PUB.SYS Return
isql=>CONNECT TO 'musicdbe'; Return
isql=>INSERT INTO music.composers Return
>values ('Schubert','1797','1828','Austria','Died at age 31');Return
Number of rows processed is 1
isql=>INSERT INTO music.composers Return
>values ('Chopin','1810','1849','Poland','Died at age 39');Return
Number of rows processed is 1
isql=>COMMIT WORK; Return
isql=>SELECT * FROM music.composers; Return
____________________________________________________________________________________
| |
| |
| select * from music.composers; |
| ----------------+----------------+----------------+------------------------ |
| COMPOSERNAME |BIRTH |DEATH |BIRTHPLACE |
| ----------------+----------------+----------------+------------------------ |
| Schubert |1797 |1828 |Austria |
| Chopin |1810 |1849 |Poland |
| |
| --------------------------------------------------------------------------- |
| Number of rows selected is 2 |
| |
____________________________________________________________________________________
Figure 6-7. INSERT Command Display
The COMMIT WORK statement is necessary because SQL processes statements
in units known as a transaction. When you issue the first SQL statement
in a sequence, a transaction begins, and that transaction continues until
you do a COMMIT WORK or ROLLBACK WORK. The use of transactions guarantees
the consistency of data within the DBEnvironment.
Using the LOAD Command
In addition to the INSERT statement, you can use the LOAD command to add
data with ISQL. The LOAD command has two options: EXTERNAL and INTERNAL.
The EXTERNAL Option.
The EXTERNAL option of the LOAD command includes layout information for
the data items. The following example shows the EXTERNAL option. The
first number after the column name is the starting column position; the
second number indicates the length of the field.
isql=>LOAD FROM EXTERNAL Return
>albums.sampledb.sys TO music.albums Return
>AlbumCode 1 4 Return
>AlbumTitle 13 40 Return
>Medium 53 2 Return
>AlbumCost 55 6 Return
>RecordingCo 61 10 Return
>DateRecorded 71 10 Return
>MfgCode 89 40 Return
>Comment 137 80 Return
>end; Return
Load depending on value in input record (Y/N)> n Return
Command in progress.
Number of rows read is 9
Number of rows processed is 9
COMMIT WORK to save to DBEnvironment.
isql=>COMMIT WORK; Return
isql=>SELECT * FROM music.albums; Return
_____________________________________________________________________________________
| |
| |
| select * from music.albums; |
| -----------+----------------------------------------+------+------------+-- |
| ALBUMCODE |ALBUMTITLE |MEDIUM|ALBUMCOST |RE |
| -----------+----------------------------------------+------+------------+-- |
| 2002|Famous Bel Canto Arias |ca | 22.00|dg |
| 2003|Concertos for Diverse Instruments |cd | 19.00|rc |
| 2004|Symphonies and Chamber Works III |cd | 29.00|rc |
| 2005|Nielsen Symphonies 4 & 5 |ca | 13.00|llo |
| 2006|Lenontyne Price: a Christmas Offering |ca | 13.00|lo |
| 2007|Sergei Rachmaninov Symphony No. 2 |cd | 19.00|me |
| 2008|Franz Schubert: Lieder |cd | 14.00|at |
| 2009|Beethoven Quartets No. 2 and No. 4 |cd | 18.00|de |
| 2010|Chopin Recital: Ivo Pogorelich |cd | 16.00|dg |
| |
| --------------------------------------------------------------------------- |
| Number of rows selected is 9 |
| |
_____________________________________________________________________________________
Figure 6-8. Result of the EXTERNAL Option
The INTERNAL Option.
The INTERNAL option of the LOAD command works with a special type of file
that is generated by the UNLOAD INTERNAL command. Additional information
is placed in the file describing the file layout. This command is simple
to use because of this additional information. For example:
isql=>LOAD FROM INTERNAL Return
>hits.sampledb.sys to music.selections; Return
Command in progress.
Number of rows read is 18
Number of rows processed is 18
COMMIT WORK to save to DBEnvironment.
isql=>COMMIT WORK; Return
isql=>SELECT * FROM music.selections; Return
____________________________________________________________________________________
| |
| |
| select * from music.selections; |
| -----------+----------------------------------------+----------------+----- |
| ALBUMCODE |SELECTIONNAME |COMPOSERNAME |TIMIN |
| -----------+----------------------------------------+----------------+----- |
| 2008|Der Saenger |Schubert | |
| 2008|Fruehlingslied |Schubert | |
| 2008|Fruehlingslaube |Schubert | |
| 2008|Vor Meiner Wiege |Schubert | |
| 2008|Drang in die Ferne |Schubert | |
| 2008|Der Musensohn |Schubert | |
| 2008|Viola |Schubert | |
| 2008|Vergissmeinnicht |Schubert | |
| 2010|Klaviersonate Nr. 2 - 1 |Chopin | |
| 2010|Klaviersonate Nr. 2 - 2 |Chopin | |
| 2010|Klaviersonate Nr. 2 - 3 |Chopin | |
| 2010|Klaviersonate Nr. 2 - 4 |Chopin | |
| 2010|Prelude cis-moll op 45 |Chopin | |
| 2010|Scherzo cis-moll op 39 |Chopin | |
| 2010|Nocture Es-dur op 55 No 2 |Chopin | |
| 2010|Etude F-dur op 10. No 8 |Chopin | |
| 2010|Etude As-dur op 10. No 10 |Chopin | |
| 2010|Etude gis-moll op 25. No 6 |Chopin | |
| --------------------------------------------------------------------------- |
| Number of rows selected is 18 |
| |
____________________________________________________________________________________
Figure 6-9. Result of the INTERNAL Option
You have now successfully inserted data into COMPOSERS, ALBUMS, and
SELECTIONS tables.
The result of the above SELECT shows selections for Albumcodes 2008 and
2010 corresponding with music by Schubert and Chopin, as indicated by the
ComposerName values. It was possible to add these selections only
because the Albums and Composers tables already contain these values.
This is referential integrity. Referential integrity exists between
these three tables based upon these column values.