[RESOLVED] adArray and ADO's memory-based (aka, disconnected) recordsets

[Elroy]

As far as anyone knows, is that adArray bit-flag useless with respect to these ADO disconnected recordsets?

I've tried about all the combinations I can think of, and always get an error 3001 when trying to add the field. I also Googled it about every way I could think of, and everyone lists it in the DataTypeEnum, but nobody shows an example of how to use it.

------------

ADDED: I could see two ways it might work:

1) It places a pointer to a SafeArray structure in the record, with the array being off in other memory (similar to the way an adBSTR seems to be working).

2) Say, for a Long (adInteger), it uses DefinedLength to create space within the record, with DefinedLength somehow specifying the number of array items.

But again, I tried every combination I could think of, and nothing worked.

[VanGoghGaming]

MSDN says nope to fields of type adArray: https://learn.microsoft.com/en-us/sq...end-method-ado

Field objects of the following data types (DataTypeEnum) cannot be appended to the Fields collection and will cause an error to occur: adArray, adChapter, adEmpty, adPropVariant, and adUserDefined. Also, the following data types are not supported by ADO: adIDispatch, adIUnknown, and adIVariant. For these types, no error will occur when appended, but usage can produce unpredictable results including memory leaks.

[Elroy]

MSDN says nope to fields of type adArray: https://learn.microsoft.com/en-us/sq...end-method-ado

Ahhh, good catch. Thanks. I'll quit beating my head against the wall.

[VanGoghGaming]

Just tested ".Append "Test", adBinary, 100" and it works so this may be used as a byte array.

[Elroy]

Just tested ".Append "Test", adBinary, 100" and it works so this may be used as a byte array.

Oh yeah, I'm aware of those options. Or even using adBSTR in a tricky way to have variable length arrays. There are also some other "pointer in the record" options for variable length data.

I'll continue exploring those. I'm just working on getting clear as to what "tools" I do and don't have with these disconnected recordsets.

Not sure when it'll be, but I'll eventually post something in the codebank.

[dilettante]

adLongVarBinary is the closest match to a VB Byte array.

Other values may work, but probably just get treated as synonyms by the Cursor Service.

[dilettante]

The PPT here is useful for playing with fabricated recordsets in ADO: https://www.slideserve.com/devi/disconnecting-ado

[Schmidt]

In case the whole exercise is about "stuffing arbitrary VB-Arrays into Rs-Fields" -
there's always the adVariant = vbVariant = 12 Field-Type:

Private Sub Form_Load()
  Dim Rs As New Recordset
      Rs.Fields.Append "LArr", vbVariant
      Rs.Fields.Append "SArr", vbVariant
  Rs.Open
  
  Dim L(1 To 2) As Long: L(1) = 1: L(2) = 2
  Rs.AddNew: Rs!LArr = L: Rs!SArr = Split("x,y", ",")
  
  Debug.Print Rs!LArr(1), Rs!LArr(2), Rs!SArr(0), Rs!SArr(1)
End Sub

I think you have to get rid of the notion,
that a free-standing ADO-Rs follows "ISAM-record-storage-rules" exactly,
when it comes to User- (not DB-) supplied FieldValues.

"It knows" already, that it is "not connected to a DB-Driver" (and never will be).

Olaf

[VanGoghGaming]

https://learn.microsoft.com/en-us/sq...i/datatypeenum

adVariant	12	Indicates an Automation Variant (DBTYPE_VARIANT).

Note This data type is currently not supported by ADO. Usage may cause unpredictable results.

[Elroy]

In case the whole exercise is about "stuffing arbitrary VB-Arrays into Rs-Fields" -
there's always the adVariant = vbVariant = 12 Field-Type:

Well that's quite interesting. And yeah, VanGogh beat me to it, the MSDN definitely states in a few places that adVariant shouldn't be used with these things.

However, I'm guessing, because they can be persistent (with .Save), and also have none of the object reference machinery within them, that they're worried we'll put an object in our Variant.

For instance, I've got no idea what the following code is doing:

Option Explicit

Private Sub Form_Load()
  Dim Rs As New Recordset
      Rs.Fields.Append "Var", vbVariant
  Rs.Open

  Dim c As New Collection
  c.Add "a", "b"

  Rs.AddNew

  Rs!Var = c



