Re: [sqlite] 'Database table is locked' error with libgpkg and SQLite >= 3.24.0

[Benjamin Stadin]

Thank you. The ruby helper class used by this library has indeed nested query 
executions. Modifying this class accordingly fixes the issue. 

Thanks
Ben


Am 21.11.18, 21:39 schrieb "drhsql...@gmail.com im Auftrag von Richard Hipp" 
:

On 11/21/18, Benjamin Stadin  wrote:
> Hi,
>
> I've forked libgpkg on Github to merge fixes from other repositories and
> update the embedded SQLite (was at 3.8.). Though in the RTREE tests I get 
a
> 'database table is locked' error for the update statements (see exact 
error
> message below).

Perhaps this is a result of the following change:

 https://www.sqlite.org/src/info/d4ce66610851c825

That change is actually a bug fix.  So we cannot back it out.

Is your application trying to update the r-tree table while
simultaneously reading from the same table?  That is the source of the
problem.

The SQLITE_LOCKED_VTAB error will occur on an attempt to write to the
R-Tree.  You can perhaps discover what queries are running
concurrently against the R-Tree by querying the sqlite_stat virtual
table (https://www.sqlite.org/stmt.html) whenever you get the
SQLITE_LOCKED_VTAB error.  Perhaps:

   SELECT sql FROM sqlite_stmt WHERE busy;

It might that the concurrent queries have actually finished for
practical purposes, but the application merely failed to run
sqlite3_reset() or sqlite3_finalize() on the query and thus it
continues to hold locks that prevent writes against the R-Tree.

Perhaps you can work around this by putting an ORDER BY on the read
queries that run in parallel with the writes while making sure the
ORDER BY clause really does require a sort operation to occur.  You
can make that guarantee by adding a unary "+" operator in front of one
of the ORDER BY terms.  Perhaps:  "ORDER BY +rowid".  Adding an ORDER
BY in this way will fix the problem because it will run the entire
R-Tree query to completion, storing the results in a buffer for
sorting, prior to returning any rows.


-- 
D. Richard Hipp
d...@sqlite.org


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

Re: [sqlite] 'Database table is locked' error with libgpkg and SQLite >= 3.24.0

[Richard Hipp]

On 11/21/18, Benjamin Stadin  wrote:
> Hi,
>
> I've forked libgpkg on Github to merge fixes from other repositories and
> update the embedded SQLite (was at 3.8.). Though in the RTREE tests I get a
> 'database table is locked' error for the update statements (see exact error
> message below).

Perhaps this is a result of the following change:

 https://www.sqlite.org/src/info/d4ce66610851c825

That change is actually a bug fix.  So we cannot back it out.

Is your application trying to update the r-tree table while
simultaneously reading from the same table?  That is the source of the
problem.

The SQLITE_LOCKED_VTAB error will occur on an attempt to write to the
R-Tree.  You can perhaps discover what queries are running
concurrently against the R-Tree by querying the sqlite_stat virtual
table (https://www.sqlite.org/stmt.html) whenever you get the
SQLITE_LOCKED_VTAB error.  Perhaps:

   SELECT sql FROM sqlite_stmt WHERE busy;

It might that the concurrent queries have actually finished for
practical purposes, but the application merely failed to run
sqlite3_reset() or sqlite3_finalize() on the query and thus it
continues to hold locks that prevent writes against the R-Tree.

Perhaps you can work around this by putting an ORDER BY on the read
queries that run in parallel with the writes while making sure the
ORDER BY clause really does require a sort operation to occur.  You
can make that guarantee by adding a unary "+" operator in front of one
of the ORDER BY terms.  Perhaps:  "ORDER BY +rowid".  Adding an ORDER
BY in this way will fix the problem because it will run the entire
R-Tree query to completion, storing the results in a buffer for
sorting, prior to returning any rows.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'Database table is locked' error with libgpkg and SQLite >= 3.24.0

[Dan Kennedy]

On 11/21/2018 11:01 PM, Benjamin Stadin wrote:

Hi,

I've forked libgpkg on Github to merge fixes from other repositories and update 
the embedded SQLite (was at 3.8.). Though in the RTREE tests I get a 'database 
table is locked' error for the update statements (see exact error message 
below). I ran the tests manually in the gpkg shell and could not reproduce the 
issue for the same sequence of commands. The tests are written in ruby, which 
could explain the different behaviour.

I tried different versions of SQLite and can confirm the RTREE tests for SQLite 
versions prior to 3.24.0 succeed (tested with 3.23.1 and 3.19.3). All recent 
versions break tests with mentioned error (tested versions include 3.24.0, 
3.25.0, 3.25.3, current snapshot).



As of 3.24.0, rtree prevents you from writing to a table while the same 
connection has an active SELECT on the same table. More detail in this 
thread:



http://sqlite.1065341.n5.nabble.com/3-24-database-table-is-locked-td102856.html

Dan.





To reproduce the issue:
- Clone the repository at https://github.com/benstadin/libgpkg
- Configure with tests and RTREE:
cmake -DCMAKE_BUILD_TYPE:STRING=Release -DGPKG_TEST:BOOL=on 
-DSQLITE_ENABLE_RTREE=1 .
- Build
make
- Run the tests with detailed error messages:
make CTEST_OUTPUT_ON_FAILURE=1 test

Cheers
Ben

Error message:

1) CreateSpatialIndex should create working spatial index
 Failure/Error: expect("UPDATE test SET geom = GeomFromText('POINT(2 2)') WHERE 
id = 1").to have_result nil
   expected UPDATE test SET geom = GeomFromText('POINT(2 2)') WHERE id = 1 
to have result nil but raised error 'database table is locked'
 # ./rtree_spec.rb:41:in `block (2 levels) in '

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



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

[sqlite] 'Database table is locked' error with libgpkg and SQLite >= 3.24.0

[Benjamin Stadin]

Hi,

I've forked libgpkg on Github to merge fixes from other repositories and update 
the embedded SQLite (was at 3.8.). Though in the RTREE tests I get a 'database 
table is locked' error for the update statements (see exact error message 
below). I ran the tests manually in the gpkg shell and could not reproduce the 
issue for the same sequence of commands. The tests are written in ruby, which 
could explain the different behaviour.

I tried different versions of SQLite and can confirm the RTREE tests for SQLite 
versions prior to 3.24.0 succeed (tested with 3.23.1 and 3.19.3). All recent 
versions break tests with mentioned error (tested versions include 3.24.0, 
3.25.0, 3.25.3, current snapshot). 

