RE: [sqlite] database table is locked
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] Convert and isnumeric function
Jay, I kindly beg to differ. I have a situation where a column can contain either a name (like Joe Blow) or a numeric link to another name (like 12345). If the column contains a numeric value then the link is followed otherwise just the name is used. I would normally just handle it in code, but I need to create a view that does a union with another table that only allows numeric values in the same column. Hence I need the isnumeric to eliminate the rows where the name is not numeric. I don't want them in the view. I can probably get away without the convert funvtions. I'm going to try that now. I compiled the isnumeric function into the sqlite3 code and it works great. Here's the view create view CombinedLinksTo as select nUID, nLinkedTo, cFullName, 'I' cType from WorldCheckData_LinksTo union select nUID, cCompany nLinkedTo, cFullName, 'E' cType from WorldCheckData_Companies where isNumeric (cCompany) = 1 and cCompany != '.' Mike > -Original Message- > From: Jay Sprenkle [mailto:[EMAIL PROTECTED] > Sent: Friday, May 13, 2005 3:47 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Convert and isnumeric function > > > All or any, > > > > Has anyone implemented these as built in functions > in sqlite > > 3? If not - I was thinking of doing it and submitting to the > > sqlite.org web site for submission. > > The api converts data types for you. Isnumeric might be > useful but since the database isn't strongly typed it > probably wouldn't get used much. >
RE: [sqlite] Convert and isnumeric function
Here is the isnumeric function. Don't forget to add the line to the
sqlite3RegisterBuiltinFunctions function.
{ "isnumeric", 1, 0, SQLITE_UTF8,0, isnumericFunc},
I put it right after the abs function declartation
/*
--*/
/*
** Implementation of the isnumeric() function
*/
static void isnumericFunc(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
int i;
int nResult = 1;
assert( argc==1 );
switch( sqlite3_value_type(argv[0]) ){
case SQLITE_INTEGER: {
sqlite3_result_int(context, 1);
break;
}
case SQLITE_NULL: {
sqlite3_result_int(context, 0);
break;
}
case SQLITE_TEXT: {
const char *z = sqlite3_value_text(argv[0]);
for (i = 0; i < strlen (z); i++) {
if (!isdigit (z[i])) {
nResult = 0;
break;
}
}
sqlite3_result_int(context, nResult);
break;
}
default: {
sqlite3_result_int(context, 0);
break;
}
}
}
/*
--*/
Michael Evenson
P.s.
It sure would be nice to have this in the next release
Re: [sqlite] Convert and isnumeric function
> All or any, > > Has anyone implemented these as built in functions in sqlite 3? If > not - I was thinking of doing it and submitting to the sqlite.org web site > for submission. The api converts data types for you. Isnumeric might be useful but since the database isn't strongly typed it probably wouldn't get used much.
[sqlite] Convert and isnumeric function
All or any, Has anyone implemented these as built in functions in sqlite 3? If not - I was thinking of doing it and submitting to the sqlite.org web site for submission. Mike
Re: [sqlite] nested functions in select
> ... select count(distinct(something)) ... http://www.sqlite.org/omitted.html BTW, distinct is not a function. Regards
RE: [sqlite] database table is locked
> 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
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
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
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] Does sqlite really support transaction?
>Jay Sprenkle wrote: >The transaction doesn't seem any different than a snapshot of the >database that you can restore to. I just wondered why there were two >methods of doing the same thing. Only in a single user/single thread case are they the same thing. With Sqlite that might always be the case, but with non-embedded databases it gets much more complicated.
[sqlite] Any interest on locking for 3.x in windows CE?
Hello, I finally implemented locking on the Windows CE port for the 3.x version, and did it some time ago now. The problem is I need to test it and as I don't need to use it, have been delaying the making of the tests. I was thinking if anyone that would actually use it was interested of making that work for me. Any takers? The code is available via CVS from the sourceforge site (I didn't made a release for it, as it's completely untested) and uses the 3.2.1 SQLite source: http://sourceforge.net/cvs/?group_id=88393 I can make a build of it for any one interested, off course. You can contact me at "lucas(remove-this-thing)(at)xpto.ath.cx" Regards, ~Nuno Lucas
Re: [sqlite] request for additions to sqlite 3.xx
[13-05-2005 9:31, Chmielewski Andrzej escreveu] comm Wiadomosc ta jest przeznaczona jedynie dla osoby lub podmiotu, ktory jest jej adresatem i moze zawierac poufne i/lub uprzywilejowane informacje. Zakazane jest jakiekolwiek przegladanie, przesylanie, rozpowszechnianie lub inne wykorzystanie tych informacji lub podjecie jakichkolwiek dzialan odnosnie tych informacji przez osoby lub podmioty inne niz zamierzony adresat. Jezeli Panstwo otrzymali przez pomylke te informacje prosimy o poinformowanie o tym nadawcy i usuniecie tej wiadomosci z wszelkich komputerow. The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This is the kind of messages that get into my nerves... Sorry, but I didn't agree on your terms before you sent me the message so (even if it wasn't sent over to a public mailing list) I reserve the right to do whatever I want with it. If you don't agree, then don't send it on the first place. And by the way, a simple link to a public server where the terms are should be more than enough (as it doesn't serve nothing anyway). g
[sqlite] SQLite Java Wrapper?
I tried to use the SQLite Java Wrapper wich I download froh here http://www.ch-werner.de/javasqlite/ I extract it I do $./configure $make but I have this errors ./libtool gcc -I -o native/mkconst native/mkconst.c \ /usr/local/lib/libsqlite3.la gcc -I -o native/mkconst native/mkconst.c /usr/local/lib/libsqlite3.so -Wl,--rpath -Wl,/usr/local/lib -Wl,--rpath -Wl,/usr/local/lib gcc: native/mkconst: No such file or directory native/mkconst.c:1:20: sqlite.h: No such file or directory native/mkconst.c: In function `main': native/mkconst.c:29: error: `SQLITE_OK' undeclared (first use in this function) native/mkconst.c:29: error: (Each undeclared identifier is reported only once native/mkconst.c:29: error: for each function it appears in.) native/mkconst.c:30: error: `SQLITE_ERROR' undeclared (first use in this function) native/mkconst.c:31: error: `SQLITE_INTERNAL' undeclared (first use in this function) native/mkconst.c:32: error: `SQLITE_PERM' undeclared (first use in this function) native/mkconst.c:33: error: `SQLITE_ABORT' undeclared (first use in this function) native/mkconst.c:34: error: `SQLITE_BUSY' undeclared (first use in this function) native/mkconst.c:35: error: `SQLITE_LOCKED' undeclared (first use in this function) native/mkconst.c:36: error: `SQLITE_NOMEM' undeclared (first use in this function) native/mkconst.c:37: error: `SQLITE_READONLY' undeclared (first use in this function) native/mkconst.c:38: error: `SQLITE_INTERRUPT' undeclared (first use in this function) native/mkconst.c:39: error: `SQLITE_IOERR' undeclared (first use in this function) native/mkconst.c:40: error: `SQLITE_CORRUPT' undeclared (first use in this function) native/mkconst.c:41: error: `SQLITE_NOTFOUND' undeclared (first use in this function) native/mkconst.c:42: error: `SQLITE_FULL' undeclared (first use in this function) native/mkconst.c:43: error: `SQLITE_CANTOPEN' undeclared (first use in this function) native/mkconst.c:44: error: `SQLITE_PROTOCOL' undeclared (first use in this function) native/mkconst.c:45: error: `SQLITE_EMPTY' undeclared (first use in this function) native/mkconst.c:46: error: `SQLITE_SCHEMA' undeclared (first use in this function) native/mkconst.c:47: error: `SQLITE_TOOBIG' undeclared (first use in this function) native/mkconst.c:48: error: `SQLITE_CONSTRAINT' undeclared (first use in this function) native/mkconst.c:49: error: `SQLITE_MISMATCH' undeclared (first use in this function) native/mkconst.c:50: error: `SQLITE_MISUSE' undeclared (first use in this function) make: *** [native/mkconst] Erreur 1 Thanks for help _ Découvrez le nouveau Yahoo! Mail : 1 Go d'espace de stockage pour vos mails, photos et vidéos ! Créez votre Yahoo! Mail sur http://fr.mail.yahoo.com
RE: [sqlite] request for additions to sqlite 3.xx
comm Wiadomosc ta jest przeznaczona jedynie dla osoby lub podmiotu, ktory jest jej adresatem i moze zawierac poufne i/lub uprzywilejowane informacje. Zakazane jest jakiekolwiek przegladanie, przesylanie, rozpowszechnianie lub inne wykorzystanie tych informacji lub podjecie jakichkolwiek dzialan odnosnie tych informacji przez osoby lub podmioty inne niz zamierzony adresat. Jezeli Panstwo otrzymali przez pomylke te informacje prosimy o poinformowanie o tym nadawcy i usuniecie tej wiadomosci z wszelkich komputerow. The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.
Re: [sqlite] Duplicate keys
Hi, SQL standard does not define the order of the duplicate rows and sqlite does not either. I would really not depend on a thing like this, even if the rows are in fact retrieved in the same order they were inserted (some DB systems I know of do this). If you need the rows retrieve Rows in a specific order, it is a good idea to use a column C3 or the integer primary key. Martin Michael Knigge schrieb: Hi, is the order, in which rows with duplicate keys are retrieved, specified? For example: A table with two columns - the first is a Index. If I INSERT in the following order: C1 C2 - 3 1 1 1 1 2 1 1 1 3 2 1 If I now do a "SELECT * FROM Table ORDER BY C1;", is it guranteed that the rows with identical "C1" are retrieved in "FIFO" order, as 1 1 1 2 1 1 1 3 2 1 3 1 Or is it somehow undefined?!?!? Thanks, Michael
RE: [sqlite] java class to connect to an sqlite DB
thank you but I'm sory I'm on linux. any one have a java class to connect to an an sqlite data base. Working on linux. thaks for help --- Steve O'Hara <[EMAIL PROTECTED]> a écrit : > > Assuming you're on Windows, try this for size. > Put the this DLL and the latest sqlite3.dll from the > web site in the path > and away you go. > The Java source should be self explainatory. > > Steve > > -Original Message- > From: majed chatti [mailto:[EMAIL PROTECTED] > Sent: 12 May 2005 18:34 > To: sqlite-users@sqlite.org > Subject: [sqlite] java class to connect to an sqlite > DB > > > Hi all > any one have a java class to connect to an an sqlite > data base. > > thaks for help > > > > > > > > _ > Découvrez le nouveau Yahoo! Mail : 1 Go d'espace de > stockage pour vos mails, > photos et vidéos ! > Créez votre Yahoo! Mail sur http://fr.mail.yahoo.com > > > __ Découvrez le nouveau Yahoo! Mail : 250 Mo d'espace de stockage pour vos mails ! Créez votre Yahoo! Mail sur http://fr.mail.yahoo.com/
[sqlite] Duplicate keys
Hi, is the order, in which rows with duplicate keys are retrieved, specified? For example: A table with two columns - the first is a Index. If I INSERT in the following order: C1 C2 - 3 1 1 1 1 2 1 1 1 3 2 1 If I now do a "SELECT * FROM Table ORDER BY C1;", is it guranteed that the rows with identical "C1" are retrieved in "FIFO" order, as 1 1 1 2 1 1 1 3 2 1 3 1 Or is it somehow undefined?!?!? Thanks, Michael
