Thank you! that did the trick. Its interesting that even though you
can commit a transaction, the cache sync doesn't happen until you
finalize or reset all you statements. I wish this was documented
somewhere better!
Thanks,
Yuriy
On Jun 25, 2013, at 2:19 PM, Richard Hipp <d...@sqlite.org> wrote:
> 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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