To reproduce the issue:
- Clone the repository at https://github.com/benstadin/libgpkg
- Configure with tests and RTREE: 
cmake -DCMAKE_BUILD_TYPE:STRING=Release -DGPKG_TEST:BOOL=on 
-DSQLITE_ENABLE_RTREE=1 .
- Build
make
- Run the tests with detailed error messages:
make CTEST_OUTPUT_ON_FAILURE=1 test

Cheers
Ben

Error message:

1) CreateSpatialIndex should create working spatial index
 Failure/Error: expect("UPDATE test SET geom = GeomFromText('POINT(2 2)') 
WHERE id = 1").to have_result nil
   expected UPDATE test SET geom = GeomFromText('POINT(2 2)') WHERE id = 1 
to have result nil but raised error 'database table is locked'
 # ./rtree_spec.rb:41:in `block (2 levels) in '

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

Re: [sqlite] database table is locked issue

[Sandu Buraga]

> > I have a process with several threads working in the same time on a

> > database file. I have 0 or 1 writers and 0 or N readers at a moment. All

> > write accesses are isolated in transactions, I am using WAL and shared

> > cache, but sometimes during the DELETE statemens I get "database table
is

> > locked" errors, either in the write or read thread.



> Have you set a timeout value for all your connections ?  If you haven't,
SQLite never does any waiting for locks at all, it just returns an error.

You were right the busy timeout was set only for one of the connections.
But, anyway the table lock continued to be triggered immediately not
waiting to pass 2500 ms.

I thought that WAL should've solved this aspect about the concurrency, but
it seems that for DELETE statements it still needs to lock the table
somehow.

Regards

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

Re: [sqlite] database table is locked issue

[Simon Slavin]

On 12 Feb 2014, at 4:44pm, Sandu Buraga  wrote:

> I have a process with several threads working in the same time on a
> database file. I have 0 or 1 writers and 0 or N readers at a moment. All
> write accesses are isolated in transactions, I am using WAL and shared
> cache, but sometimes during the DELETE statemens I get "database table is
> locked" errors, either in the write or read thread.

Have you set a timeout value for all your connections ?  If you haven't, SQLite 
never does any waiting for locks at all, it just returns an error.  Use either 
of these:





A reasonable number of milliseconds is however long you would want your program 
to wait before giving up and reporting an error to the user.  Values like 6 
are not out of place.

Simon,


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

[sqlite] database table is locked issue

[Sandu Buraga]

Hi,

I have a process with several threads working in the same time on a
database file. I have 0 or 1 writers and 0 or N readers at a moment. All
write accesses are isolated in transactions, I am using WAL and shared
cache, but sometimes during the DELETE statemens I get "database table is
locked" errors, either in the write or read thread.

Since the new content is not committed yet, why I am getting this errors?
My expectations would've been that during the end transaction to have such
errors, but not long before.

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

Re: [sqlite] "database table is locked" in sqlite_exec

[Alexey Pechnikov]

Hello!

On Friday 10 April 2009 16:23:43 D. Richard Hipp wrote:
> On Apr 10, 2009, at 5:58 AM, Alexey Pechnikov wrote:
> > Note: TCL interface binds all as text values
>
> False.  The following TCL script is proof by counter-example:
>
> package require sqlite3
> sqlite3 db :memory:
> set x [expr {1+2}]
> db eval {
>   CREATE TABLE t1(x);
>   INSERT INTO t1 VALUES($x);
> }
> puts [db one {SELECT typeof(x) FROM t1}]

But there is "constraint failed" error:

    package require sqlite3
    sqlite3 db :memory:
    set x 1
    db eval {
      CREATE TABLE t1(x integer check(typeof(x)='integer'));
      INSERT INTO t1 VALUES($x);
    }
puts [db one {SELECT typeof(x) FROM t1}]

TCL interface does use tcl variable type instead of database field type. It's 
not good because typeof(x) will be 'integer' here:

package require sqlite3
sqlite3 db :memory:
set x 1
db eval {
  CREATE TABLE t1(x integer);
  INSERT INTO t1 VALUES($x);
}
puts [db one {SELECT typeof(x) FROM t1}]

So value of variable will be inserted as integer but typeof() function in 
constraint does return type 'text'. I think type conversion must be _before_ 
constraints checks.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database table is locked" in sqlite_exec

[D. Richard Hipp]

On Apr 10, 2009, at 5:58 AM, Alexey Pechnikov wrote:
>
> Note: TCL interface binds all as text values

False.  The following TCL script is proof by counter-example:

package require sqlite3
sqlite3 db :memory:
set x [expr {1+2}]
db eval {
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES($x);
}
puts [db one {SELECT typeof(x) FROM t1}]


D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] "database table is locked" in sqlite_exec

[Alexey Pechnikov]

Hello!

Note: TCL interface binds all as text values and we can't use typeof() 
function (typeof returns 'text' for all fields). So we may use manual cast() to 
field datatype instead.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "database table is locked" in sqlite_exec

[Alexey Pechnikov]

Hello!

This function doesn't work:
sqlite> select userkey_uninstall();
SQL error: database table is locked

static void userkey_uninstallFunc(sqlite3_context *context, int argc, 
sqlite3_value **argv){
sqlite3 *db;
int rc; /* Result code */
const char zSql[] = "DROP TABLE userkeys"; /* An SQL statement */

db = (sqlite3*) sqlite3_context_db_handle(context);
rc = sqlite3_exec(db, zSql, NULL, NULL, NULL);
if( rc != SQLITE_OK ){
sqlite3_result_error(context, sqlite3_errmsg(db), -1);
return; 
}
// return null value
sqlite3_result_null(context);
}


But this work fine:
sqlite> select userkey_install();


#define SCHEMA_USERKEY \
"CREATE TABLE userkeys (  -- persistent parameters storage (same as my AOL 
Server module ns_userkey)\n" \
"id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n"  

\
"save_date REAL NOT NULL DEFAULT (julianday('now')) 
check(cast(save_date as real)=save_date),\n"\
"update_date REAL NOT NULL DEFAULT (julianday('now')) 
check(cast(update_date as real)=update_date),\n"  \
"delete_date REAL NOT NULL DEFAULT '' check(cast(delete_date as 
real)=delete_date or delete_date = ''),\n"  \
"unit text not null,\n" 

\
"param text not null,\n"

\
"value text not null,\n"

\
"UNIQUE (unit,param) on conflict replace\n" 

\
");"


static void userkey_installFunc(sqlite3_context *context, int argc, 
sqlite3_value **argv){
sqlite3 *db;
int rc; /* Result code */

db = (sqlite3*) sqlite3_context_db_handle(context);
rc = sqlite3_exec(db, SCHEMA_USERKEY, NULL, NULL, NULL);
if( rc != SQLITE_OK ){
sqlite3_result_error(context, sqlite3_errmsg(db), -1);
return; 
}
// return null value
sqlite3_result_null(context);
}


Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] database table is locked when trying to delete a record after select

