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 “A Database Example”, 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:
Composers and Albums
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
|
Figure 6-7 INSERT Command Display
select * from music.composers;
----------------+----------------+----------------+------------------------
COMPOSERNAME |BIRTH |DEATH |BIRTHPLACE
----------------+----------------+----------------+------------------------
Schubert |1797 |1828 |Austria
Chopin |1810 |1849 |Poland
---------------------------------------------------------------------------
Number of rows selected is 2
|
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 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
|
Figure 6-8 Result of the EXTERNAL Option
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
|
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
|
Figure 6-9 Result of the INTERNAL Option
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
|
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.
