Re: [sqlite] locked - what am I doing wrong?

[D. Richard Hipp]

David Swigger wrote:
I open two connections:
pConn1 = sqlite_open(m_sDbPath.c_str(),0,NULL);
pConn2 = sqlite_open(m_sDbPath.c_str(),0,NULL);
I do the query and compile a query on conn1, then
start stepping through the rows of results.
If I try to do any execs using pConn2 - I always get
an error code saying the database is locked.
As pointed out by others, the query is doing a read-lock
on the databae which prevents writes to the database from
other connections.
Several things you can do here:

  1:  Use sqlite_get_table() or the equivalent to load
  the entire result set into memory.  This will release
  the read-lock quickly and allow you to write as you
  step throught the result set.
  2:  Copy the result set into a temporary table then
  step through the temporary table.  Like this:
CREATE TABLE results AS SELECT ;
SELECT * FROM results;
  Read-locks are not held when reading from temporary
  tables so you should be able to write to the main
  database while the SELECT of the temporary tables
  is on-going.
  3:  Sometimes an ORDER BY clause on the SELECT statement
  will cause the entire result set to be read into memory.
  (This happens if the ORDER BY clause cannot be satisfied
  by an index.)  This is usually considered a bug, but in
  your case, you might be able to employ this property as
  a feature.  As soon as the result set is entirely in
  memory, the read-lock is released and other threads can
  begin writing to the database.
  4:  If you only do a single sqlite_open() and then compile
  the query and the inserts from that one connection,
  then you can read and write the database at the same
  time.  You still won't be able to read and write the
  same table at the same time, but you can read and write
  separate tables in the same database.


--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] locked - what am I doing wrong?

[Kennedy, Dan]

When you first call sqlite_step() for your SELECT statement, SQLite
grabs a read-lock on the database file. It doesn't release this lock
until you call sqlite_finalize(). When you execute your UPDATE, SQLite
needs to get a write-lock on the database file. It can't get the 
write-lock while another connection (including one from the same 
process) is holding a read-lock.

So there is no way you can perform an UPDATE, INSERT or DELTE operation 
in the middle of a SELECT. Unfortunately, I think you'll need to 
structure your program differently.

Dan.

Re: [sqlite] locked - what am I doing wrong?

[ben . carlyle]

- Forwarded by Ben Carlyle/AU/IRSA/Rail on 09/01/2004 10:44 AM -


Ben Carlyle
09/01/2004 09:35 AM


To: "David Swigger" <[EMAIL PROTECTED]>@CORP
cc: 
Subject:Re: [sqlite] locked - what am I doing wrong?

G'day,





"David Swigger" <[EMAIL PROTECTED]>
09/01/2004 12:04 AM

 
To: <[EMAIL PROTECTED]>
cc: 
Subject:[sqlite] locked - what am I doing wrong?


> I am having a bit of a hard time understanding something:

> When I am in the middle of stepping (sqlite_step) through the
> results of a table query, the database is locked.  Is this normal?  Have
> I missed something?

This is discussed in Question 7 of the FAQ, but I think the first 
paragraph is a little misleading. I think that:
"Multiple processes can have the same database open at the same time. 
Multiple processes can be doing a select at the same time. But only one 
process can be making changes to the database at once."
should read:
"Multiple processes can have the same database open at the same time. 
Multiple processes can be doing a select at the same time. But a process 
can't make changes to the database while any readers or other writers are 
active."

The important thing to understand here, is that you're not stepping 
through a set of results that you have in memory and that were extracted 
from the database. You are (at least for simple queries) stepping through 
data that is still in the database. Because you're still actively reading 
the database the writer thread can't get access until your query is 
finished. If, instead, you want to load the whole results set into memory 
before stepping through them consider the sqlite_get_table_* functions. 
Alternatively you can collect the results into memory any way you like 
before stepping through the structures you've created yourself.

Benjamin.




-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] documentation question - functions

[Kurt Welgehausen]

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] documentation question - functions

[rob]

I am trying to find a list of what functions sqlite supports. Is there any 
documentation on that anywhere?

Thanks,

-- 
Rob

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Mac OS X and Java

[D. Richard Hipp]

Tim Anderson wrote:
Using version 2.8.9 and OS X 10.2.8

I had no trouble compiling Sqlite, although if I enable-shared I get a
bus error on one of the tests (in make test). 

FWIW, the relevant ticket is:

  http://www.sqlite.org/cvstrac/tktview?tn=369

--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] Mac OS X and Java

[Tim Anderson]

Using version 2.8.9 and OS X 10.2.8

I had no trouble compiling Sqlite, although if I enable-shared I get a
bus error on one of the tests (in make test). I have to disable shared
library support anyway because...

I had considerable difficulty compiling the Java wrapper at
http://www.ch-werner.de/javasqlite/. The configure script needs
--with-jdk and --disable-shared; I couldn't get it to work with the
shared library build of Sqlite. I also had to add a new target for
libsqlite_jni.jnilib, built using gcc -bundle. However I do now have it
working :-)

If anyone more knowledgeable has a revised configure script or Makefile
for the Java wrapper on OS X, I'd be interested to see it.

Tim




-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] locked - what am I doing wrong?

[David Swigger]

Hello everyone,

I have built sqlite myself (using VC 6.0),

I am using the sqlite_step interface for iterating a tables
rows.

I am having a bit of a hard time understanding something:

When I am in the middle of stepping (sqlite_step) through the
results of a table query, the database is locked.  Is this normal?  Have
I missed something?

I open two connections:
pConn1 = sqlite_open(m_sDbPath.c_str(),0,NULL);
pConn2 = sqlite_open(m_sDbPath.c_str(),0,NULL);

I do the query and compile a query on conn1, then
start stepping through the rows of results.

If I try to do any execs using pConn2 - I always get
an error code saying the database is locked.

If I finalize the query (on conn1) then execs work.
I have THREADING=1 defined when I compile (just for your info).

So I am confused.  Lets say I wanted to do this (psuedocode):

pConn1 = sqlite_open(m_sDbPath.c_str(),0,NULL);
pConn2 = sqlite_open(m_sDbPath.c_str(),0,NULL);

(create & compile query selecting ROWID,* from my_table for conn1)
sqlite_step(m_pVM, &numCols, &results, &cols)

.. look at at stuff

..find a piece of data that needs updated.
sqlite_exec(pConn2,sqlupdatestatement,NULL,NULL,NULL);//<--Always returns a
lock

..continue stepping..

Thanks for any help,

David



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] web hosting recommendations?

[Tom Weir]

Personally, I use pair.com. They provide good service, and many 
features. Highly recommended. And the do appear to have DBD::SQLite 
installed.

On thing to consider: even if a host doesn't have DBD::SQLite installed 
by default, its reasonably easy for you to build/install yourself. The 
DBD::SQLite source tarball includes sqlite, so all you need to do is 
run the build scripts: it is, IMHO, the easiest DBI driver to build.

Of course that assumes that the host you choose allows shell access & 
allows you to access the GNU build tools (pair.com does...)

Regards,

Tom

At 1:08 AM + 1/8/04, rob wrote:
Hi list,
Can anyone make any recommendations for a good web host that
offers SQLite--  and along with that either has DBD-SQLite and Perl
or PHP available with it?
Thanks.
Rob Bryant


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]