SQLite Connection Limit

Karbon · Post by **Karbon** » Thu Jul 03, 2008 9:35 pm

Because recommendations aren't laws and I've been doing this with SQLite for years. It was only the PB implementation that has been having trouble.

*edit - and in the end this problem didn't have anything to do with network drives - the same problem could have happened on a local drive too.

the.weavster · Post by **the.weavster** » Thu Jul 03, 2008 10:03 pm

Karbon wrote:recommendations aren't laws

It's not against the law to ride a moped down the A1, but I wouldn't recommend it.

Karbon · Post by **Karbon** » Thu Jul 03, 2008 11:59 pm

Yet I'm sure plenty of people get away with it

mesozorn · Post by **mesozorn** » Sun Mar 15, 2009 5:11 am

What is the current status of the PB Sqlite implementation regarding all these bugs and errors? I am just starting out here and will in fact need to use a Sqlite database file on a network drive, with concurrent read/write operations occurring from multiple computers on the network.

And I really need it to work (as) cleanly, smoothly, and perfectly right out of the box (as possible). What is my best bet here with PB? The built-in implementation? Some other available library? What is the most reliable, idiot-proof way to accomplish Sqlite network access in PB?

Karbon · Post by **Karbon** » Mon Mar 16, 2009 2:07 pm

As far as I can tell the 1 connection limit is still imposed by PureBasic. You can get around it by using the ODBC driver for SQLite.

SQLite is not a very good multi-user database. For multi-user, believe it or not, you're better off using Access. It isn't because of any limitation with SQLite directly, it's more of a problem with the latency of network drives. You have to test *every* result and make sure it isn't returning SQLITE_BUSY (and re-submit your failed query as many times as is needed) -- that pretty much eliminates atomic read/write operations and leads to all sorts of nightmares. Trust me

mesozorn · Post by **mesozorn** » Mon Mar 16, 2009 3:15 pm

Thanks for your reply, Karbon. I have a few follow-up questions..

Karbon wrote:As far as I can tell the 1 connection limit is still imposed by PureBasic.

What do you mean by 1 connection limit..? I've read through all posts in this thread but am not sure I understand what that refers to...

You can get around it by using the ODBC driver for SQLite.

ODBC? I've always disliked ODBC inherently because it relies on a Data-Source-Name "bridge" being provided between the application and the database, by the Windows OS. One of the things I've always liked about Sqlite is that it needs no configuration, just a direct connection between the app and the DB file. All the same, what do you mean by the ODBC driver for Sqlite? What is that and how is it used? Is a DSN required?

SQLite is not a very good multi-user database. For multi-user, believe it or not, you're better off using Access.

But.. if I'm developing an app in PB that will be distributed to people who don't have Access installed on their machine, that's not possible, right?

You have to test *every* result and make sure it isn't returning SQLITE_BUSY (and re-submit your failed query as many times as is needed)

This is something I still don't understand. Over at Sqlite.org it says:

When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business.

That makes it sound like there is no need to test for sqlite_busy, since the other processes just wait for the file to become unlocked again, and then proceed with their tasks, automatically. Why does Sqlite.org say that if it's not true?

Obviously I am new at this, and just trying to understand what's involved. Earlier in the thread you mentioned you had been doing this (multi-user network access with Sqlite) for years, but then also warn about nightmares. Sqlite is my only option as my app will need to be distributed to people elsewhere and work with zero configuration. The users will not know how to set anything up, so it just has to work "right out of the box" without setting up data source names or anything like that. Do I have another option?

Thanks again...

the.weavster · Post by **the.weavster** » Mon Mar 16, 2009 7:05 pm

However much you want SQLite to do the job it's just not the right tool in this circumstance and from personal experience I wouldn't recommend sharing an Access database on a network either. You're just likely to cause yourself a lot of heartache and if it's an app for a client they may well end up being pretty p****d off with you.

