Exploration of which request headers you need, should and cannot set when using MsXml2.XmlHttp, Microsoft.XmlHttp and MsXml2.ServerXmlHttp

System Requirements:

  • Windows NT 4.0 Server SP4+
  • Windows Server 2000
  • Windows Server 2003
  • Windows Server 2008, R2
  • Windows Server 2012, R2
  • Windows 2000 Professional
  • Windows XP
  • Windows Vista
  • Windows 7
  • Windows 8, 8.1
  • IIS 4.0, 5.0, 5.1, 6.0, 7.0, 7.5, 8.0
  • ASP 3.0 (Classic)
  • VBScript

The Problem:

I was browsing around the web earlier looking for inspiration on making PowerShell send asynchronous XmlHttp requests when I came across a block of VBScript examples. In them, I observed that the coder was religiously attempting to set the Content-Length request header, something that when using MsXml I don’t usually do – it’s a different story with PHP.

Equally, I observed that the programmer was using Len() to define the length of the content body for text, but ASP usually operates using an internal UTF-16 encoding to represent strings, thus does it follow that Len() will give the correct value?

So I thought that I would explore the issue to see for my own amusement, what was actually going on.

More Info

Let us take a standard code block to use for our tests and use the services of the good people at httpbin.org for a public message echo system.

Option Explicit
Dim x
Dim strRequest : strRequest = "a=1&b=2&c=3" ' <- 11 charactersset x = CreateObject("MsXml2.XmlHttp")
call x.open("POST", "http://httpbin.org/post", false)
call x.setRequestHeader("Content-Length", 11)
call x.setRequestHeader("Accept-Language", "EN-GB")
call x.setRequestHeader("Connection", "keep-alive")x.send(strRequest)

The above gives us a boiler plate for what we believe the correct header should looklike written in VBScript. When executed, HttpBin responds with:

{
"args": {},
"data": "a=1&b=2&c=3",
"files": {},
"form": {},
"headers": {
"Accept": "*/*",
"Accept-Encoding": "gzip, deflate",
"Accept-Language": "EN-GB",
"Cache-Control": "no-cache",
"Connection": "close",
"Content-Length": "11",
"Host": "httpbin.org",
"User-Agent": "<omitted>",
"X-Request-Id": "<omitted>"
},
"json": null,
"origin": "<ommitted>",
"url": "http://httpbin.org/post"
}

HttpBin provides us with an echo of the header and WireShark provides us with a wire capture of the transport packet being sent between the XmlHttpResponse provider and the HttpBin server. With these two tools we can analyse the header states.

Content-Length

If you take out the line call x.setRequestHeader(“Content-Length”, 11) and do not specify a content-length, both HttpBin and Wireshark’s trace of the connection report Content-Length: 11\r\n, which is correct. So logically, this has been inserted by the XmlHttp parser during the request to .send().

So what happens if we attempt to override it. If we inject call x.setRequestHeader(“Content-Length”, 15), which is 4 characters too long, again both WireShark and HttpBin report

Content-Length: 11\r\n

The same occurs if we under-report the content length, i.e. 10 characters. Therefore it is safe to conclude that MsXml2.XmlHttp’s send() operator handles this for you and any processing of the Len() of the message body before sending is just wasting CPU time, memory and lines of code as it is ultimately an action that will be duplicated.

To further test the theory to see if it is required in down-level versions of MsXml, I repeated the experiment using HttpBin and Wireshark with the following COM objects:

Microsoft.XmlHttp
MsXml2.XmlHttp            <-which is logically v3.0
MsXml2.XmlHttp.3.0
MsXml2.XmlHttp.6.0
MsXml2.ServerXmlHttp      <-which is logically v3.0
MsXml2.ServerXmlHttp.3.0
MsXml2.ServerXmlHttp.6.0

None of them could be coerced to include an incorrectly sized Content-Length and all of them appended the correct 11 character content length on their own when no attempt was made to manually provide it.

We can therefore categorically conclude that with MSXML 3.0 and higher, you do not need to waste time calculating the content length.

A an aside, when dealing with character data, the use of Len() appears to be correct for UTF-8 encoding. LenB() should be used when passing in binary data i.e. ADODB.Stream data, FileSystemObject data etc.

Content-Type

If you do not manually specify a Content-Type header, all MSXML providers appear to transmit POST encoded data as

Content-Type: text/xml; charset="utf-8"\r\n

As we were sending POST data (i.e. a HTML form), this is incorrect as the content-type should be “application/x-www-form-urlencoded; Charset=UTF-8”. Thus is is appropriate and necessary to include the Content-Type header manually as shown below.

call x.setRequestHeader("Content-Type", "application/x-www-form-urlencoded; Charset=UTF-8")

Accept-Language

Accept language was decidedly different depending on which provider you are using. You can safely override Accept-language by manually specifying it using setRequestHeader(“Accept-Language”, “EN-GB”).

