Doing It Yourself

[Previous] [Next]

Many programmers hate relying on code written by anyone else. It's as much a matter of control as it is of trust. They want to retain as much control as possible over exactly how they're updating data in their database. There are two main vehicles that these developers use to update their databases—action queries and stored procedures.

Action Queries

Before the Microsoft empire was even a glint in Bill Gates's glasses, developers relied on action queries as the tried-and-true method of modifying data. Data access developers from all walks of life have used action queries, which are sometimes referred to as query-based updates (QBU). The following is an example of an action query:

 UPDATE Customers SET BalanceDue = 100 WHERE CustomerID = 7 

Don't discount this method of updating your database simply because it's not a trendy or sexy concept. Action queries offer a great deal of flexibility and control. Some large companies have built multitiered applications that perform all updates through action queries. What's so impressive about this feat? All the data about the structure of the database used to build these action queries lies in an area of the database rather than in the application itself. Now that takes some planning.

The flexibility and control of action queries stem from the fact that you specify exactly how—and whether—the information in the database should be updated. For example, the previous action query simply sets the BalanceDue field for a particular customer. Suppose that the balance due is $50 and you want to increase it $50 by setting it to $100. However, you don't want to change the balance due for that customer if someone else changes it between the time you retrieve the record and the time you try to update it. Assuming the CustomerID has a value of 7, you could issue the following query:

 UPDATE Customers SET BalanceDue = 100 WHERE CustomerID = 7 AND BalanceDue = 50 

If another user has changed this customer's BalanceDue field, this query won't find the record it's requesting and, as a result, won't update that record in the table.

If you simply want to add $50 to the current balance due, you could issue this query instead:

 UPDATE Customers SET BalanceDue = BalanceDue + 50 WHERE CustomerID = 7 

It's easy to execute action queries using the ADO object model. Just use the Execute method on either the Connection or Command object. Here's an example that uses the Connection object:

 Dim cnDatabase As ADODB.Connection  strSQL = "UPDATE Customers SET BalanceDue = 100 WHERE CustomerID = 7" cnDatabase.Execute strSQL, intRowsAffected, _ adCmdExecuteNoRecords + adCmdText MsgBox intRowsAffected & " record(s) affected." 

For more information on executing action queries, see the information on the Connection and Command objects in Chapters 3 and 5, respectively.

Action Queries and the ADO Cursor Engine

I'll let you in on a little secret. The ADO Cursor Engine creates action queries to perform updates. Even if you're planning to write your own action queries to update your database, you can still learn a lot from reading Chapters 10 and 11 on how the ADO Cursor Engine builds and handles action queries. You might get some great ideas. You might also realize that it's more difficult than you thought to build a multiuser application that utilizes action queries to update your database. Perhaps those of you who once scoffed at the thought of letting someone else's code handle your database updates will look at everything the ADO Cursor Engine does and decide to use it instead of doing all that work yourself.

Pros and cons of using action queries

What are the pros and cons of using action queries to modify data in your database? First, let's look at the pros.

One reason for using action queries is that they are extremely flexible. You can accomplish just about any modification to a record by implementing one or more action queries. Another reason is that almost all database systems support standard SQL action queries. If you decide to change back-end databases, you might not need to change the structure of your action queries at all.

Finally, action queries can simplify the process of handling updates from multiple users. You can specify values in your WHERE clause that ensure you're not overwriting another user's changes. If the query doesn't update any records, you can create additional queries to determine why.

The one major drawback of updating your database by creating action queries is that you need to write a great deal of code. You'll need to store the query results in your application and maintain all the data necessary to build your action queries. What's so difficult about that?

Your first instincts for building large, multiuser applications might be to wrap data in your own easy-to-manage objects. For example, rather than using an ADO Recordset object to store the contents of the Customers table, you'd create a collection of Customer objects along with all the supporting properties and methods. Populating that collection based on the results of a query isn't difficult—it's only a simple loop.

Building the action query to submit changes made to a Customer object to your database is more of a challenge. If you want to avoid overwriting changes made by other users, you'll want to use previously retrieved values or a timestamp value in the WHERE clause of your query. If you want to modify a field that contains character data, you'll need to delimit the strings in your queries and probably check the strings you're submitting to see whether that delimiter is in the string. If you need to submit BLOB (binary large object) data, you'll probably need to create a parameterized query.

Suffice it to say, this work is far from trivial. However, once you've built one application to handle the complexities involved, you might find yourself using similar routines in other applications. Perhaps you'll create your own objects to handle the bulk of this work and use them in multiple applications. Maybe, just maybe, you'll build something similar to the ADO Cursor Engine.

Stored Procedures

Like action queries, stored procedures offer a great amount of flexibility and control. Many database administrators (DBAs) allow users to perform updates only through stored procedures because of the level of control they offer.

Working with Your DBA

