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

Reply via email to