Re: dhSQLite question/problem

"Schmidt" <sss@xxxxxxxxx> wrote in message
news:OEA0fHuAIHA.5868@xxxxxxxxxxxxxxxxxxxxxxx

"Peter T" <peter_t@discussions> schrieb im Newsbeitrag
news:%23BfNT%23qAIHA.3940@xxxxxxxxxxxxxxxxxxxxxxx

Following works fine if no lengths exceed 913, otherwise it errors
(according to RBS) -
Dim rngSizedToArray as Excel.Range
rngSizedToArray.Value = cRS.GetRows(, , , True)

yet this works fine up to 1823, above which no error and lengths are
merely
truncated to 1823.
rngSizedToArray.Value = arr

So why is it that " using 'arr' for storage of this array does help ",
if
effectively in all respects 'arr' is the same as cRS.GetRows, ie both
are
Variant arrays of Variants.

Like I said before, it's only for curiosity :-)

GetRows is implemented, to return an *array*.
The returnvalue is declared "As Variant()".
The difference is, if you Dimension:
Dim arr as Variant
arr = Rs.GetRows(...)
then arr is a Variant containing a
VariantArray - so there's definitely a (small)
difference.
But I have no clue, why XL handles this
differently under the hood, filling its Cell-
Values from it.

Olaf


Olaf,
Yes, I see the "Variant containing a VariantArry" vs "As Variant()"
difference. However if the array is declared As Variant() in VB6/VBA, and
populated, it behaves same as regards filling XL cells, IOW -

Dim varArr(1 to 10, 1 to 2) as Variant
' populate varArr
Dim arr as Variant
arr = Rs.GetRows(...)

Now when it comes to dumping the arrays to cells, both arr and varArr will
'truncate at 1823'

In contrast -
Dim strArr(1 to 10, 1 to 2) as String
' populate strArr
dump strArr to cells and the 'any length exceeds 913 errors'
scenario applies. Doing this step does NOT help -
arr = strArr

So it still puzzles me why the arr = Rs.GetRows(...) step, or Variant =
Variant(), solves the '913 error' problem, or rather improves to the more
acceptable to 'truncate at 1823'. Also bearing in mind a normally populated
Variant() works same as the 'arr' step.

FWIW below is the original VBA test I did to confirm this Excel issue.

Stephan,
I'd be willing to bet that it's as simple as m$ doing
whatever they can do to prevent interoperability.

A good conspiracy theory but I'm sure not in this case. It's a bug that's
been around since at least since Excel97 and probably longer.

Ralph,
It is kind of interesting that 1823 is so close to twice 913, yet not

Yes it does stand out.

Regards,
Peter T


'' There's a small risk of crashing Excel doing the strArr dump
'' with length of 914. Risk increases with longer lengths and older
'' version of Excel. Use of error handler does not reduce risk.
Sub ArrayTest()
Dim i&, j&, k&, rws&, cols&
Dim s1$, sTmp$
Dim strArr() As String
Dim varArr() As Variant
Dim arr As Variant
Dim rng As Range

s = "Some long text. "
Do Until Len(s) > 2000
s = s & s
Loop
Debug.Print "Len(s): " & Len(s)

rws = 5: cols = 2
ReDim strArr(1 To rws, 1 To cols)
ReDim varArr(1 To rws, 1 To cols)

Set rng = Active***.Range("B2") ' VBA
'if using VB6 qualify to XL, eg xlApp.Active***... etc
Set rng = rng.Resize(rws, cols)

i = 900
On Error Resume Next
Do
i = i + 1
sTmp = Left$(s, i)
For j = 1 To rws: For k = 1 To cols
strArr(j, k) = sTmp
Next: Next
rng.Value = strArr
Loop Until Err Or i > 2000

' errors at 913, occasionally at 914
Debug.Print "Len(strArr(1, 1)) " & Len(strArr(1, 1)), _
"Len(rng(1, 1)) " & Len(rng(1, 1))
If Err.Number Then
Debug.Print Err.Number; Err.Description
Err.Clear
Else
Debug.Print "Didn't error"
End If


Set rng = rng.Offset(rws + 2)

For j = 1 To rws: For k = 1 To cols
varArr(j, k) = s
Next: Next

rng.Value = varArr
' truncates to 1823 without error

Debug.Print "Len(varArr(1, 1)) " & Len(varArr(1, 1)), _
"Len(rng(1, 1)) " & Len(rng(1, 1))
If Err.Number Then
Debug.Print Err.Number; Err.Description
Err.Clear
Else
Debug.Print "Didn't error"
End If


sTmp = Left$(s, 1000)
For j = 1 To rws: For k = 1 To cols
strArr(j, k) = sTmp
Next: Next

arr = strArr
Set rng = rng.Offset(rws + 2)
rng.Value = arr

Debug.Print "Len(strArr(1, 1)) " & Len(strArr(1, 1)), _
"Len(rng(1, 1)) " & Len(rng(1, 1))
If Err.Number Then
Debug.Print Err.Number; Err.Description
Err.Clear
Else
Debug.Print "Didn't error"
End If

End Sub

















.