[Justin Wu]

It will be always get 'database table is locked' message when trying to
delete
a record after select.

I'v tested on WinXP and Ubuntu 5.10, but got the same error.

SQLite version v3.2.8

please see the following code

#include 
#include 
#include 

#define DATFILE "test.dat"

int main(int argc, char *argv[]) {
   // sqlite
   sqlite3 *db = 0;
   sqlite3_stmt *stmt = 0;
   char *sql;
   int rc;
   int id = 0;

   // Open the database
   if (SQLITE_OK != sqlite3_open(DATFILE, &db)) {
   printf("!!! Couldn't open the database - %s\n", DATFILE);
   exit(1);
   }

   sqlite3_busy_timeout(db, 3000);

   if (SQLITE_OK != sqlite3_prepare(db, "SELECT ProxyId, Host, Port FROM
ss_proxy", -1, &stmt, 0)) {
   printf("!!! sqlite3_prepare::%s", sqlite3_errmsg(db));
   sqlite3_close(db);
   exit(1);
   }

   //sqlite3_exec(db, "BEGIN;", 0, 0, 0);
   while (SQLITE_ROW == sqlite3_step(stmt)) {
   id = sqlite3_column_int(stmt, 0);
   printf("*** %d. %s:%d\n", sqlite3_column_int(stmt, 0),
sqlite3_column_text(stmt, 1), sqlite3_column_int(stmt, 2));
   // i will do something with every record, currently simple use '(id
==
1742 || id == 1743)' instead of
   if (id == 1742 || id == 1743) {
   //sqlite3_reset(stmt);
   // database table is locked - [rc=6]
   sql = sqlite3_mprintf("DELETE FROM ss_proxy WHERE ProxyId=%d;",
id);
   rc = sqlite3_exec(db, sql, 0, 0, 0);
   if (SQLITE_OK != rc) {
   printf("*** delete failed - %s - [rc=%d]\n", sqlite3_errmsg
(db), rc);
   } else {
   printf("*** %d record deleted!\n", sqlite3_changes(db));
   }
   sqlite3_free(sql);
   }
   };
   //sqlite3_exec(db, "COMMIT;", 0, 0, 0);

   sqlite3_finalize(stmt);
   // Close the database
   sqlite3_close(db);
   return 0;
}

Re: [sqlite] database table is locked

[Jay Sprenkle]

> It's probably not a good idea, because it depends on some
> behaviour that is not specified, but I once used a trick
> like this to get good performance:
> 
> CREATE TABLE abc(a, b);
> UPDATE abc SET b = user2(a, b) WHERE  AND user1(a, b);
> 
> SQLite loops through the rows where  is true, and
> remembers those for which user1() returns true. It then runs
> a second loop through those rows and calls user2() for each
> of the remembered rows, setting 'b' to the return value.

That's an interesting trick. It has no way to retrieve the rows you want
to operate on though. I guess you could make every column you needed
a parameters to the user2() function and do your processing there.

Re: [sqlite] database table is locked

[Gerry Snyder]

Dan Kennedy wrote:
It's probably not a good idea, because it depends on some
behaviour that is not specified, but I once used a trick
like this to get good performance:
CREATE TABLE abc(a, b);
UPDATE abc SET b = user2(a, b) WHERE  AND user1(a, b);
SQLite loops through the rows where  is true, and
remembers those for which user1() returns true. It then runs
a second loop through those rows and calls user2() for each
of the remembered rows, setting 'b' to the return value.
But like I said, it depends on unspecified behaviour so you
had better have some assert() statements to make sure SQLite
is calling the functions in the expected order.
Two comments:
This approach should handle many cases which might seem to need UPDATEs 
nested within a SELECT. Thank you for reminding me how versatile SQL (as 
implemented by SQLite) is.

It looks like good SQL, totally within specs (as abridged below), to my 
too-often clueless eyes. Can you elaborate where your concerns about 
unspecified behavio(u)r are? I suspect that, as usual, I am missing 
something obvious, but maybe you are being overly cautious.

UPDATE statement from the web page (abridged--CONFLICT clause and db 
name omitted for readability)
-
UPDATE table-name SET assignment [, assignment]* [WHERE expr]

assignment ::=  column-name = expr
The UPDATE statement is used to change the value of columns in selected 
rows of a table. Each assignment in an UPDATE specifies a column name to 
the left of the equals sign and an arbitrary expression to the right. 
The expressions may use the values of other columns. All expressions are 
evaluated before any assignments are made. A WHERE clause can be used to 
restrict which rows are updated.
--

TIA,
Gerry, big SQLite fan
--
--
Gerry Snyder
American Iris Society Director, Symposium Chair
in warm, winterless Los Angeles -- USDA zone 9b, Sunset 18-19

RE: [sqlite] database table is locked

[Dan Kennedy]

It's probably not a good idea, because it depends on some
behaviour that is not specified, but I once used a trick
like this to get good performance:

CREATE TABLE abc(a, b);
UPDATE abc SET b = user2(a, b) WHERE  AND user1(a, b);

SQLite loops through the rows where  is true, and
remembers those for which user1() returns true. It then runs
a second loop through those rows and calls user2() for each
of the remembered rows, setting 'b' to the return value.

But like I said, it depends on unspecified behaviour so you
had better have some assert() statements to make sure SQLite
is calling the functions in the expected order.

Dan.

--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:

