Re: [sqlite] locking problem (on insert inside callback)
Nah, ignore that, I was talking garbage... Using the view is a neater way of doing that select statement, but you still need to create the temp table to avoid the locking issues... Rachel On 03/12/05, Rachel Willmer <[EMAIL PROTECTED]> wrote: > (Replying to my own email so I can find this answer again via google > in the future...) > > I have an even better solution... > > CREATE VIEW NewView AS SELECT * FROM table1 LEFT JOIN table2 on > table1.field=table2.field WHERE table2.field is NULL; > > Works just fine... > > Cheers > Rachel > > Back In Oct, I asked: > Rachel Willmer <[EMAIL PROTECTED]> wrote: > > I want to search two tables which should contain the same records and > > add any that are missing from the second into the first. > > > > So I do > > > > SELECT * FROM table1 LEFT JOIN table2 on table1.field=table2.field > > WHERE table2.field is NULL > > > > So far, so good, I get the records I want. Then in the callback, I try > > > > INSERT INTO table1 etc... > > > > This fails with a "database table is locked" error. > > > > I'm assuming that this is because I'm still in the middle of doing the > > SELECT statement. > > > > So my question is this, do I have to use the callback to copy the > > records into a temp table, and then only add them after the > > sqlite3_exec() which calls the SELECT has returned? or is there a more > > elegant/obvious solution? > > On 14/10/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > > Solution 1 is to use a TEMP table: > > > > CREATE TEMP TABLE diffs AS > > SELECT * FROM table1 LEFT JOIN table2 ; > > SELECT * FROM diffs; -- Insert into table1 in the callback; > > DROP TABLE diffs; > > > > Solution 2 is a dirty trick. It works now and in all historical versions > > of SQLite and there are no plans to change it, but there are also no > > promises not to change it. In solution 2, add > > > > ORDER BY +table1.rowid > > > > to the end of your SELECT statement. The "+" sign in front of the > > "table1.rowid" is *essential* if this is trick is to work. > > > > -- > > D. Richard Hipp <[EMAIL PROTECTED]> >
Re: [sqlite] locking problem (on insert inside callback)
(Replying to my own email so I can find this answer again via google in the future...) I have an even better solution... CREATE VIEW NewView AS SELECT * FROM table1 LEFT JOIN table2 on table1.field=table2.field WHERE table2.field is NULL; Works just fine... Cheers Rachel Back In Oct, I asked: Rachel Willmer <[EMAIL PROTECTED]> wrote: > I want to search two tables which should contain the same records and > add any that are missing from the second into the first. > > So I do > > SELECT * FROM table1 LEFT JOIN table2 on table1.field=table2.field > WHERE table2.field is NULL > > So far, so good, I get the records I want. Then in the callback, I try > > INSERT INTO table1 etc... > > This fails with a "database table is locked" error. > > I'm assuming that this is because I'm still in the middle of doing the > SELECT statement. > > So my question is this, do I have to use the callback to copy the > records into a temp table, and then only add them after the > sqlite3_exec() which calls the SELECT has returned? or is there a more > elegant/obvious solution? On 14/10/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > Solution 1 is to use a TEMP table: > > CREATE TEMP TABLE diffs AS > SELECT * FROM table1 LEFT JOIN table2 ; > SELECT * FROM diffs; -- Insert into table1 in the callback; > DROP TABLE diffs; > > Solution 2 is a dirty trick. It works now and in all historical versions > of SQLite and there are no plans to change it, but there are also no > promises not to change it. In solution 2, add > > ORDER BY +table1.rowid > > to the end of your SELECT statement. The "+" sign in front of the > "table1.rowid" is *essential* if this is trick is to work. > > -- > D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] locking problem (on insert inside callback)
> Solution 1 is to use a TEMP table: > > CREATE TEMP TABLE diffs AS > SELECT * FROM table1 LEFT JOIN table2 ; > SELECT * FROM diffs; -- Insert into table1 in the callback; > DROP TABLE diffs; that sounds like the answer for me thanks Rachel
Re: [sqlite] locking problem (on insert inside callback)
> I'm not sure I understand your logic. Your left join > indicates that there are records missing from table2, > so I would expect that you want to insert the missing > records into table2. Assuming that's what you meant, > > insert into table2 > select * from table1 > where table1.field not in (select field from table2) > > or > > insert into table2 > select * from table1 > where not exists > (select field from table2 where table2.field=table1.field) > > The subquery in the first form is static (executed only > once). The subquery in the second form is correlated > (executed many times), but the second form can be > faster in some circumstances. I missed out a bit of the logic. I want to be able to ask the user if they want to approve the addition of the new record, so I need to be able to do this as two separate operations, rather than combine them into a single sql statement. Rachel
Re: [sqlite] locking problem (on insert inside callback)
> SELECT * FROM table1 LEFT JOIN table2 on table1.field=table2.field > WHERE table2.field is NULL > > So far, so good, I get the records I want. Then in the callback, I try > > INSERT INTO table1 etc... I'm not sure I understand your logic. Your left join indicates that there are records missing from table2, so I would expect that you want to insert the missing records into table2. Assuming that's what you meant, insert into table2 select * from table1 where table1.field not in (select field from table2) or insert into table2 select * from table1 where not exists (select field from table2 where table2.field=table1.field) The subquery in the first form is static (executed only once). The subquery in the second form is correlated (executed many times), but the second form can be faster in some circumstances. Regards
Re: [sqlite] locking problem (on insert inside callback)
Why do it in the call back? Why not just do it in sql: CREATE TEMP TABLE diffs AS SELECT * FROM table1 LEFT JOIN table2 ; insert into table1 SELECT * FROM diffs; -- Insert into table1 DROP TABLE diffs; On 10/14/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > Rachel Willmer <[EMAIL PROTECTED]> wrote: > > Hi, apologies if this is a trivial question, but I'm a newbie to > > sqlite3. (very impressed so far) > > > > I want to search two tables which should contain the same records and > > add any that are missing from the second into the first. > > > > So I do > > > > SELECT * FROM table1 LEFT JOIN table2 on table1.field=table2.field > > WHERE table2.field is NULL > > > > So far, so good, I get the records I want. Then in the callback, I try > > > > INSERT INTO table1 etc... > > > > This fails with a "database table is locked" error. > > > > I'm assuming that this is because I'm still in the middle of doing the > > SELECT statement. > > > > So my question is this, do I have to use the callback to copy the > > records into a temp table, and then only add them after the > > sqlite3_exec() which calls the SELECT has returned? or is there a more > > elegant/obvious solution? > > > > Solution 1 is to use a TEMP table: > > CREATE TEMP TABLE diffs AS > SELECT * FROM table1 LEFT JOIN table2 ; > SELECT * FROM diffs; -- Insert into table1 in the callback; > DROP TABLE diffs; > > Solution 2 is a dirty trick. It works now and in all historical versions > of SQLite and there are no plans to change it, but there are also no > promises not to change it. In solution 2, add > > ORDER BY +table1.rowid > > to the end of your SELECT statement. The "+" sign in front of the > "table1.rowid" is *essential* if this is trick is to work. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > -- --- The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264
Re: [sqlite] locking problem (on insert inside callback)
Rachel Willmer <[EMAIL PROTECTED]> wrote: > Hi, apologies if this is a trivial question, but I'm a newbie to > sqlite3. (very impressed so far) > > I want to search two tables which should contain the same records and > add any that are missing from the second into the first. > > So I do > > SELECT * FROM table1 LEFT JOIN table2 on table1.field=table2.field > WHERE table2.field is NULL > > So far, so good, I get the records I want. Then in the callback, I try > > INSERT INTO table1 etc... > > This fails with a "database table is locked" error. > > I'm assuming that this is because I'm still in the middle of doing the > SELECT statement. > > So my question is this, do I have to use the callback to copy the > records into a temp table, and then only add them after the > sqlite3_exec() which calls the SELECT has returned? or is there a more > elegant/obvious solution? > Solution 1 is to use a TEMP table: CREATE TEMP TABLE diffs AS SELECT * FROM table1 LEFT JOIN table2 ; SELECT * FROM diffs; -- Insert into table1 in the callback; DROP TABLE diffs; Solution 2 is a dirty trick. It works now and in all historical versions of SQLite and there are no plans to change it, but there are also no promises not to change it. In solution 2, add ORDER BY +table1.rowid to the end of your SELECT statement. The "+" sign in front of the "table1.rowid" is *essential* if this is trick is to work. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] locking problem (on insert inside callback)
Hi, apologies if this is a trivial question, but I'm a newbie to sqlite3. (very impressed so far) I want to search two tables which should contain the same records and add any that are missing from the second into the first. So I do SELECT * FROM table1 LEFT JOIN table2 on table1.field=table2.field WHERE table2.field is NULL So far, so good, I get the records I want. Then in the callback, I try INSERT INTO table1 etc... This fails with a "database table is locked" error. I'm assuming that this is because I'm still in the middle of doing the SELECT statement. So my question is this, do I have to use the callback to copy the records into a temp table, and then only add them after the sqlite3_exec() which calls the SELECT has returned? or is there a more elegant/obvious solution? Any pointers most welcome Rachel
