Results 1 to 22 of 22

Thread: sqlite3win32

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,511

    sqlite3win32

    This modified sqlite3 c library supports the _stdcall convention which VB needs to call this dll. (like any other win32 API call)

    The initial concept and core of this was developed by 'wqweto'. (vbsqlite)

    The source code of the project can also be viewed on GitHub. (incl. c source in the Lib subfolder)

    Out of this library an Ax-DLL COM-Wrapper was created.

    The attached file sqlite3win32.rar.zip should be renamed to sqlite3win32.rar after download.
    Attached Files Attached Files
    Last edited by Krool; Oct 8th, 2024 at 04:26 AM.

  2. #2
    PowerPoster
    Join Date
    Jan 2020
    Posts
    4,463

    Re: sqlite3win32

    dos it support sqlite3_get_table?, for best,if you can add sqlite3_get_table16,sqlite3_exec16,it's wonderful,thank you

  3. #3

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,511

    Re: sqlite3win32

    Quote Originally Posted by xiaoyao View Post
    dos it support sqlite3_get_table?, for best,if you can add sqlite3_get_table16,sqlite3_exec16,it's wonderful,thank you
    Yes it supports all.

  4. #4
    PowerPoster
    Join Date
    Jan 2020
    Posts
    4,463

    Re: sqlite3win32

    Quote Originally Posted by Krool View Post
    Yes it supports all.
    no,maybe ca't support these api,no unicode api for exec,gettable。

  5. #5

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,511

    Re: sqlite3win32

    Quote Originally Posted by xiaoyao View Post
    no,maybe ca't support these api,no unicode api for exec,gettable。
    Can you stop to claim false statements?
    These API use UTF8. All of sqlite3 is UTF8.
    Those API functions with a suffix 16 are just converters which takes a UTF16 string and pass internally to UTF8 function.

  6. #6
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,415

    Re: sqlite3win32

    deleted..., wrong thread, sorry...

  7. #7
    PowerPoster
    Join Date
    Jan 2020
    Posts
    4,463

    Re: sqlite3win32

    Quote Originally Posted by Krool View Post
    Can you stop to claim false statements?
    These API use UTF8. All of sqlite3 is UTF8.
    Those API functions with a suffix 16 are just converters which takes a UTF16 string and pass internally to UTF8 function.
    Code:
    oices" (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(it's full test get table data )
    VBSQLite10-Select            28.87msec ( need read all data to arrary (rows,cols ))
    vbRichClient-Select          9.65msec ( need read all data to arrary (rows,cols ))
    who can do full test ? ( need read all data to arrary (rows,cols ))
    Last edited by xiaoyao; Jan 30th, 2020 at 01:24 AM.

  8. #8
    PowerPoster
    Join Date
    Jan 2020
    Posts
    4,463

    Re: sqlite3win32

    converters in vb6,This is cumbersome and slowly.
    if converters by vc++ in sqlite.dll ,This is of course the fastest method, and the most convenient and perfect.
    an itA few days ago I took over a project and found that every time I insert data or read data to add fields, all operations need to be converted.
    Writing code is cumbersome and error-prone.
    Just like when we are traveling, in order to save tires, we deliberately call an excavator to put the car on, and then drive to the beach to travel.
    we need enough unicode api,
    At least three functions are needed.Execute the sql sentence to get all the data in the table and get one row of data.
    It would be perfect if you could import or export csv format data.

  9. #9
    PowerPoster
    Join Date
    Jan 2020
    Posts
    4,463

    Re: sqlite3win32

    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-)

    maybe call standard dll,or com object dll also slowly,so If we need to obtain the data of the entire table, it is best to convert it once in the sqlite source code (utf8 to unicode,) and use it directly in vb6.
    chinese: 如果我们需要获取整个表格的数据,最好在sqlite源码里一次性给它转换完成(utf8 to unicode,),vb6直接使用。

  10. #10

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,511

    Re: sqlite3win32

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

  11. #11

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,511

    Re: sqlite3win32

    The sqlite3 c source was upgraded from version 3.31.1 (2020-01-27) to 3.34.1 (2021-01-20).

  12. #12

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,511

    Re: sqlite3win32

    Added a list of all callback functions which are __stdcall in the README on GitHub. (documented for a better reference)
    The list may be expanded in future to improve __stdcall callback support.
    Last edited by Krool; Mar 21st, 2021 at 08:15 AM.

  13. #13

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,511

    Re: sqlite3win32

    The sqlite3 c source was upgraded from version 3.34.1 (2021-01-20) to 3.37.2 (2022-01-06).

  14. #14

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,511

    Re: sqlite3win32

    Better performance due to SQLITE_DEFAULT_CACHE_SIZE -8000. (instead of default -2000)
    Last edited by Krool; Aug 1st, 2022 at 02:48 PM.

  15. #15

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,511

    Re: sqlite3win32

    The sqlite3 c source was upgraded from version 3.37.2 (2022-01-06) to 3.39.2 (2022-07-21).

    Also upgraded the regexp c extension to the latest.

    Important to note also is that all callback method/functions are now __stdcall.
    Prior to this update it was only a few.. But now it is everything. Even the SQL functions or aggregates. (e.g. for overloading)

  16. #16

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,511

    Re: sqlite3win32

    The sqlite3 c source was upgraded from version 3.39.2 (2022-07-21) to 3.39.3 (2022-09-05).

  17. #17

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,511

    Re: sqlite3win32

    The sqlite3 c source was upgraded from version 3.39.3 (2022-09-05) to 3.41.2 (2023-03-22).

  18. #18

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,511

    Re: sqlite3win32

    removed
    Last edited by Krool; Sep 19th, 2023 at 11:53 AM.

  19. #19
    PowerPoster
    Join Date
    Jan 2020
    Posts
    4,463

    Re: sqlite3win32

    what's sqlite3win32helper.cobj? 1.7MB?
    sqlite3win32stubs.cobj?

  20. #20

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,511

    Re: sqlite3win32

    The sqlite3 c source was upgraded from version 3.41.2 (2023-03-22) to 3.43.2 (2023-10-10).

  21. #21

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,511

    Re: sqlite3win32

    The sqlite3 c source was upgraded to 3.45.3 (2024-04-15).

  22. #22

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,511

    Re: sqlite3win32

    The sqlite3 c source was upgraded to 3.46.1 (2024-08-13).

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