CODEWITHSUNDEEP
androidsqlitetemporary
Marco S.
Marco S.

Reputation: 86

Android sqlite can't create TEMP table

I'd like to create and use a temporary table in a android sqlite database.
SQL statement is executed without any exception but no table is created!
Running my app with the sample code below, I got no tables:

START EDIT

public class DatabaseHelper extends SQLiteOpenHelper {
public static final String DB_NAME = "mydb.db";
private static DatabaseHelper instance;

public static synchronized DatabaseHelper getHelper(Context context){
    if (instance == null)
        instance = new DatabaseHelper(context);
    return instance;
}

private DatabaseHelper(Context c) {
    super(c,DB_NAME,null,1);
}

@Override
public void onConfigure(SQLiteDatabase db) {
    super.onConfigure(db);
    db.enableWriteAheadLogging ();
    db.setLocale(Locale.getDefault());
}

@Override
public void onCreate(SQLiteDatabase db) {
    // lots of table creation
    db.execSQL("CREATE TABLE my_table (id INTEGER PRIMARY KEY, value TEXT)");
    // more tables ....
}}
...... sample 
DatabaseHelper databaseHelper = DatabaseHelper.getHelper(this);

END EDIT

SQLiteDatabase db = databaseHelper.getWritableDatabase();
db.execSQL("create TEMP table my_temp_table (id integer primary key, value text)"); // no exceptions

Cursor cursor = db.query("SQLITE_TEMP_MASTER", null, null, null, null, null, null);

while( cur.moveToNext()) {
    // no lines (no temporary tables)
}

If I create a temporary table from a select statement and then query (within the same db connection) the created table, I get "no such table .... exception"!

db.execSQL("create TEMP table my_temp_table as select * from my_table");
Cursor cursor = db.query("temp.my_temp_table", null, null, null, null, null, null);
^^^ no such table: temp.my_temp_table(code 1): , while compiling: SELECT * FROM temp.my_temp_table

What is confusing me is that ... the same sql code works perfectly within SQLiteStudio i.e. outside the app and outside the android device. Maybe I forgot to enable something or ... I need specific device permission?

Upvotes: 2

Views: 1490

Answers (2)

Yuichi Araki
Yuichi Araki

Reputation: 3458

When you enable Write-Ahead Logging with db.enableWriteAheadLogging(), SQLiteDatabase automatically multiplexes database connections. The connection used for db.query(...) is not the same one used for db.execSQL("CREATE TEMP TABLE ...").

You can wrap db.query(...) in a transaction and enforce SQLiteDatabase to use the same connection as execSQL.

db.beginTransaction();
Cursor cursor = db.query("temp.my_temp_table", null, null, null, null, null, null);
try {
    while (cursor.moveToNext()) {
        // ...
    }
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
}

Upvotes: 1

mksteve
mksteve

Reputation: 13073

The result of create temp table x is a table x not temp.x

Change your code to use my_temp_table

Upvotes: 0

Related Questions