SQLite Connection Limit

Karbon · Post by **Karbon** » Mon Jun 23, 2008 4:30 pm

Is there any hard coded connection limit built into the PB SQLite support? I switched an app from using ODBC (with the SQlite ODBC driver) to the native PB support and now the application hangs if 3 or 4 connections are made to the db file (it seems to vary on XP and Vista?). None are explicitly asking for exclusive access but is that built-in to the PB lib somehow?

AFAIK SQlite itself enforces no limit other than whole-database locking on write (which is fine since the clients I'm speaking of only need read access)..

bobobo · Post by **bobobo** » Mon Jun 23, 2008 5:47 pm

do you use databaseerror() to recognize db/file-locks ?

i do preferr a delayed loop while databaseerror() ist not ok with sqlite.

Karbon · Post by **Karbon** » Mon Jun 23, 2008 5:51 pm

Yes, I do and there are no errors - the OpenDatabase call never returns.

Num3 · Post by **Num3** » Mon Jun 23, 2008 7:14 pm

I just tested a huge project i am working on with 8 concurrent clients and all works well... 2 databases, 16 connections...

I have noticed sqlite plugin is very picky about DatabaseQuery() and DatabaseUpdate() so be very careful and always test each sql statement with both !

If one gives an error, try the other command with the same sql statement!

Sometimes, when i'm in SQLITE MANAGER in firefox i get db locks, specially after a begin transaction / commit, they always lock the database, so try to avoid them under sqlite PB!

They work great outside but under PB i get db locks ...

Karbon · Post by **Karbon** » Mon Jun 23, 2008 7:34 pm

SQLite's locking is whole-file locking so you'll run into problems trying to write to the database from concurrent connections.

DatabaseUpdate() should be used for queries that write to the database (INSERT UPDATE DELETE, ALTER, etc), DatabaseQuery() is only used when the query returns a result set (SELECT).

In your project are you connecting to a file on a network share?

Num3 · Post by **Num3** » Mon Jun 23, 2008 7:43 pm

Yes, it's on a network share and i had no troubles so far...
I also test it on my pen drive for even slower times, and it works too.

Oh, wait, but i'm using the beta revised version!

http://purebasic.com/beta/windows/

Maybe that helps!

Karbon · Post by **Karbon** » Mon Jun 23, 2008 8:15 pm

I'll re-run all my tests with the newest version..

Karbon · Post by **Karbon** » Mon Jun 23, 2008 9:06 pm

Unfortunately it is still happening.. It looks like the very first call to OpenDatabase on the 4th workstation hangs everything.

Post by **Fred** » Tue Jun 24, 2008 12:20 am

OpenDatabase for SQLite is just a wrapper to sqlite3_open_v2(), so i don't think it's specific to PB, you may try to test with the API to be sure..

dige · Post by **dige** » Tue Jun 24, 2008 8:18 am

Check out "Pragmas" http://www.sqlite.org/pragma.html
if you dont use a single access.

pdwyer · Post by **pdwyer** » Tue Jun 24, 2008 8:29 am

Fred wrote:OpenDatabase for SQLite is just a wrapper to sqlite3_open_v2(), so i don't think it's specific to PB, you may try to test with the API to be sure..

Thats useful to know, what params are used?

The sqlite3_open_v2() interface works like sqlite3_open() except that it acccepts two additional parameters for additional control over the new database connection. The flags parameter can be one of:

- SQLITE_OPEN_READONLY
- SQLITE_OPEN_READWRITE
- SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE

The first value opens the database read-only. If the database does not previously exist, an error is returned. The second option opens the database for reading and writing if possible, or reading only if if the file is write protected. In either case the database must already exist or an error is returned. The third option opens the database for reading and writing and creates it if it does not already exist. The third options is behavior that is always used for sqlite3_open() and sqlite3_open16().

Post by **Fred** » Tue Jun 24, 2008 12:55 pm

dige wrote:Check out "Pragmas" http://www.sqlite.org/pragma.html
if you dont use a single access.

By default, it allows many access, or i miss something ?

pdwyer: it uses SQLITE_OPEN_READWRITE

dige · Post by **dige** » Tue Jun 24, 2008 2:09 pm

Yes, by default is: synchronous = FULL and locking_mode = NORMAL,
so my posting is redundant.
It was just an idea ... :?

pdwyer · Post by **pdwyer** » Tue Jun 24, 2008 2:18 pm

Cheers Fred!

Karbon, maybe as a test you can try the API method and use the readonly flag to open the DB, see it it helps.

My guess is that over time the PB sqlite implementation will mature and put more options for us to use but it might not quite be everything to everyone in this first release

Karbon · Post by **Karbon** » Tue Jun 24, 2008 2:22 pm

I've solved it, or at least I think I have..

The database was being locked in between connections (at application start the DB is written to several times). Apparently the ODBC driver had additional checks built-in and that is why it was working before using ODBC.

Thanks to all!