Hello. It looks like I found a bug in sqlite3_step. Here is the sample code.
#include <windows.h> #include <stdio.h> #include <assert.h> #include "sqlite3.h" #ifdef _DEBUG #define verify(f) assert(f) #else #define verify(f) ((void)(f)) #endif char const * const fname = "bug.db3"; int cb(void*, int, char**, char**) { // If here, then got kav=-1 for uid=2 printf("ok"); return 0; } int main() { sqlite3* db = 0; char* err; DeleteFileA(fname); verify(sqlite3_open(fname, &db) == SQLITE_OK); verify(sqlite3_exec(db, "drop table if exists mails;", 0, 0, &err) == SQLITE_OK); verify(sqlite3_exec(db, "drop table if exists plugins;", 0, 0, &err) == SQLITE_OK); verify(sqlite3_exec(db , "create table if not exists mails (" "uid integer not null primary key unique" ", kav integer not null default 0" ");" , 0, 0, &err) == SQLITE_OK); verify(sqlite3_exec(db , "create table plugins (" "uid integer not null" ", plugin varchar(32) not null" ", result varchar(32) not null" ", primary key (uid, plugin));" , 0, 0, &err) == SQLITE_OK); verify(sqlite3_exec(db, "insert into mails (uid) values (1);", 0, 0, &err) == SQLITE_OK); verify(sqlite3_exec(db, "insert into mails (uid) values (2);", 0, 0, &err) == SQLITE_OK); verify(sqlite3_exec(db, "insert into plugins (uid, plugin, result) values (1, 'kav', 'clean');", 0, 0, &err) == SQLITE_OK); verify(sqlite3_exec(db, "insert into plugins (uid, plugin, result) values (2, 'kav', 'infected');", 0, 0, &err) == SQLITE_OK); sqlite3_stmt* stmt = 0; #if 0 // Work's correctly verify(sqlite3_prepare_v2(db, "create trigger updater update of result on plugins" " begin" " update mails set kav=case old.result when 'infected' then -1 else 0 end where uid=old.uid;" " end;" , -1, &stmt, 0) == SQLITE_OK); // In this case I get kav=-1 for uid=2 in mails table #else int idx = 0; // case 1: exception //verify(sqlite3_prepare_v2(db, "create trigger updater update of result on plugins" // " begin" // " update mails set kav=case old.result when 'infected' then ? else 0 end where uid=old.uid;" // " end;" // , -1, &stmt, 0) == SQLITE_OK); //verify(sqlite3_bind_int(stmt, ++idx, -1) == SQLITE_OK); // case 2: exception //verify(sqlite3_prepare_v2(db, "create trigger updater update of result on plugins" // " begin" // " update mails set kav=case old.result when 'infected' then -1 else ? end where uid=old.uid;" // " end;" // , -1, &stmt, 0) == SQLITE_OK); //verify(sqlite3_bind_int(stmt, ++idx, 0) == SQLITE_OK); // case 3: nothing happens //verify(sqlite3_prepare_v2(db, "create trigger updater update of result on plugins" // " begin" // " update mails set kav=case old.result when ? then -1 else 0 end where uid=old.uid;" // " end;" // , -1, &stmt, 0) == SQLITE_OK); //verify(sqlite3_bind_text(stmt, ++idx, "infected", -1, SQLITE_STATIC) == SQLITE_OK); // case 4: nothing happens verify(sqlite3_prepare_v2(db, "create trigger updater update of result on plugins" " begin" " update mails set kav=case old.result when ? then ? else 0 end where uid=old.uid;" " end;" , -1, &stmt, 0) == SQLITE_OK); verify(sqlite3_bind_text(stmt, ++idx, "infected", -1, SQLITE_STATIC) == SQLITE_OK); verify(sqlite3_bind_int(stmt, ++idx, -1) == SQLITE_OK); // In this case I get kav=0 for uid=2 in mails table #endif verify(sqlite3_step(stmt) == SQLITE_DONE); verify(sqlite3_finalize(stmt) == SQLITE_OK); verify(sqlite3_exec(db, "update plugins set result=result;", 0, 0, &err) == SQLITE_OK); verify(sqlite3_exec(db, "select kav from mails where kav=-1;", cb, 0, &err) == SQLITE_OK); verify(sqlite3_close(db) == SQLITE_OK); return 0; } -- Best regards, Unsupported mailto:unsuppor...@mail.ru _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users