Flat file database and the number of records per file.

[IliaPreston]

I am developing a database program and for some reason it cannot be based on SQL.
Rather, it needs to be based on flat files.
For each entity for which I have to keep track of data, I declare a record type:

public type Something
...
...
End Type

Dim MyRec     as Something

And then I create a random access file like this:

      MyFilePath = "C:\somefolder ... \MyRecFile.DAT"
      FNr = FreeFile
      Open MyFilePath For Random As FNr Len = RecLen
         Put #FNr, 1, MyRec
      Close #FNr

Now, the issue is that, potentially, there could be millions of records, maybe tens of millions, or even more.
With a system like the above, all tens of millions of records will be stored in a single file (in the above example "MyRecFile.DAT").
Is this SINGLE FILE approach a good way of dealing with this situation or maybe it is better to break it down into multiple files?
What I am thinking is that I can for example set a limit of 100,000 records (or whatever number that is best) per file, and produce numbered files like:

MyRecFile1.DAT
MyRecFile2.DAT
MyRecFile3.DAT
...
and so on and so forth
...

so that if a new record is to be inserted into the N'th file and that N'th file already has 100,000 record, the program would instead create an N+1'th file, and insert the new record into that new file.
I am not sure which approach is better, and that is why I am asking.
Please note that there will be a lot of activities on this database, for example updating some records, searching, etc.
If a single file grows to tens of millions of records or even more, won't it be a problem to handle all kinds of updates and other activities?
Which approach is better; a single file, or a growing number of multiple files?
And if multiple files is better, what should the number of records per file be?
Please advise.
Thanks.

[Schmidt]

If a single file grows to tens of millions of records or even more, won't it be a problem to handle all kinds of updates and other activities?

Sure.
Aside from the problem of managing such a thing without introducing a layer of indirection (a paging-concept),
there'd be also the problems:
- how to keep your indexes "in sync" (in case you plan to support them)
- how to manage concurrency (multiple processes/threads who try to access the file in parallel)
- how to manage transactions (related to the above point, but the ability to "rollback" also comes into play)
- and of course some way, to allow a User to define his own "UDTs" (possibly with variable lenght members)
- and in addition, to offer some API for your Users, to fomulate Search-Queries, Insert- and Update-Queries in a flexible way

Which approach is better; a single file, or a growing number of multiple files?

Multiple (smaller) Files introduce quite some complexity with regards to their Handling -
but offer performance-advantages over a single (large) file. Basically they try to mimick
a true "paging-concept" (which in turn could be implemented in a single file again).

And if multiple files is better, what should the number of records per file be?

That depends on the usage-scenario of the DB, how often it gets updated, if it's many
smaller Records which need to be handled or larger ones, etc.
There's a trade-of... the smaller the "Chunks", the faster (small Record)-Updates or Deletes
will become - *but* the Handling of "very many small chunks" will again require more efforts
(CPU-Cycle-wise) in your "Page-Manager" (when compared to "less, but larger chunks").


