Asp Forum - Olaf, any further forward on dhSQLite documentation?

(Mike Mitchell)

8/23/2011 11:21:00 AM

Over the past few days I have been reviewing various wrappers for VB6
to interface to an SQLite database. My particular interest is
full-text searching. Apart from dhRichClient3 (free) and SQLitePlus
(not free) there don't seem to be any current.

You've mentioned previously that there will one day be documentation
for dhRichClient3 forthcoming and you also referred to "wiki"
(wikipedia?) Is this making any progress?

I recall our previous brief discussions back in late 2010 in this
newsgroup about full-text searching in conjunction with speeding up
VB6 data retrieval from an Access mdb, and I have dug out the code
samples ("ADOandDAO" and "SQLite") you provided. The full-text
searching in the latter sample is impressively fast. Where you had
40,000 rows, I also tried it with 200,000 rows and I also included an
additional test row "The cat sat on the mat" to be added last of all.
In all cases the full-text search response was near instantaneous.

Is there a limit to this? For instance, would it work with a million
rows?

I have also started to look at your Nwind-Demo in the latest
dhRichClient3.zip which I grabbed a day or two ago. (You recommend new
users should study the Nwind-Demo code in order to find out how to use
the wrapper.)

This is possible, but documentation would be better!

I read through the http://www.datenhaus.de/RC-Help/dhRichC...
online docs, but these are pretty basic.

Finally, what is the preferred way of contacting you?

(a) This newsgroup?
(b) Your email?
(c) Any other forum?

Many thanks.

MM

22 Answers

Schmidt

8/23/2011 4:22:00 PM

Am 23.08.2011 13:21, schrieb MM:
> Over the past few days I have been reviewing variouswrappers for VB6
> to interface to an SQLite database. My particular interest is
> full-text searching. Apart from dhRichClient3 (free) and SQLitePlus
> (not free) there don't seem to be any current.

Aside from, that the "most current" is vbRichClient4, there's
perhaps no other "currently maintained" COM-Wrappers for SQLite
(there was for some time an additional one, from TerraInformatica).

> You've mentioned previously that there will one day be documentation
> for dhRichClient3 forthcoming and you also referred to "wiki"
> (wikipedia?) Is this making any progress?

Not yet, my current efforts all go into RichClient4
(and the latest SQLite-lib-versions are now compiled
into the companion-dll: vb_cairo_sqlite) ...
So RichClient3 (although being pretty stable by now)
will not receive any updates anymore - and will also
not get documentation.

Documentation is planned for RichClient4 (which is
calling-compatible with regards to all the Classes
from former RichClient3), but I'll tackle that *after*
I've finished the new (RC4-adapted) Cairo-Tutorial and
the new Widget-Tutorial (based on the new graphics-Classes,
which are now "officially in there and supported").

So the new graphics-stuff is, where most of my work
currently gets invested, to be able to write Applications,
which will not require any MS-GUI-Controls anymore -
and to drop that dependency as well.

As for the "Wiki" I've mentioned, this will not be hosted
on Wikipedia... ;-)
The Wiki-term is usually related to a WebServer-side
(community-editable) Content-engine - and in case of
RichClient4 then, to provide a HTML-based Documentation
and a FAQ, which will not only be changeable by me.

But setting up a new WebServer (with the Wiki-engine
and a Forum) will also have to wait, until the
"Documentation-phase starts" ... after all the more
"pressing" things are accomplished.

> I recall our previous brief discussions back in late 2010 in this
> newsgroup about full-text searching in conjunction with speeding up
> VB6 data retrieval from an Access mdb, and I have dug out the code
> samples ("ADOandDAO" and "SQLite") you provided. The full-text
> searching in the latter sample is impressively fast. Where you had
> 40,000 rows, I also tried it with 200,000 rows and I also included an
> additional test row "The cat sat on the mat" to be added last of all.
> In all cases the full-text search response was near instantaneous.
>
> Is there a limit to this? For instance, would it work with a million
> rows?

