Managing Command Objects
Remember that there are four phases to the creation and execution of Command objects:
- Create the Command object and construct the Parameters collection.
- Set the Parameter Value property for each input parameter.
- Execute the query.
- Process the results.
Phase 1 should be executed once. The remaining phases can be done on an "as required" basis, later in your application. If you review a client/server application that reruns the first phase repeatedly, revisit the source, and code the initialization process to a one-time-only event procedure. This is another best practice. Unfortunately, in the middle tier (and ASP) code, you have to construct the Command objects each time. But remember that Command objects are not required unless you're executing queries that return OUTPUT or Return Status values from the query.
Executing and Reexecuting Command Objects
After the Command object is constructed, the expensive part of constructing the Command object on the client is completed. We discussed some of the ways to execute queries in Chapter 4, so what follows won't be all that new. The Command's query can be executed in several ways:
- Using the Execute method on the constructed Command object.
- Using the Command object as a method on the Connection object.
- Referencing the Command with the Execute method on the Connection object.
- Referencing the Command with the Open method on the Recordset object. This technique is discussed in Chapter 6 where we focus on creating Recordsets.
Let's take a look at these techniques one at a time. Some of the primary differences you want to watch out for are how the Recordset is constructed (do you get to build it ahead of time or do you have to take the standard default) and how the parameters are passed to the Command object (do you have to provide them parameter-by-parameter or can you pass them as method arguments one way or another).
Using the Execute Method on a Command Object
One of the simplest and least flexible techniques is to simply "execute" the Command. This approach tells ADO to take the Command as it's currently populated and run it, and (optionally) construct a new Recordset object to receive the rowset. The Command object's Execute method is basically the same as when used against a Connection object (as we discussed in Chapter 4).
The following code example illustrates the use of the Execute method. (I use this Command object for the rest of the Command execution examples.) The principal difference here is that when you use the Execute method, ADO constructs a virgin Recordset set to ReadOnly/ForwardOnly to handle the rowset. If you use the Command as Connection method technique, you get to construct your own Recordset beforehand. Although the default Recordset constructed by the Execute method retrieves rows efficiently, if you expect updatability, you won't get it this way.
Set cmd = New Command With cmd .Name = "GetTitles" .ActiveConnection = cn .CommandText = "Select title from titles " _ & "where title like ? " _ & "and year_published between ? and ?" .Parameters.Append CreateParameter("TitleWanted", adVarChar, adParamInput, 20) .Parameters.Append CreateParameter("YearLow", adInteger, adParamInput,, 1940) .Parameters.Append CreateParameter("YearHigh", adInteger, adParamInput) .Prepared = False End With
Handling Parameter Default Values
Note that in the preceding example, the second parameter is set up in the Parameters collection with a value (1940). This tells ADO to reset the parameter to this value after each use of the Command object. After a Command object is executed, ADO resets each Parameter.Value back to the initial setting. In the case of the first and third Parameters, the Value is set back to empty.
Now that the Command is set up, we're ready to execute it. The following line might do the trick:
cmd.Execute
This code simply ignores the "rows affected" value—it won't return anything for this query anyway—and accepts the existing parameter value settings. Oops, because they weren't set, we get a trappable error. We should have set the parameter values first.
cmd(0) = "Hitch%" cmd(1) = 1940 cmd(2) = 1990 cmd.Execute
Now the Execute method works fine. After the Command is executed, the Parameters are reset to their initial state—empty, 1940, and empty.
Passing Arguments with Variant Arrays
Another way to pass in input parameters is to use a Variant array. This approach is kinda cool. And better yet, if you don't provide one of the elements, ADO does not submit the parameter—it assumes the provider will insert the default value. If there is no default value set, either in the called stored procedure or in the initial Parameter collection, ADO returns a trappable error: "-2147217900 Incorrect syntax near the keyword 'DEFAULT'."
vParms = Array("Hitch%", 1940, 1990) cmd.Execute lRA, vParms Let's try another variation:
cmd.Execute lRA, Array("Hitch%", 1940, 1990) This technique bypasses the creation of a separate Variant array and simply uses the new Visual Basic Array function. In this case, we let the existing setting of the second parameter (cmd(1)) be used, because it has a default value. This variation, however, prevents you from reading the OUTPUT parameters off the array.
If you're executing an action query, it's nice to know how many rows are affected. If the provider can tell you, the value is returned in the Long variable passed as the first argument, RecordsAffected. It does not return the number of records in your rowset. It's passed to our routines above in the lRA variable.
Setting Additional Execution-time Options
When we defined the Command object, we specified the CommandType property, so we shouldn't have to provide it again when it comes time to execute a Command, but you can. There are also other more interesting options that do make sense when using the Execute method, including options that tell ADO you aren't expecting rows, that it should execute asynchronously, and several others, as shown in Table 5-3:
| CONSTANT | DESCRIPTION |
|---|---|
| adAsyncExecute | Indicates that the command should execute asynchronously. This returns control to your application or component immediately (just after the first CacheSize rows are fetched), freeing up your thread for other tasks. It does not make a lot of sense to use this option for the middle tier, but it can really help "perceived" performance with client/server rigs. |
| adAsyncFetch | Tells ADO to asynchronously fetch the remaining rows after the initial quantity specified in the CacheSize are fetched. If this option is specified, ADO keeps fetching rows so that rowset population occurs more quickly. This is important for the overall system, as well as for your application, as rows past the initial cache are available more quickly. I discuss asynchronous fetching in Chapter 6. |
| adAsyncFetchNonBlocking | Prevents the main thread from blocking while retrieving. If the requested row has not been retrieved, the current row automatically moves to the end of the result set (EOF). |
| adExecuteNoRecords | Indicates that the command text is a command or stored procedure that does not return rows (for example, a command that only inserts data). If any rows are retrieved, they are discarded and not returned. This option is always combined with CommandType options of adCmdText or adCmdStoredProc. This option prevents ADO from constructing a Recordset object in cases where it's not needed. |
By using the right set of options you help ADO do less work to run your query and fetch rows more efficiently. You might have to "add" several of these together, as shown in the following code:
cn.Execute("MyUpdateSP", , adCmdStoredProcedure + adExecuteNoRecords) When the Command object completes (successfully or not) an ExecuteComplete event fires.
Executing a Command Object as a Connection Method
Because we are also discussing developer performance, it makes sense to mention the Command as Connection method technique. This technique uses a named Command object as a method against the ADO Connection associated with it. That is, once you name a Command object (by setting its Name property) and set the ActiveConnection to a Connection object, you can use the following syntax to execute it:
Private Sub cmdRunQuery_Click() cnMyConnection.GetAuthors txtYearWanted.Text, rs ' parameter, Recordset ProcessResults(rs) End Sub
In this case, I'm executing a Command object called GetAuthors. The input parameters are passed as arguments to the method. The last argument passed is a reference to an instantiated Recordset object, which will contain the rowset when ADO completes executing the query.
| Tip | For reasons that have to do with how COM works behind the scenes,[11] you must fully qualify all object-sourced parameters when using this technique. This means that you can't just pass in txtYearWanted, which should refer to the default Text property of the TextBox control referenced. Instead, you must pass in txtYearWanted.Text. This prevents a number of strange behaviors, including confused parameters being passed to the data provider. |
This Command as Connection method technique is also very easy to code and yields excellent performance. In this case, you work with a preconstructed Recordset object configured with the correct CursorType, LockType, and other properties that make sense. Unlike the Command object's Execute method (discussed next), you get to describe how the Recordset object is to be constructed.
| Warning | There is an outstanding bug posted against this technique. For more information, see "The Other Side of the Refresh Method Story" section, earlier in this chapter. |
Using the Command Object's Execute Method
The Command object's Execute method is far less flexible than other techniques. It also has a number of, well, interesting side effects that you might not be aware of. There are two flavors of the Execute method:
- Against a Connection object: In this case, you provide a Source argument, but you can't specify a Command object here as you can with the Recordset object's Open method (which I discuss in Chapter 6). You have to pass the SQL query as an SQL statement, a table name, a URL, or the name of a stored procedure. You also need to pass an appropriate CommandType in the Options argument—or let ADO guess the type of command to execute—but you should always provide the CommandType to prevent ADO from "guessing" what's in the query you're submitting.
- Against a Command object: In this case, the Source argument is contained n the Command object, as are all of the other arguments needed to run the query, including the CommandType. But, again, ADO constructs the Recordset for you, so you have to settle for the "default" Recordset properties—read-only, forward-only.
In either case, if you want to return a rowset, you must capture the Recordset passed back from the Execute method. The Execute method always constructs the Recordset to the default specifications (read-only, forward-only) for you. For example:
Set rs = cmd.Execute
If you don't need a Recordset, use the alternative syntax:
cmd.Execute
However, there is an interesting side effect. In some cases, and especially when you create a Recordset using the Execute method against a Connection object, ADO opens an additional connection to run the query, and closes it afterwards. This is especially true if you use the Execute method again before fully populating the original Recordset.
Actually, the sequence of operations triggers this behavior. For example, if you set cmd.ActiveConnection to cn, and use the cmd.Execute method followed by cn.Execute, ADO does not open a new connection. However, if you use the cn.Execute method followed by the cmd.Execute method, ADO does create another connection to run the Command.
Tracing the Server and Data Provider
When you use ADO to execute your query, the server dutifully executes what ADO and the data provider sends it. However, how efficiently these statements are executed and how they affect scalability is an important consideration. The CursorLocation (client-side or server-side cursors), CursorType, LockType, and even the syntax you use can affect what type of query is sent to the backend. Because only SQL Server exposes enough trace information for you to be able to see what's actually getting transmitted to the server, I'm going to walk you through a number of configurations so that you can choose the query style that makes the most sense for your application or component. At least you'll understand which factors affect the resulting commands sent to SQL Server.
| Note | Yes, you can also dump the ODBC logs to get an idea of what the upper layer of ODBC is telling the driver to do. But this does not really show how the data driver implements this ODBC call. That's what the SQL Server Profiler (or SQLTrace) shows. And this does not show how OLE DB providers work behind the scenes. |
I'm going to step through these configurations one group at a time. First, server-side cursors with the default Recordset settings of read-only, forward-only are listed in Table 5-4. Table 5-5 lists server-side cursors with keyset/optimistic cursors. Notice the considerable difference in the number of round-trips and the complexity of the queries that the server has to process to execute basically the same Command but using different syntax to support the selected cursor types.
| COMMAND SYNTAX | GENERATED QUERY |
|---|---|
| Cn.GetTitlesByYear | GetTitlesByYear 'Hitch%', 1900, 1999 |
| rs.Open cmd | sp_executesql N'Select Top 50 title, ISBN, Year_Published from titles where title like @P1 and year_published between @P2 and @P3', N'@P1 varchar(20),@P2 int,@P3 int', 'Hitch%', 1900, 1999 |
| rs.Open strSQL | Select Top 50 title, ISBN, Year_Published from titles where title like 'Hitch%' and year_published between 1900 and 1999 |
| Cn.GetTitles | Sp_executesql N'Select Top 50 title, ISBN,… |
| Set rs = cmd.Execute…[a] | Sp_executesql N'Select Top 50 title, ISBN, … |
|
[a]Using the Command.Execute method only makes sense with forward-only/read-only, because ADO manufactures the Recordset on each invocation. | |
| COMMAND SYNTAX | GENERATED QUERY |
|---|---|
| Cn.GetTitlesByYear | declare @P1 int declare @P3 int declare @P4 int declare @P5 int set @P1=NULL set @P3=102401 set @P4=311300 set @P5=NULL exec sp_cursoropen @P1 output, N'Select Top 50 title, ISBN, Year_Published from titles where title like @P1 and year_published between @P2 and @P3', @P3 output, @P4 output, @P5 output, N'@P1 varchar(20),@P2 int,@P3 int', 'Hitch%', 1900, 1999 select @P1, @P3, @P4, @P5 |
| rs.Open cmd | Sp_cursoropen … sp_cursorfetch …(several times) |
| rs.Open strSQL | Sp_cursoropen… sp_cursorfetch (several times) |
| Cn.GetTitles[a] | sp_cursoropen … (several times) sp_cursorfetch … (several times) |
|
[a]There seems to be some controversy about this technique. The ADO people are still working on it. | |
Client-side cursors are far less flexible than server-side cursors. In this case, only the Static CursorType is supported, and updatability is only provided if you choose adLockBatchOptimistic as the LockType. No, I don't expect you to study these tables that closely. I do expect you to try these tests on your own. The source code to set up all of these tests is on the book's CD. Dig into the ..\sample application\command objects\ directory for the code. Table 5-6 lists client-side static/optimistic cursors, and Table 5-7 lists client-side static/batch optimistic cursors.
| COMMAND SYNTAX | GENERATED QUERY |
|---|---|
| rs.Open cmd | sp_executesql… |
| cmd.Execute | sp_executesql … |
| Cn.GetTitlesByYear | GetTitlesByYear 'Hitch%', 1900, 1999 |
| Cn.GetTitles | sp_executesql… |
| rs.Open strSQL | Select Top 50 title, ISBN, Year_Published from … |
| COMMAND SYNTAX | GENERATED QUERY |
|---|---|
| rs.Open cmd | sp_executesql… |
| cmd.Execute | sp_executesql … |
| Cn.GetTitlesByYear | GetTitlesByYear 'Hitch%', 1900, 1999 |
| Cn.GetTitles | sp_executesql… |
| rs.Open strSQL | Select Top 50 title, ISBN, Year_Published from … |
As you can see, there are a variety of approaches that the SQL Server data provider takes when executing queries on ADO's behalf. However, ADO seems to be fairly predictable, regardless of the cursor location setting. If you use client-side cursors and execute an ad hoc query using the Recordset Open method, or execute a stored procedure, ADO simply takes the SQL and "passes it through," not attempting to leverage any of the system-level sp_cursor functions or even use the sp_executesql function. This is not necessarily wrong. SQL Server knows how to persist query plans for ad hoc queries, and the query plans for SQL Server are already compiled.
Calling stored procedures seems to be far more efficient than using ad hoc queries or Command objects. When you stick with forward-only/read-only concurrency, ADO just passes the stored procedure invocation on through to the server, even managing the parameters. If you ask for an updatable cursor, ADO prefixes the stored procedure call with a sp_cursoropen to handle scrolling and updating.
Detecting Command State
The Command object's State property is only really interesting when you execute a query asynchronously. That is, if you start executing a Command using any one of the techniques discussed in this chapter, the State property can indicate whether or not the query has completed. As long as ADO is busy managing the execution of your Command object, the Executing (adStateExecuting) bit of the State property (4) is on and the Open (adStateOpen) bit is off. Yes, you can poll the adStateExecuting bit as a way of waiting until an asynchronous operation is done, but I would rather use the ExecuteComplete event to do the job. Using the event consumes far less CPU resources when compared to polling—especially when compared to loop polling. It's like having your daughter sitting in the back seat on the way to grandma's house asking (every two minutes), "Are we there yet, daddy?"
The Cancel Method
So, you started the execution of your Command object with the adAsyncExecute option, and this returned control of your thread so that you could entertain the user with a progress bar or launch "Age of Empires II" while the user waits. If your user decides to quit waiting, you can use the Cancel method to stop processing the query—or at least you can try. Sometimes it's like trying to stop a train 40 feet from the rail crossing.
| Note | The Cancel method can also be applied to the Connection object to cancel an asynchronous open operation, but that's not as likely to be necessary—most connections complete before the user can blink, and if they are delayed longer than that, it's usually a network delay that can't be interrupted anyway. |
You get a trappable error if the operation is not stoppable, such as if you try to cancel an operation that wasn't started asynchronously.
Consider that it might not be safe to cancel an operation midstream—especially operations making changes to the database. SQL Server and other providers won't (necessarily) back out changes already made unless there was a transaction started beforehand.
ADO Command Object's "Fallback" Plan
The ADO named command fallback plan seems to be stored procedures. That is, if you don't create a Command object and you reference a named object off of the Connection object (like this):
cnOLEDB.TestQuery txtParm, rs
ADO will assume that TestQuery is a stored procedure. If it's not, you'll trip an error complaining that the stored procedure TestQuery could not be located. The same will happen if you use adCmdText as the Command.CommandType property and submit a single object name.
This means you can execute any stored procedure (parameters and all) using the following syntax (assuming GetTitles is a stored procedure):
Cn.GetTitles "1980", rsMyRecordset
The problem with the stored procedure as Connection method technique is that it cannot handle OUTPUT parameters. This means that if the stored procedure has OUTPUT parameters, this technique will not return them to you—no Command object is created to handle them. While you can execute the stored procedure, you have to pass placeholders for each of the OUTPUT parameters because ADO defaults OUTPUT Parameter.Direction to INPUT/OUTPUT (adParamInputOutput). But when the query is executed, there is no Command object created to fetch the returning parameters.
Command Object Tips and Warnings
As I was working with the Command object to develop the example and test code, I came across several miscellaneous tips. A summary of those tips and techniques appears below. I've also included a few suggestions that I picked up from my students and the folks I communicate with via e-mail:
- If you try to execute a stored procedure, and the parameters you describe don't match what SQL Server expects, almost anything can happen, including the error: "-2147418113 Unexpected failure."
- If you set the CommandType property to adCmdStoredProc and you reference a table instead of a stored procedure, you'll trip this trappable error: "-2147217900 — The request for procedure 'Publishers' failed because 'Publishers' is a table object." It's clear that ADO is watching out for you. The Profiler showed no signs of running round-trips to the server to figure this out, even though one actually takes place. However, this information might have been cached.
- If the argument passed to the Command object's parameter does not meet the datatype requirements (string, number, integer, date, currency, and so forth) your code will trigger a 3421 error: "The application is using a value of the wrong type for the current operation." Who writes these error messages anyway?
- If the CommandType is left at adCmdUnknown and the query contains a SELECT statement, ADO figures it out on its own and resets the CommandType to adCmdText.
- If the CommandType is adCmdTable, anything other than a table name in the CommandText trips the following syntax error when you attempt to execute the query: "-2147217900 Incorrect syntax near the keyword 'select'." However, the ODBC provider does not trap incorrect table names. I set up a Command object using adCmdTable with Fred as the CommandText, and when ADO submitted SELECT * FROM FRED, ADO did not complain. On the other hand, the OLE DB provider returned: "-2147217865 Invalid object name 'fred'." Interesting.
| Note | Error messages returned from the OLE DB provider arrive without any prefix indicating which layer caused the error. The ODBC messages come back looking like this:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'fred'. |
Handling Connection Object Events
This is easy because there aren't any events exposed on the Command object. You have to depend on the Connection or Recordset object to handle events associated with the Command. If you run a Command object asynchronously, you can trap the WillExecute or ExecuteComplete event on the associated Connection to determine when it's done, or poll the Command object's State property.
[11]All parameters are stored in a DISPARAMS structure of pure variants, but control references are valid variants, so Visual Basic just passes those—and ADO chokes.
Team-Fly |
EAN: 2147483647
Pages: 106