This section contains topics that relate to advanced ADO objects programming.
Data definition language (DDL) is 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 result 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 queries from a table or stored procedure name, the CommandType property of the Command object should be set to adCmdText.
DDL commands are a great case in which to use the adExecuteNoRecords option in conjunction with adCmdText. This option will cause ADO not to look for any result sets when executing the command, improving performance of the operation.
SQL Server provides a set of query processing options that you can 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 following example demonstrates how to use 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 + adExecuteNoRecords
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 string. 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 example also executes the SET NOCOUNT ON SET option.
You can prepare queries before they are executed, or execute them 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 or just a few times, you should execute the query string directly instead of preparing it.
You can also use the Prepared property when executing with multiple parameter sets. To execute a parameterized query string more than once, an application can supply 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 or OLE DB provider 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.
You can disable this option through the SQL Server ODBC Data Source Setup dialog box if you are using an ODBC data source 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 following example demonstrates how to use a prepared statement to update a query and dynamically construct 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 uses different parameter values to update data in the titles table. 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. The CreateParameters method creates them and appends them 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 similar to executing a prepared query, except the stored procedure exists in the database as an object even after execution is finished. You can also use a stored procedure to hide the complex SQL statements from the application.
When executing a 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.
The following example demonstrates how to execute 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, never prepare the execution of an SQL Server stored procedure. If you do so, you incur additional overheard of creating a temporary stored procedure and indirection.
Stored procedures may contain input and output parameters and return values. For example, the following myProc stored procedure contains the @ioparam 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
You can specify an input parameter for a stored procedure through the Parameter object. Specify the output and return parameter through the Parameter objects as well, but know that 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 as follows:
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 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 result 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 any of the changes fail to be applied to the database, an error is returned. You can access the 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 following example demonstrates how to use 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 Recordset 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 you to issue and execute a batch of queries. When executing a batch of queries, more than one result set can be generated. In additional batches of queries, multiple result 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 result sets are generated, it is important to retrieve one result set at a time until no more result sets are available. The NextRecordset method of the Recordset object allows you to retrieve any subsequent Recordset objects. If no more result sets are available, the returned Recordset object is set to Nothing.
The following example demonstrates how to use the NextRecordset method to retrieve multiple Recordset objects.
The stored procedure syntax is as follows:
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 as follows:
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.
It's also possible to execute a command or a stored procedure that mixes row-returning and non-row-returning commands. In this case, SQL Server processes results in one of two modes, controlled by the SQL Server SET NOCOUNT Environment variable.
When SET NOCOUNT is on, calls to NextRecordset will skip over commands that did not return a result set (such as an insert, update, or set command). When SET NOCOUNT is off, NextRecordset will stop on each command, returning a result for those commands which have them, and returning the number of rows affected by each of the non-row-returning commands. See the SQL Server documentation for more information about SET NOCOUNT.
SQL Server provides a set of server cursor types for applications to use. By default, an ADO application does not use server cursors when using the ODBC Provider with SQL Server. The default cursor in such cases 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 you use server cursors. An active statement means that there are some pending results in the statement handle. If you are not using server cursors, 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:
The following example demonstrates how to open 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 so large that it cannot be retrieved in a single operation or all fit into memory. If the long data can all fit into memory, use the Value property of the Field object to retrieve all the data in one operation. If the long data is too large 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 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 following example demonstrates how to read and write data using the AppendChunk and GetChunk methods.
The destination table structure is as follows:
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 as follows:
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 Recordset objects 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 1,000 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.