Microsoft.XmlHttp         <- NOT SET
MsXml2.XmlHttp.3.0        <- NOT SET
MsXml2.XmlHttp.6.0        <- Derived it from OS i.e. Accept-Language: en-gb\r\n
MsXml2.ServerXmlHttp.3.0  <- Derived it from OS i.e. Accept-Language: en-gb\r\n
MsXml2.ServerXmlHttp.6.0  <- Derived it from OS i.e. Accept-Language: en-gb\r\n

Thus, if you are using the legacy Microsoft.XmlHttp or the client MsXml2.XmlHttp.3.0, it is necessary to manually enter the Accept-Language if of course it is needed.

Connection

All examples set the connection value to Connection: Keep-Alive\r\n when communicating with the HTTP/1.1 protocol. If you need to force the remote server to close the TCP connection immediately after the response has been received, you must manually set Connection: Close\r\n using setRequestHeader(“Connection”, “Close”).

Cache-Control

Cache-control is only set by the client providers and can be added manually to the server providers.

Microsoft.XmlHttp         <- Cache-Control: no-cache\r\n
MsXml2.XmlHttp.3.0        <- Cache-Control: no-cache\r\n
MsXml2.XmlHttp.6.0        <- Cache-Control: no-cache\r\n
MsXml2.ServerXmlHttp.3.0  <- NOT SET
MsXml2.ServerXmlHttp.6.0  <- NOT SET

Accept-Encoding

Accept-encoding is only set by the client providers and can be added manually to the server providers.

Microsoft.XmlHttp         <- Accept-Encoding: gzip, deflate\r\n
MsXml2.XmlHttp.3.0        <- Accept-Encoding: gzip, deflate\r\n
MsXml2.XmlHttp.6.0        <- Accept-Encoding: gzip, deflate\r\n
MsXml2.ServerXmlHttp.3.0  <- NOT SET
MsXml2.ServerXmlHttp.6.0  <- NOT SET

Accept