This page-handling ("the Pager") is basically a "Mini-DB" in itself - it's the "first stage"
any request has to go through (that's what I meant with "layer of indirection" above).

You want to reach the 10-Millionth-record in a given Table?
- Ask the Pager first (handing over Table-ID and RecordID or -Index)
- Pager will answer with a returned Chunk (a Page + an ByteOffset on that Page), where your Record starts

Please advise.

Seriously, use an existing DB instead of trying to write your own one.
(you will need man-years to come up with something as well-tested and stable as the existing alternatives).

If it's about the problem that JET-DBs can't handle Files larger than 2GB (or 4GB), SQLite will happily
handle Files far above that (with Billions of Records) - all in a single File with exceptional performance.


Olaf

[NeedSomeAnswers]

Yeah developing a flat file solution for that many records is a bad idea!!

Also is this going to be multi user? because if so just forget it immediately.

Many years ago i was once asked to development a small VB6 flat file system as the company i worked for only used Oracle database and they wouldn't give us one for such a small project, they also banned anyone from using Access.

The system was sooooo slow. With multi-users connected it was glacial. In the end they didn't even role the project out as the users said it was unusable.

Now i inherited an initial design so it could be done better than in my system, but even so to make anything workable they are essentially asking you to design a database system anyway.

Doing that would as Olaf said take you years to create something good. There is a reason everyone uses databases when working with large data sets.

If it were me i would be going back to the person asking me to do this and giving them an estimate of 3 years, and watch them chock on there sandwich while you tell them.

[szlamany]

Increasing the number of files does not make things simpler.

All data in one file - each time you get a new record put it at the end.

You DO need another file for the INDEX to this data - let's say some ID or NAME. You simply keep that data in a sorted list in that INDEX file. By keeping buffers 50% filled you have room for new INSERT's alphabetically without recreating the whole index each time.

You can have as many INDEX files as you need.

When you run a sort for a report you create a KEY pointer file - with all the REC#'s that were "selected" in some "sorted" order.

This is how we did it in the 80's on mainframe/mini computers.

Not difficult at all.

[NeedSomeAnswers]

out of interest did they say why it cannot be based on SQL??

[NeedSomeAnswers]

This is how we did it in the 80's on mainframe/mini computers.

Not difficult at all.

So are you saying you are advocating a flat file approach using VB6 on a PC ?

[szlamany]

I am developing a database program and for some reason it cannot be based on SQL.
Rather, it needs to be based on flat files.

I'm not advocating it - I am answering the OP's question.

They didn't give us a reason - just said it cannot be based on SQL.

[Schmidt]

I'm not advocating it - I am answering the OP's question.

Not really, no.

It would have been fine, when the OP had stated, that this solution is only for
- AddNewRecords
- otherwise only Reads are allowed
...purposes.

Then it would have been as "simple" as you tried to suggest.
As soon as Indexes (Index-Files) come into play, it already starts to get "far less simple".

But the OP did mention additional requirements too:
- "Please note that there will be a lot of activities on this database, for example updating some records, searching, etc."

And the part "updating some records" alone, will make the simplicity of the original flat-file-approach
already fall apart, especially when there's "a lot of activities on this database".

We had this discussion already in the last 200 postings over in the "What if there was a new VB6"-thread.
How can we "regulars" be more helpful to Newbies in the "VB-communities knowledge-gathering-compartment"?

Do we "give them what they want" (already seeing, that they will hit a brick-wall soon, when proceeding in that way)?
Or do we try to give advice, to "change the path early" (to something else) which we know will not lead into a dead-end?

From what the OP wrote in his additional requirements, he's heading right into a dead-end with his self-managed flatfile-approach

... it cannot be based on SQL.

And that statement does not make any sense in the given context ...

...directed more @ the OP
(sorry Ilia, but I think we talk about the "storage-engine" here).

SQL is not a "data-format for storage" - it's a programming language for queries.

If you're well versed enough, you can introduce SQL (*after* an invested man-year) atop of a then well-tested:
"concurrency-hardened, one flat-file per Table, one flat-file per Index"-storage-engine of course ...
(I mean, there are and were enough Engines out there, which did just that -
dBase being one of the first and most well-known among them).

If it's more for your own educational purposes, to come up with a well-working flat-file-
approach (to understand better, how such record-based-storage-engines work underneath),
I won't suggest any coding - but reading a book about that topic instead.

But if it's in development due to a customer-request, I'd use an existing DB-Engine, instead of
re-inventing the wheel (in probably worse performance).

Olaf

[Schmidt]

I'm not sure, why these old-fashioned flat-file-approaches are so attractive to Newbies...

I think it has to do with the parts of:
- "How do I create a new DB-File instead of a Flat-File"
- followed by: "How do I create a Table instead of simply defining a UDT"

Well, here's a comparison between the allegedly simpler "UDT-Record-based Flat-File" vs "using a DB"-approach,
which addresses (and shows the differences) between the two problems above.

FlatFile-approach (creation-part) - all code in a Form:

Option Explicit

Private Type PersonsData
  ID As Long
  Name As String * 50
  DateBorn As Date
End Type

Private FileHandle As Long

Private Sub Form_Load()
  OpenOrCreateFile "c:\temp\MyFile.dat"
End Sub

Private Sub OpenOrCreateFile(FilePath As String)
Dim FNr As Long, Record As PersonsData
    FNr = FreeFile
    Open FilePath For Random As FNr Len = Len(Record)
  FileHandle = FNr
End Sub

The above covers the creation- and opening-related Parts for the FlatFile-approach.

Now to the DB-File approach, covering the same thing (Type-Definition and opening):
(here using SQLite as an example, which officially states in its documentation, that it understands itself
as a simple replacement for the C-Runtimes "fileopen"-call - Project needs a reference to vbRichClient5):

Option Explicit
 
Private DBHandle As cConnection

Private Sub Form_Load()
  OpenOrCreateFileDB "c:\temp\MyFile.db3"
End Sub
 
Private Sub OpenOrCreateFileDB(FilePath As String)
  If New_c.FSO.FileExists(FilePath) Then 'it already exists, so just open it
    Set DBHandle = New_c.Connection(FilePath, DBOpenFromFile)
    
  Else 'we open a new DBFile and apply the "Table-Type"
    Set DBHandle = New_c.Connection(FilePath, DBCreateNewFileDB)
    
    With DBHandle.NewFieldDefs
      .Add "ID Integer"
      .Add "Name Text(50)"
      .Add "DateBorn Date"
    End With
    DBHandle.CreateTable "PersonsData"
  End If
End Sub

So, instead of a FileHandle (of Type Long), we now have a DBHandle (of Type cConnection),
but otherwise the two Code-Blocks are quite similar...
Note that the "UDT-TypeDef" is now simply moved a little bit further down in the Code,
becoming a Table-Def instead (I've marked these parts magenta).

Ok, what now remains is the comparison of some "Actions" on our two Forms...

Here the version for the FlatFile (doing two Inserts in Form_Click - also ensuring HandleClosing in Form_Unload):

Private Sub Form_Click()
  InsertNewRecord 1, "Person1", "1991-01-01"
  InsertNewRecord 2, "Person2", "1992-02-02"
  Caption = GetRecordCount
End Sub

Private Sub InsertNewRecord(ByVal ID As Long, Name As String, ByVal DateBorn As Date)
  Dim Record As PersonsData
  With Record
    .ID = ID
    .Name = Name
    .DateBorn = DateBorn
  End With
  If FileHandle Then Put FileHandle, GetRecordCount + 1, Record
End Sub

Private Function GetRecordCount() As Long
  Dim Record As PersonsData
  If FileHandle Then GetRecordCount = LOF(FileHandle) \ Len(Record)
End Function

Private Sub Form_Unload(Cancel As Integer)
  If FileHandle Then Close FileHandle: FileHandle = 0
End Sub

And here the DBHandle-based approach, doing the same thing:

Private Sub Form_Click()
  InsertNewRecord 1, "Person1", "1991-01-01"
  InsertNewRecord 2, "Person2", "1992-02-02"
  Caption = GetRecordCount
End Sub

Private Sub InsertNewRecord(ByVal ID As Long, Name As String, ByVal DateBorn As Date)
  With DBHandle.OpenRecordset("Select * From PersonsData Where 1=0")
    .AddNew
        !ID = ID
        !Name = Name
        !DateBorn = DateBorn
    .UpdateBatch
  End With
End Sub

Private Function GetRecordCount() As Long
  GetRecordCount = DBHandle.OpenRecordset("Select Count(*) From PersonsData")(0)
End Function

Private Sub Form_Unload(Cancel As Integer)
  Set DBHandle = Nothing
End Sub

I've marked the similar parts again magenta.

So, using a DB-File instead of a FlatFile is hardly "more difficult" to handle or understand.

It basically differs only with regards, where "TypeDefs have to be made" - but is otherwise
roughly the same. All the additional advantages are immediately available with the DB-based
approach as well (indexing, handling unique-values - filtering of "SubSets of Records" etc.).

Olaf

[szlamany]

That is a lot of words.

We need the OP to come back to clarify some things here...

[IliaPreston]

Wow! I am really thankful to all of you who posted great comments and advice on this issue.
And, in view of all the explanations that you provided in here, I have to admit that the approach should change towards an SQL-based system.
However, there are a number of concerns that need to be addressed.
The main concern is how much disk space is going to be wasted by a DB system such as SQL Express?
This project is very simple, with not so many tables (probably less than 15 tables), with the only complexity being that the number of records in a few of tables can grow to tens of millions of records or even more.
As with a flat file system, the only disk space that will be used is for the data itself (plus indexes).
But, I am not sure about SQL.
So, here are the questions:

1. How much disk space is taken to install SQL Express?
2. How much disk space is taken by creating an empty SQL Express database?
3. On a go-forward basis, how much disk space is taken by inserting a new record (aside from the size of that record and corresponding additional bits of data for indexing purposes)?

Please note that from the perspective of minimizing disk space, the flat file approach has an indisputable advantage:

1. Initial installation? Not required. Takes zero disk space.
2. Creating an empty database? It amounts to creating a number of empty flat files. Takes almost zero disk space.
3. Inserting a new record? It takes only as much disk space as the size of the record plus a small fraction of that for indexing.

So, with the disk space consumption being of a concern, I need to understand the three questions above, and I appreciate your comments on that.
Please advise.
Regards.

[Carlos Rocha]

I don't know about SQL Express, but it should take A LOT of space and resources.
But SQLite is another story: The needed dll's take < 6 Mb (Olaf's vbRichClient5), the empty database just a few Kb (not sure why is this important, as it will grow up with data), and you'll have a big advantage when filled with data, because, for strings, SQLite only use the space needed to store the data (if you define, lets say, a field to the Name with 40 characters but only use 10, SQLite will only use space for 10 characters).
Just my 2cents

[szlamany]

Those questions really need to be answered by yourself after doing exactly those things.

SQL has lots of design issues that must be overcome - and some of those are all about taking your disk space.

When a record is inserted into a table that REQUEST to INSERT is placed at the end of what amounts to really just a long text log file.

If records are read from that table just after that REQUEST to INSERT then that DATA might come from the TABLE itself or it might come from the LOG file (as long as the request to insert was committed).

At some point the record will be migrated from the LOG to the DATA space (all kinds of lazy write logic going on here).

This log is highly tied to backups and the ability to do a point-in-time restore. It can grow to GIGABYTES of space if not properly managed.

As for indexes they are intentionally left very "partially filled" so that new entries can find the proper home without rebuilding the index constantly. Disk space is not the first priority here - it's the ability to get new entries in quickly.

All these partial buffer settings can be managed by you - it might take an expert to make them do what you seem to want...

If you want to know the size of MS SQL Express as far as install space that info must be available at the MS site.

There used to be a thing called SQL Compact - which might be called LocalDB. This might be smaller footprint - not sure about the millions of rows... It is a MS product.

[Schmidt]

1. How much disk space is taken to install SQL Express?
2. How much disk space is taken by creating an empty SQL Express database?
3. On a go-forward basis, how much disk space is taken by inserting a new record (aside from the size of that record and corresponding additional bits of data for indexing purposes)?

The download for the smallest package of SQLServer-Express (2012 - 'LocalDb-mode') is about 30MB compressed
(on your disk probably expanding to roughly 100MB or something then).
The packages for different versions of SQLServer-Express-2012 are obtainable here for example:
https://www.microsoft.com/en-us/down....aspx?id=29062
Smallest msi-package is the one, labelled with: ENU\x86\SqlLocaLDB.MSI

Please note that from the perspective of minimizing disk space, the flat file approach has an indisputable advantage:

1. Initial installation? Not required. Takes zero disk space.
2. Creating an empty database? It amounts to creating a number of empty flat files. Takes almost zero disk space.
3. Inserting a new record? It takes only as much disk space as the size of the record plus a small fraction of that for indexing.

So, with the disk space consumption being of a concern, I need to understand the three questions above, and I appreciate your comments on that.

I will address your points 1-3 (where you list the advantages of Flat-Files) with regards to
SQLite as the (IMO) better alternative for Flat-Files (compared with SQLServer-Express).

Points 1. and 2. played through, under the assumption you're using SQLite from the vbRichClient5-package.

Your point 1. (initial installation):
- Download about 2.3MB (then only one single Dll has to be registered in a path of your choice - vbRichClient5.dll)
- at the customer-side (when you finally ship your App) - no installation for vbRichClient5.dll is needed
.. (it can be used regfree, from a Bin-Subfolder below your App.Path)

Your point 2. (Creating an empty database):
- one line of code: Set Cnn = New_c.Connection(FilePath, DBCreateNewFileDB)
- the above call will result in an emtpy File on your disk (with Zero-ByteLength, just as with a Flat-file)
- all tables will end up in the same File (no need for "multiple flat-files per table")
- the total DB-Size of that single-file is basically "unlimited" (no 2, or 4, or 10GB-barrier exists)

Your point 3. (Inserting a new record, consumed space "on-disk" for data and indexes):
- that's one of the biggest advantages over your Flat-File approach
- SQLite stores String-Data (by default) as UTF8 - for english texts this means no difference from "ASCII-based storage"
- For Flat-Files (when they work UDT-based) you have to define String-Members with a fixed length
- SQLite stores Strings efficiently with an "adaptive length-indicator" (so the String: "ABC" will only need 4bytes on disk)
- SQLite stores Integers (up to 64Bit signed) using a similar mechanism
.. (small integer-values are stored in only one byte -a.s.o. up to the full range of "signed values" of 64Bit-integers)
- Same storage-efficiency for Index-storage

As already mentioned - SQLite *is* exactly the DB-Engine which was developed with the primary goal,
to be a (better) replacement for flat-files (then used primarily as an Apps-"local DB" which stores
Information not only more efficiently than Flat-Files, but also delivering comparable performance for
Inserts - and much better performance and comforts when SQL-based data-retrieval comes into play).

So, in my opinion it is the most logical "step-up" from your FlatFile (SQLServer-Express already being
a number or two "too big" for your purposes IMO).

If you don't believe what I wrote (especially in point 3.) I can prove that with a small Code-Snippet,
if you want (e.g. generating a 5GB-DB-File on Disk with Millions of Records - then performing fast
indexed queries against that data).

Olaf

[Schmidt]

SQL has lots of design issues that must be overcome - and some of those are all about taking your disk space.

When a record is inserted into a table that REQUEST to INSERT is placed at the end of what amounts to really just a long text log file.

If records are read from that table just after that REQUEST to INSERT then that DATA might come from the TABLE itself or it might come from the LOG file (as long as the request to insert was committed).

At some point the record will be migrated from the LOG to the DATA space (all kinds of lazy write login going on here).

This log is highly tied to backups and the ability to do a point-in-time restore. It can grow to GIGABYTES of space if not properly managed.

As for indexes they are intentionally left very "partially filled" so that new entries can find the proper home without rebuilding the index constantly. Disk space is not the first priority here - it's the ability to get new entries in quickly.

All of the above does not happen with SQLite (no Transaction-Log, if you don't explicitely request SQLites new "WAL-Mode",
no "Indexes wich intentionally leave gaps") - it is throughout more efficient (storage-wise) than Flat-Files.

I'm not trying to talk-down the MS-SQLServer here - it's (along with Excel and VB6) one of the really great products
MS has to offer (using it here at work - and in large customer-installations - it's reliable, fast and easy to work with).

But for a step-up from Flat-Files it is a bit "over-dimensioned", I'd say.

Olaf

[IliaPreston]

Thanks a lot for the help and advice.
Based on your explanations, I believe that SQLite is the product that I should use.
There are a few issues left:

1. Is SQLite free to download and install and distribute?
2. Can you please provide the link where I can download the standard version of SQLite? Should I also then register its dlls or anything, or the installation will automatically register?
3. I have a lot of experience with SQL programming (with Ms. SQL Server), but, it looks like SQLite is different. Is there a small example of VB6 program using SQLite?
4. I assume SQLite programming should be only slightly different from SQL programming. Is there a quick tutorial (so that an SQL programmer can use to learn SQLite programming)?
5. Is there something like a SQL server 7's Query Analyzer? (In later versions of SQL server it was contained within Management studio, but I don't need the whole Management studio. I might only need the Quary Analyzer and only if that is available)

Thanks for the help.

[Schmidt]

1. Is SQLite free to download and install and distribute?

SQLite is an OpenSource-Project which is in the Public-Domain.
Any kind of usage (commercial distribution included) is allowed.

2. Can you please provide the link where I can download the standard version of SQLite? Should I also then register its dlls or anything, or the installation will automatically register?

You don't want to use the standard-version of the library (since it is compiled with __cdecl calling-convention).
But since you asked - the official download-site is here: http://sqlite.org/download.html

For more comfort (intellisense etc.), you should use it over a COM-library - which
can either be ADO (in conjunction with an SQLite-ODBC-driver) - or the vbRichClient5.dll
(which is the fastest COM-wrapper for SQLite - and smaller than the ODBC-driver)

I've just uploaded a fresh (vb-friendly) compile of the latest SQLite-version (3.8.11.1)
(please google for the term vbRichClient5 to reach my site).

3. I have a lot of experience with SQL programming (with Ms. SQL Server), but, it looks like SQLite is different. Is there a small example of VB6 program using SQLite?

The SQL-dialect of SQLite is quite similar to JET or MS-SQL (e.g. the MySQL-syntax is a bit more "off").
And when you use it over the vbRichClient-lib, nearly the complete Set of "built-in VBA-functions"
is available too (like Instr(...), Format$(...), DateDiff(...), DatePart(...) etc.).

4. I assume SQLite programming should be only slightly different from SQL programming. Is there a quick tutorial (so that an SQL programmer can use to learn SQLite programming)?

A small (and working) code-snippet I've posted already in #9.

But this extended Demo-Package (including NWind.mdb-Importing to SQLite - and quite a few comparisons to ADO/JET),
should bring you up to speed: SQLiteDemos.zip
(besides the larger NWind-Demo- two smaller Demo-Folders are also included - one of them a very easy _Hello World)

Here's a ScreenShot, what the larger Demo contains (in several, relative simple Forms, which are startable from the small Entry-Form below):

5. Is there something like a SQL server 7's Query Analyzer? (In later versions of SQL server it was contained within Management studio, but I don't need the whole Management studio. I might only need the Quary Analyzer and only if that is available)

