SQLite

Hello,
Can I use SQLite for an Application which runs on a network. Database is read & write by 5 to 10 users at a time? what about the speed for read, write & queries?
Thanking you,
Regards,
Dhiraj

You can but you shouldn’t :wink:

If you want to share a database on a network go for a client/server option.

CubeSQL is your friend (https://www.sqlabs.com/cubesql.php)

speed depends on data quantity.

speed depends on the quality of the query

+1 for Cubesql.

++1 for Cubesql :smiley:

speed depends on the data base size and the kind of query.

Thanks to all of you. I need a database having 2 features 1. No installation 2. Free
Regards,
Dhiraj

I preface this comment with the acknowledgement that I have NO EXPERIENCE with cubeSQL.

I see this in the FAQ of cubeSQL website. It seems a little strange to me that they have not migrated to the verbiage of XOJO.

[quote]Can I access cubeSQL from languages other than REALbasic?
Yes. cubeSQL can be accessed from PHP, C, JSON and any application that support the Open Database Connectivity (ODBC) standard.[/quote]

Gives me an uneasy feeling that they are not really “on the ball”.

There is no server without installation. For free you can get PostgreSQL. Valentina also can be used as SQLite server. They hide that pretty well.

for postgres, I can recommend “postgres by bigsql” for their hassle-free installers
http://www.openscg.com/bigsql/

for a normal postgres, you have a lot of work before it works as a server.

An alternate solution might be to write a web app that interfaces with the actual database and a desktop app that accessed the web app. There are examples of this (video) on the xojo webinar pages.
That way, the web app handles the database traffic from multiple users. I haven’t done this, but it looks interesting

Sorry guys, but I think SQLite can be good choice.

You can run SQLite in the so named “WAL”-Mode, which gives you the possibility to connect to the DB from several users at the same time. It is really fast and has a good performance. Sometimes one does not need a server/client system or you can’t build one into a given infrastructure. In this case SQLite is your friend.

I had an application with up to 50 users at the same time, which are reading and writing from the SQLite DB. Worked perfect for me.

Only thing you have to keep in mind is, that your application needs the full file access tho the SQLite DB-file, since SQLite is a simple file in your filesystem.

If you go the Valentina route you’ve got the database server and reports covered.

Another option is creating your own JSON-RPC server and getting that to handle interactions with the database. I have done something similar but I used PureBasic for the server side because it can compile shared libraries which meant I could create modular ‘services’ which were just made available simply by putting another shared library in the appropriate folder. Perhaps it would be possible to do something similar using XojoScripts.

[quote=388350:@Lars Lehmann]I had an application with up to 50 users at the same time, which are reading and writing from the SQLite DB. Worked perfect for me.[/quote]Or… I’ve got away with it so far. :slight_smile:

If you have just a regular application with no high load on the database ( I mean several read/write processes per minute) then SQLite has no issues or performance lags.

From SQLite’s website:

[quote][i]If there are many client programs sending SQL to the same database over a network, then use 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, file locking logic is buggy in many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in 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 to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network.[/i][/quote]

[quote=388272:@Dhiraj Mehrotra]Hello,
Can I use SQLite for an Application which runs on a network. Database is read & write by 5 to 10 users at a time?[/quote]

I agree with Steve Wilson. You should read:

http://www.sqlite.org/whentouse.html

before deciding.

We did a session at our conference about SQLite that might be helpful. Here is the link.