Hi, I am getting a problem if I am modifying cache size. This can be reproduced by the following steps:- We need two connections to reprodce this.
Say the database name is: "test.db" *"test.db" SHOULD NOT BE EXISTING ALREADY, WE HAVE TO CREATE EACH TIME WE WANT TO GET THE PROBLEM.* 1. Create a connection to test.db. Here "test.db" should be created physically. If it is existing please DELETE it. We will call this connection as First Connection 2. Set PRAGMA cache_size = XXXX for this connection 3. Create another connection to test.db. This is called the Second Connection. 4. Creat a a table (say "student_master") in this second connection. 5. Insert some data in this table through second connection. 6. Then try to select from the same table with the first connection. I am getting error while preparing the SELECT statement. And the error is no such table: "student_master". So I think the changes we are making in the second connection are not visible. But I do not understand, why does it work if I am not executing that CACHE resize query! I am pasting the code for linux here. It can be reproduced in windows too. There is one macro in my code:- #define SET_CACHE_SQLITE 1 You can comment it, if you dont want the cache resize code, and then it will work properly! Is it my problem or a SQLite problem? THE CODE:- /******************************************************/ #include<stdlib.h> #include <stdio.h> #include<pthread.h> #include "sqlite3.h" // Define SET_CACHE_SQLITE if you want the cache to be enabled, otherwise comment it #define SET_CACHE_SQLITE 1 int func_reproduce( ) { sqlite3 *db1; // Database handle for first connection sqlite3 *db2; // Database handle for second connection sqlite3_stmt * pstmt1 = NULL; sqlite3_stmt * pstmt2 = NULL; int ret = 1; void **unused; // Open the first connection int ret1 = sqlite3_open( "test.db", &db1 ); if(ret1 != SQLITE_OK) { printf("OPEN error - db\n", sqlite3_errmsg(db1)); exit(EXIT_FAILURE); } printf("Opened db1\n"); #ifdef SET_CACHE_SQLITE // Modify Cache? // Modify the cache size in first connection char * query_cache = "PRAGMA cache_size = 5000"; //Prepare Cache query ret = sqlite3_prepare(db1, query_cache, (int)strlen(query_cache)*sizeof(char), &pstmt1, (const char **)&unused); if(ret != SQLITE_OK) { printf("Prepare Error - CACHE: (%s)\n", sqlite3_errmsg(db1)); exit(EXIT_FAILURE); } printf("Prepared: CACHE\n"); //Step Cache query ret = sqlite3_step(pstmt1); if(ret != SQLITE_DONE) { printf("Step Error - CACHE: (%s)\n", sqlite3_errmsg(db1)); exit(EXIT_FAILURE); } printf("Stepped CACHE\n"); sqlite3_finalize(pstmt1); printf("Finalized cache\n"); #endif //SET_CACHE_SQLITE //Open the second connection ret1 = sqlite3_open( "test.db", &db2 ); if(ret1 != SQLITE_OK) { printf("OPEN error - db2\n", sqlite3_errmsg(db2)); exit(EXIT_FAILURE); } printf("Opened db2\n"); char * query_create = "CREATE TABLE IF NOT EXISTS [student_master] ( \ [student_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, \ [student_name] VARCHAR(50))"; //Prepare CREATE TABLE query ret = sqlite3_prepare(db2, query_create, (int)strlen(query_create)*sizeof(char), &pstmt2, (const char **)&unused); if(ret != SQLITE_OK) { printf("Prepare Error - CREATE: (%s)\n", sqlite3_errmsg(db2)); exit(EXIT_FAILURE); } printf("Prepared for CREATE\n"); //Step CREATE TABLE query ret = sqlite3_step(pstmt2); if(ret != SQLITE_DONE) { if(ret == SQLITE_ERROR) sqlite3_finalize(pstmt2); printf("Step Error - CREATE: (%s)\n", sqlite3_errmsg(db2)); exit(EXIT_FAILURE); } printf("Stepped for CREATE\n"); sqlite3_finalize(pstmt2); printf("Finalized CREATE\n"); // Insert a row with the second connection char *query_insert = "INSERT INTO [student_master]([student_name]) values ('CrazyBoy')"; // Prepare for INSERT query ret = sqlite3_prepare(db2, query_insert, (int)strlen(query_insert)*sizeof(char), &pstmt2, (const char **)&unused); if(ret != SQLITE_OK) { printf("Prepare Error - INSERT: (%s)\n", sqlite3_errmsg(db2)); exit(EXIT_FAILURE); } printf("Prepared for INSERT\n"); // Step INSERT query ret = sqlite3_step(pstmt2); if(ret != SQLITE_DONE) { if(ret == SQLITE_ERROR) sqlite3_finalize(pstmt2); printf("Step Error - INSERT: (%s)\n", sqlite3_errmsg(db2)); exit(EXIT_FAILURE); } printf("Stepped for INSERT\n"); sqlite3_finalize(pstmt2); printf("Finalized INSERT\n"); // Now try Selecting from the first connection char * query_select = "SELECT MAX(student_id) FROM student_master"; // Prepare for SELECT ret = sqlite3_prepare(db1, query_select, (int)strlen(query_select)*sizeof(char), &pstmt1, (const char **)&unused); if(ret != SQLITE_OK) { printf("Prepare Error - SELECT: (%s)\n", sqlite3_errmsg(db1)); exit(EXIT_FAILURE); } printf("prepared for select\n"); // Step SELECT ret = sqlite3_step(pstmt1); if(ret != SQLITE_DONE && ret != SQLITE_ROW) { if(ret == SQLITE_ERROR) { sqlite3_reset(pstmt1); printf("Step Error - SELECT: (%s)\n", sqlite3_errmsg(db1)); exit(EXIT_FAILURE); } } while(ret == SQLITE_ROW) { int data = sqlite3_column_int(pstmt1, 0); printf("Data: %3d\n", data); ret = sqlite3_step(pstmt1); } printf("Stepped for SELECT\n"); sqlite3_finalize(pstmt1); printf("Finalized SELECT\n"); return 0; } int main() { return func_reproduce(); } /***********************************/ -- Sabyasachi