Of course.
There's a small "non-linearity" of course (due to cache-limits),
but not a large one - and 1Mio or even 10Mio records should
perform fine with SQLites FTS3 or FTS4.
To be sure, just enhance your 200000 to 1Mio records
and test again.

> I have also started to look at your Nwind-Demo in the latest
> dhRichClient3.zip which I grabbed a day or two ago. (You recommend new
> users should study the Nwind-Demo code in order to find out how to use
> the wrapper.)
>
> This is possible, but documentation would be better!

Sure, when documentation is provided *in addition*
to demo-code, then this would be better.
Yeah, I "heard you all" (already in the other thread) -
but as said, until the "documentation-phase" starts, you
will have to live with the examples, intellisense - and the
Class-browsing capabilities of the VB6-Object-Explorer (F2).

At least in my opinion - aside from, that it perhaps will
"hurt" a bit more, compared to: "learning to apply
functions or methods step by step, by studying their
documentation"...
....In terms of: "time to invest, until one becomes
productive and efficient", I'd prefer some commented
and "best-practise" Demo-Code anytime.

And in case of the SQLite-NWind-comparison-Code
(most of the functionality in this Demo also has
an not that much different ADO-equivalent) -
there is pretty much everything SQLite-related covered.

If you know ADO, then you can work after a few days
also with the RC4-based SQLite-wrapper.

When something seems not so obvious, then just step
through the Code in one of the different "topic-related"
Demo-Forms in "F8-mode"...

If you *really* invest 2 or 3 days into the NWind-Demo,
stepping through all the examples, then you will be
"in better shape" than by studying "only a documentation"
in the same amount of time - I'm pretty sure about that.

And after one has done so, there's knowledge about
the location of "already working snippets, ready to
copy over into your own, real-world applications.

> I read through the http://www.datenhaus.de/RC-Help/dhRichC...
> online docs, but these are pretty basic.
Yep, this is currently at the level, the VB6-ObjectExplorer
also provides (and the VB6-OE offers also a search-mode).

> Finally, what is the preferred way of contacting you?
>
> (a) This newsgroup?
> (b) Your email?
> (c) Any other forum?

Point c) comes later ... currently a) or b) would
be the way to go - Option a) depending on, if your
current problem is "generic enough", so that
perhaps more users of the RichClient are able,
to profit from the answer - Option b) when
you're stuck with a pretty specific problem
(usually accompanied in the E-Mail by some
small *.zip with a small project and a small
Demo-DataBase).

If you're not sure, then just send an e-mail -
so far I've managed, to respond "within hours"
on average (bad conscience, due to missing
documentation, you know <g>).

Olaf

GS

8/23/2011 4:59:00 PM

Olaf,
Thanks so much for the update! Please keep us informed...

Thanks also to MM for asking!

--
Garry

Free usenet access at http://www.eternal-sep...
ClassicVB Users Regroup! comp.lang.basic.visual.misc

(Mike Mitchell)

8/23/2011 5:16:00 PM

On Tue, 23 Aug 2011 18:22:16 +0200, Schmidt <sss@online.de> wrote:

>Am 23.08.2011 13:21, schrieb MM:
>> Over the past few days I have been reviewing variouswrappers for VB6
>> to interface to an SQLite database. My particular interest is
>> full-text searching. Apart from dhRichClient3 (free) and SQLitePlus
>> (not free) there don't seem to be any current.
>
>Aside from, that the "most current" is vbRichClient4, there's
>perhaps no other "currently maintained" COM-Wrappers for SQLite
>(there was for some time an additional one, from TerraInformatica).

Yes, I tried SQLiteDB from the Wayback Machine, just to see what would
happen, but it barfed at around 1,490,000 records. Anyway, it seems to
have completely disappeared, no idea what happened.

