首页 > Sqlite > Sqlite too many SQL variables

Sqlite too many SQL variables

LZ昨天在进行Android2.2 Contacts批量删除操作的时候报 “too many SQL variables”异常导致数据回滚,出现脏数据。
通过google相关资料查找到如下链接,http://www.sqlite.org/limits.html

关键说明信息如下

Maximum Number Of Host Parameters In A Single SQL Statement

A host parameter is a place-holder in an SQL statement that is filled in using one of the sqlite3_bind_XXXX() interfaces. Many SQL programmers are familiar with using a question mark ("?") as a host parameter. SQLite also supports named host parameters prefaced by ":", "$", or "@" and numbered host parameters of the form "?123".

Each host parameter in an SQLite statement is assigned a number. The numbers normally begin with 1 and increase by one with each new parameter. However, when the "?123" form is used, the host parameter number is the number that follows the question mark.

SQLite allocates space to hold all host parameters between 1 and the largest host parameter number used. Hence, an SQL statement that contains a host parameter like ?1000000000 would require gigabytes of storage. This could easily overwhelm the resources of the host machine. To prevent excessive memory allocations, the maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999.

The maximum host parameter number can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_VARIABLE_NUMBER,size) interface.

大意为:单个SQL语句的最大数量的主机参数上限为999,限定上限是为了防止大量的sql操作占用过量的系统资源,引发大规模内存回收甚至会出现内存溢出

解决思路:
1.修改 android-froyo/external/sqlite/dist/sqlite3.c 文件 第207行 SQLITE_MAX_VARIABLE_NUMBER 999 可以把999设置成更大的值
2.把原来一次性大量操作分解成多次,逐一提交。

出于不修改系统默认环境的原则,LZ采用了第二种方法.

  1. 本文目前尚无任何评论.
  1. 本文目前尚无任何 trackbacks 和 pingbacks.
*