Convert binary data to a valid Unicode string whitout wasting (much) space ❓

[Niya]

I don't know why you guys are so afraid of DoEevents really. I make programs that seem to be multithreaded just by using DoEvents

I agree with wqweto on this one. DoEvents is truly evil. One of the last things I did when VB6 was my main language was a Winsock component to provide an auto-update feature for our main VB6 application. It had a GUI and I used DoEvents to fake asynchronous downloading so the UI could remain responsive. To this day, it was the worst experience I have ever had writing code. This thing would break constantly because of reentrancy issues and other issues caused by DoEvents. The entire program became an ugly patch work of spaghetti code from me frantically running around patching out errors. It got so bad that it reached a point where I absolutely could not make changes to the program because it was sure to interfere with one of the many patches I made to keep it from breaking. My work on this component was actually the specific event that led me into the arms of VB.Net.

The primary problem with DoEvents is that the application is still single threaded no matter how much I wanted to pretend it was multi-threaded. This led me to ways of writing the code as if it were multi-threaded which just doesn't work.

Unfortunately for me, back then there was no Olaf. I knew nothing of people like the trick. Even if I were on VBForums in those days, these guys didn't come here until much later so I would not have known then that it was possible to write multi-threaded applications in VB6. My only choice then was to move to .Net which offered what I wanted, true multi-threading.

Anyways, my advice would be to stop using DoEvents. It is the work of the devil! Use real multi-threading instead. I could totally see where wqweto is coming from because I have been there.

[Eduardo-]

Olaf, once again it has nothing to do with scanning files (I'm not using the database along with the scanning of files in any way).
You can see that I'm not scanning files in the demo.

The re-entrancy protection I forgot, but is not needed anyway for the demo because I don't expect that you'll click the Start button while in the process, because, if you read the steps to reproduce, that's not the problem (that's why I forgot).

But another thing:

I see that if I run the demo after a while, the problem does not happen.
It seems that it only happen if the database was filled recently.

[Eduardo-]

Yes Niya, I agree that people that don't know how to use DoEvents will have lot of problems.

I thought that advanced programmers would not have such a problems (and limitations) but I see I'm wrong.

What else can I add? DoEvents (proper us of) brings VB6 to another level. It brings VB6 to century XXI.
Yeah, it it had threading most probably I use it instead, but it does not have it.
There are hacks, but they are cumbersome to use and debug an already complex program.

There is no problem with DoEvents.
It is not evil, it is actually nice. (and nicer when you don't have threading).

I can't believe you guys. wqweto, Niya, not sure about Olaf's position.

[Eduardo-]

By the way Olaf, you can comment out the DoEvents there in the sample, and you'll see that you experience the exact same problem.
DoEvents has nothing to do with it, as I said from the beginning.

[Schmidt]

Olaf, once again it has nothing to do with scanning files

I'm aware of that - because you mentioned it already ...

I had it prepared at that time (from guessing, what your problem might look like) -
and so I've posted it "as it was"...

I still think, that you can steal some stuff ...
(e.g. how I've defined the two Create Table Statements - Without RowId and the Hash as Primary Key)

Also - regarding your Problem - I've found it.
From the SQLite-transaction-documentation:
An implicit transaction (a transaction that is started automatically, not a transaction started by BEGIN) is committed automatically when the last active statement finishes. A statement finishes when its last cursor closes, which is guaranteed to happen when the prepared statement is reset or finalized.

So, the cCursor is (as said already) very low-level.
And thus a cCursor.Step is leaving the Cursor "within an implicite transaction-state".

Only when you've set the next Parameter on the Cursor - does it do an (implicite) Reset (leaving the implicite Transaction).

That's the reason, why my example works -
because I do all the: Mod Counter = 0: DoEvents stuff in-between:
- setting a cursor-parameter
- and the next step

So, what you need is an explite cCursor.Reset at the end of your little GetImageData-Routine.

Stuff like that cannot happen with the "established" SQLite-Objects (like e.g. cRecordset) -
because their Methods will not leave "a statement alive without resetting or finalizing it".

HTH

Olaf

[Schmidt]

DoEvents has nothing to do with it, as I said from the beginning.

Yes, but I would still avoid calling DoEvents in-between a Begin- and -CommitTrans.

There's just too much risk, that you mess up the transaction-stack this way,
when no re-entrancy protection is on the routine itself.

If you have to, then do it like shown in my recent example above:

mCnn.CommitTrans 'close the transaction temporarily
   '... update some progress-elements
   DoEvents 'allow the GUI, to reflect the Progress-Infos 
mCnn.BeginTrans  're-open again, and continue looping

Olaf

[Eduardo-]

I still think, that you can steal some stuff ...

I don't want to steal anything (at this time) because the part of scanning file is working well already and it is quite complex, involving some classes, an object that can return all the files indexes within an specific folder and all the folders ramifications inside that folder anytime when needed.

I only commented that I considered to perhaps do the scan multiprocessing when there are too many files, but in a future.
But didn't figure a way to do it properly because I don't know in advance where the files are (in number of files) so I cannot split the task if I don't know that.
Once I know that, the task is mostly done already. So I don't see how to split it.
And I don't think your example address that anyway. Splitting by disk drives does not make much sense to me as I said.

(e.g. how I've defined the two Create Table Statements - Without RowId and the Hash as Primary Key)

The file scanning part does not currently involve the database in any way. And I repeat, the scanning is already finished by the time of this "loading image data" process. It has nothing to do.

Also - regarding your Problem - I've found it.
From the SQLite-transaction-documentation:
An implicit transaction (a transaction that is started automatically, not a transaction started by BEGIN) is committed automatically when the last active statement finishes. A statement finishes when its last cursor closes, which is guaranteed to happen when the prepared statement is reset or finalized.

So, the cCursor is (as said already) very low-level.
And thus a cCursor.Step is leaving the Cursor "within an implicite transaction-state".

Only when you've set the next Parameter on the Cursor - does it do an (implicite) Reset (leaving the implicite Transaction).

That's the reason, why my example works -
because I do all the: Mod Counter = 0: DoEvents stuff in-between:
- setting a cursor-parameter
- and the next step

So, what you need is an explite cCursor.Reset at the end of your little GetImageData-Routine.

Stuff like that cannot happen with the "established" SQLite-Objects (like e.g. cRecordset) -
because their Methods will not leave "a statement alive without resetting or finalizing it".

HTH

Olaf

But even if the transaction was commited in the connection (that the cCursor uses)? And even if I'm dealing from another connection with another table? Strange.

[Eduardo-]

Yes, but I would still avoid calling DoEvents in-between a Begin- and -CommitTrans.

There's just too much risk, that you mess up the transaction-stack this way,
when no re-entrancy protection is on the routine itself.

If you have to, then do it like shown in my recent example above:

mCnn.CommitTrans 'close the transaction temporarily
   '... update some progress-elements
   DoEvents 'allow the GUI, to reflect the Progress-Infos 
mCnn.BeginTrans  're-open again, and continue looping

Olaf

That would slow down everything. No way.
And there is no re-entrancy at all, do not worry.

You guys fear DoEvents too much.
There is no problem with DoEvents.

And you can see from my sample that mCnn.CommitTrans did not prevent the problem at all anyway.

As I said, I'll repeat again (one more time): DoEvents has nothing to do with this issue.
Comment DoEvents in the code and you'll see the exact same issue.

[Schmidt]

...even if I'm dealing from another connection with another table?

No, the problem happened because you are dealing with another, separate Connection-Object (opened on the same DB-File).
If you change (as suggested already), your Class's OpenDatabase-Method to the version below:

Public Sub OpenDatabase()
    If mDataBase Is Nothing Then
        Set mDataBase = gDataBase 'New_c.Connection(DatabasePath)
        Set mRecCommand = mDataBase.CreateCommand("INSERT INTO Files (Path_Hash, FileLen, Date, Image_Data) VALUES (?,?,?,?)")
        Set mRecImageData = mDataBase.CreateCursor("Select * From Files Where Path_Hash=?")
    End If
End Sub

Everything will work fine, because SQLite now has "more context available" (behind the now shared DB-Connection-Handle),
to resolve the Problem of the "Cursor-being-in-an-implicit-transaction" without clashing with Rs.UpdateBatch -
(which was derived from the same ConnectionObj.

HTH

Olaf

[Niya]

Yes Niya, I agree that people that don't know how to use DoEvents will have lot of problems.

I thought that advanced programmers would not have such a problems (and limitations) but I see I'm wrong.

What else can I add? DoEvents (proper us of) brings VB6 to another level. It brings VB6 to century XXI.
Yeah, it it had threading most probably I use it instead, but it does not have it.
There are hacks, but they are cumbersome to use and debug an already complex program.

There is no problem with DoEvents.
It is not evil, it is actually nice. (and nicer when you don't have threading).

I can't believe you guys. wqweto, Niya, not sure about Olaf's position.

Well I don't want to derail your thread but this whole thing about DoEvents vs multi-threading is certainly something I'm willing to discuss. There is a lot I'd have to say. If you're interested, we could talk really dig into this topic in another thread.

[Eduardo-]

No, the problem happened because you are dealing with another, separate Connection-Object (opened on the same DB-File).
If you change (as suggested already), your Class's OpenDatabase-Method to the version below:

Public Sub OpenDatabase()
    If mDataBase Is Nothing Then
        Set mDataBase = gDataBase 'New_c.Connection(DatabasePath)
        Set mRecCommand = mDataBase.CreateCommand("INSERT INTO Files (Path_Hash, FileLen, Date, Image_Data) VALUES (?,?,?,?)")
        Set mRecImageData = mDataBase.CreateCursor("Select * From Files Where Path_Hash=?")
    End If
End Sub

Everything will work fine, because SQLite now has "more context available" (behind the now shared DB-Connection-Handle),
to resolve the Problem of the Cursor-being-in-an-implicit-transaction without clashing with Rs.UpdateBatch -
(which was derived from the same ConnectionObj.

HTH

Olaf

So you are basically saying that just a single connection would work.
What wqweto denied.

And not only because wqweto said it, it does not make sense that a database can have only a single connection.
Yes, I could solve it in that way in this case, but that would leave me "worried".
Sorry that I'm insisting, but I want to trust that I'm using a robust system. This is my first experience wit SQLite (and RC6), but if everything goes right I'm thinking in possibly moving other program to SQLite. If everything goes right.
I know you are offering this for free and "Use at your own risk" and you are also doing this support for free what I consider and appreciate, but still, I need to trust in what I'm doing. It does not seem right to me this limitation.
(You cannot say anymore that I don't want to use RC6 now )

[Eduardo-]

Well I don't want to derail your thread but this whole thing about DoEvents vs multi-threading is certainly something I'm willing to discuss. There is a lot I'd have to say. If you're interested, we could talk really dig into this topic in another thread.

I'm not very much willing to discuss it now, since I understand the subject pretty well I think.
But open the thread if you want
Maybe at some point I say something (or not).

[wqweto]

So you are basically saying that just a single connection would work.
What wqweto denied.

And not only because wqweto said it, it does not make sense that a database can have only a single connection.

Hey, I said nothing about transactions. Connection <> Transaction. You can have multiple connections to a single sqlite DB but only of these can have a transaction with pending (uncommitted) modifications.

Basically sqlite has only a single type of write (exclusive) lock and this is exclusive DB lock -- no exclusive table lock, no exclusive row lock, no index lock like other "big" RDBMS have for implementing updatable concurrency.

The moment a second connections tries to write or to *read* on an X locked db it just waits for BusyTimeOutSeconds number of seconds before raising error "Too Busy to execute anything".

cheers,
</wqw>

[Eduardo-]

Hey, I said nothing about transactions. Connection <> Transaction. You can have multiple connections to a single sqlite DB but only of these can have a transaction with pending (uncommitted) modifications.

Basically sqlite has only a single type of write (exclusive) lock and this is exclusive DB lock -- no exclusive table lock, no exclusive row lock, no index lock like other "big" RDBMS have.

cheers,
</wqw>

But I didn't have any pending transaction, and the issue still happened.
According to Olaf I should use the same connection. That's is what:

Set mDataBase = gDataBase

means.

Olaf said something about the Cursor being low level, and that is needs a cCursor.Reset, and that that was the cause of the problem.
Then I don't know why he said also to use the same connection.

[Schmidt]

So you are basically saying that just a single connection would work.
What wqweto denied.

That's not what wqweto said, at all.

...it does not make sense that a database can have only a single connection.

SQLite does allow multiple connections against the same database-file.

It just does not make much sense, to have several DB-Connections open (on the same DBFile, in the same process and thread)!

Yes, I could solve it in that way in this case, but that would leave me "worried".

I've mentioned, how you can avoid the error (even with multiple open Connections on the same DBFile in the same process),
when you just Reset your Cursor properly (according to what I've quoted from the SQLite-documentation).

Sorry that I'm insisting, but I want to trust that I'm using a robust system.

Then trust in the suggestions of the experts who are using this system for years now.
- by either using a single (global, App-wide ConnectionObj for the same DBFile)
- or by resetting the cCursor-instance before trying any other write-operations on
.. other Connection-Objects, the Cursor was not derived from

Olaf

[Schmidt]

Olaf said something about the Cursor being low level, and that is needs a cCursor.Reset,
and that that was the cause of the problem.
Then I don't know why he said also to use the same connection.

Yes, the Cursor being low-level (and not being reset, still being in an implicite-transaction) introduces a problem,
when you try to do a write-operation (as e.g. Rs.UpdateBatch) on a *different* connection.

The problem of the Cursor being in an implicit-transaction-state (when not properly reset),
is still the same, when you'd try an Rs.UpdateBatch on the *same* Connection-Object -
but in this case SQLite can *detect* the Problem (resolving it without errors), *because*
the Cursor-Handle and the Rs-CommandObj-Handle are operating on the same Connection-instance.

Kinda wrote the same thing in my last post, but maybe the way I expressed it here is better understandable.

Olaf

[Eduardo-]

It just does not make much sense, to have several DB-Connections open (on the same DBFile, in the same process and thread)!

I cannot guarantee that gDatabase is open, or rather, I prefer not to worry about whether it is open or not, or if it is set to nothing.
This program is ready to be run in command line mode, without any GUI. And also as a worker, so I prefer to isolate things as much as possible. Does that make sense?

Still, I'll have to work with all the limitations and complications that SQLite has, if the experts say so.

PS: I think I'm starting to understand the "Lite" part.

[Schmidt]

Still, I'll have to work with all the limitations and complications that SQLite has, if the experts say so.

There will be no "limitation" (not with regards to multiple Connections in the same Process and Thread),
if you just reset your Cursor properly (after performing a Step), as shown in blue below:

Private Sub GetImageData(nIndex As Long)
    Dim iBytes() As Byte
    
    mRecImageData.SetBlobPtr 1, StrPtr(gFilePathHashes(nIndex)), cHashByteCount
        If mRecImageData.Step Then
            If mRecImageData.ColVal(2) = gFileImageData(nIndex).FileLen Then
                If mRecImageData.ColVal(3) = gFileDates(nIndex) Then
                    iBytes = mRecImageData.ColVal(4)
                    CopyMemory ByVal VarPtr(gFileImageData(nIndex)), ByVal VarPtr(iBytes(0)), gLenB_TImageData
                    gFileImageData(nIndex).Index = nIndex
                    mRecImageData.Reset 'reset the Cursor properly before leaving the Sub
                    Exit Sub
                Else
                    mRowsIDToDelete.Add mRecImageData.ColVal(0)
                    Stop
                End If
            Else
                mRowsIDToDelete.Add mRecImageData.ColVal(0)
                Stop
            End If
        End If
    mRecImageData.Reset 'reset the Cursor properly 
    ' if we are here is because the data is still not cached, otherwise it would have exited from the Exit Sub above
    ComputeImageData gFileImageData(nIndex)
    SaveImageData nIndex
End Sub

HTH

Olaf

[wqweto]

So you are using the cursor without wrapping the access in a transaction like in the benchmark test?

    iSQLite.Execute "Begin"
        Set iCur = iSQLite.CreateCursor("Select * From Files Where Path_Hash=?")
 
        For C = 0 To UBound(iIndexes)
            iCur.SetParameterValue 1, iKeys(iIndexes(C))
     
            If iCur.RecordCount Then
    '            ID = iCur.Value(0)
'                PH = iCur.Value(1)
                FL = iCur.Value(2)
                DT = iCur.Value(3)
                BD = iCur.Value(4)
            Else
                Stop
            End If
        Next
    iSQLite.Execute "Commit"

Not having an explicit transaction and not modifying data would be quite weird to lead to an implicit transaction being spawned because of the cursor but this should be consulted in sqlite sources exactly what's going on.

cheers,
</wqw>

[Eduardo-]

Does that Reset cursor take time? Because I could do it just before the Commit if it takes some meaningful time.

[Eduardo-]

So you are using the cursor without wrapping the access in a transaction like in the benchmark test?

    iSQLite.Execute "Begin"
        Set iCur = iSQLite.CreateCursor("Select * From Files Where Path_Hash=?")
 
        For C = 0 To UBound(iIndexes)
            iCur.SetParameterValue 1, iKeys(iIndexes(C))
     
            If iCur.RecordCount Then
    '            ID = iCur.Value(0)
'                PH = iCur.Value(1)
                FL = iCur.Value(2)
                DT = iCur.Value(3)
                BD = iCur.Value(4)
            Else
                Stop
            End If
        Next
    iSQLite.Execute "Commit"

Not having an explicit transaction and not modifying data would be quite weird to lead to an implicit transaction being spawned because of the cursor but this should be consulted in sqlite sources exactly what's going on.

cheers,
</wqw>

It is all inside a main transaction as in the sample project.

[wqweto]

It is all inside a main transaction as in the sample project.

Ahaa, and you open a second connection and want to fetch some extra data while the first connection is in a transaction and the DB is exclusively locked already?

cheers,
</wqw>

[Eduardo-]

Ahaa, and you open a second connection and want to fetch some extra data while the first connection is in a transaction and the DB is exclusively locked already?

cheers,
</wqw>

No. I open a first connection and a recordset that uses another table.

Then I open this connection, open a transaction, do some searches using a cursor, close the transaction, but the connection remains open.

Then I try to add some data or update some records on the other table from the other connection and the error occurs.

[Schmidt]

Does that Reset cursor take time? Because I could do it just before the Commit if it takes some meaningful time.

The Cursor (when explicitely reset via the COM-Method from the outside),
will not reset itself implicitely again when the COM-Method cCursor.SetBlobPtr(...) is called.

So, a single COM-MethodCall-Overhead is all that's done in addition (timingwise probably only 0.5% loss or so).

The important difference in the Code is, that you call the Reset now after you have "stepped"
(shortly before leaving the function).

Whereas before, you left the Cursor "Open after stepping"... then exiting the routine this way -
and the needed reset was done "too late" - on Procedure-entry again (implicitely, when setting the Parameter).

Olaf

[Eduardo-]

The Cursor (when explicitely reset via the COM-Method from the outside),
will not reset itself implicitely again when the COM-Method cCursor.SetBlobPtr(...) is called.

So, a single COM-MethodCall-Overhead is all that's done in addition (timingwise probably only 0.5% loss or so).

The important difference in the Code is, that you call the Reset now after you have "stepped"
(shortly before leaving the function).

Whereas before, you left the Cursor "Open after stepping"... then exiting the routine this way -
and the needed reset was done "too late" - on Procedure-entry again (implicitely, when setting the Parameter).

Olaf

Is that "too late" implicit reset faster than the explicit from outside or is the same? (I don't mean just the overhead of invoking a method).

[Schmidt]

Is that "too late" implicit reset faster than the explicit from outside or is the same? (I don't mean just the overhead of invoking a method).

No, it's the same speed (the same internal sqlite3_reset-API is used in cCursor.Reset or
alternatively in cCursor.Setparameter (but there only - when not already in "resetted state").

Here the RC6-code for the relevant calls (I've marked the reset-API-call in blue):

Public Function Step() As Boolean
  If hStmt = 0 Then Err.Raise vbObjectError, , "Please define a correct SQL-Statement first!"
 
    Select Case sqlite3_step(hStmt)
      Case SQLITE_ROW 'step-statement found a record
        mStepsDone = 0
        mStepCounter = mStepCounter + 1
        Step = True
        Exit Function
      Case SQLITE_DONE 'step-statement finished
        sqlite3_reset hStmt
        mStepsDone = mStepCounter
        mStepCounter = 0
        Exit Function
      Case Else
        ... some Busy- and other Error-detection and handling
    End Select
 
End Function

Public Sub Reset()
  If hStmt = 0 Then Err.Raise vbObjectError, , "Please define a correct SQL-Statement first!"
  sqlite3_reset hStmt: mStepCounter = 0: mStepsDone = 0
End Sub

Public Sub SetBlobPtr(ByVal ParamIdxOneBased As Long, ByVal pb As Long, ByVal BByteLen As Long)
  If hStmt = 0 Then Err.Raise vbObjectError, , "Please define a correct SQL-Statement first!"
  If mStepCounter Then sqlite3_reset hStmt: mStepCounter = 0: mStepsDone = 0
  If BByteLen < 0 Then BByteLen = 0
  If Not sqlite3_bind_blob(hStmt, ParamIdxOneBased, pb, BByteLen, -1) = SQLITE_OK Then
     Err.Raise vbObjectError, , "Cannot set Parameter: " & StringFromPtr(sqlite3_errmsg(Cnn.DBHdl))
  End If
End Sub

Olaf

[Eduardo-]

Thank you.
It seems to be working. I think I'll keep the double connection if it is not necessary to change that.

[Schmidt]

Thank you.
It seems to be working. I think I'll keep the double connection if it is not necessary to change that.

As you wish...
But keep in mind, that the "assigned SQLite-Cache" (which was a topic already in the other thread),
is "per Connection-instance" - not "per Process".

Also "opening an SQLite-Connection" (e.g. via New_c.Connection-constructor),
takes a bit of time (5-500msec, depending on the amount of "Schema-Tables" to parse) -
but in your case (with only 2 Tables) this is probably neglectable...

But these are the two main-reasons (aside from the avoidable little Problem we had here with the "Cursor-Collision"),
why I'd suggest to work with only one (global) connection-instance per SQLite-DBFile in a singlethreaded VB6-App.

Olaf

[Eduardo-]

They were at first totally unrelated tasks. They became somewhat related when I decided to move the Get/Set/DeleteSettings to the database, since there was "no problem" in doing that.
I could use INI files instead, but why having a database...
Or I could use another database file. Maybe not a bad idea.
But I should be able to work with 2 tables

[Eduardo-]

Hey, I said nothing about transactions. Connection <> Transaction. You can have multiple connections to a single sqlite DB but only of these can have a transaction with pending (uncommitted) modifications.

Basically sqlite has only a single type of write (exclusive) lock and this is exclusive DB lock -- no exclusive table lock, no exclusive row lock, no index lock like other "big" RDBMS have for implementing updatable concurrency.

The moment a second connections tries to write or to *read* on an X locked db it just waits for BusyTimeOutSeconds number of seconds before raising error "Too Busy to execute anything".

cheers,
</wqw>

This is an important limitation.

In another program I use the locking feature to not allow two users to edit the same record at the same time (they access the database through a lan network).

If I have to lock all the database, nobody could do anything.
I would have to modify the program to write in an auxiliary table if someone is going to edit something and check there before allowing an edition...
And if someone crashed the program? How to remove the lock? It gets complex...

[wqweto]

You won’t be able to write aux table as the db is locked. There is no need (and no way) to remove any lock, this is not an Access MDB :))

There is no problem with sqlite locking model and implementation per se. Moreover in WAL mode it’s possible for readers to continue reading their version of the db while a (single) writer modifies it.

[Eduardo-]

You won’t be able to write aux table as the db is locked. There is no need (and no way) to remove any lock, this is not an Access MDB )

There is no problem with sqlite locking model and implementation per se. Moreover in WAL mode it’s possible for readers to continue reading their version of the db while a (single) writer modifies it.

c'mon wqweto, re read my message.

[Eduardo-]

Unfortunately, I definitively came to the conclusion that there must be a bug in RC6. There is no database (connection) open, and I open a new cConnection and can't do anything that involves an update, I keep getting the error:



It is driving me crazy, makes no sense.

[Schmidt]

Unfortunately, I definitively came to the conclusion that there must be a bug in RC6.
There is no database (connection) open, and I open a new cConnection and can't do anything that involves an update...

You've probably messed up the transaction-stack again (due to DoEvents) -
or you pressed Stop in the IDE (when within a transaction) -
which left you with a set FileLock on the DB-File in question.

Close all processes which interacted with the DB and start new
(and do not allow any DoEvents from within a code-block which has an open DB-Transaction).

A bug in the RC6-SQLiteWrapper classes is highly, highly unlikely
(it's one of the most hardened and well-used parts of RC5/RC6) -
there's services we use in production, which run for years 24/7 (based on RC5/6 and SQLite) - and other RC-users do too.

Olaf

[Eduardo-]

You've probably messed up the transaction-stack again (due to DoEvents) -
or you pressed Stop in the IDE (when within a transaction) -
which left you with a set FileLock on the DB-File in question.

Close all processes which interacted with the DB and start new
(and do not allow any DoEvents from within a code-block which has an open DB-Transaction).

A bug in the RC6-SQLiteWrapper classes is highly, highly unlikely
(it's one of the most hardened and well-used parts of RC5/RC6) -
there's services we use in production, which run for years 24/7 (based on RC5/6 and SQLite) - and other RC-users do too.

Olaf

I can't work like that. I need the component to be more intelligent in handling error situations transparently.

Also, I see some misconceptions.
There is no cConnection.Close. This relies entirely that the variable holding the connection need to be set to Nothing and execute the Terminate event.
But that forgets that another variable could be holding a reference to the same instance, so at some point in code there is no way that you could ensure that a connection will close (because even if you put the variable to Nothing you cannot ensure the instance will be destroyed).

Another misconception is that there is no NoMatch, because allegedly it is not needed.
But yes, it is needed, otherwise I need to write code like this:

    If mRecSettings.FindFirst("Key LIKE '" & iStr & "*'") Then
        AddToList iRet, Right$(mRecSettings!Key, Len(mRecSettings!Key) - Len(iStr))
        AddToList iRet, mRecSettings!value.value
        Do While mRecSettings.FindNext("Key LIKE '" & iStr & "*'")
            AddToList iRet, Right$(mRecSettings!Key, Len(mRecSettings!Key) - Len(iStr))
            AddToList iRet, mRecSettings!value.value
        Loop
    End If

duplicating code.
it should be instead:

    mRecSettings.FindFirst "Key LIKE '" & iStr & "*'"
    Do Until mRecSettings.NoMatch
        AddToList iRet, Right$(mRecSettings!Key, Len(mRecSettings!Key) - Len(iStr))
        AddToList iRet, mRecSettings!Value.Value
        mRecSettings.FindNext "Key LIKE '" & iStr & "*'"
    Loop

The .Delete of cRecordSet never works.

[Eduardo-]

I also noticed that at a time, when the database file was opened by another program,

Set gDataBase = New_c.Connection(DatabasePath)

opened the database anyway. But much further in the code, when it tried to make a write operation, it raised an error saying that the database was opened read-only.

That's not right, it should raise an error when the connection is attempted, and if I want to open a connection in read-only mode that must be explicitly stated when establishing the connection.
And not to raise an error in any other place in the code when it tries to write, that could be a lot of places.

[Schmidt]

I can't work like that.

Umhh, then don't?

I need the component to be more intelligent in handling error situations transparently.

If you mess-up the transaction-stack via your own coding (using DoEvents and stuff, then using the Stop-Button in the IDE),
then all the component can do is, to inform you about the "messed up state" (which it does).

Also, I see some misconceptions
There is no cConnection.Close.

That's intentional (because it offers only advantages, advantages you cannot see yet)
But as said, if you don't like it - use something else...

Another misconception is that there is no NoMatch, because allegedly it is not needed.
But yes, it is needed, otherwise I need to write code like this:

    If mRecSettings.FindFirst("Key LIKE '" & iStr & "*'") Then
        AddToList iRet, Right$(mRecSettings!Key, Len(mRecSettings!Key) - Len(iStr))
        AddToList iRet, mRecSettings!value.value
        Do While mRecSettings.FindNext("Key LIKE '" & iStr & "*'")
            AddToList iRet, Right$(mRecSettings!Key, Len(mRecSettings!Key) - Len(iStr))
            AddToList iRet, mRecSettings!value.value
        Loop
    End If

Rs.NoMatch is superfluous (the same way as a Close-Method on Recordsets and Connections)...
It is not needed at all, when you write your do-loop properly.

Private Sub EnumRecordsWithKeyLike(Key As String, Rs As cRecordset)
  Dim SearchExpr As String
      SearchExpr = "Key LIKE '" & Key & "'"
  If Rs.FindFirst(SearchExpr) Then
     Do
        Debug.Print , Rs!Key, Rs!Value '<- no replication of code-lines for found records here
     Loop While Rs.FindNext(SearchExpr)
  End If
End Sub

The .Delete of cRecordSet never works.

That's another myth, as the code below shows (which incorporates also your Like-Enumerations)

Option Explicit

Private Sub Form_Load()
  With New_c.MemDB
    .Exec "Create Table Settings(Key Text Collate NoCase Primary Key, Value Text)"
        .ExecCmd "Insert Into Settings Values(?,?)", "Setting A", "Value A"
        .ExecCmd "Insert Into Settings Values(?,?)", "Setting B1", "Value B1"
        .ExecCmd "Insert Into Settings Values(?,?)", "Setting B2", "Value B2"
        .ExecCmd "Insert Into Settings Values(?,?)", "Setting C", "Value C"
      
    Dim mRecSettings As cRecordset
    Set mRecSettings = .GetRs("Select * From Settings")
    Debug.Print "after 4 inserts, RecCount:", mRecSettings.RecordCount
    Dim iStr$: iStr = "setting b"
   
    '*** FindFirst-FindNext Enumeration
    EnumRecordsWithKeyLike "setting b*", mRecSettings '<- this should enumerate two found records
    
    '*** cRecordset.Delete-Test (on the "B1-Setting")
    If mRecSettings.FindFirst("Key Like 'setting b1'") Then
       mRecSettings.Delete
       mRecSettings.UpdateBatch
    End If
    mRecSettings.ReQuery 'get the Set fresh from the DB again (to see if the delete was successful)
    Debug.Print "after B1 delete, RecCount:", mRecSettings.RecordCount
    
    EnumRecordsWithKeyLike "setting b*", mRecSettings '<- this should now enumerate only the one remaining B2-record
  End With
End Sub

Private Sub EnumRecordsWithKeyLike(Key As String, Rs As cRecordset)
  Dim SearchExpr As String
      SearchExpr = "Key LIKE '" & Key & "'"
  If Rs.FindFirst(SearchExpr) Then
     Do
        Debug.Print , Rs!Key, Rs!Value
     Loop While Rs.FindNext(SearchExpr)
  End If
End Sub

HTH

Olaf

[Eduardo-]

Umhh, then don't?


If you mess-up the transaction-stack via your own coding (using DoEvents and stuff, then using the Stop-Button in the IDE),
then all the component can do is, to inform you about the "messed up state" (which it does).

I don't think that the database driver (the C code) is creating any dummy window to process messages, and I'm not messing up anything in my own code, maybe your component creates a window and does something behind the scenes (processing some message, may be a timer), but I can do nothing about that (if it is the case), because in that case would be your component what is messing up transactions, not my code.
DoEvents should not be a problem if you are not doing something like that.

(I'm assuming you understand how DoEvents works)

I can't work like that. I need the component to be more intelligent in handling error situations transparently.

Also, I see some misconceptions.
There is no cConnection.Close. This relies entirely that the variable holding the connection need to be set to Nothing and execute the Terminate event.
But that forgets that another variable could be holding a reference to the same instance, so at some point in code there is no way that you could ensure that a connection will close (because even if you put the variable to Nothing you cannot ensure the instance will be destroyed).

And this?

Also, when a connection is closed, it should automatically commit a pending transaction (or otherwise cancel it), but not leave it in a situation where the connection is closed but not the transaction. Simple common sense.

I also noticed that at a time, when the database file was opened by another program,

Set gDataBase = New_c.Connection(DatabasePath)

opened the database anyway. But much further in the code, when it tried to make a write operation, it raised an error saying that the database was opened read-only.

That's not right, it should raise an error when the connection is attempted, and if I want to open a connection in read-only mode that must be explicitly stated when establishing the connection.
And not to raise an error in any other place in the code when it tries to write, that could be a lot of places.

And this?

[Schmidt]

When you are not willing to follow advice, then your problems will continue I guess...

Here's my advice-list again:
- first and foremost: remove all DoEvents-calls which sit (even indirectly) between Begin/End-Transaction-calls
- better even, remove any DoEvents-calls completely (use a Timer instead, or multiple threads or processes)
- hold your App-Settings in its own AppSettings-DBFile
- put your cache-dbfile(s) into a separate \Cache\-Subfolder
.. (which should be deletable by the User, then automatically recreating itself in further runs without problems...
... these potential cache-deletes will not affect any User-Settings in your separate AppSettings-DBFile)

If you don't follow any of this - then at least post code-examples which clearly show,
that the problem came from the RC6-wrapper (and not your own user-code).

E.g. your "ReadOnly-problem" falls clearly into this category (I have no clue, what you're doing there)...
Keep in mind, that "opening a DB in read-only mode" is "per Connection" (not per DBFile).

Olaf

[Elroy]

This whole thread makes me want to write a self-rolled binary tree key/value class. I'd do it, but the "self-balancing" aspect is a bit daunting to me. The rest is pretty straight-forward stuff.

And, if we had our own, we could decide if it was ANSI or Unicode keys, and also do things like "return key(s) for value" type functions.