>> You've mentioned previously that there will one day be documentation
>> for dhRichClient3 forthcoming and you also referred to "wiki"
>> (wikipedia?) Is this making any progress?
>
>Not yet, my current efforts all go into RichClient4
>(and the latest SQLite-lib-versions are now compiled
> into the companion-dll: vb_cairo_sqlite) ...
>So RichClient3 (although being pretty stable by now)
>will not receive any updates anymore - and will also
>not get documentation.

I'll just carry on 'playing' with RC3 until I see signs of RC4.

[snip]
>>
>> Is there a limit to this? For instance, would it work with a million
>> rows?
>
>Of course.
>There's a small "non-linearity" of course (due to cache-limits),
>but not a large one - and 1Mio or even 10Mio records should
>perform fine with SQLites FTS3 or FTS4.
>To be sure, just enhance your 200000 to 1Mio records
>and test again.

Just did that. Worked!

Thanks.

MM

Ulrich Korndoerfer

8/23/2011 7:12:00 PM

MM schrieb:

> On Tue, 23 Aug 2011 18:22:16 +0200, Schmidt <sss@online.de> wrote:
>
>> Am 23.08.2011 13:21, schrieb MM:
>>> Over the past few days I have been reviewing variouswrappers for VB6
>>> to interface to an SQLite database. My particular interest is
>>> full-text searching. Apart from dhRichClient3 (free) and SQLitePlus
>>> (not free) there don't seem to be any current.
>> Aside from, that the "most current" is vbRichClient4, there's
>> perhaps no other "currently maintained" COM-Wrappers for SQLite
>> (there was for some time an additional one, from TerraInformatica).
>
> Yes, I tried SQLiteDB from the Wayback Machine, just to see what would
> happen, but it barfed at around 1,490,000 records. Anyway, it seems to
> have completely disappeared, no idea what happened.
> ...

The developer of SQLiteDB (then an employee of TerraInformatica, George
Ionescu) for some reasons left the company to make up his own. Before he
left he promised to keep his VB6 SQLite wrapper up to date and make
further enhancements, but in effect he never did. In fact, he simply
"disappeared". Never heard from him again.

For further info some links to relevant (very old) TerraInformatica
forum threads:

SQLiteDb / SQLiteDbServer future
<http://terrainformatica.com/forums/topic.php?...

SQLiteDB replacements (recommends Olaf Schmidts solution)
<http://terrainformatica.com/forums/topic.php?...

--
Ulrich Korndoerfer

VB tips, helpers, solutions -> http://www.prosource.de/...
MS Newsgruppen Alternativen -> http://www.prosource.de/ms-ng-...

(Mike Mitchell)

8/23/2011 7:43:00 PM

On Tue, 23 Aug 2011 21:11:36 +0200, Ulrich Korndoerfer
<ulrich_wants_nospam@prosource.de> wrote:

>MM schrieb:
>
>> On Tue, 23 Aug 2011 18:22:16 +0200, Schmidt <sss@online.de> wrote:
>>
>>> Am 23.08.2011 13:21, schrieb MM:
>>>> Over the past few days I have been reviewing variouswrappers for VB6
>>>> to interface to an SQLite database. My particular interest is
>>>> full-text searching. Apart from dhRichClient3 (free) and SQLitePlus
>>>> (not free) there don't seem to be any current.
>>> Aside from, that the "most current" is vbRichClient4, there's
>>> perhaps no other "currently maintained" COM-Wrappers for SQLite
>>> (there was for some time an additional one, from TerraInformatica).
>>
>> Yes, I tried SQLiteDB from the Wayback Machine, just to see what would
>> happen, but it barfed at around 1,490,000 records. Anyway, it seems to
>> have completely disappeared, no idea what happened.
>> ...
>
>The developer of SQLiteDB (then an employee of TerraInformatica, George
>Ionescu) for some reasons left the company to make up his own. Before he
>left he promised to keep his VB6 SQLite wrapper up to date and make
>further enhancements, but in effect he never did. In fact, he simply
>"disappeared". Never heard from him again.

