Variant arrays have the following characteristics:
Variant arrays were one of the first widely used transports in distributed applications. Variant arrays use a Variant data type passed into a function by reference. Because the Variant was passed in by reference, it could be filled with data inside the function and then automatically returned to the calling client when the function completed.
The problem with Variant arrays is that they are the single worst choice for a transport. Passing Variants by reference is one of the most expansive operations you can perform because the Variant must be marshaled between two machines to make the initial function call and then marshaled again on the return.
Marshaling is a process performed by COM+ and the operating system that moves complex data types like variants or objects between machines and processes. Normally when an object or Variant is passed within the same process on the same machine, COM+ can simply pass a reference to the variablenot the entire variable. However, when you move an object or Variant between machines, the reference from one machine isn't valid on the other. Therefore, a copy of the data must be moved to the other machine's memory before it can be used. This is the expansive part of the process, which is doubled when the Variant or object is passed by reference.
Nonetheless, many applications have been built successfully with Variant arrays. When you use a Variant array, you will again use a firehose cursor because you want to transfer the data from the Recordset and into the array as quickly as possible. After you open the firehose cursor, the data can be transferred into the array through the GetRows method. The code in Listing 2 shows a function that returns a Variant array.
Listing 2: Returning a Variant Array
Public Function Lookup() As Variant Dim objRecordset As ADODB.Recordset Set objRecordset = New ADODB.Recordset 'Run Query objRecordset.ActiveConnection = _ "Provider=SQLOLEDB;Data Source=(local);Database=pubs;UID=sa;PWD=;" objRecordset.Source = "SELECT pub_name FROM Publishers" objRecordset.Open 'Pass array to client Lookup = objRecordset.GetRows End Function
When the client receives the Variant array, we have more trouble. The returned array is simply a container full of data with no information about the contents. It's not self-defined. Because the payload within the array has no schema, the payload definition must be kept somewhere else. The typical answer to this problem is to define a set of enumerations that tell the client what's in the array. This allows the client to use enumerations instead of numbers as the indexes for the array. If, for example, our Variant array contained information about authors in the pubs database, we might define the following enumeration:
Public Enum ArrayFieldsEnum SSN FirstName LastName Address City State Zip End Enum