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
