Re: [sqlite] sqlite3_prepare() locking mode

[Dennis Cote]

[EMAIL PROTECTED] wrote:

Hi All,
Does anybody know how to use sqlite3_prepare() with read only locking table?
if i try to insert, delete or update on a table that is afected by 
sqlite3_prepare sqlite returns an SQLITE_LOCKED error.
i can not call sqlite3_reset, becuase the modification sentence is inside the 
sqlite3_prepare statement loop.
Please see the code below (i have simplified so some errors are not taking into 
consideration and the example is quite silly an unreal, but points out quite 
well the problem).
sqlite3* db;
char *err;
struct sqlite3_stmt* myquery;
int res = 0;
char *MySQL1 = "select * from TABLE1 where order > 1000";
if (sqlite3_open("\\example.db", &db)) {
 MessageBox((CString)sqlite3_errmsg(db),_T("Error"), MB_OK);
 return;
}
if (sqlite3_prepare(db, MiSQL1, strlen(MySQL1), &myquery, NULL)) {
 MessageBox((CString)sqlite3_errmsg(db),_T("Error"), MB_OK);
 sqlite3_reset(myquery);
 sqlite3_close(db);
 return;
}
CString szDato;
if (sqlite3_step(myquery) == SQLITE_ROW) {
 szDato = (CString)sqlite3_column_text(myquery, 3);
 if (szdato == 'HI') {
  //THIS ONE IS GOING TO FAIL BECAUSE FILE LOCKING MODE
----------------------> res = sqlite3_exec( db, "insert into TABLE1 values 
('VALUE1', 'TEST1', 1);" , 0, 0, &err );
 }
 while((rc = sqlite3_step(miquery)) == SQLITE_ROW) {
  nCurrentRecord  ;
 }
}
else 
 MessageBox(_T("No records on DB"),_T("Warning"), MB_OK);
sqlite3_reset(myquery);
sqlite3_finalize(myquery);
sqlite3_close(db);
Thnak you in advance
---Mensaje original---
¿Quieres montarte una película? ¡Elige el guión, Invéntate los diálogos y mándala a tus amigos! http://ad.doubleclick.net/clk;28265024;7829128;n?http://entretenimiento.wanadoo.es/bombaytv/index.php 

You can't write to a table at the same time as you are reading it.

The work around is to create a copy of the result rows from the read 
operation in a temporary table, and then use a second read to scan 
through the temporary table while updating the original table.

   create temp table temp_table1 as select * from table1 where order > 
1000;

Now you can  change your outer read loop to read from temp_table1 
instead of table1.

   char *MySQL1 = "select * from temp_table1 where order < 1000";

Inside this loop you can safely  update table1 as you were before.

HTH
Dennis Cote