Yes, he really did disappear, didn't he! I tried earlier to find any
reference to his existence, but apart from a few contributions to
message forums he couldn't be found, and certainly not in conjunction
with the story on SQLiteDB, which was my reason for trying to find
him. Not because I had already committed to SQLiteDB as some people
did, but purely as a matter of interest.

>For further info some links to relevant (very old) TerraInformatica
>forum threads:
>
>SQLiteDb / SQLiteDbServer future
><http://terrainformatica.com/forums/topic.php?...
>
>SQLiteDB replacements (recommends Olaf Schmidts solution)
><http://terrainformatica.com/forums/topic.php?...

I believe I have read all those, possibly ending up on them through
various circuitous routes.

In the last three hours I've also been looking at MySQL, which also
does full-text searching, and the product seems far more rounded than
SQLite. However, the only link to it from "the outside", i.e. VB6 in
my case, is through some sort of ODBC connector, which I haven't used
for years. But I suppose it must work... ;)

MM

GS

8/23/2011 9:03:00 PM

After serious thinking MM wrote :
> On Tue, 23 Aug 2011 21:11:36 +0200, Ulrich Korndoerfer
> <ulrich_wants_nospam@prosource.de> wrote:
>
>> MM schrieb:
>>
>>> On Tue, 23 Aug 2011 18:22:16 +0200, Schmidt <sss@online.de> wrote:
>>>
>>>> Am 23.08.2011 13:21, schrieb MM:
>>>>> Over the past few days I have been reviewing variouswrappers for VB6
>>>>> to interface to an SQLite database. My particular interest is
>>>>> full-text searching. Apart from dhRichClient3 (free) and SQLitePlus
>>>>> (not free) there don't seem to be any current.
>>>> Aside from, that the "most current" is vbRichClient4, there's
>>>> perhaps no other "currently maintained" COM-Wrappers for SQLite
>>>> (there was for some time an additional one, from TerraInformatica).
>>>
>>> Yes, I tried SQLiteDB from the Wayback Machine, just to see what would
>>> happen, but it barfed at around 1,490,000 records. Anyway, it seems to
>>> have completely disappeared, no idea what happened.
>>> ...
>>
>> The developer of SQLiteDB (then an employee of TerraInformatica, George
>> Ionescu) for some reasons left the company to make up his own. Before he
>> left he promised to keep his VB6 SQLite wrapper up to date and make
>> further enhancements, but in effect he never did. In fact, he simply
>> "disappeared". Never heard from him again.
>
> Yes, he really did disappear, didn't he! I tried earlier to find any
> reference to his existence, but apart from a few contributions to
> message forums he couldn't be found, and certainly not in conjunction
> with the story on SQLiteDB, which was my reason for trying to find
> him. Not because I had already committed to SQLiteDB as some people
> did, but purely as a matter of interest.
>
>> For further info some links to relevant (very old) TerraInformatica
>> forum threads:
>>
>> SQLiteDb / SQLiteDbServer future
>> <http://terrainformatica.com/forums/topic.php?...
>>
>> SQLiteDB replacements (recommends Olaf Schmidts solution)
>> <http://terrainformatica.com/forums/topic.php?...
>
> I believe I have read all those, possibly ending up on them through
> various circuitous routes.
>
> In the last three hours I've also been looking at MySQL, which also
> does full-text searching, and the product seems far more rounded than
> SQLite. However, the only link to it from "the outside", i.e. VB6 in
> my case, is through some sort of ODBC connector, which I haven't used
> for years. But I suppose it must work... ;)
>
> MM

ADODB works very well with MySQL!

--
Garry

Free usenet access at http://www.eternal-sep...
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Schmidt

8/24/2011 4:37:00 AM

Am 23.08.2011 23:03, schrieb GS:

>> In the last three hours I've also been looking at MySQL,
>> which also does full-text searching, and the product seems
>> far more rounded than SQLite.
May I ask, in what regard? ;-)
No offense of course, but please take a look at the
"feature-comparison-list" I've tried to put together
further below...