End Sub

However, maybe if we're careful to not put objects in them, maybe adVariant is fine.

Do we know that pointer stuff in a Variant is getting de-allocated correctly, when it's in an ADO record ... say when the record is deleted? (String, Array, anything else that's not actually "in" the variant?)

[Elroy]

This is even a more scary example, that executes just fine:

Option Explicit

Private Sub Form_Load()
  Dim Rs As New Recordset
      Rs.Fields.Append "Var", vbVariant
  Rs.Open

  Dim c As New Collection
  c.Add "a", "b"
  Dim v As Variant
  Set v = c

  Rs.AddNew
  Rs!Var = v

  Dim v2 As Variant
  v2 = Rs!Var

  Dim c2 As Collection
  Set c2 = v2

  Debug.Print c2.Count  ' Prints 1, showing it's got our original collection.

End Sub

I'm just really worried that deleting that record won't decrement the collection's reference count (and, for arrays and/or strings in variants, their memory won't be de-allocated).

[Schmidt]

Do we know that pointer stuff in a Variant is getting de-allocated correctly, when it's in an ADO record ... say when the record is deleted? (String, Array, anything else that's not actually "in" the variant?)

You have to do it "manually" by setting such Fields = Empty before calling Rs.Delte
(that's probably what they mean with "undefined behaviour"):

Here's an example (in the Form-Click-Event):

Option Explicit
 
Private Rs As New Recordset

Private Sub Form_Load()
  Rs.Fields.Append "SArr", vbVariant
  Rs.Open
End Sub

Private Sub Form_Click()
  Static Add As Boolean: Add = Not Add
  If Add Then
    Rs.AddNew
    Rs!SArr = Split(String(10 ^ 7, "X"))
  Else
    Rs!SArr = Empty '<- that's necessary to not leak memory
    Rs.Delete 
  End If
  Caption = Rs.RecordCount
End Sub

Olaf

[Elroy]

You have to do it "manually" by setting such Fields = Empty before calling Rs.Delte
(that's probably what they mean with "undefined behaviour"):

Ok, that I get. So, I'm guessing that only the 16 bytes of the actual variant (in the recordset) get deleted (and de-allocated). Therefore, we're responsible (outside of the recordset) for all object reference increments/decrements, and also making sure any strings or arrays (in a variant in a recordset) get maintained in memory (or the variant in the recordset will have a bad pointer).

Yeah, that's all more work than I'm wanting to do with these things. I'm just looking for the "natural edges" of these things, and I'm thinking arrays and variants are beyond that.

Thanks for exploring though.

[dilettante]

Byte arrays are a special case, useful for lots of things like storing images. I still wouldn't try stuffing lots of large images into one though.

[Schmidt]

So, I'm guessing that only the 16 bytes of the actual variant (in the recordset) get deleted (and de-allocated)

No, the ADO-Rs "ValueStore" is aware that the current Field-Slot is a true Variant-Slot (and not only 16 bytes).

Meaning:
- they call the VariantClear-API (which does deep-cleaning) properly,
.. when a new Variant-Value (like e.g. Empty) is assigned to that FieldValue-slot
- they also call VariantClear for all Records with Variant-Fields when the Rs-Instance goes out of scope
...so, no real leakage so far...
- where they don't call VariantClear (on the Variant-Field-Slots), is in case of an Rs.Delete

But ...have not tested though, whether this "non-clearance" in case of Delete is true for all Fields of a given record
(the whole record-valuestore for this row then kept in memory for performance-reasons, and only "flagged" as deleted)

So, the Memory-Leak I was talking about, is not really a "true mem-leak",
because when you set the Rs to Nothing, everything is cleaned up properly at that point.

You can check that yourself in task-manager...

HTH

Olaf

[SearchingDataOnly]

Just discovered an interesting phenomenon:

The memory-based ADODB.Recordset and RC6.Recordset(SqliteDB.Recordset) can't store object instances, but the VB6-ADO-Rs-like "multi-column-container" that Olaf released yesterday in CodeBank does.

It would be great if we could make memory-based ADODB.Recordset and RC6.Recordset(SqliteDB.Recordset) also store object instances.

[Elroy]

Just discovered an interesting phenomenon:

The memory-based ADODB.Recordset and RC6.Recordset(SqliteDB.Recordset) can't store object instances, but the VB6-ADO-Rs-like "multi-column-container" that Olaf released yesterday in CodeBank does.

It would be great if we could make memory-based ADODB.Recordset and RC6.Recordset(SqliteDB.Recordset) also store object instances.

Even if memory-based (disconnected), these things are still considered possibly persistent. That is, the .Save method still works with them. Objects, almost by definition, are not persistent. So, if we started putting object references into fields of a recordset, we'd need to be very careful about not using the .Save method with that recordset.

[Elroy]

- they call the VariantClear-API (which does deep-cleaning) properly,
.. when a new Variant-Value (like e.g. Empty) is assigned to that FieldValue-slot
- they also call VariantClear for all Records with Variant-Fields when the Rs-Instance goes out of scope
...so, no real leakage so far...
- where they don't call VariantClear (on the Variant-Field-Slots), is in case of an Rs.Delete

Ok, I've convinced myself that, when a Variant with an array in it is assigned to an adVariant field, the entire array does indeed get copied (which is good). And apparently, when you pull it out (i.e., assign the field to some other Variant), it gets copied again, which is also good I suppose.

So, the only downside is that VariantClear doesn't get called when a record is deleted. However, you're saying that it will eventually be called:

• Are you thinking that VariantClear gets called when we close the recordset for deleted records (as well as non-deleted records) (rs.Close)?
• And/or VariantClear gets called when the recordset's object variable falls out of lifetime (for both deleted and non-deleted records)?

If both of those are true, then it's all just a soft caveat to empty out or recordset variant fields before record deletion so we're not temporarily chewing up memory.

[dilettante]

Objects, almost by definition, are not persistent.

Not true. Classes can be persistable, as long as they are published.

[Elroy]

Not true. Classes can be persistable, as long as they are published.

Right, but we can stick all kinds of objects into variants. That's all I was trying to say. Thanks for clearing that up though.

----------

On another note though, I'm getting pretty comfortable with arrays in a variant in a disconnected recordset field.

Here's my test code (just watching memory usage in Task Manager):

Option Explicit
Dim rs As ADODB.Recordset

Private Sub Form_Load()
    Dim sa() As String

    Set rs = New ADODB.Recordset
    rs.Fields.Append "MyVar", adVariant
    rs.Open
    rs.AddNew

    MsgBox "before much of anything"            ' About 10MB.

    ReDim sa(1200000)
    Dim i As Long
    For i = LBound(sa) To UBound(sa)
        sa(i) = CStr(Rnd)
    Next
    MsgBox "sa() created"                       ' About 60MB (+50 change).


    Dim v As Variant
    v = sa
    MsgBox "sa() copied to Variant"             ' About 110MB (+50 change).


    rs![MyVar] = v
    MsgBox "Array placed into field"            ' About 160MB (+50 change).


    Erase sa
    MsgBox "Original sa() erased"               ' About 142MB (-18 change)??


    v = Empty
    MsgBox "Working v set to empty"             ' About 76MB (-66 change)??


    rs.Delete
    MsgBox "Deleted record"                     ' About 76MB (no change)

    'rs.Close
    'MsgBox "Closed recordset"                   ' About 10MB.

    Set rs = Nothing
    MsgBox "Uninstantiated recordset"           ' About 10MB (doesn't matter if rs.close executed or not).


    Unload Me

End Sub

It seems that either a .Close or an uninstantiation cleans things up (one or both, doesn't matter). I'm also starting to think that the .Close method call isn't really that important for these things (regardless of whether these recordsets have arrays in them or not).

Ohhh, and I did test setting the ADO field to Empty (with no delete), and that did free the array memory.

[Elroy]

Ahh, interesting. If you've got an adVariant as a field with an array in it, you can't save the recordset.



So, it seems that, at least in ADO2.8, they're trying to cover the bases.

[Elroy]

Ok, I also tested putting instantiated objects into the adVariant fields of a disconnected recordset.

They seem to be handled exactly the same way arrays are handled. And one slight anomaly ... you can't use "Set" with rs!MyVariantField, but that's what happens even though there's no explicit "Set".

So, here's what I found:

• When you place an object in an adVariant field, its reference count gets incremented.
• When you set that field to Empty (etc), the object's reference count gets decremented.
• When you rs.Close the recordset, all object references get decremented.
• When the recordset gets uninstantiated (if not already rs.Close), all object references get decremented.
• When you rs.Delete a record, object references do NOT get decremented, but this DOES get cleaned up when the recordset is either closed or uninstantiated.
• If you try to rs.Save one of these recordsets with an object reference in it, you'll get that hex 80020003 error (shown above).

So, to my way of thinking, we're good to go with respect to placing object references into these disconnected recordsets.

-------------

Just FYI, here's what I used to check the reference counts:

Private Function RefCount(ByRef o As IUnknown) As Long
    ' Private Declare Function DispCallFunc Lib "OleAut32" (ByVal ppv As Long, ByVal oVft As Long, ByVal cc As Long, ByVal rtTYP As VbVarType, ByVal paCNT As Long, ByVal paTypes As Long, ByVal paValues As Long, ByRef fuReturn As Variant) As Long
    '
    Const IID_QueryInterface_Offset As Long = 0&
    Const IID_AddRef_Offset         As Long = 4&
    Const IID_Release_Offset        As Long = 8&
    Const CC_STDCALL As Long = 4&
    '
    Dim pIUnknown As Long
    Dim vReturn As Variant
    '
    If o Is Nothing Then Exit Function ' Count is zero.
    pIUnknown = ObjPtr(o)
    ' Quickly increment and decrement the count, no harm done.
    Call DispCallFunc(pIUnknown, IID_AddRef_Offset, CC_STDCALL, vbLong, 0&, 0&, 0&, vReturn)
    Call DispCallFunc(pIUnknown, IID_Release_Offset, CC_STDCALL, vbLong, 0&, 0&, 0&, vReturn)
    '
    ' Return the count.
    ' Just calling here creates another temporary reference, so we subtract it.
    RefCount = CLng(vReturn) - 1
End Function

[SearchingDataOnly]

Even if memory-based (disconnected), these things are still considered possibly persistent. That is, the .Save method still works with them. Objects, almost by definition, are not persistent. So, if we started putting object references into fields of a recordset, we'd need to be very careful about not using the .Save method with that recordset.

If memory-based Recordset can store object instances, then we can use it as a multi-column collection. Olaf's VB6-ADO-Rs-like "multi-column-container" already does this well, the only regret is that it does not have sorting feature.

Although RC6.ArrayList contains a Sort method, sometimes we need to use associative sorting of multiple arrays, i.e., when one array is sorted, other arrays should also adjust their order according to the sorted main array, which seems to be a troublesome problem.

[VanGoghGaming]

Ahh, interesting. If you've got an adVariant as a field with an array in it, you can't save the recordset.



So, it seems that, at least in ADO2.8, they're trying to cover the bases.

Another way of saving a RecordSet is by serializing it into a PropertyBag:

With New PropertyBag
    .WriteProperty "RecordSet", objRecordSet
    byteArray = .Contents
End With

Tried that with your example just for kicks, got error &H80040E1E which is one over your error of &H80040E1D! So it looks like the PropertyBag is not able to serialize variants containing objects or arrays.

[Elroy]

Just tested, and adIUnknown seems to work just fine too, exactly the same way adVariant works when you put an object into it. RefCount is as expected, and rs.Save method generates an error.

[Schmidt]

Just tested, and adIUnknown seems to work just fine too, exactly the same way adVariant works when you put an object into it. RefCount is as expected, and rs.Save method generates an error.

That's not surprising - always suspected that there's a "Variant-based storage-engine" in play,
when it comes to "low-indexed adType-Enums" (since they match with their VB-counterparts).

adDate = vbDate
adDouble = vbDouble
adCurrency = vbCurrency
adBSTR = vbString
adInteger = vbLong
adIDispatch = vbObject
adIUnknown = vbUnknown
a.s.o.

Olaf