Databases

[Joseph E.]

I'm very new to databases, but SQL and RB seem easy enough.

I'm planning a web app that will have multiple users. I realize you need to open and close a database as quickly as possible, but what happens if two sessions are writing to it at the same time? There's a chance that could happen in a large multi-user platform.

I've read the threads on locking, but I'm not sure what actually got implemented and what didn't.

Also, I see some stuff about MySQL instead of SQLite. Which one should I use, and where are the commands for MySQL documented? The runbasic docs seem to only include SQLite methods.

I'll also need to be able to order records by the date/time they were posted.

I need a database that "just works", with minimum runtime problems (reliability is key). Suggestions and tips would be great!

[kokenge]

RunBasic does not support mySQL. However the next release is supposed to support it.

Personally, I do not have time to wait for mySQL, so I develop using SQLite knowing I will convert. But I get the job done and tested without waiting.

Not a simple answer to DB locking.

If your users are simply looking at something in the DB and then updating a record you probably will not have a problem..

If you open the DB more than once with updates in the same program you will get locks.
For example if you have 2 opens like:
sqliteconnect #sql1, "C:\rbp101\projects\my_project\data\my.db"
sqliteconnect #sql2, "C:\rbp101\projects\my_project\data\my.db"
SQLite seems to confuse the 2 opens even if you close one before updating with the other.

Also SQLite triggers cause a lot of problems with locking. No problem in mySQL. And triggers save a lot code and increase integrity.

Programming with SQLite is also a lot more complex then mySQL because you are always worried about the connects and disconnects in order to avoid locks. In mySQL you simply do not worry about it.

SQLite does not have replication. So if you are worried about integrity of your data, you need to bring down SQLite and copy the .db file somewhere. And remember with replication you have failover..

My simple advice is to go ahead with development in SQLite.
Then convert to mySQL when it's available.
The DB managers allow you to dump the tables and data and load it into other DB's.

SQL commands are almost exact between the DB's. If you do have to change some commands it can simply be done with search and replace in an editor.

I'd download the SQLite management program in the wiki project area. This will let you create and manage your DB.

I'd download RBgen in the wiki project area. It will generate your RunBasic program from the DB you create ...

Hope this helps..
Dan

[Joseph E.]

Thanks kokenge. That's really helpful. I'll continue looking into everything and start planning an sqlite database...