Re: [sqlite] Avoiding Database Is Locked Error

2010-06-15 Thread Robert Latest
Hello all,

> Oh, I completely forgot that people can do that. So, Robert, you case
> is exactly the case I was talking about. As Simon said your SELECT
> opens read-only transaction and then as you issue your first UPDATE
> this transaction have to be converted to writing one. This is a call
> for problems. So you better to issue "BEGIN IMMEDIATE" before you
> execute your SELECT statement.
>
> BTW, I hope you don't change the table you selected in this scenario?

I'm not sure right now, but I think I've done that every now and then.
With a bad feeling. Instinctively I'd rather first SELECT, store the
results, finalize the SELECT statement and then get to work on its
result using the stored data. It's just that without intermediate
storage it's a bit easier (no need to do any ressource management),
and I've found nothing in the docs that says I shouldn't do that.

robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Avoiding Database Is Locked Error

2010-06-14 Thread Robert Latest
On Mon, Jun 14, 2010 at 4:36 PM, Simon Slavin  wrote:

> Do you mean you're making changes as you call SQLite to _step() through the
> results of the SELECT ?  Or do you read all the results of the SELECT into
> memory, then make changes to the database ?

The former.
robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Avoiding Database Is Locked Error

2010-06-14 Thread Robert Latest
Thanks, your and Pavel's clarifications have been very helpful.
robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Avoiding Database Is Locked Error

2010-06-14 Thread Robert Latest
On Fri, Jun 11, 2010 at 5:26 PM, Pavel Ivanov  wrote:

> 1. Ensure that you have no transactions started with SELECT and
> continued with INSERT/DELETE/UPDATE

This is interesting. I often have situations where I SELECT something
and then do manipulations (INSERT/DELETE/UPDATE) on the db as I
iterate through the results.

Is this what you mean by your statement? If yes, how should such
situations be avoided, and why?

Regards,
robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] "unable to open database file" on DROP

2008-08-11 Thread Robert Latest
Thanks, that was it. I don't know exactly how the errors were
interrelated, but once I told sqlite (via the PRAGMA thing) to use
memory for tmp stuff, all was fine.

I'm working under a cygwin environment which seems to be a bit shaky
when it comes to system-specific stuff like permissions.

robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] "unable to open database file" on DROP

2008-08-11 Thread Robert Latest
On Mon, Aug 11, 2008 at 1:42 PM, P Kishor <[EMAIL PROTECTED]> wrote:
> ahhh... I did misread the question. Yes, the above explanation seems
> logical. Your app is probably tying up the db, so you can't drop the
> table from the command line.

No it ain't. That's of course the first thing I checked. I fact I
re-booted the machine just to make sure that no rogue process was
holding a lock ono the db.

Check this out:

$ sqlite3 /cygdrive/d/cl_hist_dat/clhist.sqlite
SQLite version 3.5.1
Enter ".help" for instructions
sqlite> create view y as select * from tools;
sqlite> create temporary view z as select * from modules;
SQL error: unable to open database file
sqlite> drop view y;
sqlite> .quit
$

So I may create a view, y. Then I'm not allowed to create a temporary
view, z (I never am when mucking around with this particular db).
After that I want to drop view y (which I just created) and can't,
either.

This is really puzzling.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] "unable to open database file" on DROP

2008-08-11 Thread Robert Latest
Hello people,

why is it that I can look at the ".schema" of a db with the sqlite3
command line tool, but can't drop a table or view? After all, if the
db file weren't open, I couldn't even see the schema.

My problem is that I've written an app that uses views to access data.
After usage, I don't want those views no more. Unfortunately I can't
DROP them, so I create more and more views with different names and
keep littering my db with an increasing number of usesless views that
I can't delete.

CREATE TEMPORARY VIEW produces an "unable to open database file" error as well.

I'm up a bit of a stump here because re-creating the database (which
contains millions of lines but is only about 140M total file size)
takes about a week.

Here's a screenshot that should tell the whole story. I happened to
try to drop a table, but it really doesn't matter what I drop.

$ sqlite3.exe clhist.sqlite
SQLite version 3.5.1
Enter ".help" for instructions
sqlite> .schema
CREATE TABLE Batches (
Id TEXT PRIMARY KEY,
Moves_V1 INTEGER,
Moves_V2 INTEGER,
Moves_V3 INTEGER,
Moves_V4 INTEGER,
Moves_V5 INTEGER,
Moves_R1 INTEGER,
Moves_R2 INTEGER,
Moves_R3 INTEGER,
Moves_R4 INTEGER,
Moves_R5 INTEGER
);
CREATE TABLE Logs (
Shortname TEXT PRIMARY KEY,
Status INTEGER
);
CREATE TABLE Modules (
Name TEXT
);
CREATE TABLE Moves (
Batch TEXT,
SlotNo INTEGER,
Tool TEXT,
Module TEXT,
TimeStart INTEGER,
Duration INTEGER,
ZipDate TEXT
);
CREATE TABLE Tools (
Name TEXT
);
CREATE VIEW "B470330" AS SELECT * FROM Moves WHERE Batch="470330";
CREATE VIEW BatchMoves AS SELECT * FROM Moves WHERE Batch="P";
CREATE VIEW "R1" AS SELECT * FROM "B470330" WHERE Tool="R1";
CREATE INDEX MovesBatch ON Moves(Batch);
sqlite> DROP TABLE "Batches" ;
SQL error: unable to open database file
sqlite> .quit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users