VB SQLite Library (COM-Wrapper)

[xiaoyao]

FYI...
The recent RC5-package comes with SQLite-version 3.28 ... whereas Krools wrapper is still at SQLite-version 3.24.

And no, a call to the deprecated, old sqlite3_get_table would be (much) slower, compared with gathering the table-values via explicit looping.

Here is some test-code with a performance-comparison on "Select * From Invoices" (from SQLite NWind.db):

Option Explicit

Private Declare Function sqlite3_get_table Lib "vb_cairo_sqlite" (ByVal hDB As Long, ByVal SQL As String, lpTable As Long, iRow As Long, iCol As Long, lpErrMsg As Long) As Long
Private Declare Sub sqlite3_free_table Lib "vb_cairo_sqlite" (ByVal lpTable As Long)
Private Declare Function MultiByteToWideChar Lib "kernel32" (ByVal CodePage As Long, ByVal dwFlags As Long, lpMultiByteStr As Any, ByVal cchMultiByte As Long, lpWideCharStr As Any, ByVal cchWideChar As Long) As Long
    
Private Cnn1 As New VBSQLite10.SQLiteConnection, Cnn2 As vbRichClient5.cConnection

Private Sub Form_Load()
  AutoRedraw = True
  Dim Ds As SQLiteDataSet, Rs As cRecordset, T1$
  
  Cnn1.OpenDB "c:\temp\nwind.db"
  Set Cnn2 = New_c.Connection("c:\temp\nwind.db")
  
  New_c.Timing True
    Dim SArr() As String
        SArr = GetTable(Cnn2.DBHdl, "Select * From Invoices")
  Debug.Print "sqlite3_get_table", New_c.Timing
  
  New_c.Timing True
    Set Ds = Cnn1.OpenDataSet("Select * From Invoices")
  Debug.Print "VBSQLite10-Select", New_c.Timing
  
  New_c.Timing True
    Set Rs = Cnn2.OpenRecordset("Select * From Invoices")
  Debug.Print "vbRichClient-Select", New_c.Timing
End Sub
 
Public Function GetTable(hDB As Long, SQL As String) As String()
  Dim pTbl&, Rows&, Cols&, pErr&, i&, j&, PArr&(), SArr$()
 
  sqlite3_get_table hDB, SQL, pTbl, Rows, Cols, pErr
  If pErr = 0 Then
     ReDim PArr(0 To Cols - 1, 0 To Rows)
     ReDim SArr(0 To Cols - 1, 0 To Rows)
     New_c.MemCopy VarPtr(PArr(0, 0)), pTbl, (Rows + 1) * Cols * 4
     For j = 0 To UBound(PArr, 2): For i = 0 To UBound(PArr, 1)
       SArr(i, j) = StringFromPtr(PArr(i, j))
     Next i, j
  End If
  If pTbl Then sqlite3_free_table pTbl
  GetTable = SArr
End Function

Function StringFromPtr(ByVal pUTF8 As Long) As String
  Dim Chars As Long
  If pUTF8 = 0 Then Exit Function
  Chars = MultiByteToWideChar(65001, 0&, ByVal pUTF8, -1, ByVal 0&, 0)
  StringFromPtr = Space$(Chars - 1) 'a VB-BString already contains a trailing Zero, so we allocate it one char less
  MultiByteToWideChar 65001, 0&, ByVal pUTF8, -1, ByVal StrPtr(StringFromPtr), Chars - 1
End Function

The results which are printed to the VB6-Debug-Window are (on my machine):

sqlite3_get_table            74.48msec
VBSQLite10-Select            28.87msec
vbRichClient-Select          9.65msec

@Krool
You have still work to do at the Unicode-front... as e.g. the following SQL-Select-String (no need to create a table first)
"Select 'ü'='Ü' Collate NoCase"
...the above should return a boolean 1 (and not 0, as it does currently in your wrapper)

Also (with regards to dreammanors suggestion)...
It definitely has value (for the User, to write better portable code), to make the interfaces between the COM-wrappers "as compatible as possible" ...
- not only with regards to "avoiding confusion with posted examples here in the forum"
- but also with "less code to rewrite when wrappers are switched" (or a new vbRuntime comes out in a few years).

This "kind of scattering" (different VB6-Usercode, due to different library-interfaces) is exactly what I'm trying to prevent with "keeping the RC5 closed" at the moment (until the new runtime- and compiler are finished).

Your sources are still "fresh" (not much Usercode out there in the wild, yet) - so there's still time to change the Interfaces to a more compatible way.

Remember, that your CommonControls-wrapper-project was succesful for exactly this reason
(being nearly 100% calling-compatible to the pre-existing MS-wrapper of the CommonControls).

Olaf

TEST Need two result: open used times,put to arr(col,rows ) used times.
three method,need six usedtime result.

if only open,need to put all rows,all cols data to arr(col,rows

  Set Ds = Cnn1.OpenDataSet("Select * From Invoices")
  Debug.Print "VBSQLite10-Select", New_c.Timing