Page 2 of 6 FirstFirst 12345 ... LastLast
Results 41 to 80 of 220

Thread: VB SQLite Library (COM-Wrapper)

  1. #41
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by ChenLin View Post
    Excuse me, does this support browsing. SQLite files?
    Both libs can do that ... although your word-choice of "browsing",
    seems to suggest, that you think about "read-only-access"...

    SQLite is a "full DB-Engine in a Dll" (supporting much more than "just browsing a file"):
    https://www.sqlite.org/transactional.html

    Olaf

  2. #42
    Hyperactive Member
    Join Date
    Jan 2015
    Posts
    323

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Schmidt View Post
    No and Yes...

    Olaf
    yes already found that

  3. #43

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    Re: VB SQLite Library (COM-Wrapper)

    Update released.

    Bugfix in SQLiteCommand for the SetParameterValue method.
    The VarType was not correctly when passing VT_DISPATCH that has a DISPID_VALUE.

    Simple scenario where this can be a problem is when passing a DataSet column as parameter for another command.
    Code:
    Command.SetParameterValue 100, DS![My Column]
    Problem was that I previously extracted the VarType with CopyMemory (2 bytes).
    That was needed to check for VT_BYREF necessary for BLOB, byte streams.

    However, VBA.VarType() checks deeper when passing a vbObject (VT_DISPATCH) for a DISPID_VALUE.
    Thus I switched over to VBA.VarType() instead of CopyMemory.
    So, in worst case (BLOB, byte stream) the VarType is double checked, once with VBA.VarType() and then again with CopyMemory (2 bytes)..

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

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Schmidt View Post
    Both libs can do that ... although your word-choice of "browsing",
    seems to suggest, that you think about "read-only-access"...

    SQLite is a "full DB-Engine in a Dll" (supporting much more than "just browsing a file"):
    https://www.sqlite.org/transactional.html

    Olaf
    Thank you for your reply. What I want to express is that after opening the .sqlite file, you can list all the tables in it and then select one of them to open.
    Last edited by ChenLin; Apr 29th, 2020 at 07:20 PM. Reason: I have found a way, I will upload the demo later.
    QQ: 289778005

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

    Re: VB SQLite Library (COM-Wrapper)

    Name:  tt.png
