When we last left off, our hero was perched precariously on the edge of a cliff...oops, wrong article.

When we last left off, we had a functional shell of an application. We had a menu that would open our five different sheets (Controls, Users, Messages, Menus, and Contacts); we had the shell of those five sheets themselves; we had some basic shared functionality with regards to managing sheets (arranging sheets, closing all open sheets); we had some basic shared DataWindow/DataStore error handling in the DBError event of our base-class u_dw and n_ds objects; we had some basic transaction management hooks in place in our base-class n_tr transaction object.

The list of the remaining steps is still quite long. We'll need to make some decisions regarding security (login and functional access), our transaction model, and our data access model. We'll have to design our DataWindows and make some decisions regarding the rest of the look-and-feel of the application sheets.

This month, I'd like to plow forward and define our data access model.

When I refer to a data access model, I'm referring to our methodology for selecting and updating the data the application will present. Although selection and update models can be different, it's usually more straightforward and certainly more consistent to use the same method for both. Assuming your application will be getting its data from a database (not always the case), you have two basic choices. As an aside, I'll simply add that, generally speaking and in my (humble) opinion, the fewer the choices the better - you can simply get down to designing and coding that much faster.

The two main choices for getting data from a database are SQL and Stored Procedures. As with everything else, there are advantages and disadvantages to each. Both can be used as a DataWindow object's data source. If you use SQL as the data source, the SQL code is stored with the DataWindow object. This makes its reuse as a data source for another DataWindow object or another application impossible. Any changes to the logic of the select must be made in the DataWindow object, which necessitates a new application release. In most cases, this may be a minor issue because you're probably making application changes anyway. But there may be times when you really only need to update a where clause or add an order by clause. In this case you still have to deploy the application and application deployment is not always easy.

The other choice is the use of stored procedures. Stored procedures have several advantages over using SQL as the DataWindow object data source. Firstly, there's a slight performance boost since the SQL has already been compiled and the query plan has already been generated. The database server simply executes the query plan to get the data. Depending on the nature of your data, however, it's very important to have the stored procedure recompiled periodically to ensure that the saved query plan is optimal for the data in the table. You can usually have the DBA group recompile your procedures weekly or at whatever interval is required. Secondly, by moving the data access out of your application, you can reuse the data access logic that's stored in the procedure from other places in your application or other applications. Thirdly, you can employ, as necessary, the more complex logic that may be required to generate the result set. You can use temporary tables, do calculations, invoke other stored procedures, or access data from other tables by using more advanced database features like proxy tables. The sky's the limit with stored procedures.

From a database security standpoint, stored procedures can be secured in ways that SQL can't. To select data using a SQL statement, the user must be given SELECT access to the tables that will be accessed. That means the user, or more importantly, anyone impersonating the user, will be able to see ALL the data in those tables. This may constitute a security violation if the data is highly confidential, for example. The application, on the other hand, may only be allowing view access to SOME of the data.

With stored procedures, a privilege must be granted to the user so the user can execute the stored procedure and have access to the result set. A user can be granted execute privileges WITHOUT being granted any access to the underlying tables accessed in the stored procedure. This is a key differentiator. When I worked for a law firm a number of years ago, the design decision was to withhold direct select, update, insert, and delete access from all tables in the system, including system tables. This way, even if someone could get into the database, they wouldn't be able to query the system tables even to find out the names of the stored procedures, let alone have access to the underlying data.

If your database doesn't support stored procedures, your choice is easy...SQL. If your database does support stored procedures, even if you don't have security constraints or other applications that need to share access to the same select logic, I strongly suggest you use (or begin to use) stored procedures as your access mechanism.

For inserts, updates and deletes, there are also two main choices. You can use the built-in DataWindow object update capabilities or you can use stored procedures. We'll see examples of everything as we start building the actual DataWindows we need for our application.

You can tell Powerbuilder to generate the appropriate insert/update/delete SQL statements during the DataWindow Update() call by invoking the Update Properties dialog (see Figure 1) while in the DataWindow painter using the Rows | Update Properties menu item. You specify the table to update, which columns should be updated, the key columns for the table, how to build the where clause, and what should happen if a key value has been modified. In the case of an insert statement, the where clause and key modification selections are ignored. In the case of a delete, only the where clause choices are used. In the case of an update, potentially all selections are used by Powerbuilder to construct the appropriate update statement. For each new/modified/deleted row in the DataWindow, the appropriate SQL statement is generated from these selections and then executed. Note that all deletes are processed first, followed by the inserts and updates in the order that the modified rows are found in the DataWindow's primary buffer.

Powerbuilder supports DataWindow updates via stored procedures by letting you specify the stored procedures to invoke for inserts, updates, and deletes (see Figure 2). You indicate the stored procedure for each type of update and then map the DataWindow columns to the stored procedure arguments as needed. Arguments required for the stored procedure that aren't column-based can also be specified. Then, during the Update() call, Powerbuilder invokes the appropriate procedure for each inserted, modified, and/or deleted row. In this scenario, as above, deletes will be processed first, followed by the inserts/updates in the order the rows were found in the Primary buffer.

Note that the DataWindow update properties will be used if a stored procedure has not been specified. For example, if you specify only insert and update stored procedures, deletes will be handled via the update properties of the DataWindow. This can become a very confusing situation if you're not careful and consistent. I strongly advise that if you decide to use stored procedures for one kind of update, make sure you define stored procedures for all three kinds and make sure that the Allow Updates checkbox on the Update Properties dialog is unchecked. You don't have to allow updates in the Update Properties dialog if you specify stored procedures for updates. Further, if your application, for example, doesn't allow deletes, you don't have to specify that a stored procedure be invoked for deletes. You should only specify those procedures that you need to support your application functionality. On the other hand, if your application allows row deletions and you don't specify a stored procedure for deletes and you don't specify that the DataWindow is updatable via the Update Properties dialog, the Update() function will fail because no delete criteria has been specified. This can quickly become very confusing so, again, choose one way to handle your updates consistently and be sure the other method isn't available to get in the way.

For purposes of example and explanation we'll actually develop our DataWindows with a combination of stored procedures and SQL. Normally you'd never catch me doing this.

So, for our five sheets, let's map our data access as indicated in Table 1.