SQLite has a Query-Analyzing-Tool built-in - but due to its small size - it doesn't come
with a large "DB-Manager" (which visualizes things).

Instead many (most) informations can be retrieved over the SQL-querying-mechanism
(which delivers the results in normal Recordsets then).

E.g. here you see the Result of the special SQLite-command (which you can put in front
of any bad performing query you might want to analyze) -> EXPLAIN QUERY PLAN



What you see in the ScreenShot is the simple ViewEditor which comes with the NWind-Demo -
and I've applied the above Keywords "Explain Query Plan " in front of the relative complex
Invoices-Query (which contains quite a lot of Joins). The result shows, how SQLite will
resolve this Query - and what Indexes will be used along the way.

The SQLite-Website documents anything precisely, so you can read about this also here:
https://www.sqlite.org/eqp.html

There's a (much) more in-depth analysis-command (Explain), which is described here:
https://www.sqlite.org/lang_explain.html

As for "visual Tools" (like the MS-Enterprise-Manager for the SQL-Server) - there's quite
a lot of them - but I usually define my Table-Schemas as shown in posting #9 (per Code) -
will have to look-up first, which one is the currently preferred one in the SQLite-community.

(Maybe Carlos can help out in this regard - and give a recommendation).

Olaf

[Carlos Rocha]

