Developing a VBScript Class for an Extremely Lightweight Recordset Alternative - Feeling Loopy?
(Page 4 of 5 )
Do you see a bottleneck in this, though? Think about the huge amount of looping that this technique will incur. Imagine a Recordset with five fields (first name, last name, age, height and weight, let’s say) and 50 rows. We want to generate a simple HTML table. We load it to our ArraySet, paint out the "<table>" tag to our liking, and then create our table rows along these familiar lines:
Do Until objArraySet.EOF
Response.Write "<tr>"
Response.Write " <td>" & objArraySet.fields("FirstName") & "</td>"
Response.Write " <td>" & objArraySet.fields("LastName") & "</td>"
Response.Write " <td>" & objArraySet.fields("Age") & "</td>"
Response.Write " <td>" & objArraySet.fields("Height") & "</td>"
Response.Write " <td>" & objArraySet.fields("Weight") & "</td>"
Response.Write "</tr>"
objArraySet.MoveNext
Loop
Each time we write out the FirstName field, the internal loop that translates "FirstName" to the ordinal 0 has to run once (finding a match on the first pass). Each time we write out the LastName field, it has to loop twice, and so on. To paint out one row, the loop runs a total of 15 times (1 = 2 + 3 + 4 + 5). For 50 rows, that’s 750 times through the loop. Couldn’t I somehow loop it once for each field called and remember, for instance, that "FirstName" = 0 and "LastName" = 1? Then with each subsequent call for "Weight" I could have my code retrieve the stored value of 5 and carry on without more looping.
For the longest time, I couldn’t come up a suitable method. I considered building a hashing algorithm for the purpose, but with collision-handling routines and all, it would probably out-mass the rest of the ArraySet's code! And the good old Dictionary object was still sitting there, smiling at me, with that come-hither look it its binary eyes. I was about to give in when…
It occurred to me that VBScript’s rarely used, rarely mentioned Execute and Eval statements might save the day.
The Execute statement allows us to create an arbitrary string that looks like a valid VBScript command, and then force the VBScript engine to run it like a valid command.
Using Execute, we can run this command…
Execute "strName = ""Tom Kelleher"""
…and thereafter issue this command in the clear:
Response.Write strName
The output is "Tom Kelleher". The value of strName isn't set overtly, it's set by virtue of the Execute command, but it's set just the same and can be used anywhere else in your code. This means we can create ad hoc variables as we need them, and set the values we want associated with them as we go.
Let's roll back to our .Load method above. The code we used to create our field-name array was this:
Dim i, arTmpFields()
ReDim arTmpFields(intFieldCount)
For i = 0 to intFieldCount-1
arTmpFields(i) = rs.fields(i).Name
Next
arFieldNames = arTmpFields
Instead, let's use Execute and see what we can do. Let's create a set of variables using the field name as part of the variable's name. We'll tag on the prefix "fld_" for good measure, too.
Dim i
For i = 0 to intFieldCount-1
Execute "fld_" & rs.fields(i).Name & " = " & i
Next
So for our SQL query of "SELECT FirstName, LastName, Age, Height, Weight FROM MyTable" we are creating these variables and assigning them these values:
fld_FirstName = 0
fld_LastName = 1
fld_Age = 2
fld_Height = 3
fld_Weight = 4
These variables can then be used elsewhere in our code! Rather than looping again and again, we just have need to determine whether we have a variable by that name. Let's replace that looping code in our .Fields method. The code we used before to determine if intSelectedField was a field name was like this…
Dim i
intOrdinal = -1
intSelectedField = Trim(intSelectedField & "") 'Turns Nulls to ""
For i = 0 To intFieldCount-1
If LCase(intSelectedField) = LCase( arFieldNames(i) ) Then
intOrdinal = i
Exit For
End If
Next
Let's just take intSelectedField and see if we have a variable by that name! We do this using the Eval command. Like the Execute command, Eval takes a string and treats it like raw VBScript. But rather than executing it, it (can you guess?) evaluates it. Where Execute "xyz = 123" would set the variable xyz to the value 123, Eval "xyz = 123" would return True or False, depending whether xyz already equals 123. The useful part of this for our purposes is that when handed a simple string that might be the name of a variable, Eval returns the value of that variable if it exists, and Empty if it doesn't. So…
Dim strTest
strTest = "fld_" & intSelectedField
If Len( Eval( strTest ) ) = 0 Then
'No such variable exists, so this is not a valid field name
Err.Raise vbObjectError + 99999, "C_ArraySet:Fields", "No field with the
name '" & intSelectedField & "' exists."
Exit Function
Else
'Such a variable does exist, so we use Eval to tease out its value
intOrdinal = Eval( strTest )
End If
Much cleaner, requires no looping, and uses a two peculiar VBScript commands that your peers might not know about. Pretty cool.
Let the fun begin
With our three key elements in place – the data storage, the internal cursor, and the data-field addressing mechanism – we have achieved most of what we set out to do. We have an extremely lightweight VBScript class that provides the most-used functionality of a read-only ADO Recordset. We can code with it using essentially the same object syntax, so porting old code over to it is easy. And because we rely on ADO Recordsets only to deliver the data, not manage its lifecycle, we can Nothing-ize the Recordset immediately and release that bulky object from memory.
But what more can we do? Quite a bit.
My beloved .RecordCount property
Let's get this one out of the way, right away. It's so simple, so obvious. I'll even forego the blank line white space and rejoice in its splendid brevity:
Public Property Get RecordCount()
RecordCount = intArrayUBound + 1
End Property
Delightful. No matter what ADO Recordset I use to create it, I always have my .RecordCount.
Persisting and DePersisting
If you use a particular Recordset in your ASP application over and over, you've fought off the impulse to persist it to the Session or Application object. (Or you've fallen to temptation and suffered the slings and arrows of outrageous threading issues.) ADO Recordsets persisted to as Session or Application variables can bog down IIS magnificently, because they aren't built for it. In my own work there are dropdown lists I need to build over and over, tables of client data to present, lists of user-parameters to build, and so on…and I have to repeatedly pull this same data with a new ADO Recordset every time. I'd much prefer to pull it once, persist it, and then reuse it as needed.
With our ArraySet object, we can, without threading penalties.
The contents of an ArraySet is held in two variant variables – one is the data array, and the other the field-name array. Unlike ADO Recordsets, which cannot be safely saved at the Session or Application level, these can. We are immediately free to implement our own persisting/depersisting mechanism, and make any ArraySet available to any user of our website. This saves us from needing to perform frequent identical trips to the database, creating identical ADO Recordsets, further reducing the load on the system.
To be 100% clear: the data can be saved at the Session or Application level, not the instance of the C_ArraySet class itself . I have not experimented, but would assume the same single-threading issues arise for VBScript classes as for most other classes – and I don't recommend it. But variants containing arrays can be saved this way, and those fully describe any given ArraySet anyway. Save those, and you've achieved the end.
(And for those of you cranking up your flywheels to rant about the evils of Session variables…I know, I know. Please see the section towards the end called "Anticipatory Objections.")
I knew I'd want to persist an arbitrary number of ArraySets, depending on circumstance. Therefore my persistence mechanism needed to let me assign each a unique name. I would assign this name when persisting, and use it to select one when "reconstituting" it later on, by reloading the persisted data, and finally to depersist it as well. And I knew I'd want the choice of whether to persist the ArraySet to the Session or Application level. So my goal was to write code along these lines:
‘Instantiate my ArraySet object
Dim objArraySet
Set objArraySet = New C_ArraySet
‘Create my ADO Recordset
Dim conn, rs, SQL
SQL = "SELECT * FROM MyTable"
Set conn = Get_Connection() 'Use your own favourite
'connection-making routine here
Set rs = conn.Execute(SQL)
objArraySet.LoadData rs
Set rs = Nothing
conn.close
Set conn = Nothing
' Persist the data, in this case to the Session object, with
' the unique identifier of "MyTable_Full"
Call objArraySet.Persist("SESSION", " MyTable_Full")
Set objArraySet = Nothing
In the above example, I wanted both the internal arrays saved (somehow) to the Session object, tagged somehow with the identifier "AllMyTable", so that on another page at another time I could reload the persisted data into a new ArraySet with code like so…
‘Instantiate my ArraySet object
Dim objArraySet
Set objArraySet = New C_ArraySet
Call objArraySet.LoadPersisted("SESSION", "AllMyTable")
Do While Not objArraySet.EOF
' …perform some task on each record
objArraySet.MoveNext
Next
The solution I wrote for persisting the data follows here:
Public Sub Persist(ByVal strMethod, ByVal strUniqueID)
'To persist these two arrays, we will create
'another array, with one-dimension and two elements,
'and load our two existing arrays into it. Then
'we will save this new array as needed.
Dim ar(1), vArray
ar(0) = arFieldNames
ar(1) = arDataset
vArray = ar
'Persist vArray as a Session or Application variable,
'creating a key for it with the structure:
'
' C_ArraySet:[unique-id]
Select Case strMethod
Case "SESSION"
Session("C_ArraySet:" & strUniqueID) = vArray
Case "APPLICATION"
Application("C_ArraySet:" & strUniqueID) = vArray
Case Else
Err.Raise vbObjectError, "C_ArraySet:Persist", "You must specify either
'Session' or 'Application' as the method."
End Select
End Sub
The approach above is to create a new one-dimensional array with a UBound of 1 (i.e., two elements), and load the arFieldNames variable to one, and arDataset to the other. Then, depending on whether the calling code requests persistence to the Session or Application, a Select Case statement completes the job., inventing a key name for it based on the strUniqueID parameter. The Session variable in my sample code earlier would therefore create this Session variable: Session("C_ArraySet:MyTable_Full"). (I chose to include the string C_ArraySet into the key name, to minimize the chance of overwriting some other Session or Application variable in the system.)
To reconstitute persisted ArraySets, we would use the .LoadPersisted method – which uses the same two parameters, as shown below:
Public Sub LoadPersisted(ByVal strMethod, ByVal strUniqueID)
'A new method (incomplete) to load persisted values
'from file or Session variable to
strMethod = Trim( UCase(strMethod) )
Dim ar
Select Case strMethod
Case "SESSION"
ar = Session("C_ArraySet:" & strUniqueID)
Case "APPLICATION"
' pull data from Application variable
ar = Application("C_ArraySet:" & strUniqueID)
Case Else
Err.Raise vbObjectError + 99999, "C_ArraySet:Persist", "You must specify
either 'Session' or 'Application' as the method."
End Select
If Not IsArray(ar) Then
Err.Raise vbObjectError + 99999, "C_ArraySet:Persist", "No persisted
ArraySet found with this ID: " & strUniqueID
Exit Sub
End If
'At this point, assume we have successfully acquired a persisted
'ArraySet variable. Prepare the other key internal variables.
arFieldNames = ar(0)
arDataset = ar(1)
bBOF = False
bEOF = False
intCursorLocation = 0
intFieldCount = UBound(arFieldNames)
intArrayUBound = UBound( arDataset, 2 )
bRsLoaded = True
bReadyForUse = True
End Sub
When using .LoadPersisted, we simply backtrack the logic of our .Persist method. We indicate the method (Session or Application) we used to persist it, provide the unique ID we assigned it, and let it try to load that variable. If none such is found, it raises an error. If it is found, it breaks it into it's two components (field-names, and dataset), prepares the usual internal variables, and it's ready for use.
To depersist an ArraySet, we merely do the following:
Public Sub DePersist(ByVal strMethod, ByVal strUniqueID)
'Destroys the persisted file or variable.
strMethod = Trim( UCase(strMethod) )
Select Case strMethod
Case "SESSION"
Session("C_ArraySet:" & strUniqueID) = Empty
Case "APPLICATION"
Application("C_ArraySet:" & strUniqueID) = Empty
Case Else
Err.Raise vbObjectError + 99999, "C_ArraySet:DePersist", "You must specify
either 'Session' or 'Application' as the method."
End Select
End Sub
This again takes the method and ID used to persist the data, and assigns the value of Empty to the appropriate variable. We don't need to check for the existence of it in this case; the Session and Application objects are very forgiving of assigning Empty to a non-existent variable. Neither causes an error.
By the way, you may have noticed that the .LoadPersisted method catches requests for non-existent Session or Application variables. Knowing it's possible that a user might call for a non-existent variable almost begs for the creation of an .IsPersisted(strMethod, strUniqueID) method. It would simply check to see if any such variable exists by that name. If so, it returns True, of not it returns False, as shown below.
Public Function IsPersisted(ByVal strMethod, ByVal strUniqueID)
'Determine if such data was persisted
strMethod = Trim( UCase(strMethod) )
Select Case strMethod
Case "SESSION"
IsPersisted = Not IsEmpty( Session("C_ArraySet:" & strUniqueID) )
Case "APPLICATION"
IsPersisted = Not IsEmpty( Application("C_ArraySet:" & strUniqueID) )
Case Else
Err.Raise vbObjectError + 99999, "C_ArraySet:IsPersisted ", "You must
specify either 'Session' or 'Application' as the method."
End Select
End Function
Roll-your-own methods
At this point, my ArraySet object provides me with just about every function I need to get by. However, there are times when the venerable old ADO Recordset's properties (or lack of them) are just plain annoying.
.EvenRow
How many times do you paint out HTML tables with alternating colored rows? To do this, do you have to Dim a new variable just to keep track of whether you're on an "even" row or an "odd" row? Something like this, perhaps:
Dim i
Do While Not rs.EOF
i = i + 1
If i Mod 2 Then
' …make this table row blue
Else
' …make this table row white
End If
rs.MoveNext
Loop
Because our internal cursor is so accommodating, we can write a property to simply ask it "Are you on an even-numbered row or an odd-numbered row?" Then we could rewrite the code above like so:
Do While Not objArraySet.EOF
If objArraySet.EvenRow Then
' …make this table row blue
Else
' …make this table row white
End If
objArraySet.MoveNext
Loop
The internals of the .EvenRow property are thoroughly mundane. We determine whether the value of intCursorLocation is even or odd with the Mod statement, and return the Boolean:
Public Function EvenRow()
EvenRow = IIF(intCursorLocation Mod 2, False, True)
End Function
.IsEmpty
Another thing that aggravates me about the ADO Recordset is the awkward and non-intuitive need to test for two properties – .BOF and .EOF – to determine whether a Recordset has no records. Why? Wouldn't a .IsEmpty property be more to the point?
Public Property Get IsEmpty()
IsEmpty = (bBOF And bEOF)
End Property
.FieldExists
How about the ability to test whether a Recordset includes a particular field?
Public Function FieldExists(ByVal FieldName)
Dim strTest, intOrdinal
strTest = "fld_" & FieldName
If Len( Eval( strTest ) ) = 0 Then
FieldExists = False
Else
FieldExists = True
End If
End Function
It becomes fun, actually, thinking of the ways that the ADO Recordset falls short, and building in the pieces we need.
Next: Concluding Thoughts >>
More ASP Articles
More By Wrox Team
