Re: [sqlite] EXTERNAL: sqlite3_step to select and update the same table
I thought call to clear bindings and reset was working but I got confused and am wherever I was before posting on the forum. Still have no conclusive evidence as to why the database in my application is behaving in a weird fashion. Any further input would be appreciated. cricketfan wrote: > > Mike I also was not able to reproduce this behavior with a small sample > program and am puzzled by this behavior in my main application. What > puzzled me is > 1. Select has criteria a=?, b=?, c=? (prepare statement) - step through it > 2. Get the value of d from database based on the above criteria > 3. Update the value of d > 4. The value of d has now been but the entire module within the step > module is being re-run. > > I have been able to get around this by calling sqlite3_clear_bindings and > sqlite3_reset within the step. > > -- View this message in context: http://old.nabble.com/sqlite3_step-to-select-and-update-the-same-table-tp30152284p30157406.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] EXTERNAL: sqlite3_step to select and update the same table
Mike I also was not able to reproduce this behavior with a small sample
program and am puzzled by this behavior in my main application. What puzzled
me is
1. Select has criteria a=?, b=?, c=? (prepare statement) - step through it
2. Get the value of d from database based on the above criteria
3. Update the value of d
4. The value of d has now been but the entire module within the step module
is being re-run.
I have been able to get around this by calling sqlite3_clear_bindings and
sqlite3_reset within the step.
Black, Michael (IS) wrote:
>
> I think the quick answer is to use a different database handle for your
> update. I think you may also be able to do this with WAL mode.
> http://www.sqlite.org/draft/wal.html
> I did some searching and couldn't find a definitive answer for doing an
> update inside a select loop (though I'm sure I've seen it on this list
> before).
>
> I was unable to duplicate your problem...perhaps you can modify this to
> show it
>
> #include
> #include "sqlite3.h"
> main()
> {
> sqlite3 *db;
> sqlite3_stmt *stmt;
> char *errmsg=NULL;
> char *sql;
> remove("update.db");
> sqlite3_open("update.db",&db);
> sqlite3_exec(db,"CREATE TABLE t (a int, b int)",NULL,NULL,&errmsg);
> sqlite3_exec(db,"insert into t values(1,1)",NULL,NULL,&errmsg);
> sqlite3_exec(db,"insert into t values(1,2)",NULL,NULL,&errmsg);
> sql = "SELECT * FROM t where a=1";
> sqlite3_prepare_v2(db,sql,strlen(sql),&stmt,NULL);
> while(sqlite3_step(stmt)==SQLITE_ROW) {
> char sqlbuf[4096];
> int ref;
> ref = sqlite3_column_int(stmt,1);
> printf("Before %d\n",ref);
> sprintf(sqlbuf,"UPDATE t set b=b+1 where a=%d",ref);
> puts(sqlbuf);
>
> sqlite3_exec(db,sqlbuf,NULL,NULL,&errmsg);
> }
> sqlite3_finalize(stmt);
> sqlite3_close(db);
>
> }
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
>
>
>
--
View this message in context:
http://old.nabble.com/sqlite3_step-to-select-and-update-the-same-table-tp30152284p30156751.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] EXTERNAL: sqlite3_step to select and update the same table
I think the quick answer is to use a different database handle for your update.
I think you may also be able to do this with WAL mode.
http://www.sqlite.org/draft/wal.html
I did some searching and couldn't find a definitive answer for doing an update
inside a select loop (though I'm sure I've seen it on this list before).
I was unable to duplicate your problem...perhaps you can modify this to show it
#include
#include "sqlite3.h"
main()
{
sqlite3 *db;
sqlite3_stmt *stmt;
char *errmsg=NULL;
char *sql;
remove("update.db");
sqlite3_open("update.db",&db);
sqlite3_exec(db,"CREATE TABLE t (a int, b int)",NULL,NULL,&errmsg);
sqlite3_exec(db,"insert into t values(1,1)",NULL,NULL,&errmsg);
sqlite3_exec(db,"insert into t values(1,2)",NULL,NULL,&errmsg);
sql = "SELECT * FROM t where a=1";
sqlite3_prepare_v2(db,sql,strlen(sql),&stmt,NULL);
while(sqlite3_step(stmt)==SQLITE_ROW) {
char sqlbuf[4096];
int ref;
ref = sqlite3_column_int(stmt,1);
printf("Before %d\n",ref);
sprintf(sqlbuf,"UPDATE t set b=b+1 where a=%d",ref);
puts(sqlbuf);
sqlite3_exec(db,sqlbuf,NULL,NULL,&errmsg);
}
sqlite3_finalize(stmt);
sqlite3_close(db);
}
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org on behalf of cricketfan
Sent: Sat 11/6/2010 11:29 PM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] sqlite3_step to select and update the same table
Hello,
I am trying to select some columns from a table and use that to
update another column in the same table using prepare/step/reset/finalize
methods. However, when I use the update statement while stepping it is
executing the flow 2 times. In order to clarify, I am pasting some pseudo
code
zSql = sqlite3_mprintf("SELECT * FROM table1 WHERE abc=? ;");
if( (rc = sqlite3_prepare_v2(db, zSql, (int)strlen(zSql), &stmt, NULL)) !=
SQLITE_OK ) {
fprintf(stderr, "SQL error: preparing update statement\n");
goto error;
}
rc = sqlite3_bind_text(stmt, 1, temp, (int)strlen(temp), NULL);
if( rc != SQLITE_OK ) { goto error;}
/* Execute the query expression */
while(sqlite3_step(stmt) == SQLITE_ROW) {
ref = sqlite3_column_int(stmt,3);
printf("Before sql execution %d \n",ref);
delSql = sqlite3_mprintf("UPDATE table1 SET ghi = %d WHERE def = %d
;",1,ref);
if( (rc = sqlite3_exec(db, delSql, NULL, NULL, &zErrMsg)) != SQLITE_OK
) {
sqlite3_free(zErrMsg); goto error; }
}
sqlite3_reset(stmt);
sqlite3_finalize(stmt);
Output :
Before sql execution 5
Before sql execution 5
Before sql execution 6
Before sql execution 6
As you can see the print statement is being printed 2 times and this code is
not in a loop, apart from the while for stepping. If I replace the update
statement with another select statement I behaves normally i.e. just
executes the query once, prints just once.
Do I have to do something special If I want to do an update on the table I
am selecting from? I am new to SQLITE hope someone can help me resolve my
newbie problem.
Thanks
--
View this message in context:
http://old.nabble.com/sqlite3_step-to-select-and-update-the-same-table-tp30152284p30152284.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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
