Database Guide
VisualAge provides a Single-Row Query part for constructing
and issuing SQL statements that return exactly one row of information from a
database. This part is useful for looking up records in a database by a
key, such as looking up an employee from the STAFF table using the ID as a
key. The functions and interface for this part are similar to the SQL
Statement part. The main difference between these parts is the
connections you can make. The Single-Row Query part has a few
additional actions, attributes, and events for updating, deleting, and
creating new rows in a table:
Actions
- newRow
- Returns an empty row. Use this action to add rows to a database
- deleteRow
- Deletes the resultRow from the database.
- updateRow
- Updates the resultRow in the database. This action also adds a row,
if it is not already in the database.
Attributes
- resultRow
- The row returned by the query. You can tear off this attribute to
access the result row returned by the query. This attribute is
analogous to the resultTable attribute of the Multi-row Query
part.
Events
- rowDeletedSuccessfully
- Signaled when a row has been deleted from the database.
- rowDeleteFailed
- Signaled when no result row is returned by the query. You can use
this event to display a message box.
- rowUpdatedSuccessfully
- Signaled when a row has been updated in the database.
- rowUpdateFailed
- Signaled when no result row is returned by the query. You can use
this event to display a message box.
The resultRow part, which you can tear off of the Single-Row
Query part, is similar in function to the currentRow part of the
Multi-row Query part, but it has two additional attributes:
isInDatabase and isNotInDatabase. These
attributes both return either true or false.
Note: | If you use this part to issue a query that returns more than one row, then an
error will be returned. The SQL Editor provides no means for checking
that your SQL will return a single row. It is up to you to ensure that
your SQL returns a single row.
|
The following example illustrates how to use the Single-Row Query
part. It focuses on using the new actions, attributes, and events
described above. For some of the other tasks required to build this
example, refer to previous database examples in this book:
- To begin this example, create a new visual part with a label, a spin
button (from the Lists category), and a push button as shown in the following
illustration. Set the minimum value of the spin button to 10, its
maximum value to 500, and its increment to 10.

- Select
, the Single-Row Query part, from the Database Functions category, and add it
to the free-form surface of the Composition Editor to create the following
query.
SELECT *
FROM STAFF
WHERE (STAFF.ID = :id)
The Single-Row Query settings window is similar to the settings view
for the SQL Statement part and you can use it the same way. For
information on creating host variables, refer to Using a host variable.
- To display the query results in the window, tear off the
resultRow attribute of the Single-Row Query part, and generate a
quick form for it. For information on quick forms, refer to Using quick forms.
- Now add four buttons below the quick form fields: New Row, Add,
Update, and Delete. In the settings view for the
Add, Update, and Delete buttons (page 3 of the General
tab), turn off the enabled setting. These buttons will
enable you to do the following:
- Add a new row to the database
- Update a row in the database
- Delete a row from the database
Your window should now appear as follows:

Adding a message box
You can use the rowNotFound event of the Single-Row Query part
to inform the user that the ID selected from the spin button does not
correspond to an entry in the table:
- Add a Message prompter (from the Prompters category) to the free-form
surface. In the settings for this part, change the title to Row
Not Found, the message to There is no entry for the ID you have
selected, and the button type to OK.
- Connect the rowNotFound event of the Single-Row Query part to
the prompt action of the message prompter.
Adding a new row
You can obtain an empty row for a user to fill with information and add it
to the database as follows:
- Connect the clicked event of the New Row push button
to the newRow action of the Single-Row Query. Then connect
its clicked event to the setFocus action of the ID text
entry part on the result row's quick form.
These connections obtain an empty row and place the cursor in the text
field so that the user can add information to the text fields.
- To add the new row to the database, connect the isNotInDatabase
attribute of the resultRow, to the enable action of the
Add push button. Then connect the clicked event
of the Add push button to the updateRow action of the
Single-Row Query part.
These connections add the new row to the database with the information in
the text fields.
Updating a row
You can update an existing row in the database as follows:
- Connect the isInDatabase attribute of the resultRow to the
enable action of the Update push button.
- Connect the clicked event of the Update push button to the
updateRow action of the Single-Row Query.
These connections allow information that has been changed in the text entry
fields to be applied to the result row.
Deleting a row
You can delete an existing row in the database as follows:
- Connect the isInDatabase attribute of the resultRow to the
enable action of the Delete push button.
- Connect the clicked event of the Delete push button to the
deleteRow action of the Single-Row Query.
These connections allow the row displayed in the text entry fields to be
deleted from the database.
When you test the window, try selecting ID 500 to display the
message box. Then add a new entry to the database and try updating and
deleting it.
[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]