(Maybe Carlos can help out in this regard - and give a recommendation).

I use SQLite Expert. The personal edition is all I need, but you can get the Professional for just a few bucks.

[IliaPreston]

Thanks for the help.
I downloaded vbRichClient5.dll and used it in a VB6 test program, and it is working for me.
Let me thank you a lot for the great work that you have done on this.
There are however a number of issues:

1. In your program UndoRedoDemo, you are opening/creating a database, but you don't close it. When I wrote a simple piece of code similar to that, and then in Windows Explorer I tried to delete the database file (in order to do a fresh test), it gave me an error to the effect that the file was in use. So, I closed the program before I could delete that file. So, how can I close the database in my VB6 code? The Cnn object does not have a CloseDB method.

2. In the same program, the database file, has the extension db3. Is that the standard extension for SQLite databases? If I use that extension, and somebody asks me why, I don't know what to answer. Is there any standard extension that all SQLite programmers use?

3. You have declared the CurRs variable with the WithEvents constraint and at form level.
I declared it in my procedure and without the WithEvents constraint:

Private Sub Command19_Click()
   Dim DBName           As String
   Dim CurRs            As cRecordset

   ... The rest of the code

End Sub

And it worked for me with no problem.
Am I missing something?
If something works with a missing component it is really strange. Can you please comment on this?