Accept is set to Accept: */*\r\n by all providers and can be overridden if required.

0x80090020 when attempting to load a .PFX Private Key Certificate into a CAPICOM_MEMORY_STORE using Store.Load() or Certificate.Load() using CAPICOM 2.1.0.2

System Requirements:

  • Windows Server 2008, R2
  • Windows Vista
  • Windows 7
  • Windows 8, 8.1
  • 7.0, 7.5, 8.0
  • ASP 3.0 (Classic)
  • CAPICOM 2.1.0.2

The Problem:

Ah encryption, that most noble of things. One thing that is sure to drive every developer close to the brink on the odd occasion. The one time where clear, concise API documentation should be considered mandatory – and the one place where good API documentation it seems is an obligation itself not to provide. Be it Microsoft, Java, BouncyCastle, PHP it would seem they are all blighted with the same issue.

Attempting to use legacy API on an unsupported platform should seem like an exercise in masochism, however, you know how much I like to avoid using .net whenever I can.

If you attempt to do this

Dim cert
set cert = Server.CreateObject("CAPICOM.Certificate")
call cert.load("c:\myPrivateKey.pfx", "test", CAPICOM_KEY_STORAGE_EXPORTABLE)

or this

const CAPICOM_MEMORY_STORE = 0
const CAPICOM_LOCAL_MACHINE_STORE = 1
const CAPICOM_STORE_OPEN_READ_WRITE = 1
const CAPICOM_KEY_STORAGE_EXPORTABLE = 1Dim store
set store = Server.CreateObject("CAPICOM.Store")
call store.Open(CAPICOM_MEMORY_STORE, "MemoryStore1", CAPICOM_STORE_OPEN_READ_WRITE)
call store.load("c:\myPrivateKey.pfx", "test", CAPICOM_KEY_STORAGE_EXPORTABLE)

you will get back

error '80090020'
/file.asp, line ###

If you send in a .cer file instead of a .pfx, it works without error but doesn’t allow you to access the Private Key.

More Info

Taking the two code samples in order

Dim cert
set cert = Server.CreateObject("CAPICOM.Certificate")
call cert.load("c:\myPrivateKey.pfx", "test", CAPICOM_KEY_STORAGE_EXPORTABLE)

Should you be getting a 0x80070056 error, your password is wrong. If the file doesn’t have a password, only send parameter 1 (which is about to cause you a problem). To resolve the 0x80090020 error while using a CAPICOM_MEMORY_STORE, you need to stop CAPICOM from attempting to insert the certificate as a resource for a user. If the IIS worker process that you are using doesn’t connect to a user account and has no permissions, the default parameter CAPICOM_CURRENT_USER_KEY or 0 will throw 0x80090020.

To change the scope, ensure that you use the fourth parameter and set the value to CAPICOM_LOCAL_MACHINE_KEY.

const CAPICOM_CURRENT_USER_KEY = 0
const CAPICOM_LOCAL_MACHINE_KEY = 1Dim cert
set cert = Server.CreateObject("CAPICOM.Certificate")
call cert.load("c:\myPrivateKey.pfx", "test", CAPICOM_KEY_STORAGE_EXPORTABLE, CAPICOM_LOCAL_MACHINE_KEY)

To resolve the second issue, modify the original code to make use of the now fixed certificate.load() call and import it vie the long route.

const CAPICOM_MEMORY_STORE = 0
const CAPICOM_LOCAL_MACHINE_STORE = 1
const CAPICOM_STORE_OPEN_READ_WRITE = 1
const CAPICOM_KEY_STORAGE_EXPORTABLE = 1Dim cert
Dim store
set store = Server.CreateObject("CAPICOM.Store")
call store.Open(CAPICOM_MEMORY_STORE, "MemoryStore1", CAPICOM_STORE_OPEN_READ_WRITE)set cert = Server.CreateObject("CAPICOM.Certificate")
call cert.load("c:\myPrivateKey.pfx", "test", CAPICOM_KEY_STORAGE_EXPORTABLE, CAPICOM_LOCAL_MACHINE_KEY)

call store.add(cert)

If you receive 0x80070005, you are either getting an Access Denied error to the MEMORY_STORE or you are attempting to import a certificate into the instantiated store which already exists. Similarly, if you receive 0x80070056, your password is wrong.

Memory Leak in SvcHost.exe on Microsoft.XmlHttp (IXMLHTTPRequest) .Send() when called from CScript.exe or WScript.exe

System Requirements:

  • Windows Server 2008 R2

The Problem:

Svchost.exe, that black box amongst many other black boxes. If you ever happened to be in the business of watching what your scripts are getting up to on a Sunday morning and you are using Microsoft.XmlHttp, then you might be in for a surprise.

Every 2 hours a batch process on a group of servers fires off a script that in turn iteratively runs a second VBS script some 200-300 times. The script, calls a Web Service and performs a push/pull of instructions. Within a few days of the patch Tuesday reboot, you start noticing that memory use is going up, and up, and up.

You’ve done all of your deallocations, right? “set xmlHttp = nothing”? Yep, but despite that, memory use continues to grow. The culprit, svchost.exe. It grows until it’s into the page file and then grows a little bit more. Every run of the script puts between 4 and 100KB onto the memory footprint. At the end of the month, the servers are groaning because of memory starvation and your SAN array’s are not happy because of all of the paging.

True story.

More Info

I have been able to reproduce this on 3 separate and wholly independent Server 2008 systems (read different clients, enterprise/retail licensing, server hardware and install images) as well as on related servers (read from the same image on same or similar hardware). I have attempted to reproduce it on Windows Server 2012 R2 and I was not successful. Server 2012 R2 does not appear to be impacted by the issue. Running the iterator loop below for 10 minutes yields no increase in the memory use curve on the operating system, just a constant cycle of assign, release, assign, release that you would expect to see.

After a lot of diagnostics and a lot of me initially assuming that the problem was the web service (many, many wasted hours… although I did find a few bugs in the service code itself…) I managed to narrow it down to Microsoft.XmlHttp. More specifically, it’s in the way that CScript or WScript interfaces with Microsoft.XmlHttp at initialisation.

As you probably know, svchost itself is just a service wrapper. Inspection of the wrapper reveals a number of services running inside the wrapper. In this case the specific services are:

  • COM+ Event System
  • Windows Font Cache Service
  • Network List Service
  • Network Store Interface Service
  • Secure Socket Tunneling Protocol Service
  • WinHTTP Web Proxy Auto-Discovery

There are two things here that could be interesting, COM+ Event System and WinHTTP Web Proxy. Microsoft.XMLHTTP itself relies upon the WinHTTP stack for operation, but we are also using a COM interface to call it from VBScript.

While we cannot shutdown the COM+ Event Service and expect the operating system to survive for long, we can the WinHTTP Web Proxy Auto-Discovery Service. Did it release the memory consumed in the leak? No. So in the balance of probabilities, it’s coming from COM+.

The problem with that is in the need to reboot the server to safely clear the memory leak, hence why Patch Tuesday has been the true savior in keeping a gradual performance bottle neck from becoming a full scale meltdown. So what is going on?

I stripped off all of the web service and customisation parts and went back to vanilla Microsoft implementation examples. We cannot get much simpler than this.

Option Explicit
Dim xmlset xml = CreateObject("Microsoft.XmlHttp")
xml.open "POST", "http://127.0.0.1", false
xml.send "he=llo"
set xml = nothing

Save it to a VBS and run it via CScript, run it a lot. Run it in a BAT file loop

:start
ccscript.exe testfile.vbs
goto start

Watch the svchost.exe processes until you spot the instance with the rising service working set (or private set). Now you know which one to focus on.

It’s memory leaked. Hold on, we’ve created the instance of Microsoft.XmlHttp (which is actually an instance of IXMLHTTPRequest), done something and told CScript to deallocate it (set xml = nothing). Why is it leaking memory?

The third parameter on .Open() is bAsync – is it an asynchronous request? It’s false above, meaning that the request is synchronous. It continues to leak. It would be more likely to leak asynchronously than synchronously, however changing that to true makes no difference.

So where is the leak being triggered? By process of line elimination we can reveal that the memory is committed into the svchost wrapper during xml.send(). Run it without .Send() as below and there is no growth in the scvhost process memory footprint no matter how many times you run it..

Option Explicit
Dim xmlset xml = CreateObject("Microsoft.XmlHttp")
xml.open "POST", "http://127.0.0.1", false
' COMMENTED OUT      xml.send "he=llo"
set xml = nothing

In the MSDN documentation for the .Send() method, it states

“If the input type is a BSTR, the response is always encoded as UTF-8. The caller must set a Content-Type header with the appropriate content type and include a charset parameter.”

So far we haven’t done that and we are sending a VBString – which is ultimately a BSTR in C++, so add in the necessary setRequestHeader beneath the .Open() method call in case it is a case of not following the documentation:

Option Explicit
Dim xmlset xml = CreateObject("Microsoft.XmlHttp")
xml.open "POST", "http://127.0.0.1", false
xml.setRequestHeader "Content-Type", "application/x-www-form-urlencoded; Charset=UTF-8"
xml.send "he=llo"
set xml = nothing

It isn’t. There is no change, it still results in an increase in process memory after cscript.exe has shutdown.

We have confirmed that there is a memory leak, where it is and what is triggering it. We can also be confident that given the extremely simple nature of the sample code printed above – and its match to the samples documentation – that it is being implemented correctly.

So the next step is to try and prove that there is an issue in the COM implementation between CreateObject and set nothing. This is achieved by running the allocate/deallocate (set/set nothing) in a loop as shown below

Option Explicit
Dim i
Dim xmlwscript.echo TypeName(xml)              ' This returns "empty" on this testfor i = 0 to 999

set xml = CreateObject("Microsoft.XmlHttp")
xml.open "POST", "http://127.0.0.1", false
xml.setRequestHeader "Content-Type", "application/x-www-form-urlencoded; Charset=UTF-8"
xml.send "he=llo"
wscript.echo xml.responsexml.xml    ' This returns nothing on this test
wscript.echo xml.statusText         ' This returns "OK" on this test
set xml = nothing

next

wscript.echo TypeName(xml)              ' This returns "nothing" on this test

At this point you would expect to see a large increase in the svchost.exe memory footprint.

It does not happen.

1000 iterations and instantiation of the IXMLHTTPRequest later and there is no obvious exponential increase in the memory footprint of svchost.exe. It simply increments once i.e. the additional memory consumption is no worse than running the script with only 1 call to CreateObject/set nothing despite the fact that .send() has been called 1000 times.

What does that mean? Well, it would seem to suggest that the fault isn’t actually in IXMLHTTPRequest (Microsoft.XMLHTTP), but actually in VBScript itself. As a speculative suggestion, I would suggest that VBScript is registering event callbacks with COM+’s Event Management System on the first call to .Send() which are not being cleaned up by the garbage collector when “set nothing” is called in the code. So either there is a bug in VBScript or there is a bug in the event handling interface for COM+ event registration through which IXMLHTTPRequest is registering its own actions.

Most people aren’t going to notice this problem, they are morelikely to iterate instance of Microsoft.XmlHttp inside VBScript than they are to repeatedly externally iterate accross it. It just so happens that I need to fire it externally to the script processor via the command shell. The chances are that if you are reading this, so do you.

The Fix

As of writing, I have not found a direct way to force VBScript to release the memory from scvhost, short of rebooting (or migrating to Windows Server 2012). Calling Microsoft.XmlHttp from WScript or CScript seems to be the problem and the fact that the web service scripts are using an external iterator to repeatedly call n new instances of CScript are exacerbating the situation. Simply put, the transaction load is the catalyst for spotting the leak. In most cases growth would be very subtle as would growth were the iteration internal to the CScript.exe script instance.

While not necessarily ideal, if you are in the position of being able to change provider, you can substitute Microsoft.XMLHTTP for MSXML2.ServerXmlHttp, which provides most of the functionality without making use if WinHTTP. This provider does not exhibit the memory growth issue as in its client counterpart, however its use requires MSXML 3 or 6 and you lose some functionality.

The fact that I could not reproduce the issue under Windows Server 2012 R2 suggests that the culprit has been fixed – either intentionally or inadvertantly. By default, Microsoft.XMLHTTP is a COM Class ID reference to msxml3.dll. Under Windows Server 2008 R2 the file version is SP11 at 8.110.7601.18334, under 2012 R2 the file version is simply 8.110.9600.16483. Yet oddly, with all systems fully patched, vbscript.dll under Windows Server 2008 R2 is version 5.8.9600.17041 (KB2929437) while its counterpart under Server 2012 R2 is 5.8.9600.17031.

What I can tell you is that these systems have been running this recursion script every 2 hours since the beginning of 2012 and the issue has only been observed in more recent months, therefore I suspect that Microsoft have a regression bug on their hands. Until it is fixed however, I have a load of (thankfully firewalled, private network) web service that have a DOS vulnerability. So do you.

VBScript Multidimensional Arrays, limits, annoyances and ways around them

System Requirements:

  • Windows 95, 98, 98SE, Millennium
  • Windows NT 4.0, 2000, XP, 2003, Vista, 2008, 7, 8, 2012, 8.1, 10
  • VB 4, 5, 6, VBScript, ASP 3, ASP Classic

The Problem:

Microsoft implemented a proprietary method of handling multi-dimensional arrays in VBScript, one which doesn’t quite allow a full range of functionality that you would expect from creating an array primitive.

This article discusses a few of those issues and some solutions to the problems.

More Info

I like using arrays, I use them a lot, especially if I want to get off of a database server quickly and only need a unidirectional cursor – frankly you seldom need anything other than a forward only cursor if you’re creative enough.

What is a Multidimensional Array

The problem with that is that in order to return a database query you have to use multi-dimensional arrays. Not a big deal, a multi-dimensional array is nothing special. In pretty much any programming language a multi-dimensional array is nothing more than an array of arrays.

A Two Dimensional (2D) array is an array or more than one array.

array(array(), array())

A Three Dimensional (3D) array is an array or more than one array each themselves containing more than one array.

array(array(array(), array()), array(array(), array()))

Things work a little differently in the VBScript, VB 6 and ASP world. Not so much in the logic of how an array is structured as an array of arrays, but as how you an interface with that structure.

Dim array(1,4)

Would create a 2 column, 5 row 2D array

0,0 1,0
0,1 1,1
0,2 1,2
0,3 1,3
0,4 1,4

The Undimensioned Dynamic Array Problem

In VB you can also do something like this

Dim myArray()
‘ Things happen here

ReDim myArray(1,4)

This step would create an empty dynamic array [Dim myArray()] – a memory pointer to a storage location that the system expects will eventually become an array sequence. Later in the code execution the array is redimensioned to become a 2×5 2D array.

Imagine that the ReDim never happens

Dim i
Dim arrOut()
if (1 > 2) then      ‘ <———– 1 is never greater than 2, so it will never be ReDim’d
ReDim arrOut(2,10)
arrOut(0,0) = 1
end ifwscript.echo IsArray(arrOut)

IsArray(arrOut) will return True (-1), it is a VB type of array after all. In our multidimensional structure this is asking whether the wrapper array is an array, which it is. It just doesn’t have any contents yet. So what happens now if we do this:

if (IsArray(arrOut)) then
for i = 0 to UBound(arrOut,2)
wscript.echo arrOut(0,i)
next
end if

… or even you might assume this

if (IsArray(arrOut)) then
if (UBound(arrOut,2) > -1) then
for i = 0 to UBound(arrOut,2)
wscript.echo arrOut(0,i)
next
end if
end if

Funnily enough, with both examples you will receive:

array.vbs (11, 1) Microsoft VBScript runtime error: Subscript out of range.

The UBound function is a VB Native function that has two parameters. UBound(<array>,<dimension>).

We can issue both UBound(arrOut,1) AND UBound(arrOut,2) against the variable arrOut and both a scan for the size of the First Dimension and Second Dimension will result in a “Subscript out of range” error.

Now, despite the fact that arrOut is an array, it is just an empty memory pointer, there is no array structure behind it – think of it like the array being there but its value is null with 0 dimensions.

OK! We have an empty array. So lets confirm that!

if NOT (IsEmpty(arrOut)) then    ‘ <—– Our array has no dimensions and null contents
for i = 0 to UBound(arrOut,2)
wscript.echo arrOut(0,i)
next
end if

Yet the IsEmpty function will return FALSE: the array is NOT empty.

So, according to VB, we have an array that is an array but that has no dimensions and no contents but isn’t empty!

To clarify, with the above example the following conditions occur

IsEmpty(arrOut) = false
IsArray(arrOut) = true
UBound(arrOut,1) = <crash: subscript out of range>
UBound(arrOut,2) = <crash: subscript out of range>

This is just a quirk of the language, it’s the way it is. But how do you test your way out of this?

Dim iRows
On Error Resume Next
iRows = UBound(arrOut,2)if (err.number <> 9) then     ‘ <——– 9 is the return code for subscript out of range
for i = 0 to UBound(arrOut,2)
wscript.echo arrOut(0,i)
next
end if
On Error Goto 0

Yes, you can do that. But it is very expensive and hardly eloquent unless you put it into an equally expensive boolean procedure for “isDimensioned()”. So what else can be done?

The problem is due to the simplicity of the language and some unfortunate design decisions by Microsoft on what to cut from VB compared to VBScript – it is a lot like Java vs. JavaScript.

If you are using VB rather than VBScript/ASP, there is a language construct that you can use to escape from this problem without resorting to exception handling.

Dim arrOut()
if (NOT arrOut) then          ‘ <—– This checks to see if there is a pointer for the array
‘ Code if the array is not initialised is run here
end if

Again, it does not however work in VBScript/ASP and to my knowledge there is absolutely no way to get past it in a pure, one line language construct in VBScript. There is however a way to solve the problem. Be it one that uses more lines.

Never use the Dynamic Array Declaration

The issue that is causing you to experience the subscript out of range statement is not in fact the VB array, but the way you declare a Dynamic array.

Dim arrOut()

With this declaration, it will always throw an exception if you call UBound() on the array before it has been ReDim’d.

Instead, you should declare your arrays using the following

Dim arrOut : arrOut = Array()

So to summarise, against this syntax the following conditions will be present:

IsEmpty(arrOut) = true
IsArray(arrOut) = true
UBound(arrOut,1) = -1

That is actually want you want. What we are doing is declaring the arrOut. As this is VBScript, the variable will be created of type Variant. The variable is then converted into a new Variant Array with no size (-1), however this time the three logic tests that we are looking to see some common sense from actually tell the truth.

Dim arrOut
if (IsArray(arrOut)) then      ‘ <—– This obviously isn’t an array
‘ Code if the array is not initialised is run here
end if

Put another way. It works, but it is sloppy, the variable is never technically initialised as an array, you are actually just relying on Variant manipulation. It can be tidied up by using

Dim arrOut
if (IsArray(arrOut)) then      ‘ <—– This obviously isn’t an array
arrOut = Array()
‘ Code if the array is not initialised is run here
end if

Instead of using colon notation (“:”) you can be more clever and write it long hand. In the example below, arrOut is legitimately becomes a VB Variant Array (type 8192 + 12) at the line arrOut = Array(). This process is closer to how you would dimension an array in Java, however, unfortunately, there is a new problem. You cannot call ‘ReDim Preserve’ on arrOut otherwise you will receive the dreaded ‘Microsoft VBScript runtime error: Subscript out of range.’ e.g.

Dim arrOut
if (IsArray(arrOut)) then      ‘ <—– This obviously is NOT an array
arrOut = Array()            ‘ <—– It is an array now
ReDim Preserve arrOut(4,5)  ‘ <—– This will throw an exception
‘ Code if the array is not initialised is run here
end if

This is only going to be a problem if you are going to dynamically resize a multidimensional array and include the preserve key word. Oddly, everything is fine if you resize it to a single dimension array and include the preserve. The solution is to ensure that the first time that you ReDim the array, you are NOT calling the Preserve key word e.g.

Dim arrOut
if (IsArray(arrOut)) then      ‘ <—– This obviously isn’t an array
arrOut = Array()            ‘ <—– It is an array now
ReDim arrOut(4,5)           ‘ <—– This will NOT throw an exception
ReDim Preserve arrOut(4,10) ‘ <—– This will NOT throw an exception
‘ Code if the array is not initialised is run here
end if

You can then immediately and safely use the preserve key word… however by now you have already lost your data.

Again, this is another unfortunate quirk of the language.

Applying it to an Example using Manually Created Multi-dimensional Arrays

Logically take a One Dimensional (1D) array.

Dim myArray(4)

If you UBound(myArray,1) this, it returns 4. Effectively the logical difference is that you created the wrapper array, and into THAT put an array with 5 rows. Calling UBound correctly identifies that there is an array within the wrapper array and returns its size – 4 (5 rows).

Repeating the same process with a 2D array

Dim myArray(1,4)

If you now call UBound(myArray,2) it returns 4. Here we logically created the wrapper array, put two arrays into that, each with 5 rows. A scan of the second column dimension reveals 5 rows.

So take a practical example.

SELECT id, name, date FROM user;

Using ADO getRows return the results into an array.

If there are 5 users in the table, we get a 2D array that responds to UBound(myArray,1) [3 == id, name, date] and UBound(myArray,2) [5 users each with an id, name and date].

What if there are no users? We can’t UBound() the array or test it, so the program crashes unless we use On Error Resume Next.

There is a solution to this.

What VB’s built-in functions lack is the ability to test for the presence of a dimensioned inner array. Effectively what we want to do is:

IsArray(myArray(0))

If that looks familiar it is because it is how virtually every C-derived language tests for a multi-dimensional array, get the array and then test to see if there is a first element in that array which itself is an array. Logically, an initialised Column set with an uninitialised Row Set would look like

myArray(0) null
myArray(1) null

While initialised Column set with an initialised Row set would look like

myArray(0) Array()
myArray(1) Array()

We know that myArray – the wrapper array – is an array, but is its first element also an array? If the element doesn’t exist we want to see FALSE come back. Sadly the VB multi-dimensional array structure is such that it is not possible to test this dimension using the proprietary methods; however you can if you do it manually using nested arrays (primitive arrays).

Dim arrWrapper(1)           ‘ <——– This is the Column set, there are 2 initialised columns (always)
Dim arrCol1(4)              ‘ <——– This is the Row set for Column 1
Dim arrCol2(4)              ‘ <——– This is the Row set for Column 2arrWrapper(0) = arrCol1     ‘ Inset the Row sets
arrWrapper(1) = arrCol2

  • We now have a 2×5 multi-dimensional array
  • We have an Array Wrapper (arrWrapper)
  • It has a legal first dimension
  • We put an array into both of its rows, each with 5 rows

Logically, this looks like

myArray(0)
arrCol1(0) null
arrCol1(1) null
arrCol1(2) null
arrCol1(3) null
arrCol1(4) null
myArray(1)
arrCol2(0) null
arrCol2(1) null
arrCol2(2) null
arrCol2(3) null
arrCol2(4) null

Now if we test IsArray(arrWrapper) we get True just as before, the Column set has been initialised and is easy to test. We can also test IsArray(arrWrapper(0)) to see if there is any Row data available. In other words we can now categorically know that arrWrapper HAS dimensions and has been properly initialised.

The same also applies if we don’t initialise the inner arrays; we can now explicitly test whether the inner array is a dimensioned array:

Dim arrWrapper(1)
Dim arrCol1(4)
Dim arrCol2(4)if (1 > 2) then    ‘ <———– 1 is never greater than 2
arrWrapper(0) = arrCol1
arrWrapper(1) = arrCol2
end if

if (IsArray(arrWrapper(0)) then
‘ Happy days had by all as this will never execute as column(0)’s contents is NOT an array
end if

What is the functional difference between this and the VB Native method?

‘ To Call Column 1 row 3 in the VB Native Array:
response.write myArray(0,2)’ To Call Column 1 row 3 in the Primitives Array:
response.write myArray(0)(2)

If you are wondering why this looks spookily familiar, don’t fret. It’s exactly what you do in most other procedural C derived languages. e.g. Java

int[][] myArray = new int[1][4];

System.out.println myArray[0][2];

Now to preserve your sanity (and line count) you can achieve all of the above using an in-line statement and deal with the sizing of the array at a later time using the following

Dim arrTest : arrTest = Array(Array(), Array())

‘ Now to prove that it isn’t sized yet (it has zero rows)
response.write UBound(arrTest,1)    <—- This will return: 1 (i.e. 2 columns)
response.write UBound(arrTest,2)    <—- This will crash: <Subscript out of range>
response.write UBound(arrTest(0))   <—- This will return: -1 (unintialised)
response.write UBound(arrTest(1))   <—- This will return: -1 (unintialised)

Advantages of Traditional C-Like Arrays

So why else might you want to build your array structures manually rather than by using the native functions?

There is are two useful and occasionally very helpful reasons why managing your arrays in the traditional C-like sense can be helpful.

Number 1: Column extraction. If you need to extract an entire columns from an array and manipulate it separately without passing a large data set around, manually defined multi-dimensional arrays is how you do it

Dim arrWrapper(1)
Dim arrCol1(4)
Dim arrCol2(4)arrWrapper(0) = arrCol1
arrWrapper(1) = arrCol2

arrWrapper(0)(0) = 5
arrWrapper(0)(1) = 10
arrWrapper(0)(2) = 12
arrWrapper(0)(3) = 8
arrWrapper(0)(4) = 2

arrWrapper(1)(0) = “Hello”
arrWrapper(1)(1) = “World”
arrWrapper(1)(2) = “How”
arrWrapper(1)(3) = “Are”
arrWrapper(1)(4) = “You?”

Dim arrStrings : arrStrings = arrWrapper(1)

The arrStrings variable now contains the entire contents of column 1. no if’s not but’s. If you want to do that with a VB Native array you have to iterate the data out, which itself carries a performance penalty:

Dim i
Dim myArray(1,4)myArray(0,0) = 5
myArray(0,1) = 10
myArray(0,2) = 12
myArray(0,3) = 8
myArray(0,4) = 2

myArray(1,0) = “Hello”
myArray(1,1) = “World”
myArray(1,2) = “How”
myArray(1,3) = “Are”
myArray(1,4) = “You?”

Dim arrString()
ReDim arrString(UBound(myArray,2)) ‘ Hopefully this is actually dimensioned!
for i = 0 to UBound(myArray,2)     ‘ At this pint we have sized myArray 3 times, costly
arrString(i) = myArray(1,i)
next

 

Number 2: Jagged Arrays. you generally don’t use Jagged Arrays very often as programmers tend to use arrays as table sets, however when operating with large volume data sets if you use VB native arrays and don’t need to logically think of your data as being tabular, you can wind up wasting a large amount of memory on empty array slots e.g.

myArray(0)
arrCol1(0) 1
arrCol1(1) 2
arrCol1(2) 3
arrCol1(3) 4
arrCol1(4) 5
myArray(1)
arrCol2(0) 1
arrCol2(1) null
arrCol2(2) null
arrCol2(3) 6
arrCol2(4) null

In a Jagged Array you do not need to over bound the inner array at all, as long as the context of your program permits it. Visually this would look like the following (note the removal of the Null values from the second column’s Row set).

myArray(0)
arrCol1(0) 1
arrCol1(1) 2
arrCol1(2) 3
arrCol1(3) 4
arrCol1(4) 5
myArray(1)
arrCol2(0) 1
arrCol2(1) 6

If we simplify the maths and pretend that each unallocated array slot is using a 32-bit pointer + a 32-bit null, we just saved 24 bytes of memory from being consumed for absolutely no reason.

The advantage therefore is that you can only manipulate Jagged Arrays using manually created primitive VB Arrays and not through the use of native multi-dimensional arrays.

Disadvantages of Traditional C-like Arrays

The technique clearly has some uses. But is there any reason not to do it?

Yes, there is and it depends upon application and the size of the array.

The VB Native functions are such that they call much faster C++ code at a lower level, where as VB activities itself happen in a slower, less efficient language. This is reflected in the RAS/CAS seek times for the arrays.

A 43 column 9,000,001 row (9 million and 1) array was tested for read and write operations using the two scripts below:

Manual (Primitive):

Option Explicit

Dim i
Dim dblStart
Dim dblEnd

Dim bol
Dim str
Dim i1
Dim i2

Dim arrMain(3)
Dim arr1(9000000)
Dim arr2(9000000)
Dim arr3(9000000)
Dim arr4(9000000)

arrMain(0) = arr1
arrMain(1) = arr2
arrMain(2) = arr3
arrMain(3) = arr4

‘ WRITE

dblStart = Timer()

for i = 0 to UBound(arrMain(0),1)
arrMain(0)(i) = true
arrMain(1)(i) = 17
arrMain(2)(i) = “hello”
arrMain(3)(i) = (10 * i)
next

dblEnd = Timer()

wscript.echo (dblEnd – dblStart)

‘ READ

dblStart = Timer()

for i = 0 to UBound(arrMain(0),1)
bol = arrMain(0)(i)
i1 = arrMain(1)(i)
str = arrMain(2)(i)
i2 = arrMain(3)(i)
next

dblEnd = Timer()

wscript.echo (dblEnd – dblStart)

VB Native:

Option Explicit

Dim i
Dim dblStart
Dim dblEnd

Dim bol
Dim str
Dim i1
Dim i2

Dim arrMain(3,9000000)

‘ WRITE

dblStart = Timer()

for i = 0 to UBound(arrMain,2)
arrMain(0,i) = true
arrMain(1,i) = 17
arrMain(2,i) = “hello”
arrMain(3,i) = (10 * i)
next

dblEnd = Timer()

wscript.echo (dblEnd – dblStart)

‘ READ

dblStart = Timer()

for i = 0 to UBound(arrMain,2)
bol = arrMain(0,i)
i1 = arrMain(1,i)
str = arrMain(2,i)
i2 = arrMain(3,i)
next

dblEnd = Timer()

wscript.echo (dblEnd – dblStart)

the first thing that you will note is that in using Manually created multi-dimensional arrays it has been necessary to write more lines of code to do the same thing,. This itself may be a disadvantage for you!

If you are willing to proceduralise the process however, the line count can be reduced back down again by using the sample generator function provided at the end of this article.

Results of the experiment (Time shown in seconds):

VB Array Method Experiment Results

Seconds
Primitive Write 5.154063
Primitive Read 4.843750
Native Write 3.695313
Native Read 3.593750

As you can see, the write process was 28.3% faster and the read 25.8% faster for the native array structure.

There is a clear and very evident performance hindrance to this method, however keep in mind that this array structure contained over 9 million rows and 3 columns – 27,000,003 ‘intersections’ to be exact. If you are writing a program that requires an array of that size that will suffer from the performance drop that comes from this method, you may want to consider changing to a compiled language for what you are doing.

Summary

There are some limits to the native VB array structure system, and as this has demonstrated there are ways around that thanks to more traditional array structure paradigms. It cannot be denied that there is a slight performance hit for larger array, possibly to do with more of the work of managing the array being performed by VB itself rather than by underlying and cleaner C++.

The benefits of the alternative cannot be ignored however, particularly when array extraction and content presence validation are required without resorting to litter you code with On Error Resume Next statements.

If you got this far into the document, well done for reading through. A function can easily be created to automate the build process for multi-dimensional VB primitive arrays. For those interested I have included an example of one below.

‘ Create a Primitive in Primitive (nested) VB Array
‘   iCols = Number of Columns
‘   iRows = Number of Rows
‘ © C:Amie (http://www.c-amie.co.uk/)public function mdArray(ByVal iCols, ByVal iRows)
Dim arrOut
Dim arrTemp

ReDim arrOut(iCols)

for i = 0 to iCols
ReDim arrTemp(iRows)
arrOut(i) = arrTemp
next

mdArray = arrOut
end function

‘ Example of its use
Dim myArr
Dim i

myArr = mdArray(1,4) ‘ <—- Here we create the empty array

wscript.echo UBound(myArr)       ‘ <—- This returns the column count (1 i.e. 2 columns)
wscript.echo UBound(myArr(0))    ‘ <—- This returns the row count (4 i.e. 5 rows)
wscript.echo IsArray(myArr)      ‘ <—- This returns: true
wscript.echo IsArray(myArr(0))   ‘ <—- This returns: true
wscript.echo “======================”

myArr(0)(0) = 1
myArr(0)(1) = 2
myArr(0)(2) = 3
myArr(0)(3) = 4
myArr(0)(4) = 5

myArr(1)(0) = “a”
myArr(1)(1) = “b”
myArr(1)(2) = “c”
myArr(1)(3) = “d”
myArr(1)(4) = “e”

for i = 0 to UBound(myArr(0))
wscript.echo myArr(0)(i)
wscript.echo myArr(1)(i)
next

Happy coding.