An install is a one off thing don't compromise your whole app to try and circumvent it, just write some clear instructions of how to do it.

If you want to share a database over a network a SQL server really is the way to go. There's plenty of high quality free ones to choose from too e.g. Firebird, MySQL or Postgres.

Fangbeast · Post by **Fangbeast** » Mon Mar 16, 2009 8:20 pm

Problem with MySQL; as good as it is; is that it isn't really free. The moment you start wanting to use it for anything other than personal use, there is a world of pain in the payments and licensing area.

the.weavster · Post by **the.weavster** » Mon Mar 16, 2009 9:21 pm

Fangbeast wrote:Problem with MySQL; as good as it is; is that it isn't really free. The moment you start wanting to use it for anything other than personal use, there is a world of pain in the payments and licensing area.

I think you're probably right although I'm not sure anybody really understands MySQL's licensing policy. I've seen very long-winded threads in various forums with people arguing back and forth when you have to pay and when you don't.

Probably best just to avoid the hassle and use one of the others.

Fangbeast · Post by **Fangbeast** » Mon Mar 16, 2009 11:28 pm

the.weavster wrote:
Fangbeast wrote:Problem with MySQL; as good as it is; is that it isn't really free. The moment you start wanting to use it for anything other than personal use, there is a world of pain in the payments and licensing area.
I think you're probably right although I'm not sure anybody really understands MySQL's licensing policy. I've seen very long-winded threads in various forums with people arguing back and forth when you have to pay and when you don't.

Probably best just to avoid the hassle and use one of the others.

I've understood some of the long winded stuff as my wife works for lawyers and she teaches me often. Come to think of it, I have yet to get a straight answer out of her for anything when asked (evil grin)

Bottom line is (according to her and another lawyer I asked), personal use is defined as "Not making any money off using the technology".

The moment you make *any* money off using MySql, that's no longer "personal use" and you have to pay buckets. And it's not cheap.

How does FireBird compare to Sqlite? Is it network capable? Pity SqLite isn't, it's a nice system that I am used to.

blueb · Post by **blueb** » Tue Mar 17, 2009 2:36 pm

Fangles....

There are some fellas over at one of the PowerBASIC forums that have a free (and open source) SQlite Server front-end that has been around for 6 months or so and appears to be rock solid. It's called SQLitening.
http://www.planetsquires.com/files/sqlitening_111.zip

You can use it as is, modify the source or...?

I'm waiting for some enterprising Pure master to write a PBI file for the rest of us. {hint}

I wrote one, but can't get it to work. You might want to use it as a starting point, just ask me.

--blueb

the.weavster · Post by **the.weavster** » Tue Mar 17, 2009 5:35 pm

Fangbeast wrote:Bottom line is (according to her and another lawyer I asked), personal use is defined as "Not making any money off using the technology".

The moment you make *any* money off using MySql, that's no longer "personal use" and you have to pay buckets. And it's not cheap.

I read somewhere if you don't distribute the server with your commercial app but your client downloads and installs the server themselves then it's still free, do you know if that's right?

Fangbeast wrote:How does FireBird compare to Sqlite? Is it network capable?

Firebird is a full-featured SQL server, it will run on a network or you can run it locally as a service.

You can also use the embedded version which is just a *.dll but that way it becomes single-user. Having said that though the api is the same as is the file format so it's easy to switch your app from single-user to client/server.

Fangbeast · Post by **Fangbeast** » Wed Mar 18, 2009 12:56 am

I read somewhere if you don't distribute the server with your commercial app but your client downloads and installs the server themselves then it's still free, do you know if that's right?

No, didn't know that but I'll read it again and ask her to take a look as well. Damn these complicated EULAS.

You can also use the embedded version which is just a *.dll but that way it becomes single-user. Having said that though the api is the same as is the file format so it's easy to switch your app from single-user to client/server.

Thank you for that. If i ever have to get back to client/server in the future, this may be a way to go.