Reached MAX size for compiled-sql statement cache for database

Question

My code is

ContentValues values; 
values = new ContentValues();
        values.put(SQLHelper.EMPLOYEE_LPN, jsObj.getString("lpn"));
db.update(SQLHelper.EMPLOYEE_TABLE, values,
                "EMPLOYEE_LPN ='" + jsObj.getString("lpn") + "'",
                null);

a warning is shown in the Log Cat

08-31 15:19:45.297: WARN/Database(2868): Reached MAX size for compiled-sql statement cache for database /data/data/org.sipdroid.sipua/databases/test.db; i.e., 
NO space for this sql statement in cache: 
SELECT EMPLOYEE_NAME FROM eyemployee WHERE EMPLOYEE_LPN ='1169162'. 
Please change your sql statements to use '?' for bindargs, instead of using actual values

How to resolve it?

do as logger is telling you...Please change your sql statements to use '?' for bindargs instead of using actual values — Rahul, Aug 31 '11 at 10:22
@ Rahul i gave db.update(SQLHelper.EMPLOYEE_TABLE, values, SQLHelper.EMPLOYEE_LPN + "=?", new String[]{jsObj.getString("lpn")}); bt still the warning is shown in the log cat — jennifer, Aug 31 '11 at 10:30
why are you trying to update EMPLOYEE_LPN to X and in your where clause also you are specifying EMPLOYEE_LPN=X..its is like 'update table set x=1 where x=1' — Rahul, Aug 31 '11 at 10:56
@ Rahul could u pls explain with sample code pls...i didn't get u — jennifer, Aug 31 '11 at 11:13
you want to update SQLHelper.EMPLOYEE_LPN to jsObj.getString("lpn")....then there is no point in putting SQLHelper.EMPLOYEE_LPN = jsObj.getString("lpn") in where clause..there is no use of this query..e.g. your query looks like "update users set name='jennifer' where name='jennifer'" — Rahul, Aug 31 '11 at 11:17

Amy B · Accepted Answer · 2011-09-13T16:47:55.813

Look at examples 8-3 and 8-4 here.

Example 8-3. Using the update method

/**
 * Update a job in the database.
 * @param job_id         The job id of the existing job
 * @param employer_id    The employer offering the job
 * @param title          The job title
 * @param description    The job description
 */
public void editJob(long job_id, long employer_id, String title, String description) {
    ContentValues map = new ContentValues();
    map.put("employer_id", employer_id);
    map.put("title", title);
    map.put("description", description);
    String[] whereArgs = new String[]{Long.toString(job_id)};
    try{
        getWritableDatabase().update("jobs", map, "_id=?", whereArgs);
    } catch (SQLException e) {
        Log.e("Error writing new job", e.toString());
    }
}

Here are some of the highlights of the code in Example 8-3:

Example 8-4 shows you how to use the execSQL method.
Example 8-4. Using the execSQL method

/**
 * Update a job in the database.
 * @param job_id         The job id of the existing job
 * @param employer_id    The employer offering the job
 * @param title          The job title
 * @param description    The job description
 */
public void editJob(long job_id, long employer_id, String title, String description) {
    String sql = 
        "UPDATE jobs " +
        "SET employer_id = ?, "+
        " title = ?,  "+
        " description = ? "+
        "WHERE _id = ? ";
    Object[] bindArgs = new Object[]{employer_id, title, description, job_id};
    try{
        getWritableDatabase().execSQL(sql, bindArgs);
    } catch (SQLException e) {
        Log.e("Error writing new job", e.toString());
    }
}

The message is asking you to make parameters use sql variables instead of sql literals.

Each sql query is parsed, plans are generated, and stored in a sql statement cache.

Queries which have the same text are fetched from the cache.

  --One query
SELECT * FROM Customers WHERE Id = @1   (@1 = 3)
SELECT * FROM Customers WHERE Id = @1   (@1 = 4)
SELECT * FROM Customers WHERE Id = @1   (@1 = 5)

Queries which have different text (including literals) cannot be found in the cache and are (uselessly) added to it.

  --Three Queries.
SELECT * FROM Customers WHERE Id = 3
SELECT * FROM Customers WHERE Id = 4
SELECT * FROM Customers WHERE Id = 5

Are you sure 8-4 is a valid choice? The latest API docs for the bindArgs version of execSQL says "Execute a single SQL statement that is NOT a SELECT/INSERT/UPDATE/DELETE." http://bit.ly/1gGhsUV — Dan J, Oct 23 '13 at 22:36

score 2 · Answer 2 · edited Mar 13 '15 at 13:14

I was searching for this today, and came across this doc.

http://ormlite.com/docs/query-builder

This solved my issue. This is the code from the link above

QueryBuilder<Account, String> queryBuilder =
  accountDao.queryBuilder();
Where<Account, String> where = queryBuilder.where();
SelectArg selectArg = new SelectArg();
// define our query as 'name = ?'
where.eq(Account.NAME_FIELD_NAME, selectArg);
// prepare it so it is ready for later query or iterator calls
PreparedQuery<Account> preparedQuery = queryBuilder.prepare();

// later we can set the select argument and issue the query
selectArg.setValue("foo");
List<Account> accounts = accountDao.query(preparedQuery);
// then we can set the select argument to another
// value and re-run the query
selectArg.setValue("bar");
accounts = accountDao.query(preparedQuery);

Reached MAX size for compiled-sql statement cache for database

2 Answers2

Linked