Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
Before I wrote this question, i've searched in all proposed answers, "stackoverflow" shows me but nothing fit my case.
My problem:
I wrote Room migration to copy old data from a table and add it to another table, and i need to specify all the columns in the query. I have something like 130 columns.
database.execSQL("INSERT INTO newTable(col1, ..n) SELECT col1, .... coln FROM oldTable")
When i executed my migration, i got this error:
method exceeds compiler instruction limit: 16602 in androidx.room.RoomOpenHelper$ValidationResult
Thx for your helps.
–
Running a test using 150 columns (151 including the id column) and thus :-
2022-05-11 06:29:50.050 16308-16308/a.a.so72188145javaroommaxcolumns D/INSERTSQL: INSERT INTO newtable (id,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col31,col32,col33,col34,col35,col36,col37,col38,col39,col40,col41,col42,col43,col44,col45,col46,col47,col48,col49,col50,col51,col52,col53,col54,col55,col56,col57,col58,col59,col60,col61,col62,col63,col64,col65,col66,col67,col68,col69,col70,col71,col72,col73,col74,col75,col76,col77,col78,col79,col80,col81,col82,col83,col84,col85,col86,col87,col88,col89,col90,col91,col92,col93,col94,col95,col96,col97,col98,col99,col100,col101,col102,col103,col104,col105,col106,col107,col108,col109,col110,col111,col112,col113,col114,col115,col116,col117,col118,col119,col120,col121,col122,col123,col124,col125,col126,col127,col128,col129,col130,col131,col132,col133,col134,col135,col136,col137,col138,col139,col140,col141,col142,col143,col144,col145,col146,col147,col148,col149,col150) SELECT id,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col31,col32,col33,col34,col35,col36,col37,col38,col39,col40,col41,col42,col43,col44,col45,col46,col47,col48,col49,col50,col51,col52,col53,col54,col55,col56,col57,col58,col59,col60,col61,col62,col63,col64,col65,col66,col67,col68,col69,col70,col71,col72,col73,col74,col75,col76,col77,col78,col79,col80,col81,col82,col83,col84,col85,col86,col87,col88,col89,col90,col91,col92,col93,col94,col95,col96,col97,col98,col99,col100,col101,col102,col103,col104,col105,col106,col107,col108,col109,col110,col111,col112,col113,col114,col115,col116,col117,col118,col119,col120,col121,col122,col123,col124,col125,col126,col127,col128,col129,col130,col131,col132,col133,col134,col135,col136,col137,col138,col139,col140,col141,col142,col143,col144,col145,col146,col147,col148,col149,col150 FROM oldtable;
Worked fine (Room 2.5.0-aplha01 and Android 30 emulator). I suspect that the issue may not be the number of columns but elsewhere in the SQL.
The above SQL was extracted from the log, it was output to the log exactly as was then used (see testing code below).
A work-around IF you have columns that can be null and a sufficient number of them to circumvent the issue. Have an INSERT that inserts a manageable number of columns allowing the other columns to default to null, followed by 1 or more UPDATEs to apply the subsequent values to override the nulls.
Another work-around could be to extract all columns SELECT * .... into a Cursor and then build a ContentValues from the Cursor (on a per row basis) and use the SupportSQliteDatabase's insert method. Something along the lines of:-
Cursor csr = database.query("SELECT * FROM oldtable");
database.beginTransaction();
ContentValues cv = new ContentValues();
while (csr.moveToNext()) {
cv.clear();
for (int i=0; i < csr.getColumnCount(); i++) {
if (!csr.getColumnName(i).equals("col133")) { //<<<<< skip col133 as an example
cv.put(csr.getColumnName(i),csr.getString(i));
database.insert("newTable", SQLiteDatabase.CONFLICT_IGNORE,cv);
database.setTransactionSuccessful();
database.endTransaction();
Full code used for testing
Note that the only change between the 2 is the version number. So initially the code is run (after uninstalling the App) with version 1 (so no Migration). To test it's just a matter, of then changing the version to 2.
The @Entity annotated class OldTable:-
@Entity
class OldTable {
@PrimaryKey
Long id=null;
String col1;
String col2;
String col3;
.... (i.e. all missing numbers)
String col147;
String col148;
String col149;
String col150;
The @Dao annotated interface (not actually used) AllDao
interface AllDao {
@Insert(onConflict = OnConflictStrategy.IGNORE)
long insert(OldTable oldTable);
The @Database annotated class TheDatabase :-
@Database(entities = {OldTable.class},version = MainActivity.DATABASE_VERSION,exportSchema = false)
abstract class TheDatabase extends RoomDatabase {
abstract AllDao getAllDao();
private static volatile TheDatabase INSTANCE = null;
static TheDatabase getINSTANCE(Context context) {
if (INSTANCE == null) {
INSTANCE = Room.databaseBuilder(context,TheDatabase.class,MainActivity.DATABASE_NAME)
.allowMainThreadQueries()
.addMigrations(MIGRATION_1_2)
.build();
return INSTANCE;
static Migration MIGRATION_1_2 = new Migration(1,2) {
@Override
public void migrate(@NonNull SupportSQLiteDatabase database) {
StringBuilder sb = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
ArrayList<String> columns = new ArrayList<>();
for (int i=0; i < MainActivity.column_count;i++) {
columns.add("col" + (i+1));
if (i>0) {
sb.append(",");
sb2.append(",");
sb.append(columns.get(i));
sb2.append(columns.get(i)).append(" TEXT");
String columnsAsCSV = sb.toString();
String columnDefs = sb2.toString();
database.execSQL("DROP TABLE IF EXISTS newtable");
database.execSQL("CREATE TABLE IF NOT EXISTS newtable (id INTEGER PRIMARY KEY," + columnDefs + ");");
String insertSQL = "INSERT INTO newtable (id,"+columnsAsCSV+") SELECT id,"+columnsAsCSV+" FROM oldtable;";
Log.d("INSERTSQL",insertSQL);
database.execSQL(insertSQL);
And finally putting it alltogether MainActivity :-
public class MainActivity extends AppCompatActivity {
public static final String DATABASE_NAME = "the_database.db";
public static final int DATABASE_VERSION = 2;
public static final int column_count = 150;
private ArrayList<String> column_names = new ArrayList<>();
private String allColumnsAsCSV = "";
TheDatabase db;
AllDao dao;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
BuildColumns();
db = TheDatabase.getINSTANCE(this);
dao = db.getAllDao();
SupportSQLiteDatabase supdb = db.getOpenHelper().getWritableDatabase();
StringBuilder sb = new StringBuilder();
for (int i=0;i < 100; i++) {
sb = new StringBuilder();
sb.append("INSERT INTO oldtable (").append(allColumnsAsCSV).append(") VALUES(");
for(int ii=0;ii<column_count;ii++) {
if (ii > 0) {
sb.append(",");
sb.append("'").append(column_names.get(ii)).append(i).append(ii).append("'");
sb.append(")");
supdb.execSQL(sb.toString());
void BuildColumns() {
column_names.clear();
allColumnsAsCSV = "";
StringBuilder sb = new StringBuilder();
for (int i=0; i < column_count; i++) {
column_names.add("col" + (i+1));
if (i>0) {
sb.append(",");
sb.append(column_names.get(i));
allColumnsAsCSV = sb.toString();
–
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
