-
Aug 10th, 2022, 11:31 AM
#81
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by 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)
You are very funny.
Your misunderstanding of programming with DoEvents seems to be uttermost (I would never guessed).
Originally Posted by Schmidt
- hold your App-Settings in its own AppSettings-DBFile
Yes, I guess that could help in some cases, but it should not be necessary.
Originally Posted by Schmidt
- put your cache-dbfile(s) into a separate \Cache\-Subfolder
It is already in a "Data" folder alone, but I can't see the point.
Originally Posted by Schmidt
.. (which should be deletable by the User, then automatically recreating itself in further runs without problems...
It works like that already.
Originally Posted by Schmidt
... these potential cache-deletes will not affect any User-Settings in your separate AppSettings-DBFile)
Well, now if the user deletes the database, the settings are also gone. Not big deal I guess, since they are just preferences and states. If the user deletes something, he must know that something will be lost.
Originally Posted by Schmidt
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
Are you saying that opening the database read-only silently, instead of issuing an error didn't come from RC6?
How can I get an error telling me that the database is read-only and that I can't write, if there is no option in the connection object to specify whether to open it read-only or read/write?
I'm quite sure that the situation was that I was zipping the database using Winrar, and it was still not finished when I've hit F5 in the IDE and ran the program.
It started OK, until minutes later it needed to write something...
It should be clear if you need or not write access at the connection time.
-
Aug 10th, 2022, 11:33 AM
#82
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by 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.
That's already done here (or something like that).
-
Aug 10th, 2022, 11:39 AM
#83
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by Elroy
This whole thread makes me want to write a self-rolled binary tree key/value class.
Which (as a file) will then have to be used "task-exclusively" - because:
- handling potential concurrent writes
- handling potential concurrent reads (on committed and/or uncommited data)
is what makes a handwritten, bugfree approach otherwise not feasible.
Eduardos problems are a bit "blown out of proportion",
because he's insisting on having "concurrent writes on a separate DB-Connection to the same file"
(whilst the other Connection is within a loooong Transaction, with "Doevents sprinkled in").
SQLite is perfectly capable to handle a simple Key/Value-store (even *with* a looong-duration-transaction) -
when the Key-Value-store-DBFile would be handled *exclusively as well*.
(by simply separating the Cache-DBFile from the AppSetting-DBFile).
Olaf
-
Aug 10th, 2022, 11:40 AM
#84
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
PS: it seems that the latter cause if the "too busy" error was caused by closing a connection without ending the current transaction.
Now I'm testing again with that fix and seems not to cause the error.
But at least the error message should be a bit more helpful.
For example an error message at the connection closing, telling me that I left a transaction open.
-
Aug 10th, 2022, 11:49 AM
#85
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
The main problem seems to be the locking mode of SQLite, but I'll have to live with that.
I think it might be possible in a component to handle transactions automatically, but not easy to do (and work OK) probably.
I'm talking for example about a DAO-like component that I have somewhat in mind.
-
Aug 10th, 2022, 11:54 AM
#86
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by Eduardo-
You are very funny.
Your misunderstanding of programming with DoEvents seems to be uttermost (I would never guessed).
Then ask JPBro, the trick, wqweto or anyone with a modicum of DB-experience - whether:
BeginTrans
... DoEvents, Eduardo-style
CommitTrans
is a good idea.
Originally Posted by Eduardo-
...now if the user deletes the database, the settings are also gone.
As already advised, easily avoidable - but if you insist...
Originally Posted by Eduardo-
Are you saying that opening the database read-only silently....
RC5/6 will *not* "open a database readonly silently"...
(it has to be done explicitely via the cConnection.OpenDBReadOnly-method).
So, my guess is, that you have "mixed SQLite-wrappers" in your CodeBase
(as e.g. the one from Krool) - or have "played around" with explicit SQLite-API-open-calls (bypassing the COM-wrappers).
As said already, I will look at this myth, as soon as you post code-examples which are able to reproduce that.
Olaf
-
Aug 10th, 2022, 12:04 PM
#87
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by Eduardo-
For example an error message at the connection closing, telling me that I left a transaction open.
That's not necessary at all, because normally Transactions should be opened and closed:
- within the same function
- which has a proper ErrHandler, so that leaving the function is only possible with either "Commit" or "RollBack"
- which shall not have any DoEvents in-between the Open and End-Transaction-calls
- and of course also no DoEvents in any potential SubRoutines this function is calling-into
This way, it is just not possible to mess-up the transaction-stack.
Olaf
-
Aug 10th, 2022, 12:12 PM
#88
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by Schmidt
Then ask JPBro, the trick, wqweto or anyone with a modicum of DB-experience - whether:
BeginTrans
... DoEvents, Eduardo-style
CommitTrans
is a good idea.
There is no reason for that to be a problem (if there are not windows that can receive messages involved). I don't know what The Trick would say, but if he says that there is a problem, I'm quite sure he will tell what exactly is the issue (and not a general fear of DoEvents due to not understanding how it works).
Originally Posted by Schmidt
As already advised, easily avoidable - but if you insist...
I don't think that the users will be deleting that file, and if someones does, yes, he will delete some (unimportant) things that all can be recreated.
Originally Posted by Schmidt
RC5/6 will *not* "open a database readonly silently"...
(it has to be done explicitely via the cConnection.OpenDBReadOnly-method).
So, my guess is, that you have "mixed SQLite-wrappers" in your CodeBase
(as e.g. the one from Krool) - or have "played around" with explicit SQLite-API-open-calls (bypassing the COM-wrappers).
I don't have Krool's component even referenced on this program now.
How a connection opened normally can become read-only?
I'll see if I can generate the same situation/error.
-
Aug 10th, 2022, 12:20 PM
#89
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by Schmidt
That's not necessary at all, because normally Transactions should be opened and closed:
- within the same function
- which has a proper ErrHandler, so that leaving the function is only possible with either "Commit" or "RollBack"
- which shall not have any DoEvents in-between the Open and End-Transaction-calls
- and of course also no DoEvents in any potential SubRoutines this function is calling-into
This way, it is just not possible to mess-up the transaction-stack.
Olaf
I see you are too narrow-minded about how program's client code could work.
And I mean, right or wrong. A component maker should think in all situations, and not to expect what he thinks is the "right" thing.
That's the same principle for every program: the programmer should think the final user would do everything that he should not do.
And of course I do not agree at all with your view on DoEvents, but that should be very clear now already.
DoEvents does nothing to the database, it has nothing to do with the database (unless you tell me that you have put a dummy window that receive timer events and does whatever strange thing).
-
Aug 10th, 2022, 12:26 PM
#90
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
[DoEvents within an ongoing, looong DB-Transaction]
Originally Posted by Eduardo-
There is no reason for that to be a problem ...
No reason? Two postings above you've just posted a problem you've fixed, *caused* by that.
(where you were closing your App - from "inside of your DoEvents-call", leaving the transaction open whilst closing the Connection)
Olaf
-
Aug 10th, 2022, 12:27 PM
#91
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
No one is forcing you to use RC6.
-
Aug 10th, 2022, 12:37 PM
#92
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by Eduardo-
I do not agree at all with your view on DoEvents, but that should be very clear now already.
Which is the main-reason, why this thread goes on and on...
(as said, you've just fixed another problem, related to your own idea, to place DoEvens within a transaction)
Originally Posted by Eduardo-
DoEvents does nothing to the database, it has nothing to do with the database
You still don't understand, that transactions are a "sensitive thing" (in any DB-scenario).
I've already explained, that one is well-advised:
1) to encapsulate Open- and EndTrans-calls within the same function
2) and making *very-sure*, that this function is not left, without either a Commit- or Rollback-call.
DoEvents has nothing directly to do with DataBases -
but it is very, very easy - to break rule 2) above (when you use DoEvents from within a "transaction-routine").
I seriously hope you get it now...
And no, there is no "Window" in the RC-SQLiteWrapper or other "async stuff"
The mess is entirely your own creation... (whilst dismissing any advice on the topic, which was given several times by now)
Olaf
-
Aug 10th, 2022, 12:40 PM
#93
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by Schmidt
[DoEvents within an ongoing, looong DB-Transaction]
No reason? Two postings above you've just posted a problem you've fixed, *caused* by that.
(where you were closing your App - from "inside of your DoEvents-call", leaving the transaction open whilst closing the Connection)
Olaf
The problem was that generic error "Too busy" instead of saying something more specific.
I now handled it from my side:
Code:
Public Sub CloseDatabase()
If Not mDataBase Is Nothing Then
If mDataBase.TransactionStackCounter > 0 Then
If gInIDE Then Err.Raise 5, TypeName(Me) ,"Database is in a transaction, end it first."
mDataBase.CommitTrans
End If
Set mRecImageData = Nothing
Set mDataBase = Nothing
End If
End Sub
Originally Posted by OptionBase1
No one is forcing you to use RC6.
You seems to be willing for me to leave RC6.
Last edited by Eduardo-; Aug 10th, 2022 at 12:54 PM.
-
Aug 10th, 2022, 12:50 PM
#94
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by Schmidt
Which is the main-reason, why this thread goes on and on...
(as said, you've just fixed another problem, related to your own idea, to place DoEvens within a transaction)
You still don't understand, that transactions are a "sensitive thing" (in any DB-scenario).
I've already explained, that one is well-advised:
1) to encapsulate Open- and EndTrans-calls within the same function
2) and making *very-sure*, that this function is not left, without either a Commit- or Rollback-call.
DoEvents has nothing directly to do with DataBases -
but it is very, very easy - to break rule 2) above (when you use DoEvents from within a "transaction-routine").
I seriously hope you get it now...
And no, there is no "Window" in the RC-SQLiteWrapper or other "async stuff"
The mess is entirely your own creation... (whilst dismissing any advice on the topic, which was given several times by now)
Olaf
And your suggestion of using timers instead...
Using timers would produce exactly the same situations.
I really think that you don't understand DoEvents.
The only difference using timers would be that it would be more complex and a lot slower.
I could not do this program without DoEvents, there is no chance.
I could disable everything, but that's not the idea. The idea is for the user to be able to cancel a task at any time.
I'm doing what someone would expect from a normal program after year 2005/2010, nothing too special.
-
Aug 10th, 2022, 12:56 PM
#95
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
So yeah, you probably shouldn't use or distribute software written by someone who you think doesn't know what they are doing. Thread solved.
Good luck.
-
Aug 10th, 2022, 12:56 PM
#96
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by Eduardo-
That's already done here (or something like that).
Yeah, I knew you had done that. But I'd really like to do it as a binary-tree. And yeah, I know hashes are faster. I just really like the intuitiveness of binary-trees. If I get some time, I'll probably study the balancing, and see if I can knock it out.
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
-
Aug 10th, 2022, 12:58 PM
#97
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by Eduardo-
I could not do this program without DoEvents, there is no chance.
Well, I could ...
(but that's probably because I understand what's going on behind DoEvents)...
Olaf
-
Aug 10th, 2022, 01:01 PM
#98
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by OptionBase1
So yeah, you probably shouldn't use or distribute software written by someone who you think doesn't know what they are doing. Thread solved.
Good luck.
OK, I won't use any OCX anymore, because MS and other authors don't know what I'm doing.
Good luck, specially with your business.
-
Aug 10th, 2022, 01:04 PM
#99
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by Schmidt
Well, I could ...
(but that's probably because I understand what's going on behind DoEvents)...
Olaf
You could? LOL!
Yes, I also could use some threading class, that would add a lot of complexity and would be a lot harder to debug (and have many, many crashes) for no reason (having exactly the same issues with the database), being able to do it much easier with DoEvents.
-
Aug 10th, 2022, 01:09 PM
#100
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by Eduardo-
OK, I won't use any OCX anymore, because MS and other authors don't know what I'm doing.
Good luck, specially with your business.
So yeah, you probably shouldn't use or distribute software written by someone who you think doesn't know what they are doing. Thread solved.
Good luck.
-
Aug 10th, 2022, 01:12 PM
#101
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by Eduardo-
You could?
Yep - and without any crashes of course...
(or any more "added complexity, due to insisting on calling DoEvents all over the place").
I'd even be willing to post an example (based on some source from you,
when it covers what you're trying to do sufficiently).
Olaf
-
Aug 10th, 2022, 01:13 PM
#102
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by OptionBase1
So yeah, you probably shouldn't use or distribute software written by someone who you think doesn't know what they are doing. Thread solved.
Good luck.
Who or what would be "they" in your sentence?
You solved the thread many times already, you seems to be a master thread-solver
-
Aug 10th, 2022, 01:16 PM
#103
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by Eduardo-
Who or what would be "they" in your sentence?
You solved the thread many times already, you seems to be a master thread-solver
Ok, I can see the ambiguity.
The "they" is the writer of RC6. I'm saying if Eduardo thinks so little of Olaf's code (rightly or wrongly (but obviously wrongly)), then Eduardo probably just shouldn't use Olaf's code.
Or, if you are going to use his code, you could trust that what he is saying is correct and modify your code as such.
Yes, it's a bit of a "thrown bottle from the stands" from me, but the thread was already going off the rails and I wanted to see if I could get a flattened penny or two out of it before it gets the mod massage.
Good luck.
Last edited by OptionBase1; Aug 10th, 2022 at 01:22 PM.
-
Aug 10th, 2022, 01:33 PM
#104
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by Schmidt
Yep - and without any crashes of course...
(or any more "added complexity, due to insisting on calling DoEvents all over the place").
I'd even be willing to post an example (based on some source from you,
when it covers what you're trying to do sufficiently).
Olaf
Do not waste time. I have this program very advanced already.
-
Aug 10th, 2022, 01:35 PM
#105
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by OptionBase1
Ok, I can see the ambiguity.
The "they" is the writer of RC6. I'm saying if Eduardo thinks so little of Olaf's code (rightly or wrongly (but obviously wrongly)), then Eduardo probably just shouldn't use Olaf's code.
Or, if you are going to use his code, you could trust that what he is saying is correct and modify your code as such.
Yes, it's a bit of a "thrown bottle from the stands" from me, but the thread was already going off the rails and I wanted to see if I could get a flattened penny or two out of it before it gets the mod massage.
Good luck.
Nice try, keep trying.
-
Aug 10th, 2022, 01:53 PM
#106
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by Eduardo-
How a connection opened normally can become read-only?
I'll see if I can generate the same situation/error.
OK, I was able to recreate the situation of the error:
I did what I already described in some other message:
I started to compress the database file to a rar file using Winrar, and then while Winrar was still working, launched the program in the IDE.
It opened the database without any error, but in a cCommand.Execute where the program attempts to add a new register, this error happens.
I have not VBSQLite referenced:
Last edited by Eduardo-; Aug 10th, 2022 at 02:01 PM.
-
Aug 10th, 2022, 02:04 PM
#107
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by Eduardo-
OK, I was able to recreate the situation of the error:
I did what I already described in some other message:
I started to compress the database file to a rar file using Winrar, and then while Winrar was still working, launched the program in the IDE.
It opened the database without any error, but in a cCommand.Execute where the program attempts to add a new register, this error happens.
If memory serves, you had been using an Access database until recently and switched to SQLite because of size limitations.
What happens if you use your old code with your old access database (presumably you still have them) under the same test circumstances? "Rar"ing the access database and then running your old program code while it is still compressing? Is there an error thrown when the connection is first made to the database? Or is there only an error thrown when the first attempt is made to modify the database? Or, is no error thrown at all and everything just works?
You may think it isn't relevant to test that because you've moved on from Access, but it would at least clarify if it is handled differently.
-
Aug 10th, 2022, 02:28 PM
#108
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
My guess is that WinRar has locked the file for read only access - RC6 won't know this until it tries to write to the DB, and that is why you get the error on write not on open/read.
If WinRar locks the file for exclusive access, then I suspect you'd get an error on open (this would need to be tested of course).
-
Aug 10th, 2022, 02:37 PM
#109
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
BTW - you can have a responsive UI with a long-running process using a timer instead of DoEvents, and it won't be much slower than a tight-loop + DoEvents approach. I know you're trying to eke out every ounce of performance, so perhaps even a small hit is too much, but the basic idea is to run the tight loop for a period (say 150-250ms or so) inside a timer with the shortest interval.
Something like this:
Code:
Private WithEvents mo_Timer As RC6.cTimer
Private Sub Class_Initialize()
Set mo_Timer = New_C.Timer(1, True)
End Sub
Private Sub mo_Timer_Timer()
Dim l_Done As Boolean
Dim l_StartedAt As Double
mo_Timer.Enabled = False
l_StartedAt = New_C.HPTimer
Do
' Do stuff here
' If Done processing, set l_Done = True
Loop While (Not l_Done) And _
(New_c.HPTimer - l_StartedAt <= .15)
mo_Timer.Enabled = Not l_Done
End Sub
I'm doing something similar to the above in the following project, and it runs fast enough for me. Perhaps not up to your standard though.
-
Aug 10th, 2022, 02:38 PM
#110
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by jpbro
My guess is that WinRar has locked the file for read only access
Yes.
Originally Posted by OptionBase1
If memory serves, you had been using an Access database until recently and switched to SQLite because of size limitations.
What happens if you use your old code with your old access database (presumably you still have them) under the same test circumstances? "Rar"ing the access database and then running your old program code while it is still compressing? Is there an error thrown when the connection is first made to the database? Or is there only an error thrown when the first attempt is made to modify the database? Or, is no error thrown at all and everything just works?
You may think it isn't relevant to test that because you've moved on from Access, but it would at least clarify if it is handled differently.
At:
Code:
Set gDataBase = DBEngine.OpenDatabase(DatabasePath)
The message is in Spanish, here is a similar menssage in English.
"Could not use the file because it is already in use."
-
Aug 10th, 2022, 02:42 PM
#111
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by Eduardo-
Yes.
...
"Could not use the file because it is already in use."
So Access probably performs some kind of write on connect, whereas RC6/Sqlite does not. You could try a quick write after you connect (maybe even trying to start a transaction would be enough?). If it fails then you know another app has locked the file read-only.
-
Aug 10th, 2022, 03:05 PM
#112
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by jpbro
BTW - you can have a responsive UI with a long-running process using a timer instead of DoEvents, and it won't be much slower than a tight-loop + DoEvents approach. I know you're trying to eke out every ounce of performance, so perhaps even a small hit is too much, but the basic idea is to run the tight loop for a period (say 150-250ms or so) inside a timer with the shortest interval.
Something like this:
Code:
Private WithEvents mo_Timer As RC6.cTimer
Private Sub Class_Initialize()
Set mo_Timer = New_C.Timer(1, True)
End Sub
Private Sub mo_Timer_Timer()
Dim l_Done As Boolean
Dim l_StartedAt As Double
mo_Timer.Enabled = False
l_StartedAt = New_C.HPTimer
Do
' Do stuff here
' If Done processing, set l_Done = True
Loop While (Not l_Done) And _
(New_c.HPTimer - l_StartedAt <= .15)
mo_Timer.Enabled = Not l_Done
End Sub
I'm doing something similar to the above in the following project, and it runs fast enough for me. Perhaps not up to your standard though.
Once more: there is no problem with DoEvents.
-
Aug 10th, 2022, 03:06 PM
#113
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by jpbro
maybe even trying to start a transaction would be enough?
No. You need to actually modify something.
-
Aug 10th, 2022, 03:22 PM
#114
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by Eduardo-
Once more: there is no problem with DoEvents.
Oh good, it must have just been a bad dream I had then!
-
Aug 10th, 2022, 03:42 PM
#115
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by jpbro
Oh good, it must have just been a bad dream I had then!
You must have read somewhere that DoEvents was "evil" and "should never be used".
Probably in many places by many different people.
But it is myth.
-
Aug 10th, 2022, 04:00 PM
#116
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by Eduardo-
No. You need to actually modify something.
Bummer. You could wrap the OpenDb method with your own that tries to create a random table. On failure due to SQLITE_READONLY, you could retry for a while (in case of something transient like a backup run/compression tool). Something like this (lightly tested):
Code:
Public Function GetConnection(p_FilePath As String, Optional ByVal EncrKey As String, Optional ByVal EnableNestedTransactions As Boolean = True) As RC6.cConnection
Const c_RetrySeconds As Long = 10 ' Retry getting write-able database for this long
Dim c As cConnection
Dim e As Long ' SQLite DB error code
Dim e2 As String ' SQLite DB error string
Dim t As String ' Random-ish table name
Dim r As Boolean ' If true, try to retry opening the connection, otherwise exit the loop
Dim d As Double ' Stores the timer value for when the method is called (for testing timeout)
d = New_c.HPTimer
t = "test_" & New_c.Crypt.SHA256(CStr(d) & Now, True) ' Random-ish table name
Do
r = False
Set c = New_c.Connection(p_FilePath, DBOpenFromFile, EncrKey, EnableNestedTransactions)
' Attempt to create a random table to see if the DB file is locked
c.BeginTrans
On Error Resume Next
c.Execute "CREATE TABLE IF NOT EXISTS """ & t & """ (test INTEGER)"
e = c.LastDBErrCode
If e Then e2 = c.LastDBError
On Error GoTo 0
c.RollbackTrans ' Rollback the transaction since we don't want to keep the table if it was created successfully
If e <> 0 Then
' There was an error
Set c = Nothing
If e = 8 Then
' SQLITE_READONLY - you could do something here like try to figure out who has it open,
' Or simply block for a while and retry
If New_c.HPTimer - d > c_RetrySeconds Then
err.Raise vbObjectError + e, , e2
Else
New_c.SleepEx 100
r = True
End If
Else
' Some other error, raise it immediately
err.Raise vbObjectError + e, , e2
End If
End If
Loop While r
Set GetConnection = c
End Function
-
Aug 10th, 2022, 04:45 PM
#117
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by jpbro
Bummer. You could wrap the OpenDb method with your own that tries to create a random table. On failure due to SQLITE_READONLY, you could retry for a while (in case of something transient like a backup run/compression tool). Something like this (lightly tested):
Code:
Public Function GetConnection(p_FilePath As String, Optional ByVal EncrKey As String, Optional ByVal EnableNestedTransactions As Boolean = True) As RC6.cConnection
Const c_RetrySeconds As Long = 10 ' Retry getting write-able database for this long
Dim c As cConnection
Dim e As Long ' SQLite DB error code
Dim e2 As String ' SQLite DB error string
Dim t As String ' Random-ish table name
Dim r As Boolean ' If true, try to retry opening the connection, otherwise exit the loop
Dim d As Double ' Stores the timer value for when the method is called (for testing timeout)
d = New_c.HPTimer
t = "test_" & New_c.Crypt.SHA256(CStr(d) & Now, True) ' Random-ish table name
Do
r = False
Set c = New_c.Connection(p_FilePath, DBOpenFromFile, EncrKey, EnableNestedTransactions)
' Attempt to create a random table to see if the DB file is locked
c.BeginTrans
On Error Resume Next
c.Execute "CREATE TABLE IF NOT EXISTS """ & t & """ (test INTEGER)"
e = c.LastDBErrCode
If e Then e2 = c.LastDBError
On Error GoTo 0
c.RollbackTrans ' Rollback the transaction since we don't want to keep the table if it was created successfully
If e <> 0 Then
' There was an error
Set c = Nothing
If e = 8 Then
' SQLITE_READONLY - you could do something here like try to figure out who has it open,
' Or simply block for a while and retry
If New_c.HPTimer - d > c_RetrySeconds Then
err.Raise vbObjectError + e, , e2
Else
New_c.SleepEx 100
r = True
End If
Else
' Some other error, raise it immediately
err.Raise vbObjectError + e, , e2
End If
End If
Loop While r
Set GetConnection = c
End Function
Thanks for your idea.
I was going to leave it as it is, because what user would be opening that file anyway? But you are correctly pointing that may be running a background backup utility or something momentarily locking the file.
Based in your general idea, I did something similar:
Code:
Public Property Get DatabasePath() As String
Static sValue As String
Dim c As Long
c = 0
If sValue = "" Then
sValue = App_Path & "\Data\imgcache"
If FileExists(sValue & ".dat") Then
If FileInUse(sValue & ".dat") Then 'let's wait a bit
Sleep 1000
End If
If FileInUse(sValue & ".dat") Then 'let's wait a bit more
Sleep 10000
End If
Do Until Not FileInUse(iIF(c = 0, sValue, sValue & "_" & c) & ".dat")
c = c + 1
Loop
End If
sValue = iIF(c = 0, sValue, sValue & "_" & c) & ".dat"
End If
DatabasePath = sValue
End Property
Code:
Private Declare Function CreateFile Lib "Kernel32" Alias "CreateFileA" (ByVal lpFileName As String, ByVal dwDesiredAccess As Long, ByVal dwShareMode As Long, ByVal lpSecurityAttributes As Long, ByVal dwCreationDisposition As Long, ByVal dwFlagsAndAttributes As Long, ByVal hTemplateFile As Long) As Long
Private Function FileInUse(ByVal strPathName As String) As Boolean
Dim hFile As Long
Const GENERIC_WRITE As Long = &H40000000
Const OPEN_EXISTING As Long = 3
Const INVALID_HANDLE_VALUE As Long = -1
Const ERROR_SHARING_VIOLATION As Long = 32
On Error Resume Next
'
'Remove any trailing directory separator character
If Right$(strPathName, 1) = gstrSEP_DIR Then
strPathName = Left$(strPathName, Len(strPathName) - 1)
End If
Err.Clear
hFile = CreateFile(strPathName, GENERIC_WRITE, 0, 0, OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL Or FILE_FLAG_WRITE_THROUGH, 0)
If hFile = INVALID_HANDLE_VALUE Then
If (Err.LastDllError = ERROR_SHARING_VIOLATION) Then
FileInUse = True
ElseIf (Err.LastDllError <> 0) Then
Sleep 100
Err.Clear
hFile = CreateFile(strPathName, GENERIC_WRITE, 0, 0, OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL Or FILE_FLAG_WRITE_THROUGH, 0)
If hFile = INVALID_HANDLE_VALUE Then
FileInUse = (Err.LastDllError = ERROR_SHARING_VIOLATION)
Else
CloseHandle hFile
End If
End If
Else
CloseHandle hFile
End If
Err.Clear
End Function
Now, the Set gDataBase = New_c.Connection(DatabasePath) comes with a path that is tested writable. Of course it may be a new database (still non-existent), but that's acceptable for my case.
-
Aug 10th, 2022, 06:10 PM
#118
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by Eduardo-
Code:
Private Declare Function CreateFile Lib "Kernel32" Alias "CreateFileA" (ByVal lpFileName As String, ByVal dwDesiredAccess As Long, ByVal dwShareMode As Long, ByVal lpSecurityAttributes As Long, ByVal dwCreationDisposition As Long, ByVal dwFlagsAndAttributes As Long, ByVal hTemplateFile As Long) As Long
Private Function FileInUse(ByVal strPathName As String) As Boolean
Dim hFile As Long
Const GENERIC_WRITE As Long = &H40000000
Const OPEN_EXISTING As Long = 3
Const INVALID_HANDLE_VALUE As Long = -1
Const ERROR_SHARING_VIOLATION As Long = 32
...
Well, a bit shorter than all this API-gymnastic would be a simple method-call on cFSO:
If New_c.FSO.IsFileWritable(YourDBFile) Then...
Olaf
-
Aug 10th, 2022, 11:18 PM
#119
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by Eduardo-
You must have read somewhere that DoEvents was "evil" and "should never be used".
Probably in many places by many different people.
But it is myth.
I neither think it's evil, nor that it shouldn't ever be used. However, I read many posts where DoEvents is clearly a contributing factor to a problem (including in this thread), and resolving those problems probably kills more time than using DoEvents saves. If the only reason you call DoEvents is for UI responsiveness, then IMO a timer based approach is generally better for a small penalty (maybe 5%?). Another option is using the RC6 cThreadHandler to pass it all off to another thread that can run at 100% and burn as much CPU as you want while keeping the UI responsive.
Doubling-down and telling guys like Olaf & wqweto that they don't understand DoEvents is laughable though, so thanks for the chuckle
-
Aug 11th, 2022, 06:13 AM
#120
Re: Convert binary data to a valid Unicode string whitout wasting (much) space ❓
Originally Posted by jpbro
I neither think it's evil, nor that it shouldn't ever be used. However, I read many posts where DoEvents is clearly a contributing factor to a problem (including in this thread), and resolving those problems probably kills more time than using DoEvents saves. If the only reason you call DoEvents is for UI responsiveness, then IMO a timer based approach is generally better for a small penalty (maybe 5%?). Another option is using the RC6 cThreadHandler to pass it all off to another thread that can run at 100% and burn as much CPU as you want while keeping the UI responsive.
I'm thinking in writing an article about DoEvents like I already did about Twips/Pixels/DPI awareness, another difficult subject that even some most skilled programmers do not get it right, because I see it is something misunderstood even from very skilled programmers.
Originally Posted by jpbro
Doubling-down and telling guys like Olaf & wqweto that they don't understand DoEvents is laughable though, so thanks for the chuckle
That's only because you base your thinking on prejudices, but they clearly are afraid as it was the boogeyman.
Olaf should had never say anything about whether I was using DoEvents or not, that had nothing to do with the database issue. He should had focused on his component, not in my code.
The right question would had been: what situation could cause for my component to give that error?
Someone with clarity could have asked about "if there is a DoEvents, what could had happened in the middle?", but that's it. Not to give any direction about not using DoEvents and such, that only evidence lack of understanding.
He does not have to say anything about how I do my program just because I used his component for the database stuff.
So, now because I had a problem regarding this database stuff, wqweto and anybody feel they have the right to criticize something based on prejudices?
Let's be clear about things: the fact that I now used Olaf's RC6 has nothing to do with him saying how I have to do my program. He should limit his instructions to the things that have to do with his component, not with other things.
I quite sure that I have a lot more experience programming with DoEvents than (probably) anyone that participated in this thread. It could seem pedantic but that's the truth.
Anyone with real understanding of an alleged issue would show with some lines of code where that big problem that they are talking about is. And not giving general advices against as it was the bogeyman.
I have big esteem of wqweto and Olaf, but what is laughable to me is when people follow other people blindly.
Last edited by Eduardo-; Aug 11th, 2022 at 06:36 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|