4. I created a table with a primary key

   sql = ""
   sql = sql & "create table Test2 ( "
   sql = sql & " TID  int  primary key, "
   sql = sql & " A  int  null, "
   sql = sql & " B  varchar(10)  null )"
   
   Cnn.Execute sql

Then when I tried to insert records with duplicate values into that field (TID), it gave me an error, which is fine, and means that the primary key is enforced (good).
But when in another test, I insert records into that same table, populating other fields, and not populating that TID field, the records are successfully inserted, which is not right, because the primary key is not enforced in this case. I don't understand why. Is there a reason?

5. When I read from the records that I have inserted, I loop through the records, moving from a record to the next, using MoveNext method:

   RC = CurRs.RecordCount
   
   If RC > 0 Then
      For i = 0 To RC - 1
         Res = Res & CurRs("TID") & vbTab & CurRs("A") & vbTab & CurRs("B") & vbCrLf
         CurRs.MoveNext
      Next i
   Else
   End If

I couldn't find a way to directly navigate to a given n'th record.
For example if I want to navigate to the 7th record without looping through all the records before that, how do I do so?

6. When I create a new (empty table) the database file is 16KB in size.
Adding another empty table adds another 16KB.
I am more than ready to waste 16 KB for each table (on top of the size of the actual data and indexes), but will the waste remain at 16 KB or will it also grow as the number of records grow to millions?
Is there any ballpark figure as to how much the maximum waste (excess file size that cannot be attributed to the actual data and indexes) will be?
I am ready to waste far more than 16 KB per table, but there is a limit too.
Should I waste 1 MB per table? 2 MB? 100 MB?
I need to have some sort of guess as to what the maximum waste is. Can you please comment on this?

Please advise.
Thanks again for this great product and the help and advice.

[IliaPreston]