> On Fri, 2005-05-13 at 15:16 +0100, Brandon, Nicholas wrote:
> > I think I might be getting confused on this subject as well. Does this mean
> > that SQLite 3.x can NOT process multiple inserts/updates within one
> > transaction if it is working on the same table?
> > 
> > ie Below would return "database table is locked"?
> > 
> > BEGIN TRANSACTION
> > SELECT * from table1 WHERE col > x
> > UPDATE table1 SET col = ...
> > INSERT INTO table1 
> > COMMIT TRANSACTION
> > 
> 
> You cannot simultaneously read and write from the same table.
> If you are in the middle of a SELECT on a table, you cannot
> UPDATE or INSERT or DELETE from that table until the SELECT
> is finished.  (NB: the SELECT is usually not finished until
> you call sqlite3_finalize() or sqlite3_reset() on the statement.
> For an exception to this rule, see solution (2) below.)
> 
> You can read and write the same table as many times as you
> want within the same transaction as long and the reading and
> writing do not overlap in time.
> 
> If you want to do an UPDATE on each row of a SELECT on the same
> table, you can do this in several ways.
> 
>   (1)  Load the results of the SELECT into a TEMP table, then
>loop over the TEMP table to do your UPDATES:
> 
>  CREATE TEMP TABLE temp1 AS SELECT * FROM table1 WHERE...;
>  SELECT * FROM temp1;
>   -- for each row of result do:
>UPDATE table1 SET ...;
> 
>   (2) Add an ORDER BY clause to the SELECT statement where the
>   ORDER BY clause contains at least one arithmetic expression.
>   For example:
> 
> SELECT * FROM table1 WHERE ... ORDER BY rowid+1;
>  -- for each row of result do:
>   UPDATE table1 SET ...;
> 
>   (3) Store your UPDATEs in a temp table then execute them after
>   the SELECT has finished:
> 
> CREATE TEMP TABLE updates(stmt TEXT);
> SELECT * FROM table1 WHERE ...;
>   -- for each row of result do:
>   INSERT INTO updates VALUES('UPDATE table1 SET ...');
> SELECT * FROM updates;
>   -- for each row of result, evaluate the stmt
>   -- column as SQL.
> 
> You can probably also think of schemes where you store either
> the SELECT results or the UPDATE statements in memory.  Note
> that technique (2) above works by moving the entire result set
> into memory for you.  Avoid solution (2) if your result set is
> exceedingly large.
> -- 
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 




__ 
Yahoo! Mail Mobile 
Take Yahoo! Mail with you! Check email on your mobile phone. 
http://mobile.yahoo.com/learn/mail 

RE: [sqlite] database table is locked

[Thomas Briggs]

 
> No, that seems to work fine. I guess the table is locked for a
> specific transaction, so you cannot have any problems with a lock held
> by the very same transaction.
> 
> > ie Below would return "database table is locked"?
> > 
> > BEGIN TRANSACTION
> > SELECT * from table1 WHERE col > x
> > UPDATE table1 SET col = ...
> > INSERT INTO table1 
> > COMMIT TRANSACTION
> 
> Just try it with the command line tool sqlite3. Works fine here.

   I expect that works only because all the results of the SELECT were
retrieved before the UPDATE was executed.  Interleaving the
UPDATEs/INSERTs with the SELECT would probably cause the error.

   -Tom

RE: [sqlite] database table is locked

[D. Richard Hipp]

On Fri, 2005-05-13 at 15:16 +0100, Brandon, Nicholas wrote:
> I think I might be getting confused on this subject as well. Does this mean
> that SQLite 3.x can NOT process multiple inserts/updates within one
> transaction if it is working on the same table?
> 
> ie Below would return "database table is locked"?
> 
> BEGIN TRANSACTION
> SELECT * from table1 WHERE col > x
> UPDATE table1 SET col = ...
> INSERT INTO table1 
> COMMIT TRANSACTION
> 

You cannot simultaneously read and write from the same table.
If you are in the middle of a SELECT on a table, you cannot
UPDATE or INSERT or DELETE from that table until the SELECT
is finished.  (NB: the SELECT is usually not finished until
you call sqlite3_finalize() or sqlite3_reset() on the statement.
For an exception to this rule, see solution (2) below.)

You can read and write the same table as many times as you
want within the same transaction as long and the reading and
writing do not overlap in time.

If you want to do an UPDATE on each row of a SELECT on the same
table, you can do this in several ways.

  (1)  Load the results of the SELECT into a TEMP table, then
   loop over the TEMP table to do your UPDATES:

 CREATE TEMP TABLE temp1 AS SELECT * FROM table1 WHERE...;
 SELECT * FROM temp1;
  -- for each row of result do:
   UPDATE table1 SET ...;

  (2) Add an ORDER BY clause to the SELECT statement where the
  ORDER BY clause contains at least one arithmetic expression.
  For example:

SELECT * FROM table1 WHERE ... ORDER BY rowid+1;
 -- for each row of result do:
  UPDATE table1 SET ...;

  (3) Store your UPDATEs in a temp table then execute them after
  the SELECT has finished:

CREATE TEMP TABLE updates(stmt TEXT);
SELECT * FROM table1 WHERE ...;
  -- for each row of result do:
  INSERT INTO updates VALUES('UPDATE table1 SET ...');
SELECT * FROM updates;
  -- for each row of result, evaluate the stmt
  -- column as SQL.

You can probably also think of schemes where you store either
the SELECT results or the UPDATE statements in memory.  Note
that technique (2) above works by moving the entire result set
into memory for you.  Avoid solution (2) if your result set is
exceedingly large.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] database table is locked

[Thomas Steffen]

On 5/13/05, Brandon, Nicholas <[EMAIL PROTECTED]> wrote:
> 
> I think I might be getting confused on this subject as well. Does this mean
> that SQLite 3.x can NOT process multiple inserts/updates within one
> transaction if it is working on the same table?

No, that seems to work fine. I guess the table is locked for a
specific transaction, so you cannot have any problems with a lock held
by the very same transaction.

> ie Below would return "database table is locked"?
> 
> BEGIN TRANSACTION
> SELECT * from table1 WHERE col > x
> UPDATE table1 SET col = ...
> INSERT INTO table1 
> COMMIT TRANSACTION

Just try it with the command line tool sqlite3. Works fine here.

You can even read the stable from a parallel transaction, but you
cannot write it. With a more fine grain locking, you could possibly
support parallel writes, too, but in most cases it will be easier just
to retry one of the transactions.

Thomas

RE: [sqlite] database table is locked

[Brandon, Nicholas]

I think I might be getting confused on this subject as well. Does this mean
that SQLite 3.x can NOT process multiple inserts/updates within one
transaction if it is working on the same table?

ie Below would return "database table is locked"?

BEGIN TRANSACTION
SELECT * from table1 WHERE col > x
UPDATE table1 SET col = ...
INSERT INTO table1 
COMMIT TRANSACTION

Nick

-Original Message-
From: Thomas Briggs [mailto:[EMAIL PROTECTED]
Sent: 12 May 2005 16:11
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] database table is locked


   *** WARNING ***

This mail has originated outside your organization,
either from an external partner or the Global Internet.

 Keep this in mind if you answer this message.



   Aha!  Now I understand what's going on.  I have been completely
missing the fact that everyone is trying to update the same table
they're reading from.  I know that's obvious to you guys, but I
completely missed that subtle fact.

   Everything makes sense now.  Thanks for your patience. :)

   -Tom


> -Original Message-
> From: Martin Engelschalk [mailto:[EMAIL PROTECTED]

> Sent: Thursday, May 12, 2005 9:53 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] database table is locked
>

> @Thomas Briggs, Jay Sprenckle
>

> I use the C Api described at http://www.sqlite.org/capi3ref.html.
>