In some cases, the DBA is also the person developing the database application. If you're the application developer and you aren't the DBA, be sure to talk to the DBA before you start writing any code. You don't want to spend countless hours developing your database application, expecting to use action queries or updatable Recordsets to modify your data, only to learn that your DBA won't allow modifications through anything other than stored procedures.

It's always a good idea to talk to your DBA about how to design your application. The DBA will probably want to know what you're up to, especially if you plan to put any undue stress on the server.

When you use a stored procedure to update your database, you pass the information needed to modify the database into the stored procedure. As with action queries, you can control how your database performs the update by how much information you provide. You can also return information from the stored procedure to help explain the results of the update attempt: success, failure, or other information.

Microsoft SQL Server allows you to pass query results back from the stored procedure. Oracle doesn't provide such functionality. However, the Microsoft ODBC driver for Oracle and the OLE DB Provider For Oracle can retrieve an array from an Oracle stored procedure and transform that data into a structure that ADO will use to create a Recordset object. (For more information about using this feature of the Microsoft ODBC driver and OLE DB Provider For Oracle, see article Q176086 in the Microsoft Knowledge Base at http://support.microsoft.com/support/kb/articles/Q176/0/86.asp.) If you're looking for more information about building stored procedures, you'll find plenty of books on the topic written about each of the major database systems.

Stored procedures are generally faster than action queries because the database system can compile the better part of the query ahead of time. You can also use stored procedures to isolate business rules from your application. For example, you can add logic to your stored procedure to send e-mail to the purchasing department when the number of widgets in stock drops below a certain level. Because the database system is responsible for executing the stored procedure, the amount of functionality you can add to your stored procedure depends on your choice of database system.

CAUTION
Don't move all your logic to stored procedures if you're unsure of the possible consequences. Although stored procedures are fast, flexible, and powerful, you can code yourself into a corner by relying on them too heavily. What happens if you rely on functionality that's particular to the stored procedures of one database system and you later migrate your database or need access to two different database systems? Granted, this scenario probably doesn't occur frequently, but it is one you should be aware of.

Don't assume that because it's possible to modify a stored procedure without changing the code in your application, you'll always be able to do so successfully. You can make modifications to a stored procedure such as adding an optional parameter, adding a business rule that doesn't affect the parameters or data returned by the stored procedure, or retrieving data from a different table—all without changing the code in your application. However, if you add a required parameter to your stored procedure, you'll obviously need to modify the code in your application. As a general rule, think of your stored procedure as a COM component. If you change the server interface, you might not be binary compatible anymore, in which case you'll need to recompile your client code.

Here's a brief example of the ADO code required to call a stored procedure in SQL Server that sets the BalanceDue field for a particular customer:

 strSQL = "CALL SetBalanceDue('ALFKI', 150.00)" cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecords 

And here's the SQL Server stored procedure that was called from the ADO code:

 CREATE PROCEDURE SetBalanceDue (@CustomerID varchar(5), @BalanceDue money) AS UPDATE Customers SET BalanceDue = @BalanceDue WHERE CustomerID = @CustomerID 

If you want to run the query multiple times, you can use a parameterized Command object.

This example is overly simplistic. If you plan to use stored procedures to update your database, you should be prepared to handle the same multiuser optimistic updating scenarios mentioned in the previous section on action queries. In this sample stored procedure, if two users retrieve the same customer's balance due and each handles an order and attempts to modify the balance due, each user's update will succeed without generating an error. This obviously isn't a desirable scenario. The changes made by the first user will be overwritten by the second user. In this case, the following stored procedure would be a better choice:

 CREATE PROCEDURE SetBalanceDue (@CustomerID varchar(5), @Delta money) AS UPDATE Customers SET BalanceDue = BalanceDue + @Delta WHERE CustomerID = @CustomerID 

Now if multiple users call the stored procedure, the change in the balance due is registered rather than the new balance due.

But even this stored procedure is simplistic. Does your application need to know the balance due for the customer after the update is complete? How will you elegantly handle it if the customer no longer exists in your table? In this stored procedure, if no such customer exists, no error occurs. The query in the stored procedure simply does not modify any records in the table.

Let's talk about updating a Customers table with stored procedures. Suppose you're interested in updating 10 fields in a table. You could create a separate stored procedure to update each field based on the primary key value. Another option is to use a single stored procedure to update all fields, which seems like a much more palatable solution. But how do you handle optimistic concurrency scenarios with this stored procedure? You could accept two values—the original value and the new value—for each field as parameters on the stored procedure. You would then use the parameters in an action query such as the following:

 UPDATE Customers SET Field1 = @NewValue1, Field2 = @NewValue2, ... WHERE Field1 = @OldValue1 AND Field2 = @OldValue2 AND ... 

Of course, the more fields your table contains, the more complex this stored procedure becomes.

