Database locking thoughts

[kokenge]

I tried it about 6 or 8 months ago. And yes it's windoze only. But the big problem is that you now have another layer. Plus I had some complex SQL that didn't work, but they worked in the query of the runbasic SQLite project. Then you suddenly have a real problem!

I can't talk for anyone else, but for me, SQL gets completed enough without another layer.

Thanks for the help..

[Mystic]

I'm just now getting into trying to write a program in Run BASIC and have been reading the database locking threads with interest.

This may be a really stupid question, but would it help a locking situation if you created different db's for each table?

i.e. customers.db, items.db, log.db

Then you would only need to open the database where you are performing the wite and it would cut down on the number of folks that might have a particular database open and locked.

Might increase difficulty in doing complex searches though.

Am I completely in left field with this line of thought?

[kokenge]

I've spent weeks trying every combination possible. Basically it depends on what you do, and not the number of tables or their size. I did the same thing you suggest by moving some smaller tables to SQLite memory. Therefore it had multiple databases, one for each memory table and the disk tables. Even then it locks. From everything I can tell, it treats locks as if they came from a single database, and only has a single instance of locks.

Simple things like engineering tasks for conveyors, gauges, scales, levels, gates and the like will probably be ok. Business applications are more complicated and will probably be a problem.

I can almost guarantee you will get a lock if you need to have two SQLite opens. Like a outer loop reading a file and a inner loop updating it. This cannot be solved by moving the file to "MEMORY" for the outer loop. It still locks. It's the only RB job running, and you would think that since the update is a separate DB, it wouldn't have anything to lock against. But it does. However, take away the outer loop in it's own isolated DB and the locks disappear.

I tried all the tricks, and it cannot be solved. The best advice I can give is go ahead and write your application, knowing you will get lock in SQLite, and hope a release of RB comes out with support for a managed DB.

BTW: An easy way to get around writing code for your application is to create your table and use RBgen to generate your program. Then you don't have to write any code.

Good luck!
Hope this helps.
Dan

[Mystic]

I read on the SQLite site that Windows file systems have locking issues.

Are you running your databases on a Windows box or on Linux?

Currently I'm developing on my Mac, but since Mac's are a no no on our network I'll need to move the completed code to our Webserver, which is an Ubuntu box.

[kokenge]

I had exactly the same issues on Windows and Linux. I used Ubuntu and Mint. Haven't tried Mac.

SQLite is a standalone piece of code that is called by RB. So it seems to do the same thing no matter what. SQLite is not managed so to make things simple and protect itself, it locks the entire database.

The good news is that your programs run on other systems without any changes. Very good when you consider that you usually have to tweak you code on other languages to do this.

The only problem I see in is converting to mySQL or Postgres or whatever. Managed DB need to be logged into. This means your Log in procedures have to be altered so that each users code knows the DB login and what privileges they get. So make sure your user master file is set up to handle this.

You have to write a lot of extra code for SQLite that is not needed in other DB's.
For one, you can start using DB triggers again. SQLite has triggers, but again - stay away - because you will definitely get lockups with the DB doing I/O and your programs also doing I/O. This means you have to write anywhere from 10 to 25 percent more code with SQLite. All the code you wrote to make sure you can connect and disconnect also goes away.

Hope this helps
Dan

[Carl Gundel - admin]

I do have a version of Run BASIC now that can access both MySQL and ODBC databases. It isn't ready for any kind of release.

I hope to have something soon.

-Carl