> My code seemed to work with sqlite 3.0.7, but I cannot be too sure

> because my project is in development. It would take some time

> to check

> with 3.0.7
>

> My code worked roughly like this:
> I created an update - Statement using compile().
> I created a query (simple select from one table) using compile() and

> fetched the data using sqlite3_step()
> When i found a record i needed to update,  i bound 2 Variables using

> sqlite3_bind_text() to the update statement, one of which was the

> integer primary key.
> The sqlite3_exec() failed with "database table is locked".
>

> This is a different thing to "database locked", i think.
>

> Martin
>

> Thomas Briggs schrieb:
>

> >   This question seems to come up often, and I'm still confused as to
> >what problems people are having.  What APIs are you using to perform
> >these steps?  In particular, when you want to update a row, are you
> >using a prepared query that is executed multiple times, or are you
> >creating an SQL statement and executing that with

> sqlite3_exec?  Are you
> >using 2.8 or 3.x?  Maybe the confusion on my part is due to different
> >database versions.
> >
> >   I'm confused as to why executing a query would lock the database.
> >The only thing I can think of is that the query required a

> temp table,
> >and the creation of that temp table led to the database being locked.
> >The entire many readers/single writer concept makes no sense if
> >executing any query locks the whole database.
> >
> >   -Tom
> >
> > 

> >
> >>-Original Message-
> >>From: Thomas Fjellstrom [mailto:[EMAIL PROTECTED]

> >>Sent: Thursday, May 12, 2005 7:10 AM
> >>To: sqlite-users@sqlite.org
> >>Subject: Re: [sqlite] database table is locked
> >>
> >>On May 12, 2005 04:59 am, Martin Engelschalk wrote:
> >>   

> >>
> >>>Hello,
> >>>
> >>>i open cursor on a table and retrieve rows from it.
> >>>For every row i decide whether to update it.  However, when

> >>> 

> >>>
> >>executing
> >>   

> >>
> >>>the update I get the error "database table is locked".
> >>>My application is the only one working on the table.
> >>>Is it illegal to update a table while selecting from it or

> >>> 

> >>>
> >>am i doing
> >>   

> >>
> >>>somethin wrong?
> >>>
> >>>Thanks,
> >>>Martin
> >>> 

> >>>
> >>Yup. you'll have to scan for updates to make, then after the

> >>scan, make the

> >>updates.
> >>
> >>--

> >>Thomas Fjellstrom
> >>[EMAIL PROTECTED]
> >>
> >>   

> >>
>



This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.

RE: [sqlite] database table is locked

[Thomas Briggs]

   Aha!  Now I understand what's going on.  I have been completely
missing the fact that everyone is trying to update the same table
they're reading from.  I know that's obvious to you guys, but I
completely missed that subtle fact.

   Everything makes sense now.  Thanks for your patience. :)

   -Tom 

