Sqlite with vb6-VBForums
Results 1 to 14 of 14

Thread: Sqlite with vb6

  1. #1

    Thread Starter
    Junior Member
    Join Date
    May 2010
    Posts
    24

    Sqlite with vb6

    Hello,

    I want to package my application to use sqlite as the local database. it says sqlite is zero configuration. Is it possible to connect to it without installing drivers? Is there any way i can use sqlite without any ODBC driver being installed? if not possible can i install the sqlite odbc in silent mode?

    Please help

  2. #2
    PowerPoster
    Join Date
    Feb 2006
    Posts
    11,418

    Re: Sqlite with vb6

    While I'm not saying there are no SQLite ODBC Drivers out there most uses of it that I have seen either call the SQLite API directly or use a more VB-friendly set of ActiveX wrappers.

    Keep in mind that in many ways SQLite is fairly primitive (everything is a String used as a pseudo-Variant) though that can also be an advantage and keeps it "light weight" in a positive sense.


    Of course Jet 4.0 is preinstalled in Windows and has been for a long time. It is quite rare to even find a working Win95 system in the wild that didn't have it installed somewhere along the line. Thus Jet 4.0 is less "zero configuration" than SQLite is, and can be considerably more powerful. But its main advantage is that its use in VB6 programs is extremely widely known and examples and help abound.

    Don't confuse Jet with "Access" as so many do. There is no need to have any version of MS Access installed to use Jet 4.0. MS Access is just another client.

    Unless you need to lift your datastore from a Windows machine and drop it on a foreign OS I'm not sure you are going to see much advantage in using SQLite. By using Jet 4.0 almost 100% of your questions go away.


    As far as packaging and deployment goes you'll have very similar problems whether you choose Jet or something like SQLite. Since programs normally install under the Programs special folder ("Program Files") just dumping the database file next to the EXE isn't a viable strategy. The Programs folder is not writable by standard users.

    Normally any files that are deployed to be (a.) read/write or written at runtime and (b.) will be shared by all users who log on to the machine should go into an application-specific folder under the ProgramData special folder (CommonAppData). To make this work correctly your installer needs to create the folder there and set appropriate permissions on it before copying the initial database there.

    Read/write data that each user has his own copy of goes into an application-specific folder under the user's LocalAppData special folder. These are normally created for each user on "first run" of your program. Your program can easily detect the first run by noting that it cannot find your application's read/write data folder. So when you can't find the folder you create it and if necessary populate it with initial files (settings, private database, etc.) typically by copying read-only "template" files that you deployed under Program Files during installation.

    Of course if your program isn't actually installed this isn't an issue, but non-installed programs are not safe unless you (a.) use no non-system-provided ActiveX dependencies or (b.) use isolation manifests and Reg-Free COM.

  3. #3
    PowerPoster
    Join Date
    Feb 2006
    Posts
    11,418

    Re: Sqlite with vb6

    SQLite COM Overview describes a wrapper for SQLite 2 that eliminates the need to use ADO or an OLEDB Provider (or ODBC Driver). There is a link there for a free download.

    It even came in an ARM version that was compatible with Windows Mobile and Pocket PC (though not the .Net-centric Windows Phone on the market today).

  4. #4

    Thread Starter
    Junior Member
    Join Date
    May 2010
    Posts
    24

    Re: Sqlite with vb6

    really awsome. first i never knew that jet is independent of MS Access. and MS Access is a UI for Jet. it works perfect with jet.

    i just did an insert of some 10000 records into sqllite ,jet and MySql. first i tried without using a transsaction and jet took only 12 seconds to insert. but sqlite and mysql was taking too much time like it took few mins to complete. But when i used transaction the output was totally different. sqlite took only less than 1 second to insert 10000 records. and jet and mysql took around 8 seconds..i m not sure how much it will be applicable in real time scenario. but other than that i think Jet 4.0 is the best option in terms of setup and configuration.
    As for creating .mdb database dynamiclly in Jet i can use ADOX (Microsoft ADO Ext 6.0 for DDl and Security) .create function.

    To add up to my anxienty what do you think about the stability of Jet engine?. when i used access few years back i had problems of it getting easily corrupted

    i am currently checking the sqlite COM too. thanks indeed

  5. #5
    PowerPoster
    Join Date
    Feb 2006
    Posts
    11,418

    Re: Sqlite with vb6

    Jet MDB corruption is rare except when the MDB file is shared among several client PCs. In such cases any of them might fail or lose network connectivity while holding dirty pages resulting in database corruption.

    Using a non-shared MDB on a local hard drive should be quite stable. Of course a catastrophe can always occur but that's why we back things up. Such catastrophes usually damage more important files as well (like parts of Windows).

    If you are using a database as a local datastore for some application you never need to use transactions anyway. They only become important when you have multiple updaters.


    You can actually improve Jet's performance when used only by a single updater by opening the database for exclusive access. This eliminates all locking which cuts a lot of overhead. In some cases it helps to use Jet in row level lock mode instead of page lock mode too, but it depends on your pattern of use.

    This is why crude benchmarks can be irrelevant. There are tons of different optimizations that can be used to favor one situation or another.

  6. #6
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Candon City, Ilocos Sur, Phils.
    Posts
    10,813

    Re: Sqlite with vb6

    Just be aware that mdb has its limitations, the last time I check it is 2GB, if your needs should exceed that limit then you can try the other alternatives.
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  7. #7
    Super Moderator Joacim Andersson's Avatar
    Join Date
    Jan 1999
    Location
    Sweden
    Posts
    14,509

    Re: Sqlite with vb6

    Moved to the Database Development forum.
    Joacim Andersson
    If anyone's answer has helped you, please show your appreciation by rating that answer.
    I'd rather run ScriptBrix...
    Joacim's view on stuff.

    MVP

  8. #8

    Thread Starter
    Junior Member
    Join Date
    May 2010
    Posts
    24

    Re: Sqlite with vb6

    Thanks a lot dilettante and dee-u. it really helped.. I had almost decided to use sqlite. But i dont see any reason not to use Jet. there is nothing to be installed extra for Jet. and as for the storage limit. it will be ok for me because i am going to use it as a local db for temporary storage. thanks

  9. #9
    Member
    Join Date
    Jun 2010
    Posts
    37

    Re: Sqlite with vb6

    Maybe you would like to checkout http://www.vbrichclient.com, includes sqlite connectivity and some other goodies. I haven't tested the latest v5 but uses v3 myself. It's a bit more lightweight as the sqlite dll is separate (and now a bit old) but have now moved into one bigger dll with other functionalities. It's good stuff that give a lot of value for the money as it costs zero.
    M$ vs. VB6 = The biggest betrayal and strategic mistake of the century!?

  10. #10

    Thread Starter
    Junior Member
    Join Date
    May 2010
    Posts
    24

    Re: Sqlite with vb6

    Thanks. yes i already tried that. but i would prefer adodb itself because my entire app uses that and there is no casting available i think from this to adodb for eg: for a recordset. but its a nice replacement.

    I have been using VB6 for quite a while and i dont see a any strong reason for the plain statement in the signature.

  11. #11
    Member
    Join Date
    Jun 2010
    Posts
    37

    Re: Sqlite with vb6

    Quote Originally Posted by thetodaisies View Post
    I have been using VB6 for quite a while and i dont see a any strong reason for the plain statement in the signature.
    I think you misunderstood my sig and have now edited it to be a bit clearer. I love VB6, since many years and while I have been looking for a replacement due to M$ abandoning it I have found nothing that comes near. VB.NET IDE is really nice, but it stops there. As I said in another topic, I still hold my hopes to that M$ one day will get a new Top Dog that realizes the gravity in abandoning it and will do something about it!
    M$ vs. VB6 = The biggest betrayal and strategic mistake of the century!?

  12. #12

    Thread Starter
    Junior Member
    Join Date
    May 2010
    Posts
    24

    Re: Sqlite with vb6

    Cheers.. i too vote for that. after few years there may be another choice to face with an all new platform with deprecated .net

  13. #13
    Serge's Avatar
    Join Date
    Feb 1999
    Location
    Scottsdale, Arizona, USA
    Posts
    2,744

    Re: Sqlite with vb6

    I don't think .NET is going anywhere. If there is anything, it's expanding. VB6 (and I used to love it) should go away. It doesn't serve that much anymore. It's lacking a lot of programming paradigms. I call VB - object based in opposed to Object Oriented as it's lacking fundamental OO principles.

    As far as nothing comes "near" VB is a very narrow minded remark. VB can't come even close to ability of .NET. In my days I've done things in VB which it wasn't meant for and although I've achieved what I wanted in my applications, it was a huge hassle.

  14. #14
    Fanatic Member
    Join Date
    Dec 2008
    Posts
    588

    Re: Sqlite with vb6

    I love portable (No install).
    Most of my VB6 programs do not require installing, as I have stuck with VB6 SP5 (runtimes already on Win 98SE and later OS's).
    When I do need a couple of dependencies (EG ADO) I create an empty project (no code) with the dependenices, and the user runs that once every 100 years, and forgets about it.

    Regarding the topic of this post, I just downloaded Flashnote portabe, which required no installation. I notice it uses sqlite3.dll (in the aplication's folder) NO INSTALLATION
    Here is a link to that Flashnote program -
    http://softvoile.com/flashnote/free-...es-manager.php

    Rob
    PS I have not fully read this thread yet, but will in the near future.
    Last edited by Bobbles; Jun 9th, 2014 at 09:48 PM. Reason: typo

Tags for this Thread

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.