Page 2 of 2 FirstFirst 12
Results 41 to 62 of 62

Thread: VB SQLite Library (COM-Wrapper)

  1. #41
    PowerPoster
    Join Date
    Jun 2013
    Posts
    5,419

    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
    Addicted Member
    Join Date
    Jan 2015
    Posts
    245

    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
    Frenzied Member
    Join Date
    Jun 2012
    Posts
    1,775

    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: 387
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
    Frenzied Member
    Join Date
    Jun 2012
    Posts
    1,775

    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
    Frenzied Member
    Join Date
    Jun 2012
    Posts
    1,775

    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
    Addicted Member
    Join Date
    Jun 2016
    Location
    Espaņa
    Posts
    242

    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
    Addicted Member
    Join Date
    Jan 2015
    Posts
    245

    Re: VB SQLite Library (COM-Wrapper)

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

  10. #50

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2012
    Posts
    1,775

    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
    Frenzied Member
    Join Date
    Jun 2012
    Posts
    1,775

    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
    678

    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
    Frenzied Member
    Join Date
    Jun 2012
    Posts
    1,775

    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
    678

    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
    Frenzied Member
    Join Date
    Jun 2012
    Posts
    1,775

    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
    678

    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
    Frenzied Member
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    1,737

    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
    Frenzied Member
    Join Date
    Jan 2020
    Posts
    1,292

    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
    4,426

    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
    Frenzied Member
    Join Date
    Jun 2012
    Posts
    1,775

    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
    Frenzied Member
    Join Date
    Jan 2020
    Posts
    1,292

    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
    4,426

    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

Page 2 of 2 FirstFirst 12

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