SQLite Connection Limit

Karbon · Post by **Karbon** » Wed Jul 02, 2008 5:50 pm

More on this..

The database is being locked but the problem is that DataBaseUpdate() is taking several minutes to return so the error can be read when it is. The lock is no problem, the software cause just pause for a few seconds and re-try.

It seems to only happen when the database is on a network drive and accessed by more than one application.

So, is there any reason that any of the query functions would take so long to return with the "database is locked" error?

Num3 · Post by **Num3** » Wed Jul 02, 2008 7:23 pm

Yes this was my nightmare too

I solved it by refining my sql queries and not using any type of begin transaction / commit, just a for / next loop with database updates or if i got this kind of delay, used databasequery instead... Not correct, but it get's the job done!

Karbon · Post by **Karbon** » Wed Jul 02, 2008 7:27 pm

Good, then I'm not crazy! Even trapping the error is causing a 1+ minute delay so it's a pretty serious problem.

When I was using the SQLite lib directly before (though a userlib) that didn't happen so I'm guessing it is something with the new PB->SQLite hook.

Fred? Help!?

npath · Post by **npath** » Thu Jul 03, 2008 12:40 am

Karbon,

You are not alone. I am also having trouble with database locks when using the databaseupdate command. I suspect the problem is with the PB wrapper, as I have used this SQLite database with several other languages, including RealBasic and Powerbasic. I am not having any trouble querying the database. However, any attempts at updating the database results in a lock. I have tried numerous approaches to prevent this, but perhaps I am missing something.

npath

npath · Post by **npath** » Thu Jul 03, 2008 12:43 am

Also, I forgot to mention that error traps are resulting in a 30-60 second delay before returning the "database is locked" error.

Does anyone know how to unlock databases?

Karbon · Post by **Karbon** » Thu Jul 03, 2008 12:47 am

There isn't any way to unlock a database except for all clients to release the lock. In my case I know it isn't locked even when my PB application is telling me it is. I've written another utility in C to connect to the SQLite database and issue SELECT and UPDATE queries - those queries complete but any queries issued through the PB application fail... Using the straight C API the query functions return instantly if the database is locked.

Locking is tricky with SQLite since it locks the entire database when a write operation is in progress. Still, the DatabaseQuery and DatabaseUpdate function should return instantly if the database is locked.

npath · Post by **npath** » Thu Jul 03, 2008 12:51 am

Interesting. Sounds quite suspicious for a bug.

npath · Post by **npath** » Thu Jul 03, 2008 12:53 am

Karbon,

Do you have any snippets for doing an update using straight API? Perhaps I can bypass the supposed lock.

Karbon · Post by **Karbon** » Thu Jul 03, 2008 12:59 am

Only in C, and it is all right here : http://www.sqlite.org/c_interface.html

Can't use any of that inside a PB application though. I wrote a standalone program to access my databases.

I've ported all the database access to use the PB Database library function so I hesitate to try to find a userlib that directly uses the SQLite DLL at this point. Hopefully it is something Fred & CO can sort out for us pretty quickly -- they do rock after all

npath · Post by **npath** » Thu Jul 03, 2008 2:42 am

Thanks Karbon,

I too have rewritten an application, converting over to built-in SQLite database functionality in PB. I will also wait for a fix, and try to find a work around in the meantime. I have versions of the application in numerous other languages, and find that Purebasic is the best of all. Fred and the team have done an amazing job.

Fangbeast · Post by **Fangbeast** » Thu Jul 03, 2008 4:22 am

[quote="Karbon"]Only in C, and it is all right here : http://www.sqlite.org/c_interface.html

Can't use any of that inside a PB application though. I wrote a standalone program to access my databases.

I've ported all the database access to use the PB Database library function so I hesitate to try to find a userlib that directly uses the SQLite DLL at this point. Hopefully it is something Fred & CO can sort out for us pretty quickly -- they do rock after all :-)[/quote]

Karbon, I use TS-Soft and company's SOURCE wrapper that directly imports the pb4.2 statically linked sqlite lib functions and it works a treat. Now that I almost know what I am doing (LOL).

So, it's not a user lib, it's a source format wrapper. That way, you can inspect and modify it yourself.

pdwyer · Post by **pdwyer** » Thu Jul 03, 2008 6:11 am

There are OSL and inc files around for using the SQLite API directly. Probably all tips and tricks SQLite posts more than 3-4 months old are not using PBs implementation.

Karbon · Post by **Karbon** » Thu Jul 03, 2008 1:17 pm

Hi Fang.. Is there a definitive version of the include you're using? I see several with 8 pages of suggestions and reports of strange things.. Where is the latest version?

gnozal · Post by **gnozal** » Thu Jul 03, 2008 1:25 pm

Karbon wrote:I see several with 8 pages of suggestions and reports of strange things.. Where is the latest version?

The latest version should be here : http://www.purebasic.fr/english/viewtop ... 853#240853

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

Why are you all intent on doing something SQLite's developer specifically recommends not doing? i.e. sharing a SQLite database on a network share.

If you want to share a database on a network why don't you use something like Firebird which is designed for the job?