On Tue, Jun 25, 2013 at 2:06 PM, Yuriy Stelmakh <yuriy...@gmail.com> wrote:
> Hi all, please help: very strange issue that should not be happening:
>
>
>
> 1. Have two connections to same database: _db1 and _db2.
>
> 2. Create table in _db1
>
> 3. Run count * from _db2 -> returns 0
>
> 4. Insert 1 row using _db1 –OK
>
> 5. Run select * from _db2 -> 0 rows return
>
>
>
> Weird thing is that if I remove step 3, then step 5 returns a row.
>
> I’m guessing there must be a bug in SQLite with cache sync, or I’m missing
> something obvious.
>
Without looking at your code, I'm guessing that you are not finalizing the
statement in step 3. It is therefore holding the transaction open,
preventing it from being committed, and thus preventing step 5 from seeing
the change.
The fact that changes are not visible to outside connections until the
change is committed is an important and very powerful feature of the SQL
language, and of SQLite, not a bug.
Looking at your code now - yes, my suspicion seems to be confirmed. I've
marked the spot below where I think you need the sqlite3_finalize().
>
>
>
> Please help:
>
> sqlite3* _db1;
>
> sqlite3* _db2;
>
> sqlite3_open("test.sqlite", &_db1);
>
> sqlite3_open("test.sqlite", &_db2);
>
>
>
> //create table
>
> char* errorMessage;
>
> sqlite3_exec(_db1, "CREATE TABLE test (column1 INTEGER)", NULL,
> NULL,
> &errorMessage);
>
>
>
> //get count of items from db2 -> should return 0
>
> string countStatement = "SELECT COUNT(*) FROM test";
>
>
>
> sqlite3_stmt* stmt;
>
> //prepare statement
>
> if(sqlite3_prepare_v2(_db2, countStatement.c_str(), -1, &stmt,
> NULL)
> != SQLITE_OK){
>
> GHAssertTrue(false, @"Should have created prepared statement:
> %s", sqlite3_errmsg(_db1));
>
> }
>
>
>
> int rc = sqlite3_step(stmt);
>
> if(rc == SQLITE_ROW){
>
> //get the count
>
> if(sqlite3_column_count(stmt) > 0){
>
> int count = sqlite3_column_int(stmt, 0);
>
> GHAssertTrue(count == 0, @"Count should be zero");
>
> }else{
>
> GHAssertTrue(false, @"Should have returned a row");
>
> }
>
> }else{
>
> GHAssertTrue(false, @"Should have returned a row");
>
> }
>
Insert "sqlite3_finalize(stmt);" here.
>
>
> //insert one row into table using db1
>
> sqlite3_exec(_db1, "INSERT into test(column1) values (4)", NULL,
> NULL, &errorMessage);
>
>
>
> //read row using db2
>
> //get count of items from db2 -> should return 0
>
> string selectStatement = "SELECT * FROM test";
>
>
>
> //prepare statement
>
> if(sqlite3_prepare_v2(_db2, selectStatement.c_str(), -1, &stmt,
> NULL)
> != SQLITE_OK){
>
> GHAssertTrue(false, @"Should have created prepared statement:
> %s", sqlite3_errmsg(_db1));
>
> }
>
>
>
> rc = sqlite3_step(stmt);
>
> if(rc == SQLITE_ROW){
>
> //get the count
>
> if(sqlite3_column_count(stmt) > 0){
>
> //good
>
> }else{
>
> //bad
>
> GHAssertTrue(false, @"Should have returned a row");
>
> }
>
> }else{
>
> //bad
>
> GHAssertTrue(false, @"Should have returned a row");
>
> }
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
