|
Generating the recordset
You decide to define the recordset on the first page the user sees after logging in successfully—the info.jsp page. The recordset will consist of only one record containing the following five pieces of information:
 |
The owner's first name |
|
The owner's last name |
|
The owner's remaining occupied hours |
|
The owner's aircraft model |
|
The file name of the aircraft's image |
In the Arrow database, this information is stored in the SHAREHOLDERS and AIRCRAFT tables.
You want to use the MM_Username session variable created by the Login page to find the owner-specific information in these tables. You do this by plugging the session variable into the SQL statement defining the recordset.
To define the session variable as a SQL variable:
| 1 |
You open the info.jsp page in UltraDev. |
| 2 |
In the Data Bindings panel (Window > Data Bindings), you click the plus (+) button and select Recordset (Query) from the pop-up menu. |
|
The simple Recordset dialog box appeared, so you clicked the Advanced button to switch to the advanced Recordset dialog box. |
| 3 |
In the Name box, you name the recordset, rsOwner, and in the Connection pop-up menu, you select the connection to the Arrow database. |
|
For more information on the connection, see Arrow Aircraft 2: Setting up the development environment. |
| 4 |
In the SQL box, you enter the following SQL statement: |
|
SELECT SHAREHOLDERS.FNAME,
SHAREHOLDERS.LNAME,
SHAREHOLDERS.OCCUPIED_HRS,
AIRCRAFT.MODEL,
AIRCRAFT.PHOTO
FROM AIRCRAFT INNER JOIN SHAREHOLDERS
ON AIRCRAFT.AC_SERIAL = SHAREHOLDERS.AC_SERIAL
WHERE SHAREHOLDERS.USER_ID = `varUsername'
|
|
In this SQL statement, the SELECT clause specifies the table columns in the Arrow database that contain information for your session variables. The FROM...ON... clauses joins the two tables (AIRCRAFT and SHAREHOLDERS) that contain these columns. The WHERE clause locates the owner's information in the SHAREHOLDERS table. Because of the table join, the related information in the AIRCRAFT table is included in the recordset. |
| 5 |
In the Variables area, you click the plus (+) button and define the following SQL variable:
 |
|
The expression, session.getAttribute("MM_Username") , provides the value to the SQL variable, varUsername , which in turn is used in the SQL statement to find the owner's information. The default value, test , is a user name in a fake record you added to the SHAREHOLDERS table for testing purposes. |
|
The completed recordset dialog box looks as follows:
|
| 6 |
Click OK to close the Recordset dialog box. |
 |
|
The new recordset appears in the Data Bindings panel. However, instead of inserting the new dynamic content directly on the page as you might normally do, you will copy the recordset values into session variables, as described in the next section. Once you accomplish this, you will use the session variables instead of the recordset to display the dynamic content on pages.
|
