Page 1 of 6 1234 ... LastLast
Results 1 to 40 of 220

Thread: VB SQLite Library (COM-Wrapper)

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,375

    VB SQLite Library (COM-Wrapper)

    This Ax-DLL project is intended to get a VB-friendly COM-Wrapper for the SQLite library, which is based on the sqlite3win32 __stdcall compilation.

    Ax-DLL version: 1.2.21
    SQLite version: 3.44.2

    A Std-EXE demo project is also included to show how to use the Ax-DLL.

    The interaction of the various components are very simplistic:
    Name:  VBSQLite.jpg
Views: 1961
Size:  15.7 KB

    There is only one public creatable component, the SQLiteConnection object.

    The SQLiteDataSet object is read-only and meaningful for SELECT statements only.
    INSERT, UPDATE and DELETE statements shall be done via the 'Execute' methods of the SQLiteConnection or SQLiteCommand object.

    The SQLiteCommand object is just a helper. All can be done without it.
    However, for recurring tasks it might perform better and can also be convient for parametrized SQL statements.

    The source code of the project can also be viewed on GitHub.

    List of revisions:
    Code:
    24-Jan-2024
    - Upgrade sqlite3 c source to version 3.44.2 (2023-11-24).
    08-Nov-2023
    - Removed the stmt_readonly check for .OpenDataSet and .CreateCursor.
      This allows for example to use a PRAGMA statement and read the result row.
    11-Oct-2023
    - Upgrade sqlite3 c source to version 3.43.2 (2023-10-10).
    08-Jul-2023
    - SQLiteCursor class is not auto-initialized anymore with sqlite3_step.
      Use .MoveFirst to ensure it is initialized, otherwise an error will occur.
    02-Jul-2023
    - Upgrade sqlite3 c source to version 3.41.2 (2023-03-22).
    26-Sep-2022
    - Upgrade sqlite3 c source to version 3.39.3 (2022-09-05).
    10-Aug-2022
    - Upgrade sqlite3 c source to version 3.39.2 (2022-07-21).
    - Upgrade of the regexp c extension.
    - Added compile-time option SQLITE_OMIT_AUTOINIT 1.
    03-Aug-2022
    - Usage of sqlite3_prepare16_v2/v3 instead of sqlite3_prepare_v2/v3, if applicable.
    01-Aug-2022
    - Better performance due to sqlite3 c source compiled with SQLITE_DEFAULT_CACHE_SIZE -8000. (instead of default -2000)
    - Usage of sqlite3_bind_text16 instead of sqlite3_bind_text.
    28-Jul-2022
    - Included the binding methods/properties in the SQLiteCursor class.
      This is meaningful only when created from a SQLiteConnection class, because the query will be expanded when created from an SQLiteCommand class.
      Using SetParameterValue or ClearParameters will move to the first row automatically.
    27-Jul-2022
    - Included the SQLiteCursor class which can be created from a SQLiteConnection or SQLiteCommand class.
      This is useful for very large tables in which data can be retrieved without memory problems. The cursor is treated here as an iterator.
    02-Mar-2022
    - Upgrade sqlite3 c source to version 3.37.2 (2022-01-06).
    19-Apr-2021
    - Included the SetProgressHandler method which registers/unregisters a progress handler callback.
      For this the new ISQLiteProgressHandler class needs to be implemented on the receiver.
    - Included the BackupDB method which backups (copies) a SQLite database between two SQLite database connections.
    - Included the SharedCache parameter in the OpenDB method.
    - Included the LastInsertRowID property in the SQLiteConnection class.
    - Included the AutoCommit (read-only) property in the SQLiteConnection class.
    30-Mar-2021
    - CONCAT() and CONCAT_WS() function included by statically linking to the concat c extension.
    27-Mar-2021
    - OpenDB method now uses the flags SQLITE_OPEN_FULLMUTEX to enable serialized threading mode.
      Also changed the compile-time option SQLITE_THREADSAFE from 2 to 1. (recommended default setting)
      This means multiple threads can safely use the same database connection.
      When each thread uses an own connection then the threading mode is as multi-thread. (equivalent as if SQLITE_OPEN_NOMUTEX or SQLITE_THREADSAFE=2)
    26-Mar-2021
    - REGEXP operator included by statically linking to the regexp c extension.
    19-Mar-2021
    - Upgrade sqlite3 c source to version 3.34.1 (2021-01-20).
    - Optimized SQLiteDataSet as ReDim Preserve is called less times (array bump of 10 is defined) to improve performance.
    29-Apr-2020
    - Bugfix in SQLiteCommand for the SetParameterValue method.
      The VarType was not correctly when passing VT_DISPATCH that has a DISPID_VALUE.
    04-Apr-2020
    - Upgrade sqlite3 c source to version 3.31.1 (2020-01-27).
    05-Feb-2020
    - Unicode support for NOCASE collating sequence.
    - Unicode support for LIKE(), UPPER(), and LOWER() functions.
    24-Jan-2020
    - First release.
    The source code of "VBSQLite12SideBySide.res" for Registration-Free (Side-by-side) is:

    Code:
    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0">
       <file name="VBSQLite12.DLL">
          <typelib tlbid="{7CC1A5F1-A0FF-4546-A0F1-FBFE744A4522}" version="1.1" flags="" helpdir="" />
          <comClass clsid="{1DE49E39-B9D4-4CDF-95AA-098B89F11318}" tlbid="{7CC1A5F1-A0FF-4546-A0F1-FBFE744A4522}" threadingModel="Apartment" progid="VBSQLite12.SQLiteConnection" />
       </file>
    </assembly>
    The attached file VBSQLite12.zip (Src) and sqlite3win32 (Bin) needs to be extracted in a same root folder.
    sqlite3win32 is only needed for compiling the Ax-DLL. The compiled Ax-DLL doesn't have any dependency, because sqlite3win32 was then compiled into it.
    Attached Files Attached Files
    Last edited by Krool; Jan 24th, 2024 at 08:06 AM.

  2. #2
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: VB SQLite Library (COM-Wrapper)

    can it support sqlite_get_table?

  3. #3
    Hyperactive Member
    Join Date
    Jun 2016
    Location
    EspaƱa
    Posts
    506

    Re: VB SQLite Library (COM-Wrapper)

    on compiler get
    LINK : fatal error LNK1181: cannot open input file "KERNEL32.LIB"

    good work

  4. #4
    Lively Member
    Join Date
    Sep 2016
    Posts
    94

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by yokesee View Post
    on compiler get
    LINK : fatal error LNK1181: cannot open input file "KERNEL32.LIB"

    good work
    same problem

  5. #5

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,375

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by yokesee View Post
    on compiler get
    LINK : fatal error LNK1181: cannot open input file "KERNEL32.LIB"
    Good point.

    It's because you only have Visual Studio 6.0 installed without C++.
    The VB6 compiler resorts by "KERNEL32.LIB" to some files in /vc98/lib.
    You can get it to work to copy paste that folder from another installation. So it's not really needed to have C++ 6.0 installed if you can get the folder somewhere else.

    EDIT:
    when not having C++ 6.0 installed it is needed to make also manually an environment variable under "Advanced system settings".
    Variable = "lib"
    Value = "C:\Program Files (x86)\Microsoft Visual Studio\VC98\mfc\lib" and "C:\Program Files (x86)\Microsoft Visual Studio\VC98\lib"

    Quote Originally Posted by xiaoyao View Post
    can it support sqlite_get_table?
    Why? It's a COM Wrapper so not need to fiddle with sqlite API's for the developer. Also sqlite_get_table is not used because it's deprecated and there are more efficient API functions to get the same result internally.
    Last edited by Krool; Jan 31st, 2020 at 06:31 AM.

  6. #6
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Krool View Post
    Good point.

    It's because you only have Visual Studio 6.0 installed without C++.
    The VB6 compiler resorts by "KERNEL32.LIB" to some files in /vc98/lib.
    You can get it to work to copy paste that folder from another installation. So it's not really needed to have C++ 6.0 installed if you can get the folder somewhere else.


    Why? It's a COM Wrapper so not need to fiddle with sqlite API's for the developer. Also sqlite_get_table is not used because it's deprecated and there are more efficient API functions to get the same result internally.
    i think gettable is the quick method,you can test:
    put all select result to :dim data(rows,cols),how much time need?
    1,sqlite3-get_table
    2,com object,
    3,ado drivers

    We need a quick way to read all the data。
    also need a way to export csv format,or import csv file.

  7. #7

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,375

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by xiaoyao View Post
    i think gettable is the quick method,you can test:
    put all select result to :dim data(rows,cols),how much time need?
    1,sqlite3-get_table
    2,com object,
    3,ado drivers

    We need a quick way to read all the data。
    also need a way to export csv format,or import csv file.
    Quote from sqlite3_get_table:
    This is a legacy interface that is preserved for backwards compatibility. Use of this interface is not recommended.
    sqlite3-get_table comes from older sqlite2 where all was stored as text.
    You can use sqlite3-get_table in the sqlite3win32.dll but NOT in the COM-Wrapper.
    You can test which option (1, 2 or 3) is the best for you and decide.
    Last edited by Krool; Jan 27th, 2020 at 12:09 AM.

  8. #8
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: VB SQLite Library (COM-Wrapper)

    Hi Krool

    Could you strip out the binaries from the attachments, please? They're only allowed in the utilities section, not code bank.

    Thanks
    FD
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  9. #9
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: VB SQLite Library (COM-Wrapper)

    more times ,we need quickly read alldatas(all row ,all cols),how can i read one row or readall?

  10. #10
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: VB SQLite Library (COM-Wrapper)

    Hi Krool, I took a preliminary look at your Sqlite COM-Wrapper. If you could wrap the Sqlite Library by simulating/referring to the SQLite objects (interfaces) in vbRichClient5, then the usability of your Sqlite COM-Wrapper will be greatly improved.

  11. #11
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by xiaoyao View Post
    more times ,we need quickly read alldatas(all row ,all cols),how can i read one row or readall?
    Hi xiaoyao, have you tried SQLite wrapped in vbRichClient5?

  12. #12

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,375

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by FunkyDexter View Post
    Could you strip out the binaries from the attachments, please? They're only allowed in the utilities section, not code bank.
    Done. sqlite3win32 (Bin) is now in the UtilityBank - Components.

    Quote Originally Posted by dreammanor View Post
    I took a preliminary look at your Sqlite COM-Wrapper. If you could wrap the Sqlite Library by simulating/referring to the SQLite objects (interfaces) in vbRichClient5, then the usability of your Sqlite COM-Wrapper will be greatly improved.
    It is NOT intended to replace ADO/vbrichclient5.

    The intention of this is to have an open-source simplistic sqlite3 COM-Wrapper with it's own interface structure, which is aligned to the internal structure of sqlite3.

    With this wrapper you can do all what sqlite3 offers. (memory-db, multiple attached db's etc.)
    What vbrichclient5 offers in addition what I do not support is in-built encryption.

    Of course this implies that this COM-Wrapper may be meaningful only for projects from scratch and is not something to quickly replace.
    Last edited by Krool; Jan 27th, 2020 at 01:41 PM.

  13. #13
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by dreammanor View Post
    Hi xiaoyao, have you tried SQLite wrapped in vbRichClient5?
    before some days,i use sqlite3.dll,by cdecl method.
    if use com method sqlite,the same question:we need quickly read all rows,all cols,data.
    quickly for millions.

  14. #14
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by xiaoyao View Post
    before some days,i use sqlite3.dll,by cdecl method.
    if use com method sqlite,the same question:we need quickly read all rows,all cols,data.
    quickly for millions.
    If you use vbRichClient5(which provides an excellent wrapper for Sqlite), then you can solve any of the problems you mentioned. vbRichClient5 is the best VB6 auxiliary development tool (platform or framework) on our planet. It's free, but not open source. I've used it for more than 10 years, and there are people in this forum (such as jpbro) who have used it for more than 15 years. Moreover, its author (Olaf Schmidt) has been active on this forum.

  15. #15
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: VB SQLite Library (COM-Wrapper)

    vbRichClient5 is big,and more time maybe use old version sqlite。
    if you know vc++,can easy chang source code,add(save out for csv or read all data like: dim data(rows,cols))
    sqlite3.dll is very good ,need add api :exec16(for unicode),gettable16.one month ago,i use sqlite3.exe,by shell command,insert dada,create new table。
    if table name or fields name is chinese,or insert new data with chinese words,so it's error。
    because sqlite3.exe or sqlite3_exec(*) not support chinese(unicode)

    if use com dll(activex dll),it's simple for use,but it will be run slowly

  16. #16
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: VB SQLite Library (COM-Wrapper)

    in vb code,i test:
    for i=1 to 1000000000
    a=1
    next
    it's used time:1.5ms
    if use activex dll,(activex.exe)
    dim obj as object
    set obj=createobject("*")
    for i=1 to 1000000000
    obj.a=1
    next
    or

    with obj
    for i=1 to 1000000000
    .a=1
    next
    end with

    it's used time:13000000 ms

    it's very slowly in com object method or put value,or read dada.

  17. #17
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: VB SQLite Library (COM-Wrapper)

    why ie is slowly,chrome is quickly,maybe com object is not good,it's bad chose for run quickly,only it's simple use for developers

  18. #18
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: VB SQLite Library (COM-Wrapper)

    Hi xiaoyao, maybe we have a different understanding about speed. Could you give a specific example of how you operate the sqliteDB? Maybe someone would give you an interesting answer.
    Last edited by dreammanor; Jan 29th, 2020 at 03:31 AM.

  19. #19

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,375

    Re: VB SQLite Library (COM-Wrapper)

    xiaoyao,
    It's very difficult to follow you. Also your understanding about sqlite3 seems to be incomplete.
    1. all in sqlite3 is UTF8 (= Unicode, all natural languages covered)
    2. Do you make a "BEGIN TRANSACTION" before your loop and a "END TRANSACTION" after your loop?

  20. #20
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Krool View Post
    xiaoyao,
    It's very difficult to follow you. Also your understanding about sqlite3 seems to be incomplete.
    1. all in sqlite3 is UTF8 (= Unicode, all natural languages covered)
    2. Do you make a "BEGIN TRANSACTION" before your loop and a "END TRANSACTION" after your loop?
    here is my cpu speed test , for call method by:activex.dll or stand dll:
    Running speed test for a=? or activex.dll(class1.a=?)-VBForums
    http://www.vbforums.com/showthread.p...65#post5448365

  21. #21
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by xiaoyao View Post
    vbRichClient5 is big,and more time maybe use old version sqlite。
    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):
    Code:
    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):
    Code:
    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

  22. #22
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Schmidt View Post
    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):
    Code:
    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):
    Code:
    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
    Code:
      Set Ds = Cnn1.OpenDataSet("Select * From Invoices")
      Debug.Print "VBSQLite10-Select", New_c.Timing
    Last edited by xiaoyao; Jan 30th, 2020 at 01:12 AM.

  23. #23
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,996

    Re: VB SQLite Library (COM-Wrapper)

    I would like to see a DAO compatible version, but I know that most of the people use ADO and probably an ADO compatible version would be more popular.

  24. #24
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: VB SQLite Library (COM-Wrapper)

    Yes, SqliteDB of vbRichClient5 is an ADO compatible version.

  25. #25
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by dreammanor View Post
    Yes, SqliteDB of vbRichClient5 is an ADO compatible version.
    IF LIKE ADO AND NOT ADO DRIVERS, IT'S EASY FOR study.
    Code:
    dim a(rows,cols)
    a(0,0)=rs("filedname1")
    a(0,1)=rs("filedname2")
    
    or 
    a(0,0)=rs(0)
    a(0,1)=rs(1)
    rs.movenext
    a(1,0)=rs(0)
    a(1,1)=rs(1)
    
    for best
    we need a api
    a=gettableArr()
    msgbox a(0,0)
    msgbox a(0,1)
    msgbox a(1,0) & "," & a(1,1)
    
    also,we can make new standll api:
    getcsvdata():
    data is string:
    id,name,age
    1,"tom",16
    2, "lucy",17
    After all, the running speed of VB6 is relatively slow. If you can export the results directly with VC and package them well, it will be most convenient for you to get the results directly from VB6

    CHINESE:如果想做的完美,这些都需要,毕竟VB6的运行速度比较慢,如果可以用VC把结果直接导出来,包装好,VB6直接得到结果,肯定是最方便的

  26. #26
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: VB SQLite Library (COM-Wrapper)

    If you don’t like VB6 then use something else.
    All your replies in all threads are all about things which can be done more easily or faster in other languages.
    Please use that programming language and stop complaining and demanding faster solutions in vb6

  27. #27
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: VB SQLite Library (COM-Wrapper)

    My meaning is,change stand stdcall dll or com sqlite sources code,add some method like import csv file or ouput to csv .
    you can testing:for get all cols ,all rows data ,need how much times ,by three way
    in ado txf,csv,xls,xml all support.
    Ofcourse we need these,sqlite.dll maybe only like .net core ,give not enough FeaturesFeatures. .

  28. #28
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: VB SQLite Library (COM-Wrapper)

    Implement them yourself, the sources are freely available

  29. #29
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by xiaoyao View Post
    TEST Need two result: open used times,put to arr(col,rows ) used times.
    three method,need six usedtime result.
    No, the test is perfectly fine as it is.

    Since all the 3 different methods are stopping the timing exactly at a point,
    when the db-table-data becomes available as a copy, in a container-structure, which is usable-in-VB6.
    (e.g. the RC5-Recordset-ResultContainer is directly bindable to a VB6-DataGrid, which makes the "total time until visualizing Invoices" 11-12msec or so).

    But FYI, the sqlite3_get_table call is the slowest also in case I leave the returned C-UTF8-Pointers "just sitting there"
    (without the copy-over-loop into a VB6-StringArray)... the timing was then reduced to 37msec or so.

    Olaf

  30. #30
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: VB SQLite Library (COM-Wrapper)

    vc++,I do n’t use these. I need a little technology. If you understand, you can easily modify it.

    I test call sub or put value ,com activex dll is slowly (It takes more then 10 ~ 30 times(200%-400%).)
    If you need to obtain a whole row of data, or all the data of the entire table, if this table has 10,000 rows and 30 columns, you need to call the API at least 1 million times, which is not only cumbersome but also very slow.
    chinese: 我不会用vc++编程,需要一点技术,如果懂的人可以很轻松的给他修改,希望能够有高手花点时间,造福大家,非常感谢。
    如果需要获取一整行的数据,或者获取整个表格的所有数据,假如这个表格有一万行,30列,那需要调用API至少100万次,这样不仅很麻烦,而且速度很慢。
    for i=1 to 1000000000
    a=i or class1.a =i,activex.dll(class1.a or call method)
    next
    BasFile_Public_A=I used: 16.727 MS
    ClassA.A =125.8725 MS
    ActiveXDll.Class2.A =125.5622 MS
    ClassA.MethedSetB( sub) =352.0073 MS

    【download】my test page url:http://www.vbforums.com/showthread.p...dll(class1-a-)

  31. #31
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,996

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by xiaoyao View Post
    My meaning is,change stand stdcall dll or com sqlite sources code,add some method like import csv file or ouput to csv .
    you can testing:for get all cols ,all rows data ,need how much times ,by three way
    in ado txf,csv,xls,xml all support.
    Ofcourse we need these,sqlite.dll maybe only like .net core ,give not enough FeaturesFeatures. .
    I ask you to make an effort and try to write in English that other people can understand. Your posts look like jargon.
    You can use Google translator for the texts.
    Thank you.

  32. #32
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by xiaoyao View Post
    ...for get all cols ,all rows data ,need how much times ,by three way
    in ado txf,csv,xls,xml all support.
    Now you're talking about "serialization" (e.g. when you want to transfer Query-Results over sockets, from behind a WebServer).

    A common (modern and standardized) format for that (which e.g. Browsers prefer),
    is JSON in the meantime (XML less so, and CSV even less).
    Code:
      New_c.Timing True
        Set Rs = Cnn2.OpenRecordset("Select * From Invoices")
        Dim JSONUTF8() As Byte
            JSONUTF8 = Rs.ToJSONUTF8
      Debug.Print "vbRichClient-Select", New_c.Timing
    E.g. the above code does that - and the total time (until the Data is serialized to JSON) is just 15msec total.

    And if you don't have "Browser-Clients" (which prefer JSON), but a VB6-Client instead -
    one can also use "the native serialization" of the ResultSet-Containers (which allows to rebuild the entire Rs at the Clientside "as it was" at the server).
    Code:
      New_c.Timing True
        Set Rs = Cnn2.OpenRecordset("Select * From Invoices")
        Dim Content() As Byte
            Content = Rs.Content
      Debug.Print "vbRichClient-Select", New_c.Timing
    This is a bit faster than the JSON-serialization, with the total time now about 12msec.

    Olaf

  33. #33

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,375

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Schmidt View Post
    @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)
    Thanks for catching the blind spot. I was not aware of this and actually shocked that this does not work "out-of-the-box".
    I will look to include the ICU extension soon into sqlite3 to solve this. Thanks again .

  34. #34

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,375

    Re: VB SQLite Library (COM-Wrapper)

    I don't get the ICU extension to work..

    Maybe it's a better idea to overload the built-in NOCASE collating sequence (using sqlite3_create_collation()) and the built-in LIKE(), UPPER(), and LOWER() functions (using sqlite3_create_function()) and let VB6 private functions do the Unicode handling for all these?

  35. #35
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Krool View Post
    I don't get the ICU extension to work..

    Maybe it's a better idea to overload the built-in NOCASE collating sequence (using sqlite3_create_collation()) and the built-in LIKE(), UPPER(), and LOWER() functions (using sqlite3_create_function()) and let VB6 private functions do the Unicode handling for all these?
    Yep, that's what I did in the RC5-wrapper (ICU is quite large).

    Olaf

  36. #36

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,375

    Re: VB SQLite Library (COM-Wrapper)

    Important update released.

    The NOCASE collating sequence and the LIKE(), UPPER(), and LOWER() functions now support Unicode.

  37. #37

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,375

    Re: VB SQLite Library (COM-Wrapper)

    Update released.

    The sqlite3 c source was upgraded from version 3.24.0 (2018-06-04) to 3.31.1 (2020-01-27).

    New introduced compile-option SQLITE_DQS (=0) was defined to disable the double-quoted string literal misfeature. (recommended according to sqlite.org)

    EDIT: Also compile-option SQLITE_OMIT_DEPRECATED was added to reduce exported functions.
    Last edited by Krool; Apr 4th, 2020 at 01:13 PM.

  38. #38
    Hyperactive Member
    Join Date
    Jan 2015
    Posts
    323

    Re: VB SQLite Library (COM-Wrapper)

    i m a little curious, does this library support password encryption sqlite database?
    does vbRichClient5 support it?

  39. #39
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by loquat View Post
    i m a little curious, does this library support password encryption sqlite database?
    does vbRichClient5 support it?
    No and Yes...

    Olaf

  40. #40
    Addicted Member
    Join Date
    May 2016
    Location
    China
    Posts
    197

    Re: VB SQLite Library (COM-Wrapper)

    Excuse me, does this support browsing. SQLite files?
    QQ: 289778005

Page 1 of 6 1234 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width