The application logic that surrounds the DB/Assist generated action routines will vary depending on the type of SQL statement that is being executed.  In most cases the SQL statement being executed will depend on user input into a data form. Before the SQL statement can be executed with this input, the information must be queried and placed into the correct variables.  Below is a short discussion of each SQL statement type and an example of how the DB/Assist action routines have been integrated into the sample session.

Multi-row Select Statement

A Multi-row select statement is used to retrieve multiple rows of data from a database into associated ESL host variables. The following action routine is used to set up the dialog "Employee List" shown below. Part of the setup routine requires that a table be filled with the Name and corresponding ID for all the employees in the STAFF table in the SAMPLE database.

Figure 1

This Multi-row SELECT statement named SelectStaff was created using DB/Assist.  When this SQL statement was compiled with DB/Assist the OPEN_SelectStaff, FETCH_SelectStaff and CLOSE_SelectStaff action routines were generated.  The OPEN_SelectStaff action routine is the first action routine called when executing a multi-row select statement and is used to open the cursor for the multi-row select statement. The FETCH_SelectStaff action routine is then called to fetch a single row of data and assign the results to the ESL host variables. This action routine will be called until there is no more data to fetch into the cursor. This condition is met when the Esqlca.Sqlcode is equal to END_OF_DATA or 100. Once this condition is met, the cursor for this select statement should be closed by calling the CLOSE_SelectStaff action routine.

Single Row SELECT Statement

A Single Row SELECT statement is used to select a single row of data from a table.

The response that follows is executed when the user clicks on the EDIT_PB in the List_DB (Figure 1). Once this is done, the name selected in the List_TBL is queried, and the ID that corresponds to this persons name is copied into the ESL host variable ID_IV. The action routine EXECUTE_SelectStaffID is executed to select all the information in the staff table that is related to the value in ID_IV. If no names are selected, a message is displayed informing  the user that a name must be selected.

The action routine named EXECUTE_SelectStaffID was generated during the DB/Assist compile of the SQL statement named InsertStaff.

Single Row INSERT Statement  

A Single Row INSERT statement is used to add a single row of data into a table.

The subroutine that follows is used to insert a single row into the STAFF table.  The information that is inserted into the database is based on user input into the Add_DB (Figure 2). This input will be queried from the fields in this dialog into the ESL host variables when the user selects the "OK" push button, and then a call will be made to the InsertStaff subroutine.

Figure 2

The action routine named EXECUTE_InsertStaff was generated during the DB/Assist compile of the SQL statement named InsertStaff.

UPDATE Statement (searched)

A searched UPDATE statement is used to modify values in a table in a database based on a query of the database.

The subroutine that follows is used to Update the STAFF table in the DB/Assist Sample Session. The information that is updated in the database is based on user input into the Edit_DB (Figure 3). This input will be queried from the fields in this dialog into the ESL host variables when the user selects the "OK" push button. A call should then be made to the UpdateStaff subroutine.

Figure 3

The action routine named EXECUTE_UpdateStaff was generated during the DB/Assist compile of the SQL statement named UpdateStaff.

DELETE Statement (searched)

A searched SQL DELETE statement is used to delete values in a table within a database based on a query of the database.

The subroutine that follows is used to delete a row in the STAFF table in the sample session. The employee that will be deleted is based on the information of the employee that is displayed in the Edit_DB(Figure 3). The employee ID of this person is located in the ESL host variable and a call to the subroutine will be executed when the user selects the "Delete" push button.

The action routine named EXECUTE_DeleteStaff is generated during the DB/Assist compile of the SQL statement named DeleteStaff.

POSITIONED SQL Statements

Positioned SQL statements allow you to perform an update or delete on the row where the cursor is currently positioned from the previous select statement. This type of statement will yield performance benefits, since the cursor is positioned on the row being updated.

The code that follows is included in Chapter 9 of the Using DB/Assist ESL manual. When the UpdateStaff_DB (Figure 4) is made visible a call to the action routine OPEN_SelectStaffUpdate is executed. This action will open the cursor for the select that retrieves all the fields within the STAFF table.

Figure 4

Each time a user clicks on the Next_PB in the UpdateStaff_DB a call will be made to the FETCH_SelectStaffUpdate action routine. This routine will increment the position of the cursor in the table. If the execution of this action results in an Esqlca.Sqlcode equal to END_OF_DATA or 100, then we have exhausted all of the information in the table. At this point we should display a message asking whether the user would like to return back to the first row selected within the table, or remain at the last row within the selected rows.

UPDATE Statement (Positioned)

A positioned UPDATE SQL statement is used to update a row in a table based on the position of the cursor within the table.

The response that follows is triggered when the user clicks on the Update_PB in the UpdateStaff_DB (Figure 4). The employee whose information is currently being displayed will be updated according to the changes made in the UpdateStaff_DB. This update is based upon the current position of the cursor from the select statement.

The action routine named EXECUTE_UpdateCurrentStaff was generated during the DB/Assist compile statement of UpdateStaff.

DELETE Statement (Positioned)

A positioned DELETE SQL statement is used to delete a row in a table based on the position of the cursor within the table.

The response that follows is triggered when the user clicks on the Delete_PB in the UpdateStaff_DB (see figure 4). The employee whose information is currently being displayed will be deleted. This delete is based upon the current position of the cursor from the select statement.

The action routine EXECUTE_DeleteCurrentStaff was generated during the DB/Assist compile statement of DeleteStaff.

Other Statements

For all other SQL statements created with DB/Assist an action routine named EXECUTE_StatementName will be created, where StatementName is the name that you have given to the SQL statement.  These action routines should be called in the same manner as the previous statements that were covered in this document. If an error occurs while executing any of these action routines the EDB2Error_BV will be set to true.

Summary

As an ESL developer using the DB/Assist development tool, you must be aware of the action routines that DB/Assist generates and know how to work these action routines into your application logic. The multi row select statement will create an OPEN, FETCH and CLOSE action routine, while all other statements will simply create an EXECUTE action routine. Before any of the DB/Assist action routines can be executed a call must be made to EDB2Connect, and a stimulus of EDB2_Started must be returned.