>> However, the only link to it from "the outside", i.e. VB6 in
>> my case, is through some sort of ODBC connector, which I haven't
>> used for years. But I suppose it must work... ;)

Yep, the latest MySQL-Version 5.5.15 (just downloaded
the 32Bit Version from):
http://www.mysql.com/downlo...
does not work directly with the somewhat outdated
MyOleDB-Driver (Version 3.9.6) anymore.
You will have to download the matching 32Bit-ODBC-Driver
from:
http://www.mysql.com/downloads/conne...

After the appropriate setup-dialogues (please use
the setting 'InnoDB', in case you want transactions),
followed by the installation of the ODBC-Driver,
followed by the import of the NorthWind.sql
File over the commandline-tool ... download from:

>https://github.com/subsonic/SubSonic-3.0/blob/master/DbScripts/Northwind_Schema_Data...
(please right-click on the 'raw'-Link and download to a file...)

I was then able, to use the following connection-string
against the northwind-DB:

Set Cnn = New ADODB.Connection
Cnn.ConnectionString = "Driver={MySQL ODBC 5.1 Driver};" & _
"Server=localhost;" & _
"Database=NorthWind;" & _
"User=root;" & _
"Password=asdf;" & _
"Option=3;"
Cnn.CursorLocation = adUseClient
Cnn.Open

> ADODB works very well with MySQL!

It certainly works ... whether well enough (compared
with SQLite) one has to decide on his own.

FWIW, here some results using identical and indexed NWind-
DBs in local Mode for RC4/SQLite, ADO/JET, ADO/MySQL...
(using the Forms of the NWind-SQLite-Demo, which
do have appropriate comparison-routines already,
simply by changing the ADO/JET NWind-Connection
against the MySQL-Connection-String shown above).

Read-Direction:
--------------------------------------------------
Full-Table-Selects:
"Select * From Orders" (830 Records, 14 Columns)
SQLite: 2.7ms JET: 3.9ms MySQL: 8.9ms

"Select * From [Order Details]" (2155 Records, 5 Columns)
SQLite: 2.1ms JET: 3.5ms MySQL: 11.4ms

Filtered and Joined Selects from complex View-Definitions:
"Select * From Invoices" (2155 Records, 26 Columns)
SQLite: 14.8ms JET: 49.7ms MySQL: 58.5ms

"Select * From [Category Sales for 1995]" (8 Records, 5 Columns)
SQLite: 5.2ms JET: 9.5ms MySQL: 13.6ms

"Select * From [Order SubTotals]" (830 Records, 2 Columns)
SQLite: 3.2ms JET: 11.9ms MySQL: 7.9ms

"Select * From [Sales by Category]" (77 Records, 4 Columns)
SQLite: 5.9ms JET: 10.2ms MySQL: 13.9ms

Write-Direction:
--------------------------------------------------
Insert of 50000 Records (6 different Column-Types)
within a transaction, using Command-Objects:

SQLite: 0.18sec(no typo) JET: 4.93sec MySQL:8.65sec

Ok, that much for a little (and of course incomplete)
performance-test against a smaller DB.

All measured on a pretty fast PC, but the relations
become obvious I'd think ... and as said, running locally
(against an SSD, so you can neglect the influence
of the HardDisk somewhat more - it's more or
less the pure performance of the DB-Engines +
the latency of the involved transport-layers,
we see here).
Maybe MySQL would be a bit faster, when
used directly over the C-API-ClientLib -
but as we can see for example from the View:
[Category Sales for 1995], which only delivers
a small 8x5 resultset (so, not that much to transport
over the "App-Connector-Layer", but behind the scenes
performing a "filtering Group By aggregate" with
enough engine-stress nonetheless), the performance-
gain from switching to a more direct "Client-Layer"-
binding will perhaps not be that large as one might
expect.

