On 9 Oct 2010, at 7:49am, P Kishor wrote:
> <stuff>
My answers to these things are a little weird and I'm not sure I understand at
all what you're doing. But it's a weekend so I'll answer anyway and let you
correct me where I've misunderstood it.
> SELECT col1 FROM table WHERE condition;
> if (col1 exists) {
> UPDATE table SET col2 = <new value>
> }
>
> Well, I get the "db is locked" error at the UPDATE statement.
Because you're trying to UPDATE a table while you're in the middle of looking
at it, and the UPDATE you make might change the thing you're looking at, which
would ruin the SELECT. There are two cures:
Cure#1: Do the whole SELECT first, and store the answer in an array. Then work
through the array issuing UPDATE commands. This is not the most efficient way
to use SQL but it makes sense to people who are used to programming.
Here is cure #2, which is more likely to be the SQL way of doing it.
I'm not really sure what you mean by your '(col1 exists)' bit, but let's get
rid of it for a second and look at the rest of it:
> SELECT col1 FROM table WHERE condition;
> UPDATE table SET col2 = <new value>
The way you do this in SQL is this:
UPDATE table SET col2 = <new value> WHERE condition
So you have one instruction that does all the work for you, including ignoring
records it doesn't need to change. Now, what did you mean by '(col1 exists)' ?
> So, I want all the perl modules to be loaded when Apache2 starts, and
> then a $dbh created for each user when the user comes to the web site,
> but not recreated for the same user on every reload.
I think you may have misunderstood how Apache does its job. Unless you do
things in an unusual manner, Apache treats each page request as a separate
'program', It spawns a separate 'httpd' process to respond to each request,
and the various processes don't talk to one-another. Variables and handles
belong to one particular request for a web page, and disappear as soon as the
reply to that request has been sent.
So you can't store a database handle between page requests. Nor should you
want to, because you won't be able to tell when the last request comes: any
user can close their browser window at any time and you'd never know when to do
sqlite3_close(). You close the connection after servicing each web page
request.
Treat the servicing of every web page request as a separate running of your
program: once it knows it's going to need a SQLite database it opens it using
its own handle, and it closes it once it had done all the SQL stuff it's going
to need to do. Of course, if you have many users, each with web browsers open
at the same time, then you have many different processes accessing your SQLite
database, and you have to handle multi-user situations correctly.
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
