This section contains topics that relate to advanced ADO objects programming.
Data definition language are SQL statements that support the definition or declaration of database objects, such as CREATE TABLE, DROP TABLE, or ALTER TABLE. Executing data definition language queries does not generate any record sets; therefore, there is no need to use the Recordset object. The Command object is ideal for issuing data definition language queries. In order to differentiate data definition language that queries from a table or stored procedure name, the CommandType property of the Command object must be set to adCmdText.
SQL Server provides a set of query processing options that can be set with the SET statement. These set options do not generate any result sets and, therefore, can be treated under the same category of data definition language queries.
The example shows using the Command object to turn off the SET NOCOUNT option of the SQL Server Transact-SQL SET statement:
Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command
' If the ADOTestTable does not exist
On Error GoTo AdoError
Cn.Open "pubs", "sa"
Set Cmd.ActiveConnection = Cn
Cmd.CommandText = "drop table ADOTestTable"
Cmd.CommandType = adCmdText
Cmd.Execute
Done:
Cmd.CommandText = "set nocount on"
Cmd.Execute
Cmd.CommandText = "create table ADOTestTable (id int, name char(100))"
Cmd.Execute
Cmd.CommandText = "insert into ADOTestTable values(1, 'Jane Doe')"
Cmd.Execute
Cn.Close
Exit Sub
AdoError:
Dim errLoop As Error
Dim strError As String
' Enumerate Errors collection and display properties of
' each Error object.
Set Errs1 = Cn.Errors
For Each errLoop In Errs1
Debug.Print errLoop.SQLState
Debug.Print errLoop.NativeError
Debug.Print errLoop.Description
Next
GoTo Done
End Sub
The example drops a table, creates the table, and inserts data into the table using the Execute method of the Command object. Recordset objects are not created for this type of query strings. The ADOTestTable table may not exist in the database, so execution of drop table ADOTestTable may generate an error to indicate that the table does not exist in the database. Some error handling code is provided to handle this situation. The set nocount on set option is also being executed.
Queries can be prepared before they are executed, or they can be executed directly. The Prepared property of the Command object allows you to specify whether or not to prepare a query.
If the Prepared property is set to TRUE, a query string is parsed and optimized at the first execution. Any subsequent execution of the query string uses the "compiled" version. It takes a longer time for the first execution, but performance improves for subsequent executions because the query string has already been parsed and optimized earlier at prepare time. However, if you are executing the query string only one time, you should execute the query string directly instead of preparing it.
The Prepared property can also be used when executing with multiple parameter sets. An application can execute a parameterized query string more than once by supplying a different parameter set at each execution instead of reconstructing the query string whenever the parameter set is different. However, if you are executing a parameterized query string only one time, it is not necessary to prepare the query string.
SQL Server does not directly support the Prepare/Execute model of ODBC. When a statement is prepared, the SQL server ODBC driver creates a temporary stored procedure for the statement. This temporary stored procedure exists in tempdb and is not dropped until the Recordset or Connection object is closed.
This option can be disabled through the SQL Server ODBC Data Source Setup dialog box if an ODBC data source is used to connect to SQL Server. If the option is disabled, the SQL statement is stored and then sent to the server each time it is executed.
The example shows using a prepared statement for updating a query and dynamically constructing the query with a different set of parameters at execution time:
Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Dim prm1 As New ADODB.Parameter
Dim prm2 As New ADODB.Parameter
Cn.Open "DSN=pubs", "sa"
Set Cmd.ActiveConnection = Cn
Cmd.CommandText = "update titles set type=? where title_id=?"
Cmd.CommandType = adCmdText
Cmd.Prepared = True
Set prm1 = Cmd.CreateParameter("Type", adChar, adParamInput, 12, "New Bus")
Cmd.Parameters.Append prm1
Set prm2 = Cmd.CreateParameter("Title_id", adChar, adParamInput, 6, "BU7832")
Cmd.Parameters.Append prm2
Cmd.Execute
Cmd("Type") = "New Cook"
Cmd("title_id") = "TC7777"
Cmd.Execute
Cmd("Type") = "Cook"
Cmd("title_id") = "TC7778"
Cmd.Execute
Cn.Close
The example updates data in the titles table by using different parameter values. The query string is prepared so that different sets of parameters can be supplied. Two parameters are required for the update operation: type and title_id. They are created by the CreateParameters and appended to the Parameters collection with the Append method.
The first set of parameters has the values of New Bus and BU7832. Different values of the parameters can be supplied before the Execute method without reconstructing the query string, because the Prepared property is set to TRUE.
Executing a stored procedure is very similar to executing a prepared query, except the stored procedure exists in the database as an object even after execution is finished. A stored procedure can also be used to hide the complex SQL statements from the application.
When executing stored procedure in a Command object, the CommandType property must be specified with the adCmdStoredProc value. With the adCmdStoredProc value specified, the corresponding SQL statement for the underlining provider is generated. With the ODBC Provider, the ODBC escape sequences for procedure calls, {[?=]call procedure-name[([parameter][,[parameter]]...)]}, are generated and the SQL Server ODBC driver is optimized to take advantage of these sequences.
This example shows executing the sp_who SQL Server system stored procedure:
Dim Cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Cmd.ActiveConnection = "DSN=pubs;uid=sa"
Cmd.CommandText = "sp_who"
Cmd.CommandType = adCmdStoredProc
Set rs = Cmd.Execute()
Debug.Print rs(0)
rs.Close
For optimal performance, an application should never prepare the execution of a SQL Server stored procedure. By doing so, the additional overheard of creating a temporary stored procedure and indirection is incurred.
Stored procedures may contain input and output parameters and return values. For example, the following myProc stored procedure contains the @ioparm output parameter and returns a value of 99.
CREATE PROCEDURE myProc @ioparm int OUTPUT AS
SELECT name FROM sysusers WHERE uid < 2
SELECT @ioparm = 88
RETURN 99
An input parameter for a stored procedure can be specified through the Parameter object. The output and return parameter can also be specified through the Parameter objects, but the actual value for both parameters is not returned until the data of the Recordset object has been completely fetched out or the Recordset has been closed.
The ADO code is:
Dim Cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim param As Parameter
Cmd.ActiveConnection = "DSN=pubs;UID=sa"
Cmd.CommandText = "myproc"
Cmd.CommandType = adCmdStoredProc
' Set up parameters.
Set param = Cmd.CreateParameter("Return", adInteger, adParamReturnValue, , 0)
Cmd.Parameters.Append param
Set param = Cmd.CreateParameter("Output", adInteger, adParamOutput, , 0)
Cmd.Parameters.Append param
Set rs = Cmd.Execute
If Not rs.EOF And Not rs.BOF Then
Debug.Print rs(0)
rs.Close
End If
Debug.Print Cmd(0) ' The return code
Debug.Print Cmd(1) ' The Output parameter
Two parameters are needed for the myProc stored procedure: an output parameter to hold the return value, 99, and the output parameter, @ioparam. The example first creates two parameters: Return and Output. The Return parameter is created as a return type of parameter adParamReturnValue, and the data type is adInteger for integer. The Output parameter is adParamOuput for the output parameter type, and the data type is adInteger for integer. Because the data type of both parameters is integer, there is no need to specify the data length.
After the parameters are appended, executing the query string creates a record set. The example closes the Recordset object to get the return code and output parameter.
The Update method of the Recordset object allows you to update the current record. The UpdateBatch method applies all pending new, updated, and deleted records to the Recordset object. Using a LockType of adLockBatchOptimistic, the UpdateBatch method allows you to commit all the pending changes in the client computer and send all the changes to the database at one time. The pending changes can alternately be canceled by the CancelBatch method.
With the UpdateBatch method, if all the changes fail to be applied to the database, an error is returned. If only some of the changes fail, a warning is returned instead of an error using the Errors collection and Error object.
In SQL Server, the UpdateBatch method is only valid when the LockType property is specified with adLockBatchOptimistic and the cursor type is either keyset-driven or static. The keyset-driven cursor can only be open with tables that have unique indexes.
The example shows using the UpdateBatch method to apply all the pending changes:
Dim rs As New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockBatchOptimistic
rs.Open "select * from titles", "DSN=pubs;uid=sa"
' Change the type for a specified title.
While (Not rs.EOF)
If Trim(rs("Type")) = "trad_cook" Then
rs("Type") = "Cook"
End If
rs.MoveNext
Wend
rs.UpdateBatch
rs.Close
The example creates a record set using the keyset-driven cursor with LockType set to adLockBatchOptimistic. After the Recordset object is created, the trad-cook type is changed to Cook, a new type value, for all the records in the title table. After all the changes are completed, changed data is committed as a batch using the UpdateBatch method.
Instead of executing one query at a time, SQL Server allows a batch of queries to be issued and executed. When a batch of queries is executed, more than one record set can be generated. In additional batches of queries, multiple record sets can also be generated by SQL statements that include COMPUTE BY and COMPUTE clauses, or by stored procedures that contain more than one SELECT statement.
When multiple record sets are generated, it is important to fetch one record set at a time until no more record sets are available. The NextRecordset method of the Recordset object allows you to fetch any subsequent record sets. If no more record sets are available, the returned Recordset object is set to Nothing.
The example shows using NextRecordset method to fetch multiple record sets.
The stored procedure syntax is:
drop proc myNextproc
go
create proc myNextproc as
select * from titles
select * from publishers
The stored procedure generates two result sets: one for the result of select * from titles and the other for the result of select * from publishers.
The ADO code is:
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Cmd.ActiveConnection = "DSN=pubs;UID=sa"
Cmd.CommandText = "myNextProc"
Cmd.CommandType = adCmdStoredProc
Set rs = Cmd.Execute()
While Not rs Is Nothing
If (Not rs.EOF) Then
Debug.Print rs(0)
End If
Set rs = rs.NextRecordset()
Wend
After the myNextProc stored procedure is executed, a Recordset object is created. Because there are two result sets generated by myNextProc, each Recordset object can be retrieved using the NextRecordset method.
SQL Server provides a set of server cursor types for applications to use. By default, an ADO application does not use server cursor when using the ODBC Provider with SQL Server. The default nonserver cursor used by the ADO application is forward and read-only.
Server cursors are useful when updating, inserting or deleting records. Server cursors also allow you to have multiple active statements on the same connection. By design, SQL Server does not allow multiple active statements per connection unless server cursors are used. An active statement means that there are some pending results in the statement handle. If server cursors are not used and the application attempts to have more than one active statement, the application gets the "Connection Busy with Another Active Statement" error message.
An application can do one of the following to use a server cursor:
Server cursors:
This example shows opening a dynamic server cursor:
Dim rs As New ADODB.Recordset
rs.Open "select * from titles", "DSN=pubs;UID=sa", adOpenDynamic, adLockOptimistic
rs.Close
Long data types include SQL server text and image data types. Text and image data sometimes can be fit into memory or be so huge that it cannot be retrieved in a single operation or all fit into memory. If the long data can all fit into memory, the Value property of the Field object can be used to retrieve all the data in one operation. If the long data is too huge to fit into memory, the data must be retrieved or written in chunks. There are two ways to manipulate the long data in chunks: One is through the Field object, and the other is through the Parameter object. Both the Field and Parameter objects provide the AppendChunk method, and the Field object provides the GetChunk method for long data.
The Field object allows you to write and read long data through the Recordset object. The AppendChunk method of the Field object allows you to append data at the end of the current data when the query has already been executed. The GetChunk method allows you to read the data in chunks.
The Parameter object handles long data in a similar way. There is no GetChunk method for the Parameter object, and there is no Recordset object when you are dealing with long data at run time. With the Parameter object, long data is bound at run time and executed with the Command object.
There are some restrictions when using the ODBC Provider for long data. If no server cursor is used, all the long columns must be to the right of all nonlong columns. If there are multiple long columns, the long columns must be accessed in order (from left to right).
The example shows reading and writing data using the AppendChunk and GetChunk methods.
The destination table structure is:
drop table myBLOB
go
create table myBLOB( id int unique, info text)
go
insert into myBLOB values(1, 'test')
go
The myBLOB table is the destination table in which the long data is inserted.
The ADO code is:
Dim Cn As New ADODB.Connection
Dim rsRead As New ADODB.Recordset
Dim rsWrite As New ADODB.Recordset
Dim strChunk As String
Dim Offset As Long
Dim Totalsize As Long
Dim ChunkSize As Long
Cn.Open "pubs", "sa"
rsRead.CursorType = adOpenStatic
rsRead.Open "select pr_info from pub_info", Cn
rsWrite.CursorType = adOpenKeyset
rsWrite.LockType = adLockBatchOptimistic
rsWrite.Open "select * from myBLOB", Cn
ChunkSize = 1000
Totalsize = rsRead("pr_info").ActualSize
Do while Offset < Totalsize
strChunk = rsRead("pr_info").GetChunk(ChunkSize)
Offset = Offset + ChunkSize
rsWrite("info").AppendChunk strChunk
Loop
rsWrite.UpdateBatch
rsWrite.Close
rsRead.Close
End Sub
The example reads pr_info long data from the pub_info table and inserts it into the myBLOB table. After the rsRead and rsWrite record sets are created, the size of the long data is stored in the Totalsize variable. In the while loop, long data is inserted in chunks of 1000 bytes. The while loop stops when more data than the original long data size is inserted. After the insertion is completed, the UpdateBatch method is used to commit the data.