Re: What to use for ADO parameter type and size

This is my function that returns the 2-D variant array:

Function GetFromDB(strSQL As String, _
Optional lReturnedRows As Long, _
Optional strError As String, _
Optional strDB As String, _
Optional bLogSQL As Boolean, _
Optional bIncludeHeaders As Boolean) As Variant

Dim cRS As cRecordset
Dim arr

10 On Error GoTo ERROROUT

20 OpenDB strDB

30 If bLogSQL Then
40 ShowStatement strSQL, , , 2, True, True, strDB
50 End If

60 Set cRS = Cnn.OpenRecordset(strSQL, True)

70 If cRS.EOF Then
80 lReturnedRows = -1
90 strError = Cnn.LastDBError
100 GetFromDB = strError
110 Else
120 If bIncludeHeaders Then
130 arr = cRS.GetRowsWithHeaders(, , , True)
140 Else
150 arr = cRS.GetRows(, , , True)
160 End If
170 lReturnedRows = cRS.RecordCount
180 GetFromDB = arr
190 End If

200 Exit Function
ERROROUT:

210 lReturnedRows = -1
220 strError = Cnn.LastDBError
230 GetFromDB = strError

240 MsgBoxDLL Err.Description & vbCrLf & vbCrLf & strSQLPublic, _
"GetFromDB error at line " & Erl, _
, , , , , , uCLR.lClrMainForm

End Function


This works with the SQLite wrapper dll dhSQLite written by Olaf Schmidt.
As far as I can see arr2(i,0) does not refer to a variant array but to a single value.


I am measuring from the start of the loop (so first line before the loop) to the end of the loop
(so first line after the loop) and the figures are in the region of 695 milliseconds versus 650 milliseconds.
I measure with the TimeGetTime API and my measurements are very consistent.


RBS


"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message news:uiDYrTG0HHA.2312@xxxxxxxxxxxxxxxxxxxxxxx
RB Smissaert wrote:
Set rs = .Execute( ,Array(arr2(i, 0)))

This does work, but is slightly slower than doing:

Set rs = .Execute( ,arr2(i, 0))

What is the reason for doing Array(arr2(i, 0)) ?



A single-dimensioned variant array containing the parameter value(s) is required here. The 2-dimensional array you have here should be causing a type mismatch ... unless the element referenced by arr2(i,0) contains a variant array. In my experience, a type mismatch is caused by this:

Set rs = .Execute( ,5)

To make it work, I've had to do this:

Set rs = .Execute( ,Array(5))

I cannot believe you are seeing a measurable performance difference here. It really makes me question exactly what you are measuring and how you are measuring it. In absolute numbers, exactly what timings are you seeing?

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.