Any response to my questions in my latest post above (post # 19) would be greatly appreciated.
Please advise.
Regards.

[Schmidt]

1. In your program UndoRedoDemo, you are opening/creating a database, but you don't close it. When I wrote a simple piece of code similar to that, and then in Windows Explorer I tried to delete the database file (in order to do a fresh test), it gave me an error to the effect that the file was in use. So, I closed the program before I could delete that file. So, how can I close the database in my VB6 code? The Cnn object does not have a CloseDB method.

It doesn't need one, since it cleans up all its "internal handles" nicely, as soon as the
Cnn Object goes out of scope (by setting it to Nothing).
(e.g. as you see it done in the Form_Unload Event in my post #5 here).

Cnn-Objects are (for a larger "App-wide" usable DB) defined as Global-Variables in a *.bas.
So, setting them to Nothing (somewhat earlier than in Form_Unload) will free their resources
any time you do a: Set globalCnn = Nothing (and the File in question is free again outside your App).

You can then "re-attach" a DB-File to a Connection-Variable anytime - by just reopen it over:
Set globalCnn = New_c.Connection(FileName)
in your App.

2. In the same program, the database file, has the extension db3. Is that the standard extension for SQLite databases? If I use that extension, and somebody asks me why, I don't know what to answer. Is there any standard extension that all SQLite programmers use?

Yes, *.db3 is acting as a kind of "default-extension" for SQLite-Databases (in version 3, which is the Major-Version-Prefix currently).
Ten years ago or so, there was another (older) SQLite-FileFormat (the major-version of SQLite being 2.xx) - and those DB-Files
usually got the ending *.db2 - but you're free to choose any ending you want.

3. You have declared the CurRs variable with the WithEvents constraint and at form level.
I declared it in my procedure and without the WithEvents constraint:

Private Sub Command19_Click()
   Dim DBName           As String
   Dim CurRs            As cRecordset

   ... The rest of the code

End Sub

And it worked for me with no problem.
Am I missing something?
If something works with a missing component it is really strange. Can you please comment on this?

Do you mean the:
Dim CurRs As cRecordset
you defined at Procedure-Scope?

When CurRs is sufficient there (at Procedure-local scope), then I probably made
a mistake, in defining it (unnecessarily) at "module- or Form-Level".

A cRecordset-Instance is an Object similar to an ADO-Rs (taking up Data from a Select) -
if you don't need the Data it "holds" internally somewhere else on your Form (or globally),
then defining it at Procedure-Scope is entirely enough.

BTW, same thing as said for the Cnn-Objects applies here - cRecordset-Instances also do
not have a Close-Method - if you don't need them any longer - just set them to nothing,
to free their internal Resources (that will happen automatically for Procedure-Scope-Definitions,
when the Procedure goes over "End Sub" or "End Function".

4. I created a table with a primary key

   sql = ""
   sql = sql & "create table Test2 ( "
   sql = sql & " TID  int  primary key, "
   sql = sql & " A  int  null, "
   sql = sql & " B  varchar(10)  null )"
   
   Cnn.Execute sql

Then when I tried to insert records with duplicate values into that field (TID), it gave me an error, which is fine, and means that the primary key is enforced (good).
But when in another test, I insert records into that same table, populating other fields, and not populating that TID field, the records are successfully inserted, which is not right, because the primary key is not enforced in this case. I don't understand why. Is there a reason?

An SQLite-Field-Definition of the Form:
"Create Table SomeTable (ID Integer Primary Key, ..."
has a special meaning, since being the Primary Key is not only enforcing its "uniqueness",
but also "AutoIncrement behaviour".
https://www.sqlite.org/autoinc.html

So, when you leave it out in an Insert-Operation, the Field will be updated anyways
to the next AutoIncrement-Value (which you can BTW read out immediately after you
called Rs.UpdateBatch on such a new Record, when you didn't gave it a unique ID yourself).

5. When I read from the records that I have inserted, I loop through the records, moving from a record to the next, using MoveNext method:

   RC = CurRs.RecordCount
   
   If RC > 0 Then
      For i = 0 To RC - 1
         Res = Res & CurRs("TID") & vbTab & CurRs("A") & vbTab & CurRs("B") & vbCrLf
         CurRs.MoveNext
      Next i
   Else
   End If

I couldn't find a way to directly navigate to a given n'th record.
For example if I want to navigate to the 7th record without looping through all the records before that, how do I do so?

How familiar are you with ADO-Recordsets?
The cRecordset-Type is quite similar (quite compatible) also in this regard,
since it also contains an: Rs.AbsolutePosition Property, which you can use,
to directly move the current Recordset-Pointer to a position between "1 to Rs.RecordCount".

But there's also another way of accessing Rs-Data...
Since Rs are quite "Table-Like", the cRecordset has an additional Method which is:
Rs.ValueMatrix(ZeroBasedRowIdx, ZeroBasedColIdx)

This Property is supported in Read- and Write-Direction - *and* it does *not*
influence the current Recordset-Pointer (the one you might have set with
Rs.AbsolutePosition or with one of the Rs.Move-Methods).

6. When I create a new (empty table) the database file is 16KB in size.
Adding another empty table adds another 16KB.
I am more than ready to waste 16 KB for each table (on top of the size of the actual data and indexes), but will the waste remain at 16 KB or will it also grow as the number of records grow to millions?
Is there any ballpark figure as to how much the maximum waste (excess file size that cannot be attributed to the actual data and indexes) will be?
I am ready to waste far more than 16 KB per table, but there is a limit too.
Should I waste 1 MB per table? 2 MB? 100 MB?
I need to have some sort of guess as to what the maximum waste is. Can you please comment on this?

As explained in my firts post into this thread, DB-Engines work "with Pages" -
and what you see there, is the allocation of "the first Page" (for a given Table) -
the increments on your DB-File-Size will happen in "Page-Size-Steps" - but only
when such a 16KB-Page is full, the Engine will "throw in the next Page" at the end
of your File - until this one is filled with Record-Data - and so on...

For "size taken up by 1Mio Records" - why not make a simple Test with a small Table
which is the only one in a small DB - then add 1Mio Records to it - and then look at the resulting
File-Size, after your 1Mio Records were added (how to do Mass-Inserts fast over a Command-Object,
is explained in a small TestForm in the larger "NWind-Demo-Folder").

Olaf

[IliaPreston]

Thanks for the help.
I am now ready to start developing my program using SQLite.
However, I have a two brief questions:
1. How to store a VB6 fixed-length byte array into an SQLite table?
Here is the current table:

   sql = ""
   sql = sql & "create table T1 ( "
   sql = sql & "TID   INTEGER Primary Key, "
   sql = sql & "STD_NAME    TEXT, "
   sql = sql & "STD_NUM     INTEGER )"

I also have a VB6 variable (a 100 byte array) like this:

   Dim Std_Data(0 To 99)     As Byte

I need to add a new column to my SQLite table definition above (T1) that would contain variables like the above VB6 variable (byte array of fixed size 100 bytes)
How can I do that?
And I currently store my VB6 values into the table by using "SetInt32" and "SetInt32" like this:

   sql = "Insert Into T1 Values (?, ?, ?)"
   Set Cmd = Cnn.CreateCommand(sql)
   
   Cnn.Synchronous = False
   Cnn.BeginTrans
      For i = 1 To 50000
         TempStr = "Johnson_" & i
         i1 = i
         i2 = i + 7
         
         Cmd.SetInt32 1, i1
         Cmd.SetText 2, TempStr
         Cmd.SetInt32 3, i2
         
         Cmd.Execute
      Next i
   Cnn.CommitTrans
   Cnn.Synchronous = True

How do I store my byte-array into the table?

2. If I have a column like this:

sql = sql & "TID   INTEGER Primary Key, "

I know that I must store unique values in it, but can those unique values be also contiguous (with no gap), or are gaps allowed as well?
When I test with values that have gaps amongst them, (for example 3, 6, 9, 12, 15, etc.) I don't face any problems, and everything appears to work.
However, it is better if I also run it by you to make sure.
Please advise.
Thanks.

[Carlos Rocha]

You can(should) define a BLOB column to store a byte array and use cnn.SetBlob to store it.
The INTEGER PRIMARY KEY assures that any new primary key is unique until it reaches the value 9223372036854775807, in which case an unused number will be used. You should be ok

EDIT: SQLite assigns a new unique INTEGER PRIMARY KEY if you don't choose a value (or choose NULL) to the respective column while creating a new row.

[Schmidt]

How do I store my byte-array into the table?

Carlos already mentioned it (thanks for helping out) - and just to give you
a fully working example (also with regards to the DataTypes which are supported)...

The Memory-DB-Support is quite handy to test stuff like that out (in a fresh VB6-Project):

Private Sub Form_Load()
  'InMemory-Connection, and Table-Creation (with all supported DataTypes)...
  Dim Cnn As cConnection
  Set Cnn = New_c.Connection(, DBCreateInMemory)
  With Cnn.NewFieldDefs
    .Add "ID  Integer Primary Key"
    .Add "Txt Text"      'can be any variable length
    .Add "Lng Integer"   'can be any signed int up to 64Bit (+-9223372036854775807)
    .Add "Dbl Double"
    .Add "Dt1 Date"      'a "long Date" (including the seconds)
    .Add "Dt2 ShortDate" 'a "short Date" (whole days only, excluding the seconds)
    .Add "Bol Boolean"
    .Add "Arr Blob"      'a Blob-Type for ByteArrays
  End With
  Cnn.CreateTable "T1"
  
  
  'now fast bulk-inserts
  Const D As Date = #1/1/2000 8:00:00 AM#
  Dim Cmd As cCommand, i As Long, B(0 To 99) As Byte: B(0) = 65: B(2) = 66: B(4) = 67
  Set Cmd = Cnn.CreateCommand("Insert Into T1 Values (?,?,?,?,?,?,?,?)")
  
  New_c.Timing True
    Cnn.Synchronous = False
    Cnn.BeginTrans
       For i = 1 To 50000
          Cmd.SetNull 1                   'we leave the AutoID-assignment to SQLite in this case (by specifying Null)
          Cmd.SetText 2, "SomeString " & i
          Cmd.SetInt32 3, i
          Cmd.SetDouble 4, i + 0.001
          Cmd.SetDate 5, D + i
          Cmd.SetShortDate 6, D + i
          Cmd.SetBoolean 7, (i Mod 2 = 0)
          Cmd.SetBlob 8, B
          
          Cmd.Execute
       Next i
    Cnn.CommitTrans
    Cnn.Synchronous = True
  Debug.Print vbLf; "Time for 50000 Inserts:"; New_c.Timing
  
  
  'finally Read-Out Time
  Debug.Print vbLf; "Read the Fields of the Record with ID 25000:"
  With Cnn.OpenRecordset("Select * From T1 Where ID = 25000")
    For i = 0 To .Fields.Count - 1
      With .Fields(i)
        Debug.Print .Name, TypeName(.Value), .ActualSize, .Value
      End With
    Next
  End With
End Sub

The above will print out in the immediate-window (note that the 100Bytes-ByteArray
got the first few "even bytes" at the indexes 0, 2, 4 set to represent the W-String "ABC" ...):

Time for 50000 Inserts: 232,94msec

Read the Fields of the Record with ID 25000:
ID            Long           4            25000 
Txt           String         16           SomeString 25000
Lng           Long           4            25000 
Dbl           Double         8            25000.001 
Dt1           Date           19           12.06.2068 08:00:00 
Dt2           Date           10           12.06.2068 
Bol           Boolean        1            True
Arr           Byte()         100          ABC

Olaf

[IliaPreston]

Thanks for the help.
Does this cmd.SetText take care of the following:
1. Replacing all the single quotes in the string with two single quotes.
2. Replacing all the double quotes in the string with two single quotes.
3. Adding a single quote to the beginning and another one to the end of the string?

What if I write a full query myself without that cmd.SetText?
For example in the following query:

      sql = ""
      sql = sql & "select CLIENT_SYSID from CLIENT"
      sql = sql & " where CLIENT_NAME = " & TextQuotes(Client_Name)

      Set Rs = Cnn.OpenRecordset(sql)
      Client_SysId = Rs.ValueMatrix(0, 0)

I have written a little function named TextQuotes as follows:

Private Function TextQuotes(ByVal InText As String) As String
   InText = Replace(InText, "'", "''")
   InText = Replace(InText, """", "''")
   TextQuotes = "'" & InText & "'"
End Function

Because as far as I know, each single quote and each double quote must be replaced with two single quotes, and then in the end the result be wrapped between two single quotes.
Can you please comment on this issue?

[techgnome]

IF you use parameters the way Olaf shows in post 24, then the quote issue becomes a non-issue... that's the point of parameters. You don't need to worry about string delimiters, date delimiters, etc... you just have to make sure if you need a string in the query, you pass a string.... or if you need a number, you pass a number, and so on.

-tg

[Schmidt]

... as far as I know, each single quote and each double quote must be replaced with two single quotes, and then in the end the result be wrapped between two single quotes.

Not sure where the requirement to treat double-quotes in the same way as single-quotes
originated - at least with SQLite (and afaik also with the JET-Engine and SQL-Server),
you will only need to take care about the single-quotes.

Here's a Code-Snippet which should help (there's a second type of Command-Object
for the Read-Direction: cSelectCommand):

Private Sub Form_Load()
  Dim Cnn As cConnection 'InMemory-Connection, and Table-Creation
  Set Cnn = New_c.Connection(, DBCreateInMemory)
      Cnn.Execute "Create Table T1(ID Integer Primary Key, Txt Text)"
 
  Const S As String = " O'Brien ""!"" " '<- our TestString
  Debug.Print "String-Content: "; S; vbLf

  Dim Cmd As cCommand, Rs As cRecordset
  Set Cmd = Cnn.CreateCommand("Insert Into T1(Txt) Values(?)")
      Cmd.SetText 1, S 'take the string as is (no special treatment needed)
      Cmd.Execute
  Set Rs = Cnn.OpenRecordset("Select * From T1")
  Debug.Print "TxtFld-Content: "; Rs!Txt
  
  Dim Sel As cSelectCommand '<- there's a second type of Command-Obj for Selects
  Set Sel = Cnn.CreateSelectCommand("Select * From T1 Where Txt=?")
      Sel.SetText 1, S 'take the string as is (no special treatment needed)
  Set Rs = Sel.Execute
  Debug.Print "TxtFld-Content: "; Rs!Txt
  
  'only in directly given SQL-Strings, a Helper-Func would make sense
  Set Rs = Cnn.OpenRecordset("Select * From T1 Where Txt=" & SingleQuotes(S))
  Debug.Print "TxtFld-Content: "; Rs!Txt; vbLf
End Sub

Private Function SingleQuotes(S As String) As String
  SingleQuotes = "'" & Replace(S, "'", "''") & "'"
End Function

The above code prints out:

String-Content:  O'Brien "!" 

TxtFld-Content:  O'Brien "!" 
TxtFld-Content:  O'Brien "!" 
TxtFld-Content:  O'Brien "!"

HTH

Olaf

[smileyoufu]

您好！Olaf！

————
https://www.vbrichclient.com/#/en/Demos/SQLite/
上面网站实际下载的Demo的版本是3.28.0
论坛中下载的版本也是3.28.0
Hello! Olaf！



————

https://www.vbrichclient.com/#/en/Demos/SQLite/

The version of Demo actually downloaded from the above website is 3.28.0

The version downloaded from the forum is also 3.28.0

Is the demo file not updated, or just the version number in the demo not updated

[Schmidt]

The version of Demo actually downloaded from the above website is 3.28.0

These Demos are VB-code-projects - and do not have "an SQLite-Version".

Instead when run, they reflect the SQLite-version from the vbRichClient5-package,
the User has installed on his machine (via the reference, currently set in these projects).

If you want to use a certain still RC5-based Demo-Project with RC6 then:
- switch the ProjectReference from vbRichClient5 to RC6
- and do a global replace on the VB-Source-Modules for the term [vbRichClient5] to RC6 as well
.. the latter is sometimes necessary, to change "fully qualified class-references" as e.g. vbRichClient5.cCollection

Olaf

[smileyoufu]

Hello, Olaf!

A screenshot of this post # 17 shows version 3.8.11.1
The Demo version actually downloaded from the above website is 3.28.0
The version downloaded from the forum is also 3.28.0

That's why we have the questions raised earlier.

After your explanation, I have understood the solution to the problem. Thank you for your help!