Feel free, to send me larger MySQL-DBs, in case
you want to see comparisons against SQLite
using your own, "special, complex View of choice".

As a conclusion and a feature-summary/-comparison:

Deployment:
RC4-based SQLite:
- 3 Dlls, about 1.5MB LZMA-compressed
- Dialogue-free Setup without any User-Interaction
- regfree usage possible

MySQL + ODBC
- about 28MB for the engine + 2.5MB ODBC in msi-setups
- setup usually requires Dialogue-interaction
- regfree usage not possible

Performance:
- SQLite about 2-3times faster in Read-Performance
- SQLite about 40 times faster with transacted bulk-inserts

InMemory-DBs:
- full support in SQLite (no traces on Disk)
- MySQL stores at least the TableDefs on Disk

Transactions:
- SQLite yes
- MySQL yes (needs InnoDB instead of MyISAM)

Nested Transactions:
- supported in SQLite over SavePoints
- supported in MySQL over SavePoints as well (needs InnoDB)

Builtin VB-Function-Set (Mid$, Instr, DatePart, CDbl, CDate, IIf etc..):
- SQLite yes (provided by the RC4-Wrapper)
- MySQL no

Userdefinable Functions per VB6-Code:
- SQLite yes
- MySQL no

Userdefinable Collations per VB6-Code:
- SQLite yes
- MySQL no

Single-File-DB:
- SQLite yes
- MySQL no (Folder-based approach with single-file-tables)

Fully transparent DB-encryption (covering the whole DB):
- SQLite yes
- MySQL no, only at Column/Function-Level

FullText-Search:
- SQLite yes
- MySQL yes

Unicode-Support:
- SQLite yes
- MySQL yes

Definable/Storable Triggers and Views:
- SQLite yes
- MySQL yes

Stored Procedures:
- SQLite only indirectly, over special Trigger-Definitions
- MySQL yes (although somewhat limited)

Multiuser-capabilities:
- SQLite: concurrent reads - queued Writes (single channel)
- MySQL: concurrent reads and writes

Multiuser-Network-Share-Mode with appropriate locking:
- SQLite yes
- MySQL no

Multiuser-Server-Mode:
- SQLite indirectly (over RC4-builtin RPC-Server - very fast)
- MySQL yes

User-and-Permission-Support:
- SQLite only indirectly (over RC4-builtin RPC-Server)
- MySQL yes

Popularity and diffusion-rate
- SQLite: quasi-industry-standard for all kind of Personal-
or Desktop-Apps: http://www.sqlite.org/mostdep...
- MySQL: perhaps the most popular "Server-Database" -
widely deployed, preinstalled and common on most Web-
servers (LAMP hosting-offers, etc.)

Ok, I hope that covers quite a range of questions
for "let's leave MS-DBs behind"-Newbies <g>.

As for tools (DB-Administration)- there's quite
a lot of free stuff for both of them ...

List of Management-Tools for SQLite:
http://www.sqlite.org/cvstrac/wiki?p=Manag...
Don't have a favorit here, rarely use such tools,
but "SQLite2009 Pro Enterprise Manager" is FreeWare -
and AFAIK the only tool, which offers support for
the DB-Encryption-Mode of my wrapper (there are
different encryption-implementations for SQLite,
and my wrapper shares the C-Code-Encryption-Module
and behaviour with at least the .NET-Wrapper).

Here the "official tool" for MySQL:
http://dev.mysql.com/downloads/...

Olaf

(Mike Mitchell)

8/24/2011 5:30:00 AM

On Tue, 23 Aug 2011 17:03:22 -0400, GS <gs@somewhere.net> wrote:

