Re: [sqlite] Update while stepping through a prepared query
Monday, March 17, 2008, 12:03:26 PM, you wrote: IT> "Neville Franks" <[EMAIL PROTECTED]> wrote IT> in message news:[EMAIL PROTECTED] >> Can I do an Update while stepping through a prepared query? IT> Yes, in recent enough versions of SQLite, and assuming both the SELECT IT> and UPDATE run on the same database connection. Thanks Igor, that is good to hear. I'm using 3.5.6 and the same database connection. -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update while stepping through a prepared query
"Neville Franks" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Can I do an Update while stepping through a prepared query? Yes, in recent enough versions of SQLite, and assuming both the SELECT and UPDATE run on the same database connection. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Update while stepping through a prepared query
Can I do an Update while stepping through a prepared query? example in psuedo code: --- compile( "select rowid, tag, expanded from tags order by tag;" sqlite3_step(); do { if ( some_clm == some_value ) sqlite3_exec( "update tags set expanded=%d where rowid=%Q;", .. ); // will the subsequent sqlite3_step() calls perform as expected? } while( sqlite3_step() == SQLITE_ROW ); - -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] step back (again)
Whoops, you're right my example won't use the index: SQLite version 3.5.6 Enter ".help" for instructions sqlite> CREATE TABLE tracks (id INTEGER PRIMARY KEY, title TEXT); sqlite> CREATE INDEX tracksIndex ON tracks (title DESC, id ASC); sqlite> EXPLAIN QUERY PLAN SELECT * FROM tracks WHERE title<:firsttitle OR (title=:firsttitle AND id>:firstrow) ORDER BY title DESC, id ASC; 0|0|TABLE tracks sqlite> EXPLAIN QUERY PLAN SELECT * FROM tracks WHERE title<=:firsttitle AND (title!=:firsttitle OR rowid<:firstrowid) ORDER BY title DESC, id ASC; 0|0|TABLE tracks WITH INDEX tracksIndex -Jeff On 3/15/08, Tomas Lee <[EMAIL PROTECTED]> wrote: > On 2008 March 15 (Sat) 05:21:53pm PDT, Jeff Hamilton <[EMAIL PROTECTED]> > wrote: > > What about something like this: > > > > SELECT title FROM tracks > > WHERE singer='Madonna' > >AND (title<:firsttitle OR (title=:firsttitle AND rowid<:firstrowid)) > > ORDER BY title DESC, rowid ASC > > LIMIT 5; > > > > Then you only have to remember the single title and rowid of the first > > item in the list. You'd have to add the rowid ASC to your index as > > well, but the index already needs to store the rowid so I don't think > > it would take more space. > > > That's a clever idea. But is SQLite's query optimizer smart enough to > know it can use the index on title for that query? If you re-write it > to be > > > SELECT title FROM tracks > WHERE singer='Madonna' > >AND title<=:firsttitle AND (title!=:firsttitle OR rowid<:firstrowid) > > ORDER BY title DESC, rowid ASC > LIMIT 5; > > > then it surely should realize that it can use the index on title. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there direct (non SQL) table access?
Now that I know it's not easily doable, I'll just use an update statement. Thanks for saving me a lot of time guys! -Dan -- View this message in context: http://www.nabble.com/Is-there-direct-%28non-SQL%29-table-access--tp16075825p1608.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there direct (non SQL) table access?
"danjenkins" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Is it possible to access a table without a SQL query? I've been > searching and haven't found a way. > I'd like to do something like this: > > sqlite3 *pDB = NULL; // database pointer > FILE* fp = fopen("people.db","a+"); > sqlite3_open("people.db",&pDB); > > // Pseudo code that I need help with follows... > while(!pDB->Eof){ >if(pDB->FieldValues["Age"] > 65; >pDB->Edit(); >pDB->FieldValues["Classification"] = "Retired"; >pDB->Post(); >} >pDB->Next(); > } Why not just run a SQL statement? update tableName set Classification='Retired' where Age > 65; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_CONSTRAINT error after sqlite3_step
Hopefully not too obvious questions, but do all your tests use the same values in the list array? What is the value of list[i] when you get the SQLITE_CONSTRAINT error? Are you confirming that the value of list[i] is in fact not somewhere earlier in the array? Any chance a NULL is creeping in to list[i]? Is the table empty before you rn the tests? The code looks okay, so my gut instinct would be to look at the data being fed in to see if that is causing the problem. David --- On Sun, 3/16/08, Vincent Vega <[EMAIL PROTECTED]> wrote: > From: Vincent Vega <[EMAIL PROTECTED]> > Subject: Re: [sqlite] SQLITE_CONSTRAINT error after sqlite3_step > To: [EMAIL PROTECTED], "General Discussion of SQLite Database" > > Date: Sunday, March 16, 2008, 4:37 AM > Sorry.. I didn't copy it correctly.. > Anyhow, as I said before the code works fine most of the > times(I can insert 500 records to the table and read them > correctly) but from time to time I get SQLITE_CONSTRAINT > error code after the sqlite3_step. > Any Idea why? > > Here are the table defenitions : > CREATE TABLE 'Inventory' ( > 'TagIndex' integer PRIMARY KEY, > 'Tag' varchar(12) NOT NULL, > ) ; > > CREATE UNIQUE INDEX InventoryIndex ON Inventory (Tag); > > My code looks like this: > > query = sqlite3_mprintf("Insert into Inventory (Tag) > values (?)"); > rc=sqlite3_prepare_v2(DB,query ,-1,&Statement,NULL); > if (rc!=SQLITE_OK) > error_print(); > sqlite3_exec(db,"BEGIN;",NULL,NULL,&err); > for (i=1;i<500;i ++) > { > rc=sqlite3_bind_text(Statement,1,list[i],-1,NULL); > if (rc!=SQLITE_OK) > error_print(); > rc=sqlite3_step(Statement); > if (rc!=SQLITE_DONE) > error_print(); > sqlite3_reset(Statement) ; > } > sqlite3_finalize(Statement) ; > > sqlite3_exec(db,"END;",NULL,NULL,&err) ; > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_CONSTRAINT error after sqlite3_step
Sorry.. I didn't copy it correctly.. Anyhow, as I said before the code works fine most of the times(I can insert 500 records to the table and read them correctly) but from time to time I get SQLITE_CONSTRAINT error code after the sqlite3_step. Any Idea why? Here are the table defenitions : CREATE TABLE 'Inventory' ( 'TagIndex' integer PRIMARY KEY, 'Tag' varchar(12) NOT NULL, ) ; CREATE UNIQUE INDEX InventoryIndex ON Inventory (Tag); My code looks like this: query = sqlite3_mprintf("Insert into Inventory (Tag) values (?)"); rc=sqlite3_prepare_v2(DB,query ,-1,&Statement,NULL); if (rc!=SQLITE_OK) error_print(); sqlite3_exec(db,"BEGIN;",NULL,NULL,&err); for (i=1;i<500;i ++) { rc=sqlite3_bind_text(Statement,1,list[i],-1,NULL); if (rc!=SQLITE_OK) error_print(); rc=sqlite3_step(Statement); if (rc!=SQLITE_DONE) error_print(); sqlite3_reset(Statement) ; } sqlite3_finalize(Statement) ; sqlite3_exec(db,"END;",NULL,NULL,&err) ; - Original Message From: Jay A. Kreibich <[EMAIL PROTECTED]> To: General Discussion of SQLite Database Sent: Saturday, March 15, 2008 3:07:46 AM Subject: Re: [sqlite] SQLITE_CONSTRAINT error after sqlite3_step On Fri, Mar 14, 2008 at 04:09:50PM -0700, Vincent Vega scratched on the wall: > for (i=1;500;i ++) > { I can't say about your CONSTRAINT problem, but that's an infinite loop. And 1? Seriously? Without a full description of the table and all the indexes on the table (and, if possible, real data values), it is difficult to say what the problem is. CONSTRAINT issues are normally data issues, not code problems. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users