RE: [sqlite] Select, update on the row, and step leads to crash
Any particular reason why you're using 3.3.13? I don't plan to compile it. --- karthikeyan <[EMAIL PROTECTED]> wrote: > Ah yes, in the sqlite3_bind_int, I need to pass id, thanks. Also, please try > compiling, the sqlite code (3.3.13) with "-DSQLITE_OMIT_SHARED_CACHE=1". It > should crash with the example code. On Versions, 3.3.17 and 3.4.1 its > working fine. Choose the right car based on your needs. Check out Yahoo! Autos new Car Finder tool. http://autos.yahoo.com/carfinder/ - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Select, update on the row, and step leads to crash
Hi,
Ah yes, in the sqlite3_bind_int, I need to pass id, thanks. Also, please try
compiling, the sqlite code (3.3.13) with "-DSQLITE_OMIT_SHARED_CACHE=1". It
should crash with the example code. On Versions, 3.3.17 and 3.4.1 its
working fine.
Thanks
Karthik
This e-mail and attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed
above. Any use of the information contained herein in any way (including,
but not limited to, total or partial disclosure, reproduction, or
dissemination) by persons other than the intended recipient's) is
prohibited. If you receive this e-mail in error, please notify the sender by
phone or email immediately and delete it!
-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED]
Sent: Monday, August 06, 2007 8:10 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Select, update on the row, and step leads to crash
Updates during a SELECT work fine once you correct these mistakes:
You shouldn't be using SQLITE_STATIC as buf is temporary - use
SQLITE_TRANSIENT instead.
The two different ways you populate the table are different.
If createUsingExec is 1, your are inserting strings beginning with 'name is
1'. Whereas when createUsingExec is 0, you are inserting strings beginning
with 'name is 0' due to your for loop:
for (i = 0; i < 5; i ++)/sqlite3_bind_int (pInsRow, 1, i);
In your last while loop you are mistakenly using the variable 'i'
instead of 'id'.
You cannot use ++ in the sprintf to change the id variable as this will
change the id which you will later bind with.
--- karthikeyan <[EMAIL PROTECTED]> wrote:
> The sample code is below. I am trying this on windows(xp)(sqlite
> 3.3.13) and have disabled almost all sqlite features.
> When createUsingExec variable is true in the sample code, I am able to
> reproduce the problem.
>
> Thanks
> karthik
>
> //== begin
> ==
> int select_while_update ();
> int main ()
> {
> select_while_update ();
> }
>
> int select_while_update ()
> {
> int i = 0;
> int ret;
> sqlite3 *pDb = NULL;
> const char *dbName = "./t.db";
> const char *crTable = "CREATE TABLE A ( ID INTEGER, NAME TEXT) ;";
> const char *selAll = "SELECT * from A;";
> const char *insRow = "INSERT INTO A VALUES (?,?);";
> const char *updRow = "UPDATE A SET NAME=? WHERE ID=?;";
>
> sqlite3_stmt *pSelAll = NULL;
> sqlite3_stmt *pInsRow = NULL;
> sqlite3_stmt *pUpdRow = NULL;
> int createUsingExec = 1;
>
> char buf [64] = { 0 };
>
> remove (dbName);
> ret = sqlite3_open (dbName, );
> sqlite3_busy_timeout (pDb, 3000);
>
> if (createUsingExec) {
> const char *c2 = "INSERT INTO A VALUES ( 1, 'name is 1' )
;";
> const char *c3 = "INSERT INTO A VALUES ( 2, 'name is 2' )
;";
> const char *c4 = "INSERT INTO A VALUES ( 3, 'name is 3') ;";
> const char *c5 = "INSERT INTO A VALUES ( 4, 'name is 4' )
;";
> const char *c6 = "INSERT INTO A VALUES ( 5, 'name is 5' )
;";
>
> sqlite3_exec (pDb, crTable, 0, 0, 0);
> sqlite3_exec (pDb, c2, 0, 0, 0);
> sqlite3_exec (pDb, c3, 0, 0, 0);
> sqlite3_exec (pDb, c4, 0, 0, 0);
> sqlite3_exec (pDb, c5, 0, 0, 0);
> sqlite3_exec (pDb, c6, 0, 0, 0);
>
> } else {
>
> ret = sqlite3_exec (pDb, crTable, NULL, NULL, NULL);
> if (SQLITE_OK != ret) {
> printf ("error creating table\n");
> return -1;
> }
>
> ret = sqlite3_prepare_v2 (pDb, insRow, -1, , NULL);
>
>
> for (i = 0; i < 5; i ++) {
> sprintf (buf, "name is %d", i);
>
> sqlite3_reset (pInsRow);
> sqlite3_bind_int (pInsRow, 1, i);
> sqlite3_bind_text (pInsRow, 2, buf, -1,
SQLITE_STATIC);
> sqlite3_step (pInsRow);
>
> }
>
> sqlite3_finalize (pInsRow);
> }
>
> ret = sqlite3_prepare_v2 (pDb, selAll, -1, , NULL);
> sqlite3_bind_parameter_count (pSelAll);
> sqlite3_reset (pSelAll);
> ret = sqlite3_step (pSelAll);
>
> while (SQLITE_ROW == ret) {
> // get id from select statemnt results
> int id;
> char *text;
>
> sqlite3_data_count (pSelAll);
> sqlite3_column_count (pSelAll);
>
> sqlite3_column_type (pSelAll, 0);
> sqlite3_column_bytes (pSelAll, 0);
>
RE: [sqlite] Select, update on the row, and step leads to crash
Updates during a SELECT work fine once you correct these mistakes:
You shouldn't be using SQLITE_STATIC as buf is temporary - use
SQLITE_TRANSIENT instead.
The two different ways you populate the table are different.
If createUsingExec is 1, your are inserting strings beginning with
'name is 1'. Whereas when createUsingExec is 0, you are inserting
strings beginning with 'name is 0' due to your for loop:
for (i = 0; i < 5; i ++)/sqlite3_bind_int (pInsRow, 1, i);
In your last while loop you are mistakenly using the variable 'i'
instead of 'id'.
You cannot use ++ in the sprintf to change the id variable as this
will change the id which you will later bind with.
--- karthikeyan <[EMAIL PROTECTED]> wrote:
> The sample code is below. I am trying this on windows(xp)(sqlite 3.3.13) and
> have disabled almost all sqlite features.
> When createUsingExec variable is true in the sample code, I am able to
> reproduce the problem.
>
> Thanks
> karthik
>
> //== begin
> ==
> int select_while_update ();
> int main ()
> {
> select_while_update ();
> }
>
> int select_while_update ()
> {
> int i = 0;
> int ret;
> sqlite3 *pDb = NULL;
> const char *dbName = "./t.db";
> const char *crTable = "CREATE TABLE A ( ID INTEGER, NAME TEXT) ;";
> const char *selAll = "SELECT * from A;";
> const char *insRow = "INSERT INTO A VALUES (?,?);";
> const char *updRow = "UPDATE A SET NAME=? WHERE ID=?;";
>
> sqlite3_stmt *pSelAll = NULL;
> sqlite3_stmt *pInsRow = NULL;
> sqlite3_stmt *pUpdRow = NULL;
> int createUsingExec = 1;
>
> char buf [64] = { 0 };
>
> remove (dbName);
> ret = sqlite3_open (dbName, );
> sqlite3_busy_timeout (pDb, 3000);
>
> if (createUsingExec) {
> const char *c2 = "INSERT INTO A VALUES ( 1, 'name is 1' )
> ;";
> const char *c3 = "INSERT INTO A VALUES ( 2, 'name is 2' )
> ;";
> const char *c4 = "INSERT INTO A VALUES ( 3, 'name is 3') ;";
> const char *c5 = "INSERT INTO A VALUES ( 4, 'name is 4' )
> ;";
> const char *c6 = "INSERT INTO A VALUES ( 5, 'name is 5' )
> ;";
>
> sqlite3_exec (pDb, crTable, 0, 0, 0);
> sqlite3_exec (pDb, c2, 0, 0, 0);
> sqlite3_exec (pDb, c3, 0, 0, 0);
> sqlite3_exec (pDb, c4, 0, 0, 0);
> sqlite3_exec (pDb, c5, 0, 0, 0);
> sqlite3_exec (pDb, c6, 0, 0, 0);
>
> } else {
>
> ret = sqlite3_exec (pDb, crTable, NULL, NULL, NULL);
> if (SQLITE_OK != ret) {
> printf ("error creating table\n");
> return -1;
> }
>
> ret = sqlite3_prepare_v2 (pDb, insRow, -1, , NULL);
>
>
> for (i = 0; i < 5; i ++) {
> sprintf (buf, "name is %d", i);
>
> sqlite3_reset (pInsRow);
> sqlite3_bind_int (pInsRow, 1, i);
> sqlite3_bind_text (pInsRow, 2, buf, -1,
> SQLITE_STATIC);
> sqlite3_step (pInsRow);
>
> }
>
> sqlite3_finalize (pInsRow);
> }
>
> ret = sqlite3_prepare_v2 (pDb, selAll, -1, , NULL);
> sqlite3_bind_parameter_count (pSelAll);
> sqlite3_reset (pSelAll);
> ret = sqlite3_step (pSelAll);
>
> while (SQLITE_ROW == ret) {
> // get id from select statemnt results
> int id;
> char *text;
>
> sqlite3_data_count (pSelAll);
> sqlite3_column_count (pSelAll);
>
> sqlite3_column_type (pSelAll, 0);
> sqlite3_column_bytes (pSelAll, 0);
> // get id
> id = sqlite3_column_int (pSelAll, 0);
>
> sqlite3_column_type (pSelAll, 1);
> sqlite3_column_bytes (pSelAll, 1);
> // get text
> text = sqlite3_column_text (pSelAll, 1);
>
> sprintf (buf, "name is %d", ++i);
>
> // prepare update statements
> if (!pUpdRow) {
> sqlite3_prepare_v2 (pDb, updRow, -1, ,
> NULL);
> }
>
> sqlite3_bind_parameter_count (pUpdRow);
> sqlite3_reset (pUpdRow);
>
> // bind values to update statement
> sqlite3_bind_text (pUpdRow, 1, buf, -1, SQLITE_STATIC);
> sqlite3_bind_int (pUpdRow, 2, i);
> sqlite3_step (pUpdRow);
>
> // step to next result row
> ret = sqlite3_step (pSelAll);
> }
>
> // need finalize other statements
> return 0;
> }
>
RE: [sqlite] Select, update on the row, and step leads to crash
Hi,
The sample code is below. I am trying this on windows(xp)(sqlite 3.3.13) and
have disabled almost all sqlite features.
When createUsingExec variable is true in the sample code, I am able to
reproduce the problem.
Thanks
karthik
//== begin
==
int select_while_update ();
int main ()
{
select_while_update ();
}
int select_while_update ()
{
int i = 0;
int ret;
sqlite3 *pDb = NULL;
const char *dbName = "./t.db";
const char *crTable = "CREATE TABLE A ( ID INTEGER, NAME TEXT) ;";
const char *selAll = "SELECT * from A;";
const char *insRow = "INSERT INTO A VALUES (?,?);";
const char *updRow = "UPDATE A SET NAME=? WHERE ID=?;";
sqlite3_stmt *pSelAll = NULL;
sqlite3_stmt *pInsRow = NULL;
sqlite3_stmt *pUpdRow = NULL;
int createUsingExec = 1;
char buf [64] = { 0 };
remove (dbName);
ret = sqlite3_open (dbName, );
sqlite3_busy_timeout (pDb, 3000);
if (createUsingExec) {
const char *c2 = "INSERT INTO A VALUES ( 1, 'name is 1' )
;";
const char *c3 = "INSERT INTO A VALUES ( 2, 'name is 2' )
;";
const char *c4 = "INSERT INTO A VALUES ( 3, 'name is 3') ;";
const char *c5 = "INSERT INTO A VALUES ( 4, 'name is 4' )
;";
const char *c6 = "INSERT INTO A VALUES ( 5, 'name is 5' )
;";
sqlite3_exec (pDb, crTable, 0, 0, 0);
sqlite3_exec (pDb, c2, 0, 0, 0);
sqlite3_exec (pDb, c3, 0, 0, 0);
sqlite3_exec (pDb, c4, 0, 0, 0);
sqlite3_exec (pDb, c5, 0, 0, 0);
sqlite3_exec (pDb, c6, 0, 0, 0);
} else {
ret = sqlite3_exec (pDb, crTable, NULL, NULL, NULL);
if (SQLITE_OK != ret) {
printf ("error creating table\n");
return -1;
}
ret = sqlite3_prepare_v2 (pDb, insRow, -1, , NULL);
for (i = 0; i < 5; i ++) {
sprintf (buf, "name is %d", i);
sqlite3_reset (pInsRow);
sqlite3_bind_int (pInsRow, 1, i);
sqlite3_bind_text (pInsRow, 2, buf, -1,
SQLITE_STATIC);
sqlite3_step (pInsRow);
}
sqlite3_finalize (pInsRow);
}
ret = sqlite3_prepare_v2 (pDb, selAll, -1, , NULL);
sqlite3_bind_parameter_count (pSelAll);
sqlite3_reset (pSelAll);
ret = sqlite3_step (pSelAll);
while (SQLITE_ROW == ret) {
// get id from select statemnt results
int id;
char *text;
sqlite3_data_count (pSelAll);
sqlite3_column_count (pSelAll);
sqlite3_column_type (pSelAll, 0);
sqlite3_column_bytes (pSelAll, 0);
// get id
id = sqlite3_column_int (pSelAll, 0);
sqlite3_column_type (pSelAll, 1);
sqlite3_column_bytes (pSelAll, 1);
// get text
text = sqlite3_column_text (pSelAll, 1);
sprintf (buf, "name is %d", ++i);
// prepare update statements
if (!pUpdRow) {
sqlite3_prepare_v2 (pDb, updRow, -1, ,
NULL);
}
sqlite3_bind_parameter_count (pUpdRow);
sqlite3_reset (pUpdRow);
// bind values to update statement
sqlite3_bind_text (pUpdRow, 1, buf, -1, SQLITE_STATIC);
sqlite3_bind_int (pUpdRow, 2, i);
sqlite3_step (pUpdRow);
// step to next result row
ret = sqlite3_step (pSelAll);
}
// need finalize other statements
return 0;
}
//== end ==
This e-mail and attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed
above. Any use of the information contained herein in any way (including,
but not limited to, total or partial disclosure, reproduction, or
dissemination) by persons other than the intended recipient's) is
prohibited. If you receive this e-mail in error, please notify the sender by
phone or email immediately and delete it!
-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED]
Sent: Sunday, August 05, 2007 12:59 AM
To: sqlite-users@sqlite.org
Subject:
Re: [sqlite] Select, update on the row, and step leads to crash
It should be possible to do what you're trying to do as of 3.3.8. See the Tcl examples at the bottom of this patch: Allows UPDATE, INSERT, and DELETEs to occur while a SELECT is pending on the same table. http://www.sqlite.org/cvstrac/chngview?cn=3355 Post a complete self-contained C program demonstrating the problem if you need further help. --- karthikeyan <[EMAIL PROTECTED]> wrote: > What we are doing in short is below (using Sqlite 3.3.13). > > Select id, data from msgTable;// ok - returns 100 > tid = sqlite3_column_int; > data = sqlite3_column_bytes; > // make a copy of the bytes > // update the byte values > update msgTable set data=new data where id=tid; //ok - returns 101 > sqlite3_step// not ok > > Sqlite3_step, crashes because (BtCursor *pCur)->pPage is null in > sqlite3BtreeNext. > > I read somewhere that its possible to update while we are in a select. Is > this possible? > Or are there some other api call, which I need to call before sqlite3_step > is called?. > > Both the statements are prepared using sqlite3_prepare_v2 and values are > assigned using the bind functions. Need a vacation? Get great deals to amazing places on Yahoo! Travel. http://travel.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
