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.
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 EngineI'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.
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.
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 DBAIn 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 |
Let's take a look at the arguments for and against using stored procedures to update a database. First we'll examine the advantages:
Now let's take a look at the flip side of using stored procedure calls to update a database: