[sqlite] Re: using triggers to lock records
On Apr 6, 2005 10:43 AM, Will Leshner <[EMAIL PROTECTED]> wrote: > How feasible would it be to use triggers as a way to implement record > locking in SQLite? It seems like if you create a BEFORE trigger for a > particular record id and have that trigger RAISE an error for updates > and deletes, you've basically locked the record. Hmm. I guess the > problem is that the record would be locked for everybody, including > the user that locked the record in the first place. After thinking about this a bit more, I realized that writing a trigger that didn't trigger for the locker is pretty simple. So I'm thinking that triggers would work in this situation, but I'm wondering if anybody has ever done anything like this before.
[sqlite] using triggers to lock records
How feasible would it be to use triggers as a way to implement record locking in SQLite? It seems like if you create a BEFORE trigger for a particular record id and have that trigger RAISE an error for updates and deletes, you've basically locked the record. Hmm. I guess the problem is that the record would be locked for everybody, including the user that locked the record in the first place.
Re: [sqlite] how to get the INTEGER PRIMARY KEY for the row just inserted?
> >>begin immediate; insert; select max(id) from blah; commit; > >> > >> > > > >Or "select last_insert_rowid() from blah limit 1" > > > >Regards > > > > > > > Better yet > > select last_insert_rowid(); > > The from clause is not needed and may imply that SQLite keeps the > last > inserted rowid for each table, which it does not. The limit clause > is > also unnecessary since the last_insert_rowid function always returns > a single result. Oh. Nifty. I would therefore be safe doing this: begin immediate; insert into master_table() values(); insert into related_table( master_id, data ) select last_insert_rowid(), 'stuff'; commit; But it would fail if I had multiple related tables? begin immediate; insert into master_table() values(); insert into related_table( master_id, data ) select last_insert_rowid(), 'stuff'; insert into related_table2( master_id, data ) select last_insert_rowid(), 'wrong id inserted here'; commit; The last_insert_rowid() would be the id for the last related table not the master table. __ Yahoo! Messenger Show us what our next emoticon should look like. Join the fun. http://www.advision.webevents.yahoo.com/emoticontest
Re: [sqlite] how to get the INTEGER PRIMARY KEY for the row just inserted?
Kurt Welgehausen wrote: begin immediate; insert; select max(id) from blah; commit; Or "select last_insert_rowid() from blah limit 1" Regards Better yet select last_insert_rowid(); The from clause is not needed and may imply that SQLite keeps the last inserted rowid for each table, which it does not. The limit clause is also unnecessary since the last_insert_rowid function always returns a single result. HTH Dennis Cote
Re: Re: [sqlite] How do I efficiently copy tables to another db within dll (sqlite3+)
-- de f <[EMAIL PROTECTED]> wrote: > Thanks Jay. > > Great! This could work... > > I'm wondering, though if you know whether the limitation below > (from sqlite documentation) could be safely circumvented if > attaching the source db with an alias name. It seems to work > when i try it but I'm concerned that there might be other > unknown issues. > > "You cannot create a new table with the same name as a table in > an attached database, but you can attach a database which > contains tables whose names are duplicates of tables in the main > database. It is also permissible to attach the same database > file multiple times." I wrote a c++ replicator for my own database. I did this: open db1; SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'yourtable'; open db2; execute sql from step 2 against db2 to create a duplicate blank table; close db2; attatch db2 to db1; insert into db2.yourtable select * from db1.yourtable; close all; - You a Gamer? If you're near Kansas City, ask me about the Recruits and Conquest conventions. - The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264 __ Yahoo! Messenger Show us what our next emoticon should look like. Join the fun. http://www.advision.webevents.yahoo.com/emoticontest
[sqlite] Column Names (revisited)
Hello, I've been looking into upgrading to 3.2.1 from 2.8.15 and am still running into problems with returned column names. I've written a piece of code to test four scenarios with 2.8.15 and 3.2.1 and these are the results: Two tables: CREATE TABLE A_TABLE (col1 INTEGER, col2 INTEGER) CREATE TABLE B_TABLE (col1 INTEGER, col2 INTEGER) Two queries: 1. SELECT A.*, B.* FROM A_TABLE, B_TABLE WHERE A.col1 = B.col1 2. SELECT * FROM A_TABLE The column names are returned as follows (with the relevant column names pragmas). --- TEST PRAGMAS2.8.15 3.2.1 --- 1 (short=0, full=0) A.col1, B.col1, ... col1, col1, ... 2 (short=0, full=0) col1, col2, ... col1, col2, ... 1 (short=1, full=0) col1, col1, ... col1, col1, ... 2 (short=1, full=0) col1, col2, ... col1, col2, ... 1 (short=0, full=1) A_TABLE.col1, B_TABLE.col1, ... col1, col1, ... 2 (short=0, full=1) A_TABLE.col1, A_TABLE.col2, ... col1, col2, ... --- In other words, the pragmas seem to have NO effect when the same piece of code compiled against 2.8.15 is compiled against 3.2.1. I ALWAYS get returned just the column names. All I need to do is guarantee that the column names will turn out like the first two rows of the table !! Can anyone offer any help? I'm using standard sqlite_step functionality, which is working as expected in 2.8.15. Regards, Steve
Re: [sqlite] Access Violations in sqlite3_step when in DLL
On 6 Apr 2005 at 3:13, Dan Kennedy wrote: Date sent: Wed, 6 Apr 2005 03:13:58 -0700 (PDT) From: Dan Kennedy <[EMAIL PROTECTED]> Subject:Re: [sqlite] Access Violations in sqlite3_step when in DLL To: sqlite-users@sqlite.org, [EMAIL PROTECTED] > > Exactly what are you calling sqlite3_free() on? Sorry, I am probably being a bit misleading there. That Free was on any error message returned from SQlite3_ErrMsg which we call if the result from SQLite3_Open isn't SQLITE_OK In theory it's never called, we never get any errors out of SQLIte_Open, it just AV's We call SQLite3_Close and pass the pointer that is returned from the open call. I havn't seen any errors on close. We see errors on Open, Step or Get_Table depending on what we use (ie, if we just Open and close, the AV occurs eventually on one of the opens. If we try Get_Table the AV will happen sooner and happen on a Get_Table) None of the code we used (and I even tried other componants and wrappers in case the one we were using is suspect) has ever caused any issues previously and we've been using it for months. Only when called from this plugin are we seeing problems. I've tried 2 wrappers and one componant and still get the issue
Re: [sqlite] Access Violations in sqlite3_step when in DLL
Exactly what are you calling sqlite3_free() on? --- Chris Schirlinger <[EMAIL PROTECTED]> wrote: > Sadly, I have narrowed down the issue to some sort of memory clash > between the SQLite3 DLL and the EXE that is calling our plugin > > I can change the code to retrieve data from almost *anything* > else from faked random data created on the fly, through ASCII CSV > files loaded into TStringLists to ADO connections to large (slow) > access databases, and it works fine > > However the second I start using any functions in SQLite3 a crash is > sure to follow. > > In the simplest form, calling SQLite3_Open followed by SQLite3_Free > is enough to eventually cause an access violation (eventually, not > necessarly first time you call the Open, could be the tenth. or so > on) > > Anyone have any ideas as to the best place in SQLite code to check > the memory management so I can pose intilligent questions to the EXE > developers? (Or any other ideas as to where to look before I delve > that far?) > > (Note this occures on all test machines, think we have ruled out any > sort of memory chip failure) > > __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail
