Printable View
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
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.
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).
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
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.
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.
Moved to the Database Development forum.
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
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.
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. :)
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!Quote:
Originally Posted by thetodaisies
Cheers.. i too vote for that. after few years there may be another choice to face with an all new platform with deprecated .net ;)
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.
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.