Views: 1887
Size:  21.1 KB


    Since the attachment cannot be uploaded all the time, just send the relevant code.

    Code:
    Private Sub CommandConnect_Click()
    If DBConnection Is Nothing Then
        With New SQLiteConnection
        On Error Resume Next
        Dim s As String
        s = AppPath() & "\data.sqlite" 'Data to be tested
        .OpenDB s, SQLiteReadWrite
        If Err.Number <> 0 Then
            Err.Clear
            If MsgBox("Test.db does not exist. Create new?", vbExclamation + vbOKCancel) <> vbCancel Then
                .OpenDB s, SQLiteReadWriteCreate
                .Execute "CREATE TABLE test_table (ID INTEGER PRIMARY KEY, szText TEXT)"
            End If
        End If
        On Error GoTo 0
        If .hDB <> 0 Then
            Set DBConnection = .Object
            CommandInsert.Enabled = True
            List1.Enabled = True
            ListAllTable
        End If
        End With
    Else
        MsgBox "Already connected.", vbExclamation
    End If
    End Sub
    
    Private Sub Requery(tlbName As String)
        On Error GoTo CATCH_EXCEPTION
        List1.Clear
        Dim DataSet As SQLiteDataSet
        Set DataSet = DBConnection.OpenDataSet("SELECT * FROM " & tlbName & " limit 0,100")
        DataSet.MoveFirst
        Dim I As Integer, j As Long, k As Long
        LynxGrid1.ClearAll
        Dim ss As String
        For I = 1 To DataSet.Columns.Count
            LynxGrid1.AddColumn DataSet.Columns(I).Name, 1000
        Next I
        DataSet.MoveFirst
        For j = 1 To DataSet.RecordCount - 1
            For k = 1 To DataSet.Columns.Count
                ss = ss & vbTab & DataSet.Columns(k).Value
            Next k
            LynxGrid1.AddItem Replace(ss, vbTab, "", 1, 1)
            DataSet.MoveNext
        Next j
        LynxGrid1.Redraw = True
        LynxGrid1.Refresh
    Exit Sub
    CATCH_EXCEPTION:
        MsgBox Err.Description, vbCritical + vbOKOnly
    End Sub
    
    Private Sub ListAllTable()
        On Error GoTo CATCH_EXCEPTION
        List2.Clear
        Dim DataSet As SQLiteDataSet
        Set DataSet = DBConnection.OpenDataSet("select name from sqlite_master where type='table' order by name")
        DataSet.MoveFirst
        Dim I As Integer
        For I = 0 To DataSet.RecordCount - 1
            List2.AddItem DataSet.Columns(1)
            DataSet.MoveNext
        Next I
    Exit Sub
    CATCH_EXCEPTION:
        MsgBox Err.Description, vbCritical + vbOKOnly
    End Sub
    Last edited by ChenLin; Apr 29th, 2020 at 08:15 PM. Reason: Attachment cannot be uploaded
    QQ: 289778005

  6. #46

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    Re: VB SQLite Library (COM-Wrapper)

    Update released.

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

    Biggest benefit for new sqlite3 version (IMO) is the support for UPDATE-FROM sql statements. (Possible since version 3.33)

    A "corner case" bugfix of version 3.35.0 got included in this build. (Incorrect optimization of IN operator)

    Function sqlite3WhereCodeOneLoopStart:
    Code:
        if( iLevel>0 && (pLoop->wsFlags & WHERE_IN_SEEKSCAN)!=0 ){
          /* In case OP_SeekScan is used, ensure that the index cursor does not
          ** point to a valid row for the first iteration of this loop. */
          sqlite3VdbeAddOp1(v, OP_NullRow, iIdxCur);
        }
    Also optimized SQLiteDataSet to improve performance for SELECT statements.
    The costly 'ReDim Preserve' is called less times now. (array bump of 10 is defined)
    Previously there was no "array bump". Means by each row a 'ReDim Preserve' was applied.
    Last edited by Krool; Mar 19th, 2021 at 12:01 PM.

  7. #47

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    Re: VB SQLite Library (COM-Wrapper)

    Update released.

    The REGEXP operator is now included by statically linking to the regexp c extension.

    The RE syntax recognized by regexp.c (from sqlite.org) is a subset of the quite large PCRE.

    The REGEXP operator is case-sensititive. To make it work as case-insensitive just do..
    Code:
    lower(B) regexp A
    where A is the pattern and B the test string. Of course all letters in the pattern A must be lower case also.

  8. #48
    Hyperactive Member
    Join Date
    Jun 2016
    Location
    España
    Posts
    506

    Re: VB SQLite Library (COM-Wrapper)

    Very good project.
    How Regexp works.
    In the inputbox I write "regexp A" or "Lower (B)" but only the text is written

    regards

  9. #49
    Hyperactive Member
    Join Date
    Jan 2015
    Posts
    323

    Re: VB SQLite Library (COM-Wrapper)

    seems use the trick's vbCdeclFix, we can use original sqlite3.dll

  10. #50

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    Re: VB SQLite Library (COM-Wrapper)

    Minor convenience update.

    The CONCAT() and CONCAT_WS() function are now included by statically linking to the concat c extension.

    The CONCAT() function does certainly the same as the concatenation operator || to concatenate strings into one.
    The two examples below will produce the same output 'SQLite concat'.
    Code:
    SELECT 'SQLite ' || 'concat';
    SELECT concat('SQLite ', 'concat');
    The main "advantage" of the CONCAT() function is when referring to field names and any NULL value is contained.
    Unlike the concatenation operator ||, the CONCAT() function ignores NULL arguments.

    The CONCAT_WS() function concatenates strings into one separated by a particular separator. (ws stands for with separator)
    So..
    Code:
    SELECT CONCAT_WS(', ', 'SQLite', 'concat');
    will output to 'SQLite, concat'.

    Also here NULL values will be ignored. Even if the seperator value is NULL.

    When CONCAT() has no args the return value is NULL.
    When CONCAT_WS() has only 1 or no arg the return value is NULL.

    PS: SQLite has already in-built GROUP_CONCAT() aggregate function so no change there.

  11. #51

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    Re: VB SQLite Library (COM-Wrapper)

    New Ax-DLL version 1.1 with below additions:

    Included the SetProgressHandler method which registers/unregisters a progress handler callback.
    For this the new ISQLiteProgressHandler class needs to be implemented on the receiver.
    This is useful to keep a GUI "alive".
    Code:
    SetProgressHandler(ByVal Handler As ISQLiteProgressHandler, [ByVal VMInstructions As Long = 100])
    Code:
    Private Sub ISQLiteProgressHandler_Callback(Cancel As Boolean)
    ' The SetProgressHandler method (which registers this callback) has a default value of 100 for the
    ' number of virtual machine instructions that are evaluated between successive invocations of this callback.
    ' This means that this callback is never invoked for very short running SQL statements.
    ' An example use case for this handler is to keep the GUI updated and responsive.
    ' The operation will be interrupted if the cancel parameter is set to true.
    ' This can be used to implement a "cancel" button on a GUI progress dialog box.
    DoEvents
    End Sub
    To unregister the progress handler callback set the Handler to Nothing.
    Code:
    DBConnection.SetProgressHandler Nothing
    Included the BackupDB method which backups (copies) a SQLite database between two SQLite database connections.
    Code:
    BackupDB(ByVal Destination As SQLiteConnection, [ByVal DestinationDBName As String = "main"], [ByVal SourceDBName As String = "main"])
    Included the SharedCache parameter in the OpenDB method. (optional)
    Included the LastInsertRowID property in the SQLiteConnection class.
    Included the AutoCommit (read-only) property in the SQLiteConnection class.
    Last edited by Krool; Apr 28th, 2021 at 02:26 AM.

  12. #52
    Fanatic Member
    Join Date
    Apr 2015
    Location
    Finland
    Posts
    679

    Re: VB SQLite Library (COM-Wrapper)

    LIKE search somewhat broken in latest 3.34.1 sqlite3win32.dll / SQLite11.dll version?

    Code:
        Dim DataSet As SQLiteDataSet
        'Description field (typename) nvarchar(250)
        'Set DataSet = PartsDBConnection.OpenDataSet("SELECT Description FROM Info WHERE Description ='192465' LIMIT 1;") 'Works ok in 3.34.1  -> returns numrows = 1
        Set DataSet = PartsDBConnection.OpenDataSet("SELECT Description, FROM Info WHERE Description LIKE '%192465%' LIMIT 1;") 'No worky in 3.34.1  -> returns numrows = 0 
        DataSet.MoveFirst
        numrows = DataSet.RecordCount
    What am i missing?
    Attached Images Attached Images  
    Last edited by Tech99; Apr 20th, 2021 at 10:20 PM.

  13. #53

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Tech99 View Post
    LIKE search somewhat broken in latest 3.34.1 sqlite3win32.dll / SQLite11.dll version?

    Code:
        Dim DataSet As SQLiteDataSet
        'Description field (typename) nvarchar(250)
        'Set DataSet = PartsDBConnection.OpenDataSet("SELECT Description FROM Info WHERE Description ='192465' LIMIT 1;") 'Works ok in 3.34.1  -> returns numrows = 1
        Set DataSet = PartsDBConnection.OpenDataSet("SELECT Description, FROM Info WHERE Description LIKE '%192465%' LIMIT 1;") 'No worky in 3.34.1  -> returns numrows = 0 
        DataSet.MoveFirst
        numrows = DataSet.RecordCount
    What am i missing?
    The LIKE operator is now 1:1 VB-Ish.
    Means '%' will not be replaced to an '*' anymore.
    This enables to use '%' as a literal and '[*]' as literal for '*'. (No wildcard, escape clause)

  14. #54
    Fanatic Member
    Join Date
    Apr 2015
    Location
    Finland
    Posts
    679

    Re: VB SQLite Library (COM-Wrapper)

    Ouch - so LIKE query wildcard char '%' in VBSQLite has been changed, from precentage char % to asterisk char *.

    VBSQLite query dialect now differs from sqlite native dialect.
    https://sqlite.org/forum/info/846632411f3fd1d2

    Don't know others, but i would prefer database native engine dialect, be it; ansi, postgre, oracle, sql server, access etc.

  15. #55

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Tech99 View Post
    Ouch - so LIKE query wildcard char '%' in VBSQLite has been changed, from precentage char % to asterisk char *.

    VBSQLite query dialect now differs from sqlite native dialect.
    https://sqlite.org/forum/info/846632411f3fd1d2

    Don't know others, but i would prefer database native engine dialect, be it; ansi, postgre, oracle, sql server, access etc.
    The GLOB sql function also uses * instead of %.

    The problem with the native sqlite LIKE operator is the problem of unicode no-case handling.
    So it is necessary to resort to VB's Like operator.
    And mimic '%' in VB's Like operator has edge case side effects when wanting to use % as literal.
    Also the sqlite LIKE sql function has a 3 arg variant with an escape clause. This 3 arg variant is disabled now also in VBSQLite, because the VB's Like operator is 2 arg only.
    The VB's Like operator is even better as it can handle multiple escape clauses put in [].
    Sorry for the quirk caused but this is the only straight forward solution.

  16. #56
    Fanatic Member
    Join Date
    Apr 2015
    Location
    Finland
    Posts
    679

    Re: VB SQLite Library (COM-Wrapper)

    I understand your point. Luckily, no serious harm caused from change. SQL sentences are corrected and all is ok.

  17. #57
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    Re: VB SQLite Library (COM-Wrapper)

    Word of warning about the VB LIKE operator - you should be careful using it anywhere the user supplies the text for the pattern, especially true for any code that is Internet facing. Specially crafted Like patterns will crash your application, and it is theoretically possible that they could be crafted to get privilege escalation. Microsoft security has confirmed the bug, but have decided not to fix it because they have decided that there is too great a risk to accidentally breaking existing apps. They might change their tune if an actual exploit appears in the wild, but thankfully I don't think this is the case (yet).

    Olaf has worked around the bug in RC5/RC6 - it might be prudent for Krool to do the same.

  18. #58
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: VB SQLite Library (COM-Wrapper)

    hoe yo load memory sqlite database from vb6 resfile?
    like show png file from res.

    It is best to add a function to load from the memory address to the memory database.
    Originally a function is: load files to the memory database. The file itself will not be modified, which is equivalent to read-only, only the tables in the memory database are modified. It may also be necessary to save the settings in a hard disk file(This is difficult, don't care about it).

    Code:
    sqlitehandle = sqlite3_open(":memory:", pdb)
    
    sqlite3_open(sPath & "data.sqlite", pdb)
    
    dim buffer() as byte
    buffer= read byte from :sPath & "data.sqlite"
    
    sqlite3_openMemory(varptr(buffer(0)), pdb)
    sqlite3_open(sPath & "data.sqlite", pdb)
    What I mean is how to open the database file in the resource or memory address (read-only) without a hard disk database file
    If you need to modify, I can also write a memory class. When the file increases, expand the capacity of this memory address. When modifying the data to be written, locate the corresponding address and write.
    Last edited by xiaoyao; May 11th, 2021 at 07:16 AM.

  19. #59
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,871

    Re: VB SQLite Library (COM-Wrapper)

    hoe yo load memory sqlite database from vb6 resfile?
    You can't
    The load to memory of a DB file can't be done from a byte array.
    It only works for physical files.

  20. #60

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    Re: VB SQLite Library (COM-Wrapper)

    xiaoyao, your texts are so awful that I am not able to understand these. So I can't respond to you, sorry!

  21. #61
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Krool View Post
    xiaoyao, your texts are so awful that I am not able to understand these. So I can't respond to you, sorry!
    I want to load the database without a hard disk file, such as downloading a byte array or resource file from the Internet.
    Maybe this is too difficult. The most appropriate approach is estimated to be to HOOK the file read and write function of this process, so that you can simulate a virtual file pointer and jump to the memory address.

    Use this method to open an abc.doc file with WORD. In fact, this file is 0 bytes, or use code to generate an empty file with the same size as a real DOC file.
    The actual content is read from memory.

  22. #62
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,871

    Re: VB SQLite Library (COM-Wrapper)

    According to me this is not possible with the current SQLite engine.
    You can load a disk file to memory, you can safe a memory db to a file, but there are no from and to memory

  23. #63

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    Re: VB SQLite Library (COM-Wrapper)

    Update released.

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

  24. #64
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: VB SQLite Library (COM-Wrapper)

    The compilation fails, and there is a lot of code. It is difficult and takes a lot of time to successfully apply.

    Could you give it a try to build a lightweight ADO recordset to encapsulate SQLite API? It only needs 12 interface processes.
    like this:
    RC5 Sqlite Like Adodb.Connection/Adodb.RecordSet(WithOut Reg Com Dll)-VBForums
    https://www.vbforums.com/showthread....t-Reg-Com-Dll)


    Sometimes, some lightweight versions are needed to implement the simplest query, reading and modification.
    Such as reading data
    A=rs ("field 1") value
    B=rs ("field 2") value
    Modify field value
    RS ("field 1") = "A1"
    RS ("field 2") = "TTT"

    -------------
    Self writing class connection and recordset, instead of adodb For objects such as recordset, only 12 interfaces (or methods) need to be implemented for normal mode calls.
    Four interfaces of connection: createnewdb, opendb, execute, openrecordset
    8 interfaces of recordset: openrecordset, get item (let item), addnew, updatebatch, RecordCount, EOF, MoveNext

    Code:
    Sub Test_Like_Adodb_RecordSet()
    ShowErr = True
    dBFile = App.Path & "\Sqlite_Test3.db"
     Dim Cnn As New Connection
     MsgBox Cnn.cConnectionA.Version
     If Dir(dBFile) = "" Then 'Create New Sqlite DB
        Cnn.CreateNewDB dBFile
        Else
         Cnn.OpenDB (dBFile)
     End If
     
    Dim RS As Recordset
    On Error Resume Next
     
    Set RS = Cnn.OpenRecordset("select * FROM UserTable")
    If ErrNumber <> 0 Then
        Err.Clear
        Set RS = Nothing
        'New Table
       Cnn.Execute "CREATE TABLE UserTable (UserName TEXT PRIMARY KEY, ID INTEGER, Info TEXT)"
       'Set RS = New Recordset
       'RS.OpenRecordset "select * FROM UserTable", Cnn
       
       Set RS = Cnn.OpenRecordset("select * FROM UserTable")
       
       RS.AddNew
       RS("UserName") = "name1"
       RS("ID") = 100
       RS("Info") = "A"
       
       RS.AddNew
       RS("UserName") = "name2"
       RS("ID") = 200
       RS("Info") = "B"
       
       RS.UpdateBatch
       
       RS.AddNew
       RS("UserName") = "name2"
       RS("ID") = 201
       RS("Info") = "Bb"
       
     
       If Not RS.UpdateBatch Then
        MsgBox "key value err:" & ErrDescription
       End If
       
       'Set RS = Nothing
        Set RS = New Recordset
         RS.OpenRecordset "select * FROM UserTable", Cnn
    End If
    'Read Rows ,Data
    MsgBox "RecordCount=" & RS.RecordCount & ", Field:UserName=" & RS("UserName") & ", Field:ID=" & RS("ID")
    
    'Edit RecordSet
    Set RS = New Recordset
    RS.OpenRecordset "select * FROM UserTable", Cnn
    RS("ID") = RS("ID") + 1
    RS.UpdateBatch
    
    'Read Rows ,Data
    Set RS = New Recordset
    RS.OpenRecordset "select * FROM UserTable", Cnn
    MsgBox "After Edit,RecordCount=" & RS.RecordCount & ", Field:UserName=" & RS("UserName") & ", Field:ID=" & RS("ID")
    
    RS.Delete
    RS.Delete
    
    If RS.Delete = False Then
        'MsgBox "Delete err:" & ErrDescription
    End If
    Set RS = Nothing
    Set Cnn = Nothing
    End Sub
    Last edited by xiaoyao; Jun 30th, 2022 at 12:18 AM.

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

    Re: VB SQLite Library (COM-Wrapper)

    Re:The SQLiteDataSet object is read-only and meaningful for SELECT statements only.

    You can fix and add code support for RS.UPDATE
    Code:
    DIM Rs as Recordset
    Set RS = New Recordset
    RS.OpenRecordset "select * FROM UserTable", Cnn
    RS("ID") = RS("ID") + 1
    
    RS.UpdateBatch
    Code:
    Public Property Get Item(Field As Variant) As Variant
        'Read Field Value/读取字段值
        Item = cRecordset1(Field)
    End Property
    Public Property Let Item(Field As Variant, ByVal vNewValue As Variant)
        'Edit Field Value/修改字段值
        cRecordset1(Field) = vNewValue
    End Property

  26. #66
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: VB SQLite Library (COM-Wrapper)

    update fields value like ado

    Rs("ID")=11
    Rs.UPDATE

    Code:
    RS.EditField("ID") = 11
    RS.EditField("Info") = "info edited"
     
    If RS.UpdateEx("UserTable", "UserName='" & RS("UserName") & "'") Then
        MsgBox "Update One Recordset ok"
    End If


    Recordset.cls

    Code:
    Public FieldList As New Dictionary
    Public Cnn As Connection
    
    Public Property Let EditField(Field As String, ByVal vNewValue As Variant)
        'Edit Field Value/修改字段值
        On Error GoTo Err
        Dim Field1 As New FieldClass
        Field1.FieldName = Field
        Field1.FieldValue = vNewValue
        Field1.TypeName = TypeName(vNewValue)
        'InputBox "", "Field1.TypeName ", Field1.TypeName
        Set FieldList(Field) = Field1
        
        Exit Property
    Err:
        DoErr "Edit Field Value"
    End Property
    
    Function UpdateEx(table_name As String, WhereStr As String) As Boolean
    'Update table_name
    'SET column1 = value1, column2 = value2...., columnN = valueN
    'WHERE [condition];
    
    Dim i As Long, SQL As String
    Dim SqlArr() As String, FieldCount As Long
    Dim Field1 As FieldClass
    Dim Fg As String
    FieldCount = FieldList.Count
    ReDim SqlArr(FieldCount - 1)
    For i = 0 To FieldCount - 1
        Set Field1 = FieldList.Items(i)
        Select Case Field1.TypeName
            Case "String"
                Fg = "'"
            Case "Date"
            Case Else
            Fg = ""
        End Select
        
        SqlArr(i) = Field1.FieldName & "=" & Fg & Field1.FieldValue & Fg
    Next
    SQL = "Update " & table_name & " SET " & Join(SqlArr, ",") & IIf(WhereStr <> "", " WHERE " & WhereStr, "")
    MsgBox SQL
    Set FieldList = New Dictionary
    On Error GoTo Err
        UpdateEx = Cnn.Execute(SQL)
    Exit Function
    Err:
    Set FieldList = New Dictionary
    DoErr "UpdateEx"
    End Function
    fieldClass.cls
    Code:
      Public TypeName As String
      Public FieldName As String
      Public FieldValue As Variant

  27. #67
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: VB SQLite Library (COM-Wrapper)

    Hello, I trying to convert code from DAO to VBSQLite and I'm wondering how to convert DAO's Seek method that works on indexed fields and is actually very fast (hundreds of times faster than DAO's FindFirst) but I don't know how to handle that.

    If I have to make a new SQL query for every row that I need to fetch I guess it will be quite slower (it is a guess, I didn't test that still).
    Any advice?

  28. #68
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: VB SQLite Library (COM-Wrapper)

    What would be the way to insert a new record into a table that has a BLOB type field (binary data in a byte array)?

  29. #69

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Eduardo- View Post
    What would be the way to insert a new record into a table that has a BLOB type field (binary data in a byte array)?
    An easy way is the Command class where you can pass a byte stream via SetParameterValue.
    If that's not wanted you can "textize" your byte stream.
    Example:
    X'0102030405060708090a0b0c0d0e0f'
    About your other question:
    When I do heavy tasks with lot of ping pong queries I usually create a memory db and attach it with the current connection. Then backup the table into memory and process query/update and backup back to file.
    But that's some kind of effort and pays of in huge number only and when the db is on a network drive etc.

    In your case you only query or update as well?
    Maybe you can first start to optimize "begin transaction" and "end transaction" so the file db journal is not updated by every single row but rather in a batch.

  30. #70
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Krool View Post
    An easy way is the Command class where you can pass a byte stream via SetParameterValue.
    If that's not wanted you can "textize" your byte stream.
    Hello Krool, thank you.

    No, I don't want to textize. That would use more space in the db and also would take some time to convert (and convert back in the retrievals).

    How would be the way to store using SetParameterValue?

    Lets assume I have these fields: The ID that is the primary key, and autoincrement integer, a second field that is a string, and a third field that is the BLOB.

    I have the BLOB data in a bite array.

    In my attempts I have something like this:

    Code:
        Dim SQLiteCon As SQLiteConnection
        Dim MyString As String
        Dim MyData() As Byte
    
        Set SQLiteCon = New SQLiteConnection
        SQLiteCon.OpenDB "D:\TestDB.db", SQLiteReadWrite
        ' ... fill the variables
        SQLiteCon.Execute "INSERT INTO MyTable (String_Field, Blob_Field) VALUES ('" & MyString & "', " & MyData & ")"
    That of course doesn't work.

    Quote Originally Posted by Krool View Post
    About your other question:
    When I do heavy tasks with lot of ping pong queries I usually create a memory db and attach it with the current connection. Then backup the table into memory and process query/update and backup back to file.
    But that's some kind of effort and pays of in huge number only and when the db is on a network drive etc.
    In this case the database will be local.

    Quote Originally Posted by Krool View Post
    In your case you only query or update as well?

    Maybe you can first start to optimize "begin transaction" and "end transaction" so the file db journal is not updated by every single row but rather in a batch.
    I have been looking for a way to do that but for the other question, to be able to put one field at a time like DAO does, but could not find how to use transactions.

    Back to the second question, I see that your component store all the queried data in memory (o that's what I understood).
    In my case I'm trying to move to SQLIte because I have hit the 2 GB limit of Access. I have all the information in just one table. If I load all the data in memory (at least in the local memory) I'lll run out of memory.
    IDK how DAO works but I think it does not use local memory, or maybe it caches in memory just some records but I guess not all.
    I would like to make this program ready to handle large data, for example 10 GB or more. If the programs attempts to load that data into memory, not only local process memory, but all the machine memory could be exhausted (in case I was using it).

    I was trying to make a test program to see how it performs making a new SQL query for each record retrieval (I confess that I don't have high hopes that it will perform as DAO's Seek, but I also hope I'm wrong), but got stuck with adding the BLOB field.

    PD: worth to mention that I created an index for the field String_Field, and the data need to be retrieved but that field.

    Thanks.

  31. #71

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    Re: VB SQLite Library (COM-Wrapper)

    You create a Command class with following sql:
    Code:
    "INSERT INTO MyTable (String_Field, Blob_Field) VALUES (?100, ?101)"
    Then you use .SetParameterValue with 100 to pass a String and again with 101 to pass a byte stream
    Then execute the command.
    You can recycle the Command object and change only the parameters. That's more performant.

    I hope you get it. I'm only on phone now so bear with me.

  32. #72
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: VB SQLite Library (COM-Wrapper)

    Thank you Krool.

    I was able to add data using the Command, but I must be doing something wrong because it was extremely slow.
    I added 1,000,000 records and it took like 2 hours 40 minutes.

    I also see that Vb6.exe was not using all the processing power, but just some minor percent. I was expecting it to be running at full processor power.
    I'm using the VBSQLite ActiveX DLL compiled, and testing in IDE.

    The code was like this:

    Code:
        Dim SQLiteCon As SQLiteConnection
        Dim MyString As String
        Dim MyData() As Byte
        Dim MyCommand As SQLiteCommand
        
        Set SQLiteCon = New SQLiteConnection
        SQLiteCon.OpenDB "D:\TestDB.db", SQLiteReadWrite
        Set MyCommand = SQLiteCon.CreateCommand("INSERT INTO MyTable (String_Field, Blob_Field) VALUES (?100 , ?101)")
        
        for ...
            MyCommand.SetParameterValue 100, MyString
            MyCommand.SetParameterValue 101, MyData
            MyCommand.Execute
        Next
    The data actually came from an Access database where I already have it.

  33. #73
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,871

    Re: VB SQLite Library (COM-Wrapper)

    Have started a transaction first? This can make a huge difference

  34. #74
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Arnoutdv View Post
    Have started a transaction first? This can make a huge difference
    No, how to do that?

  35. #75

    Thread Starter
    PowerPoster
    Join Date
    Jun 2012
    Posts
    2,373

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Eduardo- View Post
    No, how to do that?
    Make a sql execute with "BEGIN TRANSACTION". Then do your 1,000,000 inserts and then execute with "END TRANSACTION".

  36. #76
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: VB SQLite Library (COM-Wrapper)

    OK, it improved "a bit", now it took 30 seconds!!

    I'll test the speed of searches now (I have to set up the test program to perform many random searches).

  37. #77
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: VB SQLite Library (COM-Wrapper)

    Well, bad news. Making a new SQL query for each search is not close to DAO's Seek speed.

    1,000,000 random searches, all keys were found:
    SQLite: 148 seconds.
    DAO: 14 seconds

    1,000,000 random searches, all keys NOT found, but the first 32 characters matched existing keys:
    SQLite: 126 seconds.
    DAO: 7 seconds.

    1,000,000 random searches, all keys NOT found, but first character already didn't match any existing key:
    SQLite: 117 seconds.
    DAO: 1.4 seconds.

  38. #78
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: VB SQLite Library (COM-Wrapper)

    Without the underlying test-code behind these results,
    it's hard to give recommendations how to speed up SQLite properly.
    (in all of my tests, it was faster than the JET-engine).

    @Krool
    Perhaps a little addition to your wrapper would be worthwhile:
    - an SQLiteCursor-Class

    99% of the implementation for that could be copied from your SQLiteCommand-Class...

    You only have to change the:
    - Execute Method
    - to a MoveNext Method
    where what's currently done in line 120 here: https://github.com/Kr00l/VBSQLite/bl...iteCommand.cls
    will be "single-steps".

    One such step will then only perform "the Seek-Condition" (as formulated in the SQL-Select the statement is based on).

    After such a "seek-step", the User should be free,
    - to retrieve ColumnValues at the current Record-Position...
    - via Extra-MethodCalls on that Cursor-Class,
    - and not implicitely "all of them in each step" as your DataTable-Class currently does for the sake of convenience

    Such a Cursor-Class would simulate the DAO-Seek-behaviour quite nicely (even if it is "forward-only")...
    (for backward-searches on a given index, the underlying select would have to specify an ...Order By IndexedField Desc in the underlying SQL).

    Edit: ... back to Eduardo...
    Perhaps all you need (but were not offered with JET) - is an UpSert-Command?
    (Insert when a unique Field is not existent, update instead when existent)...
    It is described for SQLite here: https://www.sqlite.org/lang_upsert.html (see the examples in 2.1...)

    As said, code is needed, which describes in more detail, what you currently "seek".

    Olaf
    Last edited by Schmidt; Jul 24th, 2022 at 07:49 PM.

  39. #79
    Frenzied Member
    Join Date
    Oct 2008
    Posts
    1,181

    Re: VB SQLite Library (COM-Wrapper)

    It says "This Ax-DLL project is intended to get a VB-friendly COM-Wrapper for the SQLite library". Do I need to download both the StdCall SQLite library and the COM wrapper? Does the COM wrapper have StdCall SQLite library as a dependency?

    Or in the COM Wrapper does it have all the SQLite stuff built-in?

  40. #80
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,995

    Re: VB SQLite Library (COM-Wrapper)

    Quote Originally Posted by Schmidt View Post
    Without the underlying test-code behind these results,
    it's hard to give recommendations how to speed up SQLite properly.
    (in all of my tests, it was faster than the JET-engine).
    Ok, I'll make a test project.

    Quote Originally Posted by Schmidt View Post
    Edit: ... back to Eduardo...
    Perhaps all you need (but were not offered with JET) - is an UpSert-Command?
    (Insert when a unique Field is not existent, update instead when existent)...
    It is described for SQLite here: https://www.sqlite.org/lang_upsert.html (see the examples in 2.1...)

    As said, code is needed, which describes in more detail, what you currently "seek".

    Olaf
    If the record is existent I need to read the record, if it does not exist I need to calculate everything and save it in a new record. It is a cache.

    But never have update records in this program. When something changed, I delete the old record and schedule a delayed action to insert a new one (after I have the new data).

    But one warning, IDK how that Cursor would work, but if it needs to have all the data loaded in memory, it won't be possible.
    I need this to work with millions records (they won't fit in the RAM).
    That's why I need to change the database system, to be able to handle more records.
    In the case of DAO/mdb the problem was the 2 GB file limit, but I don't think it has all the records in memory.

Page 2 of 6 FirstFirst 12345 ... 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