Developing a VBScript Class for an Extremely Lightweight Recordset Alternative - How Much is That Recordset in the Window?
(Page 3 of 5 )
Consider how many properties even the simplest Recordset contains. It’s instructive to see what the darn thing has packed inside it by running this simple code:
Dim conn, rs, SQL
SQL = "SELECT * FROM MyTable"
Set conn = Get_Connection() 'Use your own favorite
'connection-making routine here
Set rs = conn.Execute(SQL)
Dim prop, i
Response.Write "Recordset properties<hr>"
Response.Write "<pre>"
For each prop in rs.Properties
i = i + 1
Response.Write i & ": " & vbTab & prop.name & vbcrlf
Next
Response.Write "</pre>"
rs.close
Set rs = Nothing
conn.close
Set conn = Nothing
On my ADO 2.6 machine, pulling data from an unremarkable table on a SQL Server 2000 database, I discover my humble Recordset has eighty-five properties. How many of these have any meaning to me? A scant few. Do I typically use the "Maximum BLOB Length" or "Rowset Fetch Position Change Notification" or "Re-entrant Events" properties? Not that I can tell, no. And running the same kind of For-Each routine on individual fields, I find another 16 properties attached to each – mostly useless to me.
In VB 6.0 perhaps I can find exciting uses for these – but in the variant-only, event-free world of classic ASP, I had to ask myself: "How much am I paying for all this extra stuff?" All I want to do (mostly) is run once through the Recordset and paint out my HTML – so what does this cost me, maintaining it’s hyper-propertied existence throughout its life cycle – in terms of system resources?
And could I do better?
My goals
What I wanted, then, was to build a container for my data that would have just the features I needed – all of them, not some pre-ordained mix-and-match subset – and none of the features I didn’t want. I wanted it to be as fast as the ADO Recordset at delivering up my data when I needed it. And for ease in porting old code (and to simplify training team-mates in its use), I wanted it to have as similar an object structure as the ADO Recordset, so the syntax of its usage would be familiar.
And because, by and large, my primary use for ADO Recordsets are to present HTML to the user, I'd expect to use this VBScript object only for "small" Recordsets. What does "small" mean, then? I rarely need to build a 1000-row table, or put 1000 items into a dropdown list…so I'll assume 1000 records as an upper limit of it's usefulness. (I won't design it to fail beyond 1000 records; I'll just know it's probably not winning me much benefit at that point.)
And as I started, I realized I could give it all the properties I wish every Recordset has but doesn’t. Like .RecordCount. What a great property! (Why do we have to buy the deluxe ADO Recordset just to get our hands on .RecordCount ?) In my perfect world, I’d always have a .RecordCount property…so I decided to build it in.
One annoyance I ran into right off: We really can’t avoid ADO Recordsets altogether. We need to get data out of the database. Clever as (I hope) I am, I can’t write a VBScript object that fires the low-level OLE-DB API calls to pull data without using a Recordset as the mailman. So let me be a little clearer on this point. Let's distinguish the two purposes that an ADO Recordset serves for us…
It’s the delivery system that brings data from the database to our code, and…
It contains and manages that data while our code steps through its contents…
…then we know we can’t replace purpose #1 above. If we want a chunk of data that we can mingle into our ASP code, that data must come wrapped in a Recordset. But purpose #2 above is up for grabs. A VBScript class can easily handle the lifecycle work – the delivering up of each field’s data, the stepping through records, the rendering of the precious .RecordCount value, and so on.
So our generalized plan becomes:
1. Create an ADO Recordset
2. Make it deliver up its data to our VBScript object
3. Close and Nothing-ize the Recordset as soon as possible
4. Carry on, using our VBScript object.
The Recordset will live the brief life of a skittish mayfly with a heart condition, leaving only its data for us to remember it by. I wanted, in essence, to do this:
‘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 favorite
'connection-making routine here
Set rs = conn.Execute(SQL)
objArraySet.LoadData rs
rs.Close
Set rs = Nothing
conn.close
Set conn = Nothing
Response.Write "<table>"
Response.Write "<tr>"
Response.Write "<td colspan=5>Records: " & objArraySet.RecordCount & "</td>"
Response.Write "<tr>"
Do While Not objArraySet.EOF ‘It has an .EOF property as well
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
Response.Write "</table>"
Set objArraySet = Nothing
Elements of an "ArraySet"
To meet my goals, my ArraySet object would need three key elements:
A data storage mechanism
A row-traversal mechanism (the familiar .MovePrevious, .MoveNext, etc. methods)
A data-field addressing mechanism (akin to the rs.fields("myField").value commands)
Data storage mechanism
My choice for data storage was unremarkable: a two-dimensional array. Since all typical (non-shaped) ADO Recordsets are essentially two-dimensional entities – with rows as one dimension, fields as the other – it made sense to use a two-dimensional array as the heart of the VBScript object. Because all ADO Recordsets provide the handy .GetRows() method to render their data as an array, this was doubly attractive. And because all Recordsets provide the .GetRows() method and create equivalent arrays, it occurred to me I could make my object (which I call an ArraySet) as robust and feature-packed as I liked from even the skimpiest Recordset source. For example, I could include my beloved .RecordCount property even if the original Recordset lacked it.
So let’s look at the initial stub of this VBScript class. We'll include just enough now to instantiate it, and load it with data. (Remember that for a VBScript class, all this code must be contained with the Class C_ArraySet and End Class commands.)
'Primitive variables for various purposes
Private bBOF 'Indicates if we are at beginning of record array
Private bEOF 'Boolean to indicate if we are at end of record array
Private intRecordCount 'The number of records/rows in the array
Private intFieldCount 'The count of fields/columns in the array
Private intCursorLocation 'The ordinal of the current record/row
'Array variables for containing the data
Private arDataset 'The array containing the records
Private arFieldNames 'An array containing the names of the fields
Private Sub Class_Initialize()
'Set initial values for all
bBOF = True
bEOF = True
intRecordCount = -1
intFieldCount = -1
intCursorLocation = -1
bReadyForUse = False
arDataset = Array() 'Load these with empty arrays
arFieldNames = Array()
End Sub
Public Sub Load(ByRef rs)
'Method to load the data from the Recordset
'First check that we actually received a Recordset
If TypeName(rs) <> "Recordset" Then
Err.Raise vbObjectError + 99999, "C_ArraySet:Load()", "Load method
requires a Recordset object."
End If
On Error Resume Next
arDataset = rs.GetRows() 'Harvest the Recordset's data
'Make sure some data was contained actually in the
'Recordset. If not, the .GetRows() method above returns
'an error. If so, we know there was no data, and set
'our variables to suit.
If Err.Number <> 0 Then
'.GetRows failed
intRecordCount = -1
intFieldCount = 0
bBOF = True
bEOF = True
intCursorLocation = -1
bReadyForUse = False
bRsLoaded = False
Exit Sub
End If
On Error GoTo 0
'At this point we know we have some data
bBOF = False
bEOF = False
intCursorLocation = 0
bRsLoaded = True
intRecordCount = UBound(arDataset, 2) 'Get the # of records
intFieldCount = rs.Fields.Count 'Get the # of fields
Dim i, arTmpFields()
ReDim arTmpFields(intFieldCount)
For i = 0 to intFieldCount-1
arTmpFields(i) = rs.fields(i).Name
Next
arFieldNames = arTmpFields
bReadyForUse = True
End Sub
The .Load method is where the fun begins. It's fairly simple: It accepts a Recordset object, double-checks that it is a Recordset, and if so uses the .GetRows command to save the data to arDataset. Note the use of On Error Resume Next just before the .GetRows command. As the comment explains, this lets us handle empty Recordsets properly. Assuming no errors occur, we then examine this array to determine how many rows and fields it has, storing these in variables for later use.
As a last step we load a one-dimensional array with the names of the fields. Why? Because we'll need an internal "lookup table" of field names later, so that when a user issues commands like varFN = objArraySet.fields("FirstName") we can translate the string "FirstName" to the correct ordinal value. This comes up in the section below on the data-field addressing mechanism.
Row-traversal mechanism
To recreate the familiar .MoveXXXX methods, we need to recreate the "cursor" concept. We do this with the intCursorLocation variable, using it to track which record is the current record. Each of these following methods…
. MoveNext
. MovePrevious
. MoveFirst
. MoveLast
. Move( [record-number] )
…will work simply by updating the value of this variable. The code for them follows.
Public Sub MoveNext()
'Move from the current record to the next one.
If intArrayUBound = -1 Then
Err.Raise vbObjectError + 99999, "C_ArraySet:MoveNext" , "ArraySet contains
no data"
End If
If intCursorLocation+1 > intArrayUBound Then
bEOF = True
Exit Sub
End If
intCursorLocation = intCursorLocation + 1
End Sub
The logic here is unremarkable. Each time .MoveNext is called, we increment the value of our cursor by 1 – while making sure we don't exceed the maximum range of the array. (We also do some error checking here, as elsewhere, to be sure our ArraySet actually contains some data!) The logic for the other row-navigation methods (below) follows the same basic logic.
The boundaries pose a concern for us, of course: How do we know when we have issued the last possible .MoveNext command and reached the end of the data? We do this the same way as in an ADO Recordset. The cursor can be traversing the Recordset, but it can also be in either the BOF (beginning) and EOF (end) state. We emulate these as well with Booleans, bBOF and bEOF, which get set to True when .MoveNext or .MovePrevious are issued one time too many. The cursor remains on the first (or last) record, but the
If you've grasped of the code above, then the code for .MovePrevious, .MoveFirst and .MoveLast should be fairly plain. I'll include it here for completeness.
Public Sub MovePrevious()
'Move from the current record to the previous one.
If intArrayUBound = -1 Then
Err.Raise vbObjectError + 99999, "C_ArraySet:MovePrevious", "ArraySet contains no data"
End If
If intCursorLocation < 1 Then
bBOF = True
Exit Sub
End If
intCursorLocation = intCursorLocation - 1
End Sub
Public Sub MoveFirst()
'Move from the current record to the first record.
If intArrayUBound = -1 Then
Err.Raise vbObjectError + 99999, "C_ArraySet:MoveFirst " , "ArraySet
contains no data"
End If
intCursorLocation = 0
bBOF = False
bEOF = False
End Sub
Public Sub MoveLast()
'Move from the current record to the last record.
If intArrayUBound = -1 Then
Err.Raise vbObjectError + 99999, "C_ArraySet:MoveLast " , "ArraySet contains
no data"
End If
intCursorLocation = intArrayUBound
bBOF = False
bEOF = False
End Sub
The .Move method is a little different, but not terribly so. With this method, we can jump directly to the desired row in the data. We just need to check that the desired row isn't out of bounds. If it's greater than the number of available rows, we point the cursor on the last row but set bEOF to True. If it's less than zero we point the cursor on the first row but set bBOF to True.
Public Sub Move(ByVal intRecordNumber)
'Move from the current record to the record
'at the intRecordNumber position in the array.
'intRecordNumber is always treated as an absolute
'value, not a relative value.
If intArrayUBound = -1 Then
Err.Raise vbObjectError + 99999, "C_ArraySet:Move " , "ArraySet contains no data"
End If
If Not IsNumeric(intRecordNumber) Then
Err.Raise vbObjectError + 99999, "C_ArraySet:Move " , " Move method requires an integer"
End If
If CInt(intRecordNumber) > intArrayUBound Then
intCursorLocation = intArrayUBound
bEOF = True
ElseIf CInt(intRecordNumber) < 0 Then
intCursorLocation = 0
bBOF = True
Else
intCursorLocation = CInt(intRecordNumber)
End If
End Sub
And because we have the bEOF and bBOF Boolean variables, we can quickly implement our own .BOF and .EOF properties:
Public Property Get EOF()
EOF = bEOF
End Property
Public Property Get BOF()
BOF = bBOF
End Property
Data-field addressing mechanism
So far, we have created two of our three fundamental elements. We have a data-storage system (an array) and a row-traversal system (our cursor). These let us put the object's "attention" on the row we want. Now we need a way to pluck the specific field we want from that row. This is the data-field addressing mechanism.
This posed an interesting challenge for me. I wanted to be able to use either the name of the field or its ordinal position, just like an ADO Recordset. For example, for a query based on SELECT LastName, FirstName FROM Patients, the following two commands must return the same result:
LN = objArraySet.fields("LastName")
LN = objArraySet.fields(0)
Let’s start with the second method, the use of ordinal values.
Think of each piece of data in our array as occupying a specific X-Y coordinate in a grid. Each piece of data exists in one discrete row, under one discrete field. We've already set our Y-value by using the .MoveNext method. We can use the field ordinal now, to specify our X-value. In keeping with the syntax of the ADO Recordset object, we'll call our method .Fields for consistency. (The astute – or bored – among you will notice this is technically a misnomer in our case. In an ADO Recordset, .Fields is a collection of objects – while here it is a method. We could have also made it a collection of VBScript field-objects, but we would have needed to create them as a separate class…and the benefit of all that work would be minimal.)
Public Function Fields(ByVal intSelectedField)
If intArrayUBound = -1 Then Then
Err.Raise vbObjectError + 99999, "C_ArraySet:Fields" , "ArraySet contains no
data"
End If
'Make sure we have allowed for the BOF and EOF conditions
If bBOF Then
Err.Raise vbObjectError + 99999, "C_ArraySet:Fields", "Cursor is at BOF.
Use MoveFirst method to access fields."
Exit Function
End If
If bEOF Then
Err.Raise vbObjectError + 99999, "C_ArraySet:Fields" , "Cursor is at EOF.
Use MoveFirst method to access fields."
Exit Function
End If
'Make sure the input is numeric…
If Not IsNumeric(intSelectedField) Then
Err.Raise vbObjectError + 99999, "C_ArraySet:Fields", "Fields input is non-
numeric. Expected an integer."
Exit Function
End If
intSelectedField = Cint(intSelectedField)
'Make sure the numeric input is within the range of
'zero thru the # of fields
If (intSelectedField > intFieldCount-1) OR (intSelectedField < 0) Then
Err.Raise vbObjectError + 99999, "C_ArraySet:Fields", "Field #" &
intSelectedField & " doesn't exist."
Exit Function
End If
'We seem to have a valid field number, so
'return the field data
Fields = arDataset(intField, intCursorLocation)
End Function
This works fine for ordinals. What about field names, then?
The issue now becomes one of translation. For example, for this SQL statement: "SELECT FirstName, LastName FROM MyTable"– we would need to translate the string "LastName" into the ordinal value of zero. The technique we choose has to allow for arbitrary field names – there’s no telling what field names might be used, and any alphanumeric field name should be valid. But we can assume that no two fields will have the same name though. Knowing this, the problem quickly resolves itself to a key/item issue, and various options present themselves.
You will probably be tempted to rush to our trusty Dictionary object. So was I. It’s built specifically for key/item pairs, and does a fine job of it. However, I felt it was backsliding a bit to use it, given my goals. Consider, the key benefit of this technique is to "throw away the wrapper"– to drop the compiled ADO Recordset object – as soon as possible. That benefit is somewhat lost if we have to maintain a Dictionary object instead throughout our ArraySet's lifetime. And there likely won’t be that many fields anyway – in my work it’s rare that I have even 25 fields in one Recordset – so less sexy techniques will suffice.
Recall that we created a one-dimensional array of field-names during the .Load method? Here's where it comes in handy. Let's rewrite part of our .Fields method above to handle string input as well as numeric.
Public Function Fields(ByVal intSelectedField)
If intArrayUBound = -1 Then Then
Err.Raise vbObjectError + 99999, "C_ArraySet:Fields" , "ArraySet contains no
data"
End If
'Make sure we have allowed for the BOF and EOF conditions
If bBOF Then
Err.Raise vbObjectError + 99999, "C_ArraySet:Fields", "Cursor is at BOF.
Use MoveFirst method to access fields."
Exit Function
End If
If bEOF Then
Err.Raise vbObjectError + 99999, "C_ArraySet:Fields" , "Cursor is at EOF.
Use MoveFirst method to access fields."
Exit Function
End If
Dim intOrdinal
'Determine if the input is numeric…
If IsNumeric(intSelectedField) Then
'It IS numeric, so if its within the
'upper and lower ranges, return the data
intOrdinal = Cint(intSelectedField)
'Make sure the numeric input is within the range of
'zero thru the # of fields
If (intOrdinal > intFieldCount-1) OR (intOrdinal < 0) Then
Err.Raise vbObjectError + 99999, "C_ArraySet:Fields", "Field #" &
intOrdinal & " doesn't exist."
Exit Function
End If
Else
'It's NOT numeric, so treat it as a string and
'cycle through the field-name array looking
'for a match.
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
End If
'Check to see if any matching ordinal value was found…
If intOrdinal = -1 Then
Err.Raise vbObjectError + 99999, "C_ArraySet:Fields", "No field with the
name '" & intSelectedField & "' exists."
Exit Function
End If
'We seem to have a valid field number, so
'return the field data
Fields = arDataset(intOrdinal, intCursorLocation)
End Function
What we have added is the necessary code to translate the intSelectedField parameter into the ordinal integer, if it exists – while of course coughing up the proper exception if it doesn't. It does this by looping through the available field names with each run, comparing the trimmed, lowercase input with the values in the field array.
Next: Feeling Loopy? >>
More ASP Articles
More By Wrox Team
