Materializing any ADODB Query

Question 1

Following-up onCreating ADODB Parameters on the fly and pushing the "wrapping" of ADODB a step further, I have written two more classes that allows me to expose methods that don't require aConnection object, without returning anADODB.Recordset.

Taking this method as a reference:

Public Function Execute(connection As ADODB.connection, ByVal sql As String, ParamArray parametervalues()) As ADODB.Recordset        Dim Values() As Variant    Values = parametervalues        Dim cmd As ADODB.Command    Set cmd = CreateCommand(connection, adCmdText, sql, Values)        Set Execute = cmd.Execute    End Function

A bit of context

I'm not comfortable with the idea of exposing a method that would return anADODB.Recordset without taking in anADODB.Connection, because this would mean opening a connection in a function that doesn't control when the connection needs to be closed.

To address this issue, I added two private fields to mySqlCommand:

Private connString As StringPrivate resultFactory As New SqlResult

I'm using a pre-determined connection string inClass_Initialize for theconnString value:

Private Sub Class_Initialize()    connString = Application.ConnectionStringEnd Sub

I adopted the "Quick" prefix to refer to an "overload" method that owns its own connection, hence the connection-less "overload" for theExecute method above will be calledQuickExecute:

Public Function QuickExecute(ByVal sql As String, ParamArray parametervalues()) As SqlResult            Dim parameters() As Variant    parameters = parametervalues    Dim connection As New ADODB.connection    connection.ConnectionString = connString        connection.Open        Dim rs As ADODB.Recordset    Set rs = Execute(connection, sql, parameters)        Set QuickExecute = resultFactory.Create(rs)        rs.Close    Set rs = Nothing        connection.Close    Set connection = Nothing        End Function

The method consumes the recordset and returns an object that encapsulates its contents, aSqlResult object.

SqlResult

This type encapsulates aList<string> and aList<SqlResultRow> (see List class here), respectively holding field names and field values for each row.

PropertyItem has a procedure attribute that makes it the type'sdefault property, and a procedure attribute of -4 on propertyNewEnum allows iterating theSqlResultRow items with aFor Each loop, like this:

Dim sql As Stringsql = "SELECT TOP 10 * FROM SomeTable"Dim cmd As New SqlCommandDim result As SqlResultSet result = cmd.QuickExecute(sql)Dim row As SqlResultRowFor Each row In result    Debug.Print row("SomeFieldName"), TypeName(row("SomeFieldName"))Next

Here's the code:

Private Type tSqlResult    FieldNames As List    Values As List    ToStringValueSeparator As StringEnd TypePrivate this As tSqlResultOption ExplicitPrivate Sub Class_Initialize()    Set this.FieldNames = New List    Set this.Values = New List    this.ToStringValueSeparator = ","End SubPublic Property Get ValueSeparator() As String    ValueSeparator = this.ToStringValueSeparatorEnd PropertyPublic Property Let ValueSeparator(ByVal value As String)    this.ToStringValueSeparator = valueEnd PropertyPublic Sub AddFieldName(name As String)    this.FieldNames.Add nameEnd SubPublic Function FieldNameIndex(ByVal name As String) As Long    FieldNameIndex = this.FieldNames.IndexOf(LCase$(name)) - 1End FunctionPublic Sub AddValue(value As SqlResultRow)    this.Values.Add valueEnd SubPublic Property Get Count() As Long    Count = this.Values.CountEnd PropertyPublic Property Get Item(ByVal index As Long) As SqlResultRow    Set Item = this.Values(index + 1)End PropertyPublic Property Get NewEnum() As IUnknown'Gets an enumerator that iterates through the List.        Set NewEnum = this.Values.NewEnumEnd PropertyPublic Function Create(adoRecordset As ADODB.Recordset) As SqlResult        Dim result As New SqlResult    Dim names As New List    Dim fieldValues As New List        Dim row As ADODB.fields    Dim field As ADODB.field        Dim rowFactory As New SqlResultRow        Dim grabFieldName As Boolean    grabFieldName = True        While Not adoRecordset.BOF And Not adoRecordset.EOF                For Each field In adoRecordset.fields            If grabFieldName Then result.AddFieldName LCase$(Coalesce(field.name, vbNullString))        Next                result.AddValue rowFactory.Create(result, adoRecordset.fields)                grabFieldName = False        adoRecordset.MoveNext    Wend        Set Create = result    End Function