A more elegant approach is to use a timestamp field in the table. You can then use this value—along with the primary key value in your WHERE clause—rather than the original values for the fields in the table. The query in your stored procedure would look like this:

 UPDATE Customers SET Field1 = @NewValue1, Field2 = @NewValue2, ... WHERE KeyField = @KeyValue AND TimestampField = @TimestampValue 

Now that you've simplified the process of passing data into the stored procedure by reducing the amount of data you need to pass, you must examine what data the stored procedure needs to return. You'll probably want to use the return parameter on your stored procedure to indicate the success or failure of the update attempt. If the update attempt fails—possibly as the result of a business rule or because another user has updated or deleted the record since you last fetched it—you can use an output parameter to return an error number or message.

You might also want to return data from the desired record. For example, if the update is based on a timestamp value, you might want to return that new timestamp value, thereby enabling your application to update that same record in the future. Or if the update attempt fails because another user has modified the record, you might want to return the current contents of the record. Regardless of why you want to return data from the record you're updating, you can specify that the parameters corresponding to the fields in your table will support the return of data. Or you can return the current contents of the record through a query—if your database system supports returning query results from a stored procedure.

Here's an example of a SQL Server stored procedure that uses timestamp values to prevent users from unexpectedly overwriting each other's changes. If the update succeeds, the procedure returns the new timestamp value. If the update fails, it returns an error message indicating that the record was either modified or deleted. If the record was modified, the stored procedure returns its current contents.

 CREATE PROCEDURE ModifyCustomer (@ID int, @Company varchar(64) OUTPUT, ... @ErrorMsg varchar(64) OUTPUT) AS SET NOCOUNT ON UPDATE Customers SET Company = @Company, Contact = @Contact, ... WHERE CustomerID = @ID AND TSField = @TSField /* Execute action query using new values from parameters. */ IF @@ROWCOUNT = 1 /* If the query updated one row, the update was successful. */ BEGIN /* Return the new timestamp value for the modified record in the timestamp parameter. */ SELECT @TSField = TSField FROM Customers WHERE CustomerID = @ID RETURN 0 END ELSE /* If the action query did not update one record, we know it updated zero records because of our primary key. Now we must determine why the query updated no records. */ BEGIN DECLARE @NumRows int /* Determine whether the record still exists in the table. */ SELECT @NumRows = Count(CustomerID) FROM Customers WHERE CustomerID = @ID IF @NumRows = 1 /* The record exists in the table. Therefore, the update failed because the record has been modified since last fetched. */ BEGIN /* Explain why the update failed, and return the current contents of the record in the parameters. */ SELECT @ErrorMsg = 'Record was modified by another user' SELECT @Company = Company, @Contact = Contact, ... FROM Customers WHERE CustomerID = @ID END ELSE /* The record does not exist in the table. Explain why the update failed using the error message output parameter. */ SELECT @ErrorMsg = 'Record was deleted by another user' RETURN -1 END RETURN 

Pros and cons of using stored procedures

Let's take a look at the arguments for and against using stored procedures to update a database. First we'll examine the advantages:

  • Because you generally use an action query inside a stored procedure, stored procedures are as flexible as action queries.
  • Many database systems allow you to take advantage of added features inside a stored procedure. For example, SQL Server has built-in extended stored procedures you can call to send e-mail.
  • Most client/server database systems support some form of stored procedures.
  • Stored procedures are often faster than action queries because the database server can compile the query ahead of time.
  • As a security measure, many database administrators allow updates only through stored procedures.
  • In some cases, you can modify a stored procedure to change business logic without having to modify the application.
  • A stored procedure is like a shared COM component: you can call it from different applications.

Now let's take a look at the flip side of using stored procedure calls to update a database:

  • Stored procedure code is generally back-end specific. If you move from Oracle to SQL Server, or vice versa, you will need to rewrite your stored procedures. There's also no guarantee that one database system will support all the functionality available in stored procedures written for another database system.
  • Stored procedures give you one more set of components to maintain. This might seem like a picky complaint, but I've spoken with several developers who've called support saying, "My application worked just fine the other day and now…" The reason is usually that another developer who maintains the stored procedure had modified it slightly to change the business logic and didn't alert the developer who was using the stored procedure in his Microsoft Visual Basic and ADO code.
  • Stored procedures require you to learn another programming language. Another picky point. However, if your application relies heavily on stored procedures and you have little experience with them, you should adjust your ship schedule to accommodate the time you'll need to learn the stored procedure language of that database system.
  • Stored procedures require a lot of work. Just as with using action queries in your application, you'll have to write a lot of code for stored procedures. Essentially, you're looking at the same amount of code with either option. When using stored procedures to perform your updates, you need to build the stored procedures that your application will call. Furthermore, you still need to store the results of queries, call the stored procedures, and handle the results in your code.


Programming ADO
Programming MicrosoftВ® ADO.NET 2.0 Core Reference
ISBN: B002ECEFQM
EAN: N/A
Year: 2000
Pages: 131
Authors: David Sceppa

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net