> -Original Message-
> From: Martin Engelschalk [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, May 12, 2005 9:53 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] database table is locked
> 
> @Thomas Briggs, Jay Sprenckle
> 
> I use the C Api described at http://www.sqlite.org/capi3ref.html.
> 
> My code seemed to work with sqlite 3.0.7, but I cannot be too sure 
> because my project is in development. It would take some time 
> to check 
> with 3.0.7
> 
> My code worked roughly like this:
> I created an update - Statement using compile().
> I created a query (simple select from one table) using compile() and 
> fetched the data using sqlite3_step()
> When i found a record i needed to update,  i bound 2 Variables using 
> sqlite3_bind_text() to the update statement, one of which was the 
> integer primary key.
> The sqlite3_exec() failed with "database table is locked".
> 
> This is a different thing to "database locked", i think.
> 
> Martin
> 
> Thomas Briggs schrieb:
> 
> >   This question seems to come up often, and I'm still confused as to
> >what problems people are having.  What APIs are you using to perform
> >these steps?  In particular, when you want to update a row, are you
> >using a prepared query that is executed multiple times, or are you
> >creating an SQL statement and executing that with 
> sqlite3_exec?  Are you
> >using 2.8 or 3.x?  Maybe the confusion on my part is due to different
> >database versions.
> >
> >   I'm confused as to why executing a query would lock the database.
> >The only thing I can think of is that the query required a 
> temp table,
> >and the creation of that temp table led to the database being locked.
> >The entire many readers/single writer concept makes no sense if
> >executing any query locks the whole database.
> >
> >   -Tom
> >
> >  
> >
> >>-Original Message-
> >>From: Thomas Fjellstrom [mailto:[EMAIL PROTECTED] 
> >>Sent: Thursday, May 12, 2005 7:10 AM
> >>To: sqlite-users@sqlite.org
> >>Subject: Re: [sqlite] database table is locked
> >>
> >>On May 12, 2005 04:59 am, Martin Engelschalk wrote:
> >>
> >>
> >>>Hello,
> >>>
> >>>i open cursor on a table and retrieve rows from it.
> >>>For every row i decide whether to update it.  However, when 
> >>>  
> >>>
> >>executing
> >>
> >>
> >>>the update I get the error "database table is locked".
> >>>My application is the only one working on the table.
> >>>Is it illegal to update a table while selecting from it or 
> >>>  
> >>>
> >>am i doing
> >>
> >>
> >>>somethin wrong?
> >>>
> >>>Thanks,
> >>>Martin
> >>>  
> >>>
> >>Yup. you'll have to scan for updates to make, then after the 
> >>scan, make the 
> >>updates.
> >>
> >>-- 
> >>Thomas Fjellstrom
> >>[EMAIL PROTECTED]
> >>
> >>
> >>
> 

Re: [sqlite] database table is locked

[Martin Engelschalk]

Sorry, when i wrote compile() i meant sqlite3_prepare().
Martin Engelschalk schrieb:
@Thomas Briggs, Jay Sprenckle
I use the C Api described at http://www.sqlite.org/capi3ref.html.
My code seemed to work with sqlite 3.0.7, but I cannot be too sure 
because my project is in development. It would take some time to check 
with 3.0.7

My code worked roughly like this:
I created an update - Statement using compile().
I created a query (simple select from one table) using compile() and 
fetched the data using sqlite3_step()
When i found a record i needed to update,  i bound 2 Variables using 
sqlite3_bind_text() to the update statement, one of which was the 
integer primary key.
The sqlite3_exec() failed with "database table is locked".

This is a different thing to "database locked", i think.
Martin
Thomas Briggs schrieb:
  This question seems to come up often, and I'm still confused as to
what problems people are having.  What APIs are you using to perform
these steps?  In particular, when you want to update a row, are you
using a prepared query that is executed multiple times, or are you
creating an SQL statement and executing that with sqlite3_exec?  Are you
using 2.8 or 3.x?  Maybe the confusion on my part is due to different
database versions.
  I'm confused as to why executing a query would lock the database.
The only thing I can think of is that the query required a temp table,
and the creation of that temp table led to the database being locked.
The entire many readers/single writer concept makes no sense if
executing any query locks the whole database.
  -Tom
 

-Original Message-
From: Thomas Fjellstrom [mailto:[EMAIL PROTECTED] Sent: 
Thursday, May 12, 2005 7:10 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] database table is locked

On May 12, 2005 04:59 am, Martin Engelschalk wrote:
  

Hello,
i open cursor on a table and retrieve rows from it.
For every row i decide whether to update it.  However, when 
executing
  

the update I get the error "database table is locked".
My application is the only one working on the table.
Is it illegal to update a table while selecting from it or 
am i doing
  

somethin wrong?
Thanks,
Martin

Yup. you'll have to scan for updates to make, then after the scan, 
make the updates.

--
Thomas Fjellstrom
[EMAIL PROTECTED]
  

Re: [sqlite] database table is locked

[Martin Engelschalk]

@Thomas Briggs, Jay Sprenckle
I use the C Api described at http://www.sqlite.org/capi3ref.html.
My code seemed to work with sqlite 3.0.7, but I cannot be too sure 
because my project is in development. It would take some time to check 
with 3.0.7

My code worked roughly like this:
I created an update - Statement using compile().
I created a query (simple select from one table) using compile() and 
fetched the data using sqlite3_step()
When i found a record i needed to update,  i bound 2 Variables using 
sqlite3_bind_text() to the update statement, one of which was the 
integer primary key.
The sqlite3_exec() failed with "database table is locked".

This is a different thing to "database locked", i think.
Martin
Thomas Briggs schrieb:
  This question seems to come up often, and I'm still confused as to
what problems people are having.  What APIs are you using to perform
these steps?  In particular, when you want to update a row, are you
using a prepared query that is executed multiple times, or are you
creating an SQL statement and executing that with sqlite3_exec?  Are you
using 2.8 or 3.x?  Maybe the confusion on my part is due to different
database versions.
  I'm confused as to why executing a query would lock the database.
The only thing I can think of is that the query required a temp table,
and the creation of that temp table led to the database being locked.
The entire many readers/single writer concept makes no sense if
executing any query locks the whole database.
  -Tom
 

-Original Message-
From: Thomas Fjellstrom [mailto:[EMAIL PROTECTED] 
Sent: Thursday, May 12, 2005 7:10 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] database table is locked

On May 12, 2005 04:59 am, Martin Engelschalk wrote:
   

Hello,
i open cursor on a table and retrieve rows from it.
For every row i decide whether to update it.  However, when 
 

executing
   

the update I get the error "database table is locked".
My application is the only one working on the table.
Is it illegal to update a table while selecting from it or 
 

am i doing
   

somethin wrong?
Thanks,
Martin
 

Yup. you'll have to scan for updates to make, then after the 
scan, make the 
updates.

--
Thomas Fjellstrom
[EMAIL PROTECTED]
   

RE: [sqlite] database table is locked

[Thomas Briggs]

 
> update column in result row so I won't process it again.

   How exactly were you doing this?  Building an SQL string and
executing with sqlite3_exec, or did you prepare a parameterized UPDATE
statement (using sqlite3_prepare) and executing it multiple times with
sqlite3_step?

   -Tom

Re: [sqlite] database table is locked

[Jay Sprenkle]

I had the same problem. I wrote a daemon to email people who
signed up for a service and never logged in. (offering help).

In pseudo code I did this:

  select * from people where user never logged in;
  for each row in result set
{
email offer to help;
update column in result row so I won't process it again.
}

I used the basic C api. I could never get it to work and
had to rewrite it to copy the result set to
an STL vector and iterate through the vector.

On 5/12/05, Thomas Briggs <[EMAIL PROTECTED]> wrote:
> 
>This question seems to come up often, and I'm still confused as to
> what problems people are having.  What APIs are you using to perform
> these steps?  In particular, when you want to update a row, are you
> using a prepared query that is executed multiple times, or are you
> creating an SQL statement and executing that with sqlite3_exec?  Are you
> using 2.8 or 3.x?  Maybe the confusion on my part is due to different
> database versions.
> 
>I'm confused as to why executing a query would lock the database.
> The only thing I can think of is that the query required a temp table,
> and the creation of that temp table led to the database being locked.
> The entire many readers/single writer concept makes no sense if
> executing any query locks the whole database.
> 
>-Tom
> 
> > -Original Message-
> > From: Thomas Fjellstrom [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, May 12, 2005 7:10 AM
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] database table is locked
> >
> > On May 12, 2005 04:59 am, Martin Engelschalk wrote:
> > > Hello,
> > >
> > > i open cursor on a table and retrieve rows from it.
> > > For every row i decide whether to update it.  However, when
> > executing
> > > the update I get the error "database table is locked".
> > > My application is the only one working on the table.
> > > Is it illegal to update a table while selecting from it or
> > am i doing
> > > somethin wrong?
> > >
> > > Thanks,
> > > Martin
> >
> > Yup. you'll have to scan for updates to make, then after the
> > scan, make the
> > updates.
> >
> > --
> > Thomas Fjellstrom
> > [EMAIL PROTECTED]
> >
> 


-- 
---
You a Gamer? If you're near Kansas City:
Conquest 36
https://events.reddawn.net

The Castles of Dereth Calendar: a tour of the art and architecture of
Asheron's Call
http://www.lulu.com/content/77264

Re: [sqlite] database table is locked

[Martin Engelschalk]

Thank you, Thomas. I used a temporary table, and now it works.
However, it seems that my code worked with sqlite 2. Can this be?
Martin
Thomas Fjellstrom schrieb:
On May 12, 2005 04:59 am, Martin Engelschalk wrote:
 

Hello,
i open cursor on a table and retrieve rows from it.
For every row i decide whether to update it.  However, when executing
the update I get the error "database table is locked".
My application is the only one working on the table.
Is it illegal to update a table while selecting from it or am i doing
somethin wrong?
Thanks,
Martin
   

Yup. you'll have to scan for updates to make, then after the scan, make the 
updates.

 

RE: [sqlite] database table is locked

[Thomas Briggs]

   This question seems to come up often, and I'm still confused as to
what problems people are having.  What APIs are you using to perform
these steps?  In particular, when you want to update a row, are you
using a prepared query that is executed multiple times, or are you
creating an SQL statement and executing that with sqlite3_exec?  Are you
using 2.8 or 3.x?  Maybe the confusion on my part is due to different
database versions.

   I'm confused as to why executing a query would lock the database.
The only thing I can think of is that the query required a temp table,
and the creation of that temp table led to the database being locked.
The entire many readers/single writer concept makes no sense if
executing any query locks the whole database.

   -Tom

> -Original Message-
> From: Thomas Fjellstrom [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, May 12, 2005 7:10 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] database table is locked
> 
> On May 12, 2005 04:59 am, Martin Engelschalk wrote:
> > Hello,
> >
> > i open cursor on a table and retrieve rows from it.
> > For every row i decide whether to update it.  However, when 
> executing
> > the update I get the error "database table is locked".
> > My application is the only one working on the table.
> > Is it illegal to update a table while selecting from it or 
> am i doing
> > somethin wrong?
> >
> > Thanks,
> > Martin
> 
> Yup. you'll have to scan for updates to make, then after the 
> scan, make the 
> updates.
> 
> -- 
> Thomas Fjellstrom
> [EMAIL PROTECTED]
> 

Re: [sqlite] database table is locked

[Thomas Fjellstrom]

On May 12, 2005 04:59 am, Martin Engelschalk wrote:
> Hello,
>
> i open cursor on a table and retrieve rows from it.
> For every row i decide whether to update it.  However, when executing
> the update I get the error "database table is locked".
> My application is the only one working on the table.
> Is it illegal to update a table while selecting from it or am i doing
> somethin wrong?
>
> Thanks,
> Martin

Yup. you'll have to scan for updates to make, then after the scan, make the 
updates.

-- 
Thomas Fjellstrom
[EMAIL PROTECTED]

[sqlite] database table is locked

[Martin Engelschalk]

Hello,
i open cursor on a table and retrieve rows from it.
For every row i decide whether to update it.  However, when executing 
the update I get the error "database table is locked".
My application is the only one working on the table.
Is it illegal to update a table while selecting from it or am i doing 
somethin wrong?

Thanks,
Martin

Re: [sqlite] database table is locked

[Marcel Strittmatter]

SQLITE_BUSY - another process has the whole database locked
SQLITE_LOCKED - one sqlite3_step() is trying to read (or write)
   the same table that another sqlite3_step() is
   writing (or reading) using the same DB handle.
It sounds as if Mr. Strittmatter is getting the second error.
The cause of SQLITE_LOCKED is usually because you failed to
sqlite3_finalize() the previous statement.
Yes. That's right. I found the missing sqlite3_finalize() command. But 
why did it work on Linux and failed on  Windows? Maybe because I work 
with nfs on Linux? Whatever... my problem is solved. Thanks for the 
help!

Marcel

Re: [sqlite] database table is locked

[D. Richard Hipp]

On Wed, 2005-03-16 at 22:40 +0100, Jakub Adamek wrote:
> Also the error description "database TABLE is locked" is sometimes wrong 
> because it is the whole database which is locked. Perhaps another error 
> code/message should be created for "database is locked" (e.g. with open 
> cursors).
> 

There are two different errors:

SQLITE_BUSY - another process has the whole database locked
SQLITE_LOCKED - one sqlite3_step() is trying to read (or write)
   the same table that another sqlite3_step() is
   writing (or reading) using the same DB handle.

It sounds as if Mr. Strittmatter is getting the second error.
The cause of SQLITE_LOCKED is usually because you failed to
sqlite3_finalize() the previous statement.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] database table is locked

