What is the limit of SQL variables one can specify in a single execSQL query

android, android-sqlite, sqlite

Solution

The limit is hardcoded in sqlite3.c and is set to 999. Unfortunately it can be changed but only at compile time. Here are the relevant snippets:

/*
** The maximum value of a ?nnn wildcard that the parser will accept.
*/
#ifndef SQLITE_MAX_VARIABLE_NUMBER
# define SQLITE_MAX_VARIABLE_NUMBER 999
#endif


/*
** The datatype ynVar is a signed integer, either 16-bit or 32-bit.
** Usually it is 16-bits.  But if SQLITE_MAX_VARIABLE_NUMBER is greater
** than 32767 we have to make it 32-bit.  16-bit is preferred because
** it uses less memory in the Expr object, which is a big memory user
** in systems with lots of prepared statements.  And few applications
** need more than about 10 or 20 variables.  But some extreme users want
** to have prepared statements with over 32767 variables, and for them
** the option is available (at compile-time).
*/
#if SQLITE_MAX_VARIABLE_NUMBER<=32767
typedef i16 ynVar;
#else
typedef int ynVar;
#endif

Problem

I am trying to improve the speed of my android database inserts. What I am currently doing is generate a string like: ``` SELECT ? as title, ? as musician_id, ? as album_id, ? as genre UNION SELECT ?, ?, ?, ? UNION SELECT ?, ?, ?, ? UNION SELECT ?, ?, ?, ? UNION SELECT ?, ?, ?, ? UNION SELECT ?, ?, ?, ? ``` And then executing it with ``` SQLiteDatabase database = //initialized in some way String insertQuery; // the string of the query above String [] parameters; // the parameters to use in the insertion. database.execSQL(insertQuery.toString(), parameters); ``` I am getting the following error when I try to insert about 2000 rows: ``` Caused by: android.database.sqlite.SQLiteException: too many SQL variables (code 1): , while compiling: INSERT INTO songs (title, musician_id, album_id, genre) SELECT ? as title, ? as musician_id, ? as album_id, ? as genre UNION SELECT ?, ?, ?, ? UNION SELECT ?, ?, ?, ? ``` When I try to insert about 200 rows everything works fine. I suppose it is obvious - I am trying to pass in too many variables in a single `execSQL`. Does anyone know what is the limit so that I can split the rows I insert in appropriate batches?

Original source