SqlResultRow

Each row encapsulates an array ofVariant values, and has anItem property (which also has a procedure attribute that makes it the type'sdefault property) that can take either aString representing a field's name, or any number representing a field's index. AToString method conveniently outputs all field values separated by commas (the actual separator is configurable in theSqlResult class).

Private Type tRow    ParentResult As SqlResult    Values() As Variant    IsEmpty As BooleanEnd TypePrivate this As tRowOption ExplicitPrivate Sub Class_Initialize()    ReDim this.Values(0 To 0)    this.IsEmpty = TrueEnd SubPublic Property Set ParentResult(value As SqlResult)    Set this.ParentResult = valueEnd PropertyFriend Sub AddValue(ByVal value As Variant)        If Not this.IsEmpty Then ReDim Preserve this.Values(0 To UBound(this.Values) + 1)    this.Values(UBound(this.Values)) = value        this.IsEmpty = False    End SubPublic Property Get Item(nameOrIndex As Variant) As Variant        If TypeName(nameOrIndex) = "String" Then            Item = GetFieldValueByName(nameOrIndex)        ElseIf IsNumeric(nameOrIndex) Then            Item = GetFieldValueByIndex(nameOrIndex)        Else                'return empty variant            End If    End PropertyPrivate Function GetFieldValueByName(ByVal name As String) As Variant    If Not this.IsEmpty Then GetFieldValueByName = this.Values(this.ParentResult.FieldNameIndex(name))End FunctionPrivate Function GetFieldValueByIndex(ByVal index As Integer) As Variant    If Not this.IsEmpty Then GetFieldValueByIndex = this.Values(index)End FunctionPublic Function Create(parent As SqlResult, fields As ADODB.fields) As SqlResultRow        Dim result As New SqlResultRow    Set result.ParentResult = parent        Dim field As ADODB.field    Dim value As Variant        For Each field In fields        If TypeName(field.value) = "String" Then            value = LTrim(RTrim(Coalesce(field.value, vbNullString)))        Else            value = Coalesce(field.value, vbEmpty)        End If        result.AddValue value    Next        Set Create = result    End FunctionPublic Function ToString() As String            If this.IsEmpty Then        ToString = TypeName(Me)        Exit Function    End If        Dim result As String    result = Join(this.Values, this.ParentResult.ValueSeparator)        ToString = result    End Function

The types are retained, so if a query returns aDate field, the type of that value will beDate in theSqlResultRow.

I use a small helper function,Coalesce, to deal withnull values. For reference, here's the listing:

Public Function Coalesce(ByVal value As Variant, Optional ByVal value_when_null As Variant = 0) As Variant    Dim return_value As Variant    On Error Resume Next 'supress error handling    If IsEmpty(value) Or IsNull(value) Or (TypeName(value) = "String" And value = vbNullString) Then        return_value = value_when_null    Else        return_value = value    End If    Err.Clear 'clear any errors that might have occurred    On Error GoTo 0 'reinstate error handling    Coalesce = return_valueEnd Function

Question 2

You should try to avoid using SELECT * as it can slow down querying for no good reason. SELECT x,y,z is easier to understand and is as fast/faster than SELECT *.

Question 3

Are you planning to add some filter or sort capabilities to your wrapper?

Question 4

@UnhandledException why would I want to do that? If I need filtering I can have aWHERE clause in the query; if I need sorted results I can have anORDER BY clause... Why not let the database do the hard work for me?

Question 5

Ok, maybe my intention is too specific. I use an ADODB-Recordset in VBA, which holds many records, which I have to process in different ways. To avoid to many roundtrips to the server I once load all necessary records and then use it offline and filter and sort it locally for each different analysation. It would be nice to get rid of this ADODB.Recordset at all and use a custom, more abstract, approach. Thats why I got the idea that your classes maybe could be enhanced to fulfill that.

Question 6

They could... But I'm pretty sure doing that work in code would be much less efficient than having the database server do it. DB roundtrips aren't free indeed, but to me the costly part is iterating the returned results; the more you iterate them, the less efficient your code is.

Question 7

I want to focus on theSqlResult/SqlResultRow classes here. The way it is, it is analogous to having bought a huge expensive truck then insisting on driving the original dinky car that you wouldn't trade in and paying the payments on both the truck and the dinky car.

Why?

Because you're basically taking anADODB.Recordset object, a full-featured entity that provides sorting, filtering, jumping to an arbitrary position, and few more. That's your expensive truck. You then painstakingly copy the contents of the recordset into a custom collection which has much less features... that's your dinky car.

Now, you are doing this for encapsulation and that'snot a bad thing at all! However, what I propose is that instead of copying the content from a recordset to a custom collection, that you use theADODB.Recordset as the implementation underneath theSqlResult class.

That way, it becomes very easy to wrap methods like sorting, filtering, jumping what have you. The consumers of theSqlResult class need not know about the recordset under the hood driving the class.

But, I don't want the connection leaking!

And that's a legit concern! However, with anADODB.Recordset, it is easy to manage this. What you actually want is a disconnected recordset. That way, the contents of the recordset are all available in the user's computer's memory and there's no dangling connection. What you should do is basically something like this:

Public Function Execute(connection As ADODB.connection, ByVal sql As String, ParamArray parametervalues()) As ADODB.Recordset    Dim Values() As Variant    Values = parametervalues    Dim cmd As ADODB.Command    Set cmd = CreateCommand(connection, adCmdText, sql, Values)    'Configure the recordset to use client-side snapshot    'which is the only valid option for disconnected recordset    'It needs not be readonly but updatable disconnected recordset    'is needlessly complicating things anyway.    Dim rs As ADODB.Recordset    Set rs = New ADODB.Recordset    With rs        .CursorLocation = adUseClient        .CursorType = adOpenStatic        .LockType = adLockReadOnly    End With    'Load the recordset with result of the command    'We can't assign rs directly from the Execute method of the cmd    'or it'll coerce it to the wrong type of the recordset    rs.Open cmd    'Disconnect the recordset    Set rs.ActiveConnection = Nothing            Set Execute = rsEnd Function

Now we have a disconnected recordset that can be browsed, iterated, etc. and then provided to theSqlResult class.

That way the consumers need not know about the implementation of ADO but you still get all the goodness ofADODB.Recordset without incurring any extra costs and you can then modify theSqlResult class to wrap various features on theADODB.Recordset for essentially free. By the same token,SqlResultRow is easier, since you can leverage theADODB.Record or something similar. Now you're actually driving that fancy expensive truck, something youwould have gotten anyway even if you didn't really needed all the features it has to offer.

Question 8

I'm struggling to put this into use... "Cannot modify theActiveConnection property of aRecordset object using aCommand as a data source" onSet rs.ActiveConnection = Nothing

Question 9

It's a client-side static recordset, right? That's the only type that can be disconnected.

Question 10

A quick code inspection with MZ-Tools reveals the following:

Local variablesnames,fieldValues androw can be safely removed from theCreate method.

That's all the tool is picking up though.

I like how it makes everythingautomagical, however if it were the only way to get the data I'd be worried about performance with some large recordsets. TheList class makes it easier to find a value by field name, but thesearch for the field name happens every time, which means lots of time is~~spent~~wasted finding the same field index over and over again, for each record. Keeping the index for each name in aDictionary<String,int> would be more efficient than having to search for each column index for each row.

That said,SqlCommand has methods that take aADODB.Connection and output aADODB.Recordset, having the possibility to use these methods for larger recordsets and let the client code deal with the connection and the recordset, somewhat makes up for the performance hit of the wrapperSqlResult; you get theautomagical parametersand the possibility to only iterate the data once.

Question 11

This loop (inSqlResult.Create):

For Each field In adoRecordset.fields    If grabFieldName Then result.AddFieldName LCase$(Coalesce(field.name, vbNullString))Next

will still iterate all fields even thoughgrabFieldName isFalse. And sincegrabFieldName will only beTrue for the first record, why not just do it like this - and the flag should be calledgrabFieldNames, since the code is "grabbing"all field names:

If grabFieldNames Then    For Each field In adoRecordset.fields        result.AddFieldName LCase$(Coalesce(field.name, vbNullString))    NextEnd If

Speaking ofAddFieldName, this implementation:

Public Sub AddFieldName(name As String)    this.FieldNames.Add nameEnd Sub

Might work for most scenarios, but then if you want to have aDictionary that maps field names to an index for more efficient field name lookups, a query likeSELECT NULL AS Test, NULL AS Test will blow it up, since dictionary keys must be unique.

Given this field (seeDictionary implementation here):

Private nameIndices As New Dictionary

AddFieldName could look like this:

Public Sub AddFieldName(ByVal name As String)    Static nameInstances As New Dictionary    Dim localName As String    localName = LCase$(name)    If nameIndices.ContainsKey(localName) Then        If nameInstances.ContainsKey(localName) Then            nameInstances(localName) = nameInstances(localName) + 1        Else            nameInstances.Add localName, 1        End If        AddFieldName name & nameInstances(localName) 'recursive call    Else        this.FieldNames.Add localName        nameIndices.Add localName, this.FieldNames.Count - 1    End IfEnd Sub

This way the firstTest field will be calledTest, and the 2nd one will be calledTest1, ensuring uniqueness of the field names. This could be quite surprising to the calling code, though, but selecting identically named columns shouldn't happen very often.

TheFieldNameIndex function can then look like this:

Public Function FieldNameIndex(ByVal name As String) As Long    Dim i As Long    If nameIndices.TryGetValue(name, i) Then        FieldNameIndex = i    Else        FieldNameIndex = -1    End IfEnd Function

Question 12

Is there any reason you don't use a disconnected record set and just close the connection in the function that opened it? I wouldn't keep a connection open any longer than you need.

<!doctype html><html><head><meta charset="utf-8"><title>Untitled Document</title></head><body><p>This is a way I've found useful. The general idea is never keeping the connection open any longer than you have to. </p><pre>Sub RunQuery()    '    You can declare as many arrays as you need    Dim RS1 As Variant    Dim ParameterValues As String    ParameterValues = "You can change this as needed"    RS1 = GetDiscRecordset(ParameterValues)    For c = LBound(RS1, 1) To UBound(RS1, 1)        For r = LBound(RS1, 2) To UBound(RS1, 2)            '    Iterate through the recordset            Debug.Print RS1(c, r)        Next r    Next cEnd Sub</pre><p>The <b>GetDiscRecordset</b> function is similar to your execute function but we are returning a <i>Disconnected</i> recordset.</p><pre>Function GetDiscRecordset(ParameterValues As String) As Variant    Dim Qry As String    Qry = "Select * From SourceTable Where [?PlaceHolder for Parameters?]" 'Modify as needed    Qry = Replace(Qry, "[?PlaceHolder for Parameters?]", ParameterValues)    Dim Conn As ADODB.connection    Set Conn = New ADODB.connection    Dim Rst As ADODB.Recordset    Conn.ConnectionString = "Connection String" 'Modify as needed    Conn.Open    Set Rst = New ADODB.connection    Set Rst.ActiveConnection = Conn    '    Retrieve data    Rst.CursorLocation = adUseClient    Rst.LockType = adLockBatchOptimistic    Rst.CursorType = adOpenStatic    Rst.Open Qry, , , , adCmdText   '<- we set the rst stuff above so thats cool, thats our recordset    '    NOW DISCONNECT RECORDSET HERE!    Set Rst.ActiveConnection = Nothing    Rst.MoveFirst    '    Pass the recordset back    GetDiscRecordset = Rst.GetRowsEnd Function</pre></body></html>

Question 13

Disconnected recordset is a very good point - if I wrote this today I'd scrap theSqlResult wrapper and return a disconnected recordset instead. The methods that take a connections parameter don't own it though, and thus shouldn't close it - they exist so that the code that owns the connection can initiate a transaction and run multiple commands before committing or rolling back.

Question 14

That said this looks more like a comment than an answer IMO.

Question 15

I agree with Mathieu here. As it stands this is not really an answer and more of a clarifying comment. If you could expand a bit on the point you're making and change the tone of the text to something more "answer"-y, that'd be appreciated. If you prefer to keep it like this, I can convert this to a comment for you. Just give me a heads up. Thanks!

this 2,03914 silver badges24 bronze badges · Accepted Answer · 2019-06-14 19:01:31Z

I want to focus on theSqlResult/SqlResultRow classes here. The way it is, it is analogous to having bought a huge expensive truck then insisting on driving the original dinky car that you wouldn't trade in and paying the payments on both the truck and the dinky car.

Why?

Because you're basically taking anADODB.Recordset object, a full-featured entity that provides sorting, filtering, jumping to an arbitrary position, and few more. That's your expensive truck. You then painstakingly copy the contents of the recordset into a custom collection which has much less features... that's your dinky car.

Now, you are doing this for encapsulation and that'snot a bad thing at all! However, what I propose is that instead of copying the content from a recordset to a custom collection, that you use theADODB.Recordset as the implementation underneath theSqlResult class.

That way, it becomes very easy to wrap methods like sorting, filtering, jumping what have you. The consumers of theSqlResult class need not know about the recordset under the hood driving the class.

But, I don't want the connection leaking!

And that's a legit concern! However, with anADODB.Recordset, it is easy to manage this. What you actually want is a disconnected recordset. That way, the contents of the recordset are all available in the user's computer's memory and there's no dangling connection. What you should do is basically something like this:

Public Function Execute(connection As ADODB.connection, ByVal sql As String, ParamArray parametervalues()) As ADODB.Recordset    Dim Values() As Variant    Values = parametervalues    Dim cmd As ADODB.Command    Set cmd = CreateCommand(connection, adCmdText, sql, Values)    'Configure the recordset to use client-side snapshot    'which is the only valid option for disconnected recordset    'It needs not be readonly but updatable disconnected recordset    'is needlessly complicating things anyway.    Dim rs As ADODB.Recordset    Set rs = New ADODB.Recordset    With rs        .CursorLocation = adUseClient        .CursorType = adOpenStatic        .LockType = adLockReadOnly    End With    'Load the recordset with result of the command    'We can't assign rs directly from the Execute method of the cmd    'or it'll coerce it to the wrong type of the recordset    rs.Open cmd    'Disconnect the recordset    Set rs.ActiveConnection = Nothing            Set Execute = rsEnd Function

Now we have a disconnected recordset that can be browsed, iterated, etc. and then provided to theSqlResult class.

That way the consumers need not know about the implementation of ADO but you still get all the goodness ofADODB.Recordset without incurring any extra costs and you can then modify theSqlResult class to wrap various features on theADODB.Recordset for essentially free. By the same token,SqlResultRow is easier, since you can leverage theADODB.Record or something similar. Now you're actually driving that fancy expensive truck, something youwould have gotten anyway even if you didn't really needed all the features it has to offer.

I'm struggling to put this into use... "Cannot modify theActiveConnection property of aRecordset object using aCommand as a data source" onSet rs.ActiveConnection = Nothing
It's a client-side static recordset, right? That's the only type that can be disconnected.

Movatterモバイル変換

Stack Exchange Network

Materializing any ADODB Query

A bit of context

SqlResult

SqlResultRow

4 Answers4

You mustlog in to answer this question.

Linked

Related

Hot Network Questions

Subscribe to RSS