[Jakub Adamek]

Hello,
I had a similar experience, but there was too much code to create a 
simple bug report. Surprisingly, the error disappeared if I first made a 
"SELECT FROM table" before the "INSERT INTO table" on the same table.
Also the error description "database TABLE is locked" is sometimes wrong 
because it is the whole database which is locked. Perhaps another error 
code/message should be created for "database is locked" (e.g. with open 
cursors).

Jakub
Marcel Strittmatter wrote:
Hi all
When I try to insert data into a table, sqlite3 responses with 
SQLITE_ERROR and the error message: "database table is locked". I 
searched already for unfinalized statements but couln't find any. The 
insert statement is not executed while a query is active...

The problem exists only on Windows (local storage), on Linux everything 
(except known nfs problems) works well (same code).

My question: Is there a "easy" way to test if a table is locked? Are 
there other possibilities to help debug such a problem (I already tried 
sqlit3_trace, but this doesn't help much because I don't see if a query 
is finalized or not).

Marcel

[sqlite] database table is locked

[Marcel Strittmatter]

Hi all
When I try to insert data into a table, sqlite3 responses with 
SQLITE_ERROR and the error message: "database table is locked". I 
searched already for unfinalized statements but couln't find any. The 
insert statement is not executed while a query is active...

The problem exists only on Windows (local storage), on Linux everything 
(except known nfs problems) works well (same code).

My question: Is there a "easy" way to test if a table is locked? Are 
there other possibilities to help debug such a problem (I already tried 
sqlit3_trace, but this doesn't help much because I don't see if a query 
is finalized or not).

Marcel

Re: [sqlite] database table is locked

[Thiago Mello]

Hi D. Richard,

Thanks for your help, it seems that it worked fine.

Regards,

Thiago Mello

Em Sex, 2003-11-07 às 11:46, D. Richard Hipp escreveu:
> Thiago Mello wrote:
> > 
> > BEGIN TRANSACTION; 
> > SELECT id,name FROM TABLE1;
> > 
> > /* now in the callback function */
> > IF argv[1] = "JOHN";
> > UPDATE  TABLE1 SET number=n+1 WHERE id=X
> > /* return from the Callback function */
> > 
> > END TRANSACTION;
> > 
> > But I still get a error: "database table is locked".
> > Is still something wrong that Im doing?
> > 
> 
> SQLite does not allow you to read and write the
> same table at the same time.  You need to finish the
> read of TABLE1 before you start changing it.  Perhaps
> like this:
> 
>BEGIN;
>CREATE TEMP TABLE t1 AS SELECT id,name FROM table1;
>SELECT id,name FROM t1;
> 
>/* Now in the callback function */
>UPDATE table1 SET number=n+1 WHERE id=X;
>/* Return from callback function
> 
>DROP TABLE t1;
>COMMIT;


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

Re: [sqlite] database table is locked

[Thiago Mello]

Hello,

Will that decrease the performance of my application?

Thanks,

Thiago Mello

Em Sex, 2003-11-07 às 11:46, D. Richard Hipp escreveu:
> SQLite does not allow you to read and write the
> same table at the same time.  You need to finish the
> read of TABLE1 before you start changing it.  Perhaps
> like this:
> 
>BEGIN;
>CREATE TEMP TABLE t1 AS SELECT id,name FROM table1;
>SELECT id,name FROM t1;
> 
>/* Now in the callback function */
>UPDATE table1 SET number=n+1 WHERE id=X;
>/* Return from callback function
> 
>DROP TABLE t1;
>COMMIT;


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

Re: [sqlite] database table is locked

[Thiago Mello]

Hi,

Im sorry, I was not putting the hole explanation, but here it is.

In my main I have this select:

SELECT * FROM TABLE WHERE classe LIKE '%OHN%' ;

/* inside the callback */
  bipOrigem = argv[2];
  bipDestino  = argv[3];


