Results 1 to 22 of 22

Thread: VB6 SQLite DB-Demos (based on the RichClient-Framework)

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    VB6 SQLite DB-Demos (based on the RichClient-Framework)

    SQLite (http://sqlite.org/)

    ...is the worlds most widely deployed DB-engine (running on nearly every mobile-device or tablet - but it is also "strong on the Desktop",
    being the Default-App-DB for Firefox or WebKit or Thunderbird - and many other vendors/applications.

    The (SingleFile-DB-) Format is unicode-capable and interchangeable among operating-systems (no matter if little-endian or big-endian-based).
    Means, if you copy an SQLite-DB from your iPhone (or Linux-Server) onto your Win-Desktop, you will have no problem accessing it there (and vice versa).

    It still has a pretty small footprint, but other than the name may suggest, it is by no means "Lite" in the technical sense anymore...
    So, if there is a strong competitor for the very often used JET-engine, VB5/6-users so far prefer as their "typical App-DB", SQLite is it...

    Features (not found in JET-*.mdbs)
    - Triggers
    - FullText-Search (FTS4)
    - true InMemory-DBs (for "LINQ-like" query-scenarios in your VB6-App, using cMemDB and cRecordset)
    - strong (and compared with JET "unhackable") encryption on the whole DB (only 10-15% performance-decrease)
    - userdefinable Collations (String-Comparisons for Sorts)
    - userdefinable SQL-Functions (calling back into easy codable, native compilable VB6-code)
    - UTF8-String-storage by default (resulting in typically smaller DBs, compared with JET, which preferrably stores in UTF-16)

    Performance (compared with JET)
    - typically 2-3 times as fast in read-direction (Rs-retrieval, complex Selects)
    - typically 10 times as fast in write-direction (Bulk-Inserts/Updates/Deletes wrapped in transactions, import-scenarios with typically 200000 new inserted Records per second)

    VB6-access per DAO/ADO...
    Over ODBC ... a well-written SQLite-ODBC-driver can be found here:
    http://www.ch-werner.de/sqliteodbc/

    VB6-access without any MS-(DAO/ADO) dependencies...
    per builtin (ADO-like) cConnection/cRecordset/cCommand-Classes in vbRichClient5:
    http://www.vbRichClient.com/#/en/Downloads.htm

    These wrapper-classes work faster than the above mentioned ADO/ODBC-combination.

    Ok, Demo-Apps:

    First a simple one, still using the normal GUI-controls of VB6, to not "alienate" anybody ...(as said, the usage of the DB-related classes is pretty much comparable to ADO)... ;-)

    Thanks to dilettante for the nice Original, which can be found (as an ADO/JET-version) here:
    http://www.vbforums.com/showthread.p...meters-Example

    The version below is not that much different (aside from the AddNew and Delete-Buttons - and the SQLite-engine of course).
    http://www.vbRichClient.com/Download...DemoSQLite.zip




    Finally an SQLite-Demo, which does not only replace ADO/JET, but also the VB6-GUI-controls ...
    There isn't any Common-Controls involved, only the Widget-engine of the RichClient-library comes into play here
    (in conjunction with the vbWidgets.dll, which is hosted on GitHub: https://github.com/vbRichClient/vbWidgets).

    The Original to this still simple Demo is also based on ADO/JET, and can be found on PSC:
    http://www.planet-source-code.com/vb...35601&lngWId=1

    There's one thing "special" (aside from the vbWidgets) in this demo - and that's the regfree-deployment-feature,
    which is supported (without manifests and SxS-services) by the Frameworks smallest lib, the DirectCOM.dll.

    So the archive below comes as "a RealWorld-DeployPackage", and is therefore a bit larger (it contains,
    beside the VB6-source, also the 3 Base-Dlls of the RC5-Framework in a SubFolder \RC5Bin\).

    This way the Application is directly startable from e.g. an USB-Stick, without the need to register anything -
    the deploymentsize for such a RC5-based "regfree Package" starts from about 1.6MB (when LZMA-compressed,
    e.g. with InnoSetup ... or, as the download here, in a 7z-archive):
    http://www.vbRichClient.com/Downloads/SQLiteTree.7z (about 1.7MB)

    Another thing which is different from the first demo above (which provides its new generated DB, directly from imported Text-file-snippets),
    is the fact, that this Demo is using the exact same ADO-JET-*.mdb as the original on PSC as its Import-Source for the new created SQLite-DB.
    So this example also covers a simple "Convert-From-JET-DB-to-SQLite"-scenario - and shows, how to use the builtin cCOnvert-Class for that task...





    Well, have fun with it.

    Olaf
    Last edited by Schmidt; Jul 4th, 2013 at 02:12 AM.

  2. #2
    New Member
    Join Date
    Aug 2013
    Posts
    12

    Re: VB6 SQLite DB-Demos (based on the RichClient-Framework)

    Wow.. Didn't know your here.. I tried using your SQLite wrapper and it works really great. Can i use this on my shareware apps? So instead of using ms access and ADO, i'll just use your sqlite wrapper. Is that okay?..

    Thanks and more power.

  3. #3

    Thread Starter
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: VB6 SQLite DB-Demos (based on the RichClient-Framework)

    Quote Originally Posted by jhai_salvador View Post
    Wow.. Didn't know your here.. I tried using your SQLite wrapper and it works really great. Can i use this on my shareware apps? So instead of using ms access and ADO, i'll just use your sqlite wrapper. Is that okay?..
    Sure, there's no strings attached - the vbRichClient-tools are free to use by anybody, also in commercial applications ...
    also the (LGPL- and Public Domain-) licenses of the OpenSource-companion-lib: vb_cairo_sqlite.dll allow commercial usage.

    Just be aware, that this will add about 1.6MB to your "compressed-download-size" when you deploy your shareware-app.

    The benefits are, that your app cannot break that easily anymore, should MS "torpedo" ADO or JET at some point in time.

    Olaf

  4. #4
    New Member
    Join Date
    Aug 2013
    Posts
    12

    Re: VB6 SQLite DB-Demos (based on the RichClient-Framework)

    Thanks, will just put your name on the about box. 1.6Mb is fine since i can still compress the final setup file.

    Your sqlite wrapper works with 64bit of windows 7 and 8.

  5. #5
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: VB6 SQLite DB-Demos (based on the RichClient-Framework)

    @Schmidt: Couple of comments:
    1. If searching for quote (') app bombs with GPF.
    2. `Sub Main` can be private, not to be able to call it from other modules.
    3. Form resizing is a bit laggy here (4GHz i5, GTS 550).
    4. For mass expand/collapse some grid/tree controls implement Ctrl+Click on [+/-] -- most convenient would be to work for items on the same level only.

    Great work so far! I've been using your RichClient for sqlite access since before it became RichClient :-))

    cheers,
    </wqw>

  6. #6

    Thread Starter
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: VB6 SQLite DB-Demos (based on the RichClient-Framework)

    Quote Originally Posted by wqweto View Post
    @Schmidt: Couple of comments:
    1. If searching for quote (') app bombs with GPF.
    It's just an unhandled exception, not really a "bombing" (like in: "unhandled GPF withing a framework-lib").
    Instead the library throws an error at you, that the SQL-string is incorrect - and in the IDE you can
    then jump to this point directly (it's within the cwSearch-WidgetClass in routine DoSearch):

    The fix is simply to duplicate a single ' char (or wrap it up with *''* in case it stands alone)
    If S = "'" then S = "*''*" Else S = Replace(Widgets("txtSearch").Text, "'", "''")

    But that's just one of many things which are not yet "entirely perfect" in this demo - which is only that:
    "a simple Demo, which introduces SQLite-classes as well as the new GUI-approach"

    Quote Originally Posted by wqweto View Post
    2. `Sub Main` can be private, not to be able to call it from other modules.
    Never found that really important because the SymbolName 'Main' is somehow already "protected" (in my thinking, considered only as an App-Entry-Point)-
    I don't use it anywhere else (in other modules) as a procedure-name.

    But as said, it's just a small Demo - just change it to your liking and coding-style when you move parts of it over into your own Apps.

    Quote Originally Posted by wqweto View Post
    3. Form resizing is a bit laggy here (4GHz i5, GTS 550).
    That's one of the things which have to be "paid" when you try to keep up an entirely flickerfree-handling also in case of "continuous Form-Resizing".
    But those occasions (Form-Resizing) are relatively rare I'd say (Developers *do* like that test - but Users don't drag at Form-borders "back and forth" all that often) -
    more important is (IMO), that the behaviour is flicker-free when you e.g. drag the Splitter-Control between the TreeView and the EditControl-area -
    or that you get a fluent scrolling when you use the Trees VScrollbar.

    Quote Originally Posted by wqweto View Post
    4. For mass expand/collapse some grid/tree controls implement Ctrl+Click on [+/-] -- most convenient would be to work for items on the same level only.
    Ah - didn't kow about that Short-Key, thanks.
    That's something we should tackle in the cwTree-class in the vbWidgets-project on GitHub (just in case I forget - you could open an Issue on
    https://github.com/vbRichClient/vbWidgets/ - or even fix it yourself in the sources of cwTree, new vbWidgets contributors are always very welcome ).

    Quote Originally Posted by wqweto View Post
    Great work so far! I've been using your RichClient for sqlite access since before it became RichClient :-))
    Thanks.

    Olaf

  7. #7
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: VB6 SQLite DB-Demos (based on the RichClient-Framework)

    Quote Originally Posted by Schmidt View Post
    It's just an unhandled exception, not really a "bombing" (like in: "unhandled GPF withing a framework-lib").
    Instead the library throws an error at you, that the SQL-string is incorrect - and in the IDE you can
    then jump to this point directly (it's within the cwSearch-WidgetClass in routine DoSearch)
    That's what I expected but the demo is actually GPF-ing (this is Win8)



    I think there is a problem with error handling of `ReQuery` method as in `mDS.Rs.ReQuery` (no source to dig deeper) -- probably something is left hooked or some sqlite callback is not cleaned up.

    Another thing I noticed is that `EnterMessageLoop` is not as "standard" as VB6 message loop. The effect is that in the IDE I can't put breakpoints while the app is running.

    Probably connected to this is that error handling shows some weird behavior. I put `On Error GoTo ..` in `Sub Main` but it never got called when `DoSearch` failed.

    cheers,
    </wqw>

  8. #8

    Thread Starter
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: VB6 SQLite DB-Demos (based on the RichClient-Framework)

    Quote Originally Posted by wqweto View Post
    That's what I expected but the demo is actually GPF-ing (this is Win8)
    Interesting (the Win8-screenshot) - is this in the IDE or in the compiled executable?
    And did this happen as a result of a "reproducable action" - or did it come "out of nowhere"?

    Also interesting to know would be (in case it came unexpected), how many times the
    IDE-Stop-Button was pressed before this "freezing GPF" happened (the RC5-lib survives a few Stop-Button-presses
    in the IDE - but one should try to avoid the Stop-Button whenever possible of course).


    Quote Originally Posted by wqweto View Post
    I think there is a problem with error handling of `ReQuery` method as in `mDS.Rs.ReQuery` (no source to dig deeper) -- probably something is left hooked or some sqlite callback is not cleaned up.
    Not sure, because I get a very clear SQL-error-message thrown at me (on Win7-64 that is) - in both, the IDE and the compiled Executable,
    in case I had not applied the small fixes (as shown in my last post) - and when entering a sole ' as the search-string.

    Quote Originally Posted by wqweto View Post
    Another thing I noticed is that `EnterMessageLoop` is not as "standard" as VB6 message loop. The effect is that in the IDE I can't put breakpoints while the app is running.
    That's another thing which works here on Win7... I can place breakpoints as always - and when they are reached, I can step with F8 and continue with F5 as usual -
    the only thing when you continue with F5 (or the Play-Button in the ToolBar) is, that the current Form doesn't come back into the ForeGround, as the normal VB6-
    IDE-MessageLoop would ensure (so you have to do that manually over the taskbar).

    If you want to use the VB6-Messagepump instead of Cairo.WidgetForms.EnterMessageLoop you can add a dummy-VB-form to the project -
    and then this here as the last line of the Sub Main-routine instead:

    Code:
      If App.LogMode = 0 Then Load fMsgLoopDummy Else Cairo.WidgetForms.EnterMessageLoop
    In addition you need to cleanup this (invisibly) loaded dummy-form at Unload-time of the RC5-cWidgetForm-class -
    so at the end of cfMain.cls you should paste:

    Code:
    Private Sub Form_Unload(Cancel As Integer)
      If App.LogMode = 0 Then Unload fMsgLoopDummy
    End Sub
    Then the whole thing will use the VB6-default-messagepump (as long as the App runs in the IDE).


    Quote Originally Posted by wqweto View Post
    Probably connected to this is that error handling shows some weird behavior. I put `On Error GoTo ..` in `Sub Main` but it never got called when `DoSearch` failed.
    That is normal VB6-behaviour.
    There's no real global Error-Handler available ... the Click- and other Events of the GUI (or those which result in a "not VB-Code"-entry in the Call-Stack you can show with F7) act
    either as the Root-entries of the call-stack - or as "show-stopper-entries" (with regards to VB6-error-bubbling).

    Olaf
    Last edited by Schmidt; Sep 3rd, 2013 at 09:29 AM.

  9. #9
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: VB6 SQLite DB-Demos (based on the RichClient-Framework)

    The shot is from the compiled executable -- just clicked search tab and entered single quote. I got the sqlite error in the IDE tough as expected.

    Of course you are right about the error handling -- `DoSearch` is called from an event and non-trapped errors in an event handler close the whole app (and are never propagated).

    Could be some previous versions incompatibility with my machine (win8 x64). I do have previous dhRichClient3 and dhCairo registered (nothing older, just checked)

    The invisible form for message-pump emulation was the first I thought about, just wasn't sure when to unload it (if there are more that one non-modal cWidgetForm). Your `EnterMessageLoop` must be doing the book-keeping internally.

    cheers,
    </wqw>

  10. #10
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: VB6 SQLite DB-Demos (based on the RichClient-Framework)

    Hi Olaf,

    It seems `vb_cairo_sqlite.dll` can not be loaded in NT4 because it has unresolved import dependencies:
    - `CryptDuplicateKey` in ADVAPI32.DLL
    - `GetFontUnicodeRanges` in GDI32.DLL
    - `GetGlyphIndicesW` in GDI32.DLL

    I think these can easily be delay loaded or LoadLibrary/FindProc-ed. Especially `CryptDuplicateKey` as it can fail in later OSes if the provider does not support session cloning, so this must be handled in sqlite code somehow.

    Unfortunately `CryptDuplicateKey` is imported from `sqlite36_engine.dll` too (the dhRichClient3 helper), so now I cannot fallback to this older sqlite DAL too.

    cheers,
    </wqw>

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

    Re: VB6 SQLite DB-Demos (based on the RichClient-Framework)

    Olav is not around on the forum anymore.

    Are you seriously using Windows NT4, this dates to 1996!!
    Security

    Microsoft stopped providing security updates for Windows NT 4.0 Workstation on 30 June 2004 and Windows NT 4.0 Server on 31 December 2004, due to major security flaws including Microsoft Security Bulletin MS03-010, which according to Microsoft could not be patched without significant changes to the core operating system. According to the security bulletin, "Due to [the] fundamental differences between Windows NT 4.0 and Windows 2000 and its successors, it is infeasible to rebuild the software for Windows NT 4.0 to eliminate the vulnerability. To do so would require re-architecting a very significant amount of the Windows NT 4.0 operating system, and [...] there would be no assurance that applications designed to run on Windows NT 4.0 would continue to operate on the patched system."
    Between June 2003 and June 2007, 127 security flaws were identified and patched in Windows 2000 Server, many of which may also affect Windows NT 4.0 Server; however, Microsoft does not test security bulletins against unsupported software.

  12. #12
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: VB6 SQLite DB-Demos (based on the RichClient-Framework)

    Quote Originally Posted by Arnoutdv View Post
    Are you seriously using Windows NT4, this dates to 1996!!
    When you write software for clients or for "free download" you never know what will happen in the wild. I'm testing my apps under WINE too, just in case. NT4 is perfectly usable in "kiosk mode" behind a firewall, so is win2k or XP.

    XP dates back to 2001 and is still ~30% of active windows licenses. Anyway...

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

    Re: VB6 SQLite DB-Demos (based on the RichClient-Framework)

    I understand, but you can not expect a new written library to be backwards compatible with a version of windows which has not been updated since 2001.
    A lot of new libraries have been released by MS itself, which the vbRichClient relies on.

  14. #14
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: VB6 SQLite DB-Demos (based on the RichClient-Framework)

    Quote Originally Posted by Arnoutdv View Post
    I understand, but you can not expect a new written library to be backwards compatible with a version of windows which has not been updated since 2001.
    A lot of new libraries have been released by MS itself, which the vbRichClient relies on.
    I listed the only 3 API imports in the helper DLL that are not supported by NT4.

    Only Olaf can share if vbRichClient is using anything else missing in NT4 but I doubt there are way to much other dependencies.

    cheers,
    </wqw>

  15. #15

    Thread Starter
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: VB6 SQLite DB-Demos (based on the RichClient-Framework)

    The RichClient-Classes are meant to extend the (in the meantime somewhat lacking) vb6-runtime about "modern stuff" it is missing.
    And that goes along with a kind of "minimum-OS-requirement", which for the current version 5 of the RichClient means:
    "XP and higher" (currently only the cTaskBarList-Class and the new AudioSupport-Classes would require "Vista and higher")

    Then there's a special package which was made (based on a late version 3 of the RichClient), which not only worked
    on Win98-SE, but on an "early Win98, still shipping without Unicode-stuff" as well:
    http://www.vbRichClient.com/Download...ent3_Win98.zip
    (containing Unicows.dll and a "special RC3 compile").

    The above should work "well enough" for unicode-capable SQLite3-support and the Dictionary-classes on such "ancient systems".

    As for NT4 - I'd consider that an "really excotic target" in the meantime.
    I know that there's quite a few machine- and embedded-software which still runs on this OS,
    but the applications which still run on this old plattform are "wellknown and special" - and should
    not be "upgraded" with such "modern COMponents" IMO - doesn't really worth it, to go there
    (neither for me, nor for the people who have to maintain applications for those old platforms).

    Olaf

  16. #16
    Hyperactive Member
    Join Date
    Sep 2014
    Posts
    341

    Re: VB6 SQLite DB-Demos (based on the RichClient-Framework)

    Hello, Olaf. I have a few questions:

    1, How do I convert an In-memory DB into a disk file, i.e. InMemoryDump.db? I have learnt in other examples that it's possible to save a Recordset's content and later load it back into memory, but what about tables, databases?

    2, How & When are those methods used?

    CopyDatabase
    CompactDataBase

    GetBooleanString
    GetDateString
    GetShortDateString
    GetTimeString

    3, (Not quite related to this thread, but it's RC5 related). cArrayList.BindToArray is causing my VBE in Excel to collapse without any warning or error report. Can this problem be solved? I tested in my VB6 VBE, it won't collapse, though.

    I am quite new to the concept of "list", and is desperately looking for some example code as regards to this class, particularly when and how those methods are used:

    clone
    content
    pop
    push
    queue
    deque

    and sort, with its CmpFlags and IComparer. If there is a reference and similar materials, point me the way and I will read through first. Thanks
    Last edited by bPrice; Jul 23rd, 2016 at 12:31 AM.

  17. #17

    Thread Starter
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: VB6 SQLite DB-Demos (based on the RichClient-Framework)

    Quote Originally Posted by bPrice View Post
    Hello, Olaf. I have a few questions:

    1, How do I convert an In-memory DB into a disk file, i.e. InMemoryDump.db? I have learnt in other examples that it's possible to save a Recordset's content and later load it back into memory, but what about tables, databases?
    That's what the Cnn.CopyDataBase method is for...

    You can use it to either copy a FileDB into another FileDB -
    or alternatively (as is your aim), to copy from an InMem-DB to a FileDB -
    or also to copy and open an existing FileDB "into Memory" (as an InMemory-DB).

    That last mode allows, to work with a true (DB-based) documentformat InMemory,
    which touches the Disk only for very short timespans (when you load a given
    "DB-Coument-File" - or when you save it to disk - all in one go...

    Here's a Demo (just put the Code into an empty Form):
    Code:
    Option Explicit '***Into a Form (then click on the FileList-entries)
     
    Private DBFolder As String, MemCnn As cConnection
    
    Private WithEvents FileList As FileListBox
    
    Private Sub Form_Load()
      Caption = "Click the FileList, to open FileDBs InMemory"
    
      'create a writable DBFolder in the Users local AppData
      DBFolder = New_c.FSO.GetLocalAppDataPath & "\MyDBs\"
      If Not New_c.FSO.FolderExists(DBFolder) Then New_c.FSO.CreateDirectory DBFolder
     
      'create a FileList-Control dynamically, to show the current DBs
      ScaleMode = vbPixels
      Set FileList = Controls.Add("VB.FileListBox", "FileList")
      FileList.Path = DBFolder
      FileList.Move 3, 3, 200, 200
      FileList.Visible = True
    
      'now the DB-Test-Routines
      DBTest
    End Sub
    
    Private Sub DBTest()
      Debug.Print "DB-Creation"
    
      'start with a new DB, created in Memory (including Schema-Construction)
      Set MemCnn = CreateNewInMemDB()
    
      AddNewRecord 'just a write-test (putting the very first record into Table [Test])
      PrintContentsOfLastRecord 'check for the last written records content in Table [Test]
    
      'now we copy the current (just changed) MemDB-Content to a File
      SaveInMemDBToFile "DBFileWith_1_Record.db3"
    
    
      'same thing again (adding an additional, second Record, and Save to a different File after that)
    
      AddNewRecord 'just a write-test (put some new data into Table [Test])
      PrintContentsOfLastRecord 'check for the last written records content in Table [Test]
    
      'now we copy the current (just changed) MemDB-Content to a File
      SaveInMemDBToFile "DBFileWith_2_Records.db3"
    End Sub
    
    Private Sub FileList_Click()
      Debug.Print "FileOpen (into Memory) of : " & FileList.FileName
      Set MemCnn = OpenFileDBInMemory(FileList.FileName)
      PrintContentsOfLastRecord
    End Sub
    
    Private Function CreateNewInMemDB() As cConnection
      Set CreateNewInMemDB = New_c.Connection(, DBCreateInMemory)
          CreateNewInMemDB.Execute "Create Table Test(ID Integer Primary Key,Txt Text)"
    End Function
    
    Private Function SaveInMemDBToFile(FileName As String)
      If New_c.FSO.FileExists(DBFolder & FileName) Then New_c.FSO.DeleteFile DBFolder & FileName
     
      MemCnn.CopyDatabase DBFolder & FileName
      FileList.Refresh
    End Function
    
    Private Function OpenFileDBInMemory(FileName As String) As cConnection
      With New_c.Connection(DBFolder & FileName, DBOpenFromFile) 'create a temporary FileDB-Connection
        Set OpenFileDBInMemory = .CopyDatabase(":memory:") 'derive an InMemory-DB-Connection (copying DB-contents over)
      End With '<- the temp. FileDB-Connection is closed here again, immediately
    End Function
    
    Private Sub AddNewRecord()
      With MemCnn.OpenRecordset("Select * From Test Where 1=0")
        .AddNew
          .Fields("Txt").Value = "New Record, " & MemCnn.GetDateString(Now)
        .UpdateBatch
      End With
    End Sub
    
    Private Sub PrintContentsOfLastRecord()
      With MemCnn.OpenRecordset("Select * From Test")
        Debug.Print "Records in Table Test: "; .RecordCount
        .MoveLast
        Debug.Print " Col-Values of last Record: ID="; !ID.Value; ", Txt="; !Txt.Value; vbLf
      End With
    End Sub
    Quote Originally Posted by bPrice View Post
    2, How & When are those methods used?

    CopyDatabase
    CompactDataBase

    GetBooleanString
    GetDateString
    GetShortDateString
    GetTimeString
    For CopyDataBase you now have an extensive example to study, above...
    CompactDataBase will (as usual) re-write the DB-File - leaving out "gaps in pages" (as they could be caused by deleted Records)...

    The GetSomethingString-functions are thought for, when you build a query-string yourself
    (in case you're not making use of Command-Objects) - easy to test yourself e.g. in the VB-IDEs Immediate-Window:
    ?New_c.Connection(, DBCreateInMemory).GetDateString(Now)

    The above will print an ISO-Date-String (as SQLite prefers and understands them) from the passed 'Now' VB-Date


    Quote Originally Posted by bPrice View Post
    3, (Not quite related to this thread, but it's RC5 related). cArrayList.BindToArray is causing my VBE in Excel to collapse without any warning or error report. Can this problem be solved? I tested in my VB6 VBE, it won't collapse, though.
    Perhaps better to ask such questions in the normal Forum, to not clutter this SQLite-specific thread...

    The BindToArray-method will bind the ArrayList (which is internally hosting a normal SafeArray as well), to a VB-Array of the same type
    (but without performing any copying), to e.g. allow for faster indexed access, in case that is needed (e.g. when the content is larger).

    There's a companion to that method (.ReleaseArrayBinding), which will always has to be called after you're through with the bound VB_Array -
    so one has to make sure, that a potential error in your code doesn't jump out of the procedure, without reaching the appropriate line...

    The code below works without problems in Excel 2013 for me...
    Code:
    Option Explicit
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Dim Vector As cArrayList
      Set Vector = New_c.ArrayList(vbLong, 1, 2, 3) 'init to a Long-Array and provide the first 3 members
     
      Debug.Print Vector(0), Vector(1), Vector(2)
     
      Dim LongArr() As Long
      If Vector.BindToArray(LongArr) Then 'bind to the internal content, without making a copy
         Debug.Print LongArr(0), LongArr(1), LongArr(2)
         Vector.ReleaseArrayBinding LongArr '<- make very sure, that this line is indeed reached
      End If
    End Sub

    Quote Originally Posted by bPrice View Post
    I am quite new to the concept of "list", and is desperately looking for some example code as regards to this class, particularly when and how those methods are used:

    clone
    content
    pop
    push
    queue
    deque
    - .Clone -> makes a copy of the array-content into a new cArrayList-instance of the same type
    - .Content -> used for serializing the Objects-content into a ByteArray - .Content is a Read/Write-Property (a similar Prop is available on cRecordset, cCollection)
    - .Push/.Pop -> use the cArrayList like a stack
    - .Queue/.Dequeue -> use the cArrayList for queueing

    Quote Originally Posted by bPrice View Post
    and sort, with its CmpFlags and IComparer. If there is a reference and similar materials, point me the way and I will read through first.
    For the most scenarios, a normal call to just cArrayList.Sort would be enough.

    In case of an ArrayList-Vector of Type vbString, you can nake use of the optional Parameters if you want...

    E.g. the IComparer-Option allows to do e.g. a "Multi-Column-Sorting" on the stored Items -
    e.g. when the ArrayList-Vector contains Object-Instances - or when you have e.g. "Strings with multiple parts" as your Array-Members:

    The following short example addresses such "String-Items-with-multiple Columns"-sort-scenario:

    Code:
    Option Explicit 'into a Form
    
    Implements IComparer
     
    Private Sub Form_Load()
      Dim ArrayList As cArrayList, i As Long
      Set ArrayList = New_c.ArrayList(vbString, "Item 1|2", "Item 2|0", "Item 1|1", "Item 1|0")
     
      ArrayList.Sort , , Me
      For i = 0 To ArrayList.Count - 1: Debug.Print ArrayList(i): Next
    End Sub
    
    Private Function IComparer_Compare(A As Variant, B As Variant) As Long
      Dim ASplit() As String: ASplit = Split(A, "|")
      Dim BSplit() As String: BSplit = Split(B, "|")
    
      IComparer_Compare = StrComp(ASplit(0), BSplit(0))
      If IComparer_Compare = 0 Then 'in case the first split-parts are identical...
         IComparer_Compare = StrComp(ASplit(1), BSplit(1)) 'we shift the comparison to the second split-parts
      End If
    End Function
    
    Private Sub IComparer_TypeCheck(ByVal CurrentVarType As VbVarType, Example As Variant)
    End Sub
    HTH

    Olaf

  18. #18
    Hyperactive Member
    Join Date
    Sep 2014
    Posts
    341

    Re: VB6 SQLite DB-Demos (based on the RichClient-Framework)

    Olaf. Thanks for the detailed explanation. The CopyDatabase method has solved my problem, and CompactDatabase is a bit like VACUUM command.

    I ran into another problem today, that the "LIKE" operator seems not working according to what the SQL documentation says. I am trying to match something like 'helloworld' by a pattern 'helloworl_' and there is no match. It should've worked. I tested in sqlite command tool, and my pattern did work.

    Is the LIKE operator used with RC5 SQLite classes different from original LIKE operator in SQLite?
    Last edited by bPrice; Jul 26th, 2016 at 07:37 PM.

  19. #19

    Thread Starter
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: VB6 SQLite DB-Demos (based on the RichClient-Framework)

    Quote Originally Posted by bPrice View Post
    Olaf. Thanks for the detailed explanation. The CopyDatabase method has solved my problem, and CompactDatabase is a bit like VACUUM command.

    I ran into another problem today, that the "LIKE" operator seems not working according to what the SQL documentation says. I am trying to match something like 'helloworld' by a pattern 'helloworl_' and there is no match. It should've worked. I tested in sqlite command tool, and my pattern did work.

    Is the LIKE operator used with RC5 SQLite classes different from original LIKE operator in SQLite?
    This was asked (and answered with a bit of context) already here:
    http://www.vbforums.com/showthread.p...wildcard-issue
    ... and is fixed since version 5.0.47 (current version is 5.0.50)

    Olaf

  20. #20
    New Member
    Join Date
    Aug 2017
    Posts
    2

    Re: VB6 SQLite DB-Demos (based on the RichClient-Framework)

    Hi Olaf hope you can help:

    I have been using VBRichclient5 with my VB6 app for a couple of years really well. I have now come across a problem which I require assistance with. When creating the sqlite database I use the EncrKey string to provide a sqlite password to encrypt the data file.

    Function Connection([DBFileName As String], [OpenMode As DBConnectionOpenMode], [EncrKey As String], [EnableNestedTransactions As Boolean = True]) As cConnection

    This is fine using the sqlite data base created in the vb6 app - but if I want to use the sqlite data base else where I can not decrypt the data file. What is the decryption method that can be applied to the sqlite data file to allow it to be used in a web application or mobile app ? I know I can decrypt the datafile within the VB6 app by setting the ReKey.ConnectionObject after opening the data file. But I need to do this outside of the vb6 app.

    Thanks for your help / or anyone who can solve this issue for me.

  21. #21

    Thread Starter
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: VB6 SQLite DB-Demos (based on the RichClient-Framework)

    The encryption-method is proprietary to the Wrapper (vbRichClient5.dll + vb_cairo_sqlite.dll) -
    but at the time of implementing it, I was using the very same MS-crypto-provider (RC4) as the .NET-wrapper
    (at this time developed by Robert Simpson).

    This .NET-Provider/Wrapper is now maintained by the SQLite-team (Joe Mistachkin) - and as far as I know they did not change
    the underlying crypto-extension in the wrapper (meaning, their proprietary encryption-methods should still be compatible).

    That info might be useful, when choosing one of the many DataBase-Manager-Tools (I think SQLiteExpert does
    support the same encryption, as should all the .NET-based SQLite-Management-Tools like e.g. the one on: http://datadevelop.codeplex.com
    ... yep - just tested it with the DataDevelop-Manager and it does open an RC5-encrypted SQLite-DB just fine, when the right Password is given in the Connection-String-Dialogue).

    As for support on a WebServer (serverside usage of the encrypted DBs)...
    You should choose a Provider who does offer support for either Classic-ASP or ASP.NET...
    With Classic-ASP you can use vbRichClient5 at the serverside, with ASP.NET the .NET-provider for SQLite (with your serverside, encrypted DBs).

    As for direct (clientside) usage of such encrypted DBs on a mobile-device, I think you are restricted to the MS-devices (MS-phones/tablets, for which adapted versions of the .NET-provider do exist) -
    on all other platforms you will be able to work with an unencrypted DB of course (with the preferred SQLite-wrapper which comes with these systems or system-languages).

    HTH

    Olaf

  22. #22
    New Member
    Join Date
    Aug 2017
    Posts
    2

    Re: VB6 SQLite DB-Demos (based on the RichClient-Framework)

    Thanks for your help Olaf

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