VB6 SQLite DB-Demos (based on the RichClient-Framework)-VBForums
Results 1 to 15 of 15

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

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Jun 2013
    Posts
    761

    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
    11

    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
    Fanatic Member
    Join Date
    Jun 2013
    Posts
    761

    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
    11

    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
    Junior Member
    Join Date
    May 2011
    Posts
    31

    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
    Fanatic Member
    Join Date
    Jun 2013
    Posts
    761

    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
    Junior Member
    Join Date
    May 2011
    Posts
    31

    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
    Fanatic Member
    Join Date
    Jun 2013
    Posts
    761

    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
    Junior Member
    Join Date
    May 2011
    Posts
    31

    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
    Junior Member
    Join Date
    May 2011
    Posts
    31

    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
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    770

    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
    Junior Member
    Join Date
    May 2011
    Posts
    31

    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
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    770

    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
    Junior Member
    Join Date
    May 2011
    Posts
    31

    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
    Fanatic Member
    Join Date
    Jun 2013
    Posts
    761

    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

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.