UPDATE TABLE SET limiar=limiar+1 WHERE id="argv[0]";

if(bipDestino == objAlerta.ipDestino )  ipD =  true;
if(bipOrigem == objAlerta.ipOrigem ) ipO = true

if(!ipD)
UPDATE TABLE SET classe= "+bipOrigem+" ,"+objAlerta.ipOrigem+ " WHERE
id= " +argv[0]+ " ;
.
.
.
/* end callback */

So Im have to do some UPDATEs, depending the result of the SELECT.

That is what Im trying to do, sorry for not putting the hole
information. And thanks for the patients.

Thiago Mello

Em Sex, 2003-11-07 às 11:37, Mrs. Brisby escreveu:
> On Sat, 2003-11-08 at 07:20, Thiago Mello wrote:
> > Hi Ben Carlyle, 
> > 
> > First of all, thanks for your help!
> > 
> > I can't brig these two operation togethe causa I need the result of the
> > SELECT in a if condition.
> 
> You cannot do the UPDATE inside of a SELECT callback. You do not need
> the results of a SELECT for an UPDATE. Not for this one.
> 
> I think you mean:
> 
> UPDATE TABLE1 SET number=number+1 WHERE id="JOHN";
> 
> Or maybe you meant:
> 
> UPDATE TABLE1 SET number=number+1 WHERE name="JOHN";
> 
> Either way, it seems an awful like you have a "bigger goal" that you're
> trying to solve that you're not explaining here for whatever reason--
> instead you assumed you needed to UPDATE inside the SELECT. You can't
> and if you show us what you're really trying to do, I'll bet that you
> don't either...



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

Re: [sqlite] database table is locked

[D. Richard Hipp]

Thiago Mello wrote:
BEGIN TRANSACTION; 
SELECT id,name FROM TABLE1;

/* now in the callback function */
IF argv[1] = "JOHN";
UPDATE  TABLE1 SET number=n+1 WHERE id=X
/* return from the Callback function */
END TRANSACTION;

But I still get a error: "database table is locked".
Is still something wrong that Im doing?
SQLite does not allow you to read and write the
same table at the same time.  You need to finish the
read of TABLE1 before you start changing it.  Perhaps
like this:
  BEGIN;
  CREATE TEMP TABLE t1 AS SELECT id,name FROM table1;
  SELECT id,name FROM t1;
  /* Now in the callback function */
  UPDATE table1 SET number=n+1 WHERE id=X;
  /* Return from callback function
  DROP TABLE t1;
  COMMIT;
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] database table is locked

[Mrs. Brisby]

On Sat, 2003-11-08 at 07:20, Thiago Mello wrote:
> Hi Ben Carlyle, 
> 
> First of all, thanks for your help!
> 
> I can't brig these two operation togethe causa I need the result of the
> SELECT in a if condition.

You cannot do the UPDATE inside of a SELECT callback. You do not need
the results of a SELECT for an UPDATE. Not for this one.

I think you mean:

UPDATE TABLE1 SET number=number+1 WHERE id="JOHN";

Or maybe you meant:

UPDATE TABLE1 SET number=number+1 WHERE name="JOHN";

Either way, it seems an awful like you have a "bigger goal" that you're
trying to solve that you're not explaining here for whatever reason--
instead you assumed you needed to UPDATE inside the SELECT. You can't
and if you show us what you're really trying to do, I'll bet that you
don't either...


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

Re: [sqlite] database table is locked

[Thiago Mello]

Hi Ben Carlyle, 

First of all, thanks for your help!

I can't brig these two operation togethe causa I need the result of the
SELECT in a if condition.

So, what I did was separate with transaction, soh I did something like
this:

BEGIN TRANSACTION; 
SELECT id,name FROM TABLE1;

/* now in the callback function */
IF argv[1] = "JOHN";
UPDATE  TABLE1 SET number=n+1 WHERE id=X
/* return from the Callback function */

END TRANSACTION;

But I still get a error: "database table is locked".
Is still something wrong that Im doing?

Thanks again,

Thiago Mello





Em Qui, 2003-11-06 às 22:03, [EMAIL PROTECTED] escreveu:

> > Im doing a SELECT sql query, and in the callback function of this sql
> > query I do a UPDATE, so when I do this update I get 
> > database table is locked.
> 
> > How I cant do the UPDATE in the second sqlite_exec() function?!
> 
> Either separate the two operations or bring them closer together. To 
> separate them do something like:
> 
> BEGIN TRANSACTION;
> SELECT ...; -- Collate results
> -- Action results:
> UPDATE ...;
> UPDATE ...;
> ...
> UPDATE ...;
> END TRANSACTION;



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

Re: [sqlite] database table is locked

[ben . carlyle]

- Forwarded by Ben Carlyle/AU/IRSA/Rail on 07/11/2003 10:03 AM -


Ben Carlyle
07/11/2003 10:03 AM


To: Thiago Mello <[EMAIL PROTECTED]>@CORP
cc: 
Subject:    Re: [sqlite] database table is locked






Thiago Mello <[EMAIL PROTECTED]>
08/11/2003 09:00 AM

 
To: [EMAIL PROTECTED]
cc: 
Subject:        [sqlite] database table is locked


> Im doing a SELECT sql query, and in the callback function of this sql
> query I do a UPDATE, so when I do this update I get 
> database table is locked.

> How I cant do the UPDATE in the second sqlite_exec() function?!

Either separate the two operations or bring them closer together. To 
separate them do something like:

BEGIN TRANSACTION;
SELECT ...; -- Collate results
-- Action results:
UPDATE ...;
UPDATE ...;
...
UPDATE ...;
END TRANSACTION;

To bring them closer together do something like this:

UPDATE foo = bar WHERE (SELECT ...)

It depends on what your query is and how you want to update the table in 
response to your table data.

Benjamin.



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

[sqlite] database table is locked

[Thiago Mello]

Hi,

Im doing a SELECT sql query, and in the callback function of this sql
query I do a UPDATE, so when I do this update I get 
database table is locked.

How I cant do the UPDATE in the second sqlite_exec() function?!

Thanks,

Thiago Mello


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