>After serious thinking MM wrote :
>> On Tue, 23 Aug 2011 21:11:36 +0200, Ulrich Korndoerfer
>> <ulrich_wants_nospam@prosource.de> wrote:
>>
>>> MM schrieb:
>>>
>>>> On Tue, 23 Aug 2011 18:22:16 +0200, Schmidt <sss@online.de> wrote:
>>>>
>>>>> Am 23.08.2011 13:21, schrieb MM:
>>>>>> Over the past few days I have been reviewing variouswrappers for VB6
>>>>>> to interface to an SQLite database. My particular interest is
>>>>>> full-text searching. Apart from dhRichClient3 (free) and SQLitePlus
>>>>>> (not free) there don't seem to be any current.
>>>>> Aside from, that the "most current" is vbRichClient4, there's
>>>>> perhaps no other "currently maintained" COM-Wrappers for SQLite
>>>>> (there was for some time an additional one, from TerraInformatica).
>>>>
>>>> Yes, I tried SQLiteDB from the Wayback Machine, just to see what would
>>>> happen, but it barfed at around 1,490,000 records. Anyway, it seems to
>>>> have completely disappeared, no idea what happened.
>>>> ...
>>>
>>> The developer of SQLiteDB (then an employee of TerraInformatica, George
>>> Ionescu) for some reasons left the company to make up his own. Before he
>>> left he promised to keep his VB6 SQLite wrapper up to date and make
>>> further enhancements, but in effect he never did. In fact, he simply
>>> "disappeared". Never heard from him again.
>>
>> Yes, he really did disappear, didn't he! I tried earlier to find any
>> reference to his existence, but apart from a few contributions to
>> message forums he couldn't be found, and certainly not in conjunction
>> with the story on SQLiteDB, which was my reason for trying to find
>> him. Not because I had already committed to SQLiteDB as some people
>> did, but purely as a matter of interest.
>>
>>> For further info some links to relevant (very old) TerraInformatica
>>> forum threads:
>>>
>>> SQLiteDb / SQLiteDbServer future
>>> <http://terrainformatica.com/forums/topic.php?...
>>>
>>> SQLiteDB replacements (recommends Olaf Schmidts solution)
>>> <http://terrainformatica.com/forums/topic.php?...
>>
>> I believe I have read all those, possibly ending up on them through
>> various circuitous routes.
>>
>> In the last three hours I've also been looking at MySQL, which also
>> does full-text searching, and the product seems far more rounded than
>> SQLite. However, the only link to it from "the outside", i.e. VB6 in
>> my case, is through some sort of ODBC connector, which I haven't used
>> for years. But I suppose it must work... ;)
>>
>> MM
>
>ADODB works very well with MySQL!

Via the ODBC connector?

MM

(Mike Mitchell)

8/24/2011 6:42:00 AM

On Wed, 24 Aug 2011 06:36:33 +0200, Schmidt <sss@online.de> wrote:

>Am 23.08.2011 23:03, schrieb GS:
>
>>> In the last three hours I've also been looking at MySQL,
>>> which also does full-text searching, and the product seems
>>> far more rounded than SQLite.
>May I ask, in what regard? ;-)
>No offense of course, but please take a look at the
>"feature-comparison-list" I've tried to put together
>further below...

Thanks for the comparisons. It seems more rounded because there's just
a heck of a lot more documentation for MySQL than for SQLite. For
example, on the Amazon.co.uk web site in the "Books" category I
counted about seven books on SQLite. When I punch in the word "MySQL"
I get over 20 hits (I stopped counting after about fifty titles, as
some of them may have been duplicated by earlier/later editions). So
this tells me that there is far more interest worldwide in MySQL than
in SQLite.

The next thing is the fact that there IS an ODBC wrapper for MySQL
that can be used with VB6. As far as SQLite is concerned, the SQLite
people don't seem interested in making their product accessible for
classic VB developers! I have looked in detail at their documentation,
but VB just doesn't feature on their horizon. There's stuff for C/C++
programmers, even for .Net'ers, but that's no use to me. (Although I
did ask myself whether I could write an interface to hook VB via C,
which I once did, though for VB3, and although C is a horrible
language I managed to glean enough to build a dll that VB
'understood'. Something to do with CDECL as I vaguely recall; it was
years ago now.)

Then the MySQL web site itself contains far more documentation, white
papers, even a blog, than the SQLite web site does. Type in "Visual
Basic" in the Search field on the MySQL site and you get a number of
hits, though I haven't had time to check all of them. For example,
http://dev.mysql.com/doc/refman/5.0/fr/ado--rs-a... takes me to
a very clear example of ADO, rs.addNew, rs.delete and rs.update
(albeit in French; I expect there's an English rendition available
somewhere).

Much can be gleaned from documentation, even if it isn't directly
applicable to Visual Basic or is in a different language, especially
given the accessibilty via Google Translate, which is good enough now
to get the gist.

Sorry, but I am just a "documentation" kind of guy! I translated most
of the Ford-Werke workshop manuals and owner handbooks into German
back in the 1970s (now enjoying a rewarding retirement thanks in part
to the Deutsche Rentenversicherung - vielen Dank!) I live and breathe
paper! My house (I live alone) has stacks of paper everywhere! I
cannot move for paper! I love the stuff. Dunno why, because when I
left school I was really interested (still am) in mechanical things
and did a seven-year apprenticeship as a motor fitter, which is what
eventually got me my first job at Ford-Werke.

So... documentation attracts me like a moth to a flame. As soon as I
see some I think, hmmm, very in-ter-es-ting! (strokes white cat...)

Finally, I've looked at the time test results below, but there's one
test that is missing: full-text search. The ONLY reason I am
interested in anything other than an Access mdb is for the full-text
searching capability that Access doesn't provide. When retrieving data
via a SELECT.... I don't really care whether it takes (SQLite:) 2.1ms,
(JET:) 3.5ms, or (MySQL:) 11.4ms.

********************************************************************************
But what I *cannot* do with an Access mdb is **fast** full-text search
over 2 million records on a text field of length 250 each containing a
number of words or numbers separated by spaces.
********************************************************************************

(I placed the above in *** to emphasise this requirement!)

If Access could do fast full-text search, I'd much rather use an mdb.
I feel at home with an mdb. An mdb for me, especially since the advent
of ADO, is like a nice pair of comfy socks.

Which is why I started searching, off and on, a year or more ago for
alternatives, which led me first to MySQL and then to SQLite.

***However!*** A rather large fly in the ointment may be MySQL's
inability to be run in reg-free mode! I've noted what you say about
that, because reg-free is important to me. But still, I shall probably
carry on experimenting with MySQL as well as SQLite. I don't have any
deadline. I'm only writing freeware to keep my brain in gear and,
well, because writing code is good fun! And keeps me from tidying away
all those stacks of paper (some dating back to the 1980s...)

I could 'reinvent the wheel' and write my own binary search 'add-on'
for an Access mdb! I have often wondered what kind of performance I
might achieve. For example, as I add data to the mdb, using rs.AddNew
etc, I could parse the text string, filter out stop (noise) words,
deal with duplicates, then whack the words into a sorted list of some
kind. At retrieval stage I would use the sorted list and a binary
search to pinpoint relevant records in the mdb, from which I would
retrieve the actual data. Not as fast as dhSQLite, I'm sure, not by
any means, but a darned sight faster than the way Access can do it on
its own. And nothing extra for reg-free to worry about!

MM

GS

8/24/2011 2:02:00 PM

Olaf,
That's a fantastic report! Thanks so much...

You already sold me on SQLite a long time ago when I first started
using your dhRC. Hands down, SQLite is THE way to go IMO!

--
Garry

Free usenet access at http://www.eternal-sep...
ClassicVB Users Regroup! comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

Olaf, any further forward on dhSQLite documentation?

(Mike Mitchell)

Schmidt

GS

(Mike Mitchell)

Ulrich Korndoerfer

(Mike Mitchell)

GS

Schmidt

(Mike Mitchell)

(Mike Mitchell)

GS

x Login to ForumsZone