Re: [sqlite] Update while stepping through a prepared query

[Neville Franks]

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

[Igor Tandetnik]

"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

[Neville Franks]

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)

[Jeff Hamilton]

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?

[danjenkins]

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?

[Igor Tandetnik]

"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",);
>
> // 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

[David Bicking]

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,,NULL);
> if (rc!=SQLITE_OK)
> error_print();
> sqlite3_exec(db,"BEGIN;",NULL,NULL,);
> 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,) ;
> 
___
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

[Vincent Vega]

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,,NULL);
if (rc!=SQLITE_OK)
error_print();
sqlite3_exec(db,"BEGIN;",NULL,NULL,);
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,) ;


 
- 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

Re: [sqlite] Is there direct (non SQL) table access?

[Cory Nelson]

On Sat, Mar 15, 2008 at 10:18 PM, danjenkins <[EMAIL PROTECTED]> wrote:
>
>  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",);
>
>  // 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();
>  }
>
>  fclose(fp);
>  sqlite3_close(pDB);
>
>  I'd really appreciate a couple of tips so I can get back on track with my
>  project.

Not without digging through the internals.  You might be interested in
Berkeley DB.

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