I did. Marco thought that setting db.AutoCommit=True would mean I would never get a locked database upon multiple writes. This is not the case, even with only two writing users.
So I downloaded and looked at his source code for his cubeSQLAdmin application. In it Marco never sets db.AutoCommit=True, but instead checks for db.Error and, if no error, does a db.Commit.
I changed my code according to Marco’s source code (as I described above) and have had no locking despite having a half-dozen users updating intensively.
If things change I am open to correction, and will inform you here if my optimism is unwarranted.
This is strange, David. You say that you put DB.Commit after every DB.Error=False, otherwise you do not. This is the same as Marco’s source code. So how does this change things?
db.SQLExecute( )
if db.Error then
MsgBox "Sorry, but there was a problem updating the record (" + db.ErrorMessage + ")"
else
db.Commit
end if
Sorry, if I was confusing.
[quote=371673:@David Cox]What I have discovered:
having db.AutoCommit = True before the db.Connect command causes an app crash
having db.AutoCommit = True after the db.Connect command doesn’t cause an app crash, but you can still get ‘database locked’ errors
leaving out the db.AutoCommit = True command, but placing a db.Commit command after every successful db.SQLExecute( ) (you need to check if db.Error not True) fixed the problem[/quote]
no wonder i never have problem… i use db.commit all the time… don’t use db.autocommit=true
You call this when you set db.Host, db.Port, … before you do a db.Connect.
And yes, you can use this Encryption (between your client application and CubeSQL), while CubeSQL then reads/writes to an (un)encrypted database file. Your client application doesn’t really need to care or know where/how CubeSQL gets it’s data and whether that’s encrypted or not. But what’s going over the network between your app and CubeSQL, you can define if you want that network traffic to be encrypted or not by setting db.Encryption.
You want to encrypt the database in order to prevent someone to access and read the contents of the database file.
You want to encrypt the data and sql commands when it is sent over the network, from user to the database and back, in order to protect against network sniffers.
You set the encryption property before you call .connect. A higher encryption grants more protection against brute force attacks.
I have also had issues with CubeSQL locking databases.
db.autocommit made no difference. Marco told me this has to be because of uncommitted transactions, so I checked my code carefully. I kinda fixed it by inserting commits everywhere. Not what I liked, but better be on the safe side.
About the encryption: if I set db.Encryption = CubeSQLPlugin.kAES256 that will affect my actual code ? Is it just inserting that line ?
At last, maybe you guys have an idea… I have seldom seen the DB reporting error 830 (an error occurred while executing sock_read). What could be causing this? Ideas ?
I have seen the error 830 (an error occurred while executing sock_read) a few times with the latest CubeSQL running on my Windows Server 2016. I would be also interested if anyone else has seen this or discovered what is causing it?
I have maybe 6 Users that connect to the CubeSQL though out the day so not very taxing on the CubeSQL server.
I sent a note to Marco (SQLabs) and will report back if I hear anything
Yes, just add this line where you define your connection details, it won’t affect the rest of your code but just encrypt communication between client and CubeSQLServer.
[quote=371685:@David Cox]I did. Marco thought that setting db.AutoCommit=True would mean I would never get a locked database upon multiple writes. This is not the case, even with only two writing users.
So I downloaded and looked at his source code for his cubeSQLAdmin application. In it Marco never sets db.AutoCommit=True, but instead checks for db.Error and, if no error, does a db.Commit.
I changed my code according to Marco’s source code (as I described above) and have had no locking despite having a half-dozen users updating intensively.[/quote]
Now I get it. You looked at Marco’s source code for cubeSQLAdmin, not cubeSQLServer. So you’re implementing the way he does it there, in all your database code, and forgetting about using AutoCommit altogether. I had thought you were telling me that’s how he handled AutoCommit=True in his cubeSQLServer source code, which didn’t make sense to me.
I have seen the error 830 (an error occurred while executing sock_read) a few times with the latest CubeSQL running on my Windows Server 2016. I would be also interested if anyone else has seen this or discovered what is causing it?
I have maybe 6 Users that connect to the CubeSQL though out the day so not very taxing on the CubeSQL server.
I sent a note to Marco (SQLabs) and will report back if I hear anything[/quote]
I’m pretty sure I saw that happen once in the last month or so.
[quote=371780:@Roman Varas]I have also had issues with CubeSQL locking databases.
db.autocommit made no difference. Marco told me this has to be because of uncommitted transactions, so I checked my code carefully. I kinda fixed it by inserting commits everywhere. Not what I liked, but better be on the safe side.[/quote]
I only recently got a Locked DB error, but it happened when I changed some code and did indeed forget to close a Transaction in one particular method. Otherwise I have had no such error since using AutoCommit=True.
Hmmm… At least three users have seen this. If this is not a bug It’d be nice to see some documents from Sqlabs explaining the case, where does the error come from and how to avoid it. (The same for the auto commit situation)