Sqlite_busy

[linum]

I'm stumped on an SQLite issue. I have a database app that stores it's data on an SQLite database in a Network Drive. There are multiple users that use the software at one time. The issue is that sometimes different users end up writing to the database at the same time, which causes the database to lockup.

I've been reading that I can first check the database to see if it's busy by using SQLITE_BUSY before trying to write to it. The problem is that I don't have a clue on how I can include SQLITE_BUSY on my code. Can somebody please help me with a small code example?


I'm really thankful for all your help...

 

[Caravelle]

I've been reading that I can first check the database to see if it's busy by using SQLITE_BUSY before trying to write to it.

I'd love to be told I'm wrong, but I don't think you can do this with B4PPC. The SQLite function you refer to needs a specific command to access it, and B4PPC does not provide this command. If you don't need to use a device there are various desktop solutions - for example PHP is free and has a more complete (and complex) set of sqlite functions; most programming languages have sqlite extensions available as add-ons these days.

Caravelle

 

[agraham]

From the SQLite docs about network use of SQLite. It's not really designed for network use. Especially note the second paragraph.

Situations Where Another RDBMS May Work Better

•Client/Server Applications

If you have many client programs accessing a common database over a network, you should consider using a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, the file locking logic of many network filesystems implementation contains bugs (on both Unix and Windows). If file locking does not work like it should, it might be possible for two or more client programs to modify the same part of the same database at the same time, resulting in database corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it.

A good rule of thumb is that you should avoid using SQLite in situations where the same database will be accessed simultaneously from many computers over a network filesystem.

 

[Caravelle]

I assumed "linum" had read the help - maybe wrongly - but I think it's still true to say that even if you want to use B4PPC for the sort of task it's not designed for, you can't because it just doesn't give you the facilities to check if the database is busy or to lock it while your transaction is in progress. It's the need to do things B4PPC isn't designed to do that results in the sort of libraries that Mr Graham and others very kindly provide for us.

I have a sqlite database which is constantly being updated 24/7 with thousands of items of data by a machine (a Kinetic SBS-1 "virtual radar" system). I would never dream of trying to interrupt the flow without properly queuing up to take my turn and putting the machine data on hold until I've finished. This is easily achieved with SQLite functions and I use Delphi 7 and the Disqlite3 add-on. It all works so well that I have converted all my other databases from good old faithful dbf to sqlite. B4PPC is great for quick lightweight programs to view and update personal data where you don't have to worry about sharing issues - and for most people that's all they want, simple data work on their devices.

Caravelle

 

[agraham]

The limitation in his case is not Basic4ppc, or rather the SQLite library, but the fact that he is using multiple clients to directly access a single database file over a network filesystem. For applications such as a web site where a single server application fronts the database and deals with the clients then SQLite will work quite well, even in Basic4ppc. The main problem for multiple clients, is that, even if the file locking works properly, SQLite locks the entire database on write (and for write on read) which kills concurrent access performance especially on a network where file access is much slower than to a local disc.

In case anyone hasn't found it the SQLite page here documents Appropriate Uses For SQLite

 

[Caravelle]

Yes, but the specific question he asked was how to access a specific SQLite function, SQLITE_BUSY, using B4PPC code.

He and the other users may not be worried about "performance issues", we aren't all bothered about having to wait a few microseconds longer, and there's no reason why, despite good advice and warnings, someone shouldn't want to try to experiment. But, to answer the coding question, he doesn't have the option to call SQLITE_BUSY, does he?

Caravelle

 

[linum]

Wow, thanks for the wealth of information you all provided. I had been thinking of writing a server side program so that all user transactions are sent to and handled by the server app and the server app is the only one that can write to the database, so I think I'm going to go this route.

How about the simple fact that different users are reading the database at the same time? Will this still lock the database? If so, then I guess I really should look at a whole different solution altogether.

 

[agraham]

I don't know what your issue is but he says he is getting lockups not performance degradation. I am merely pointing out that the SQLite documentation indicates that this is not a good scenario in which to use SQLite due to possibly imperfect network file locking.

 

[2220]

If you are developing in Delphi, use firebird (interbase) database or MySQL. It's fully a powerful multi user db. For standalone apps, use SQLLite.

I develop device apps in B4PPC and use SQLLite. For multiuser desktop apps: SQLServer, Firebird (Interbase), MySQL (WEB apps) and SQLLite is fine for devices. It's great cause you can use it in IPhone Devs, Android Devs, and of course WM Devs and can share the same db in different O.S.