Android: what is wrong with my create table statement?

Question

My onCreate method of the SQLiteOpenHelper contains the following code:

Log.d("mytag", "oncreate is called");
db.execSQL("CREATE TABLE TrainingSession (id INTEGER PRIMARY KEY, session_date TEXT, session_status TEXT)");
Log.d("mytag", "tables were created");

The Logcat output contains only the first Log.d:

oncreate is called

And then the app crashes. What is wrong with my create table statement?

EDIT:

Logcat actually says that the table TrainingSession already exists. However, my onUpgrade method looks like this:

Log.d("mytag", "onupgrade is called");
db.execSQL("DROP TABLE IF EXISTS TrainingSet; DROP TABLE IF EXISTS TrainingSession;");
onCreate(db);

and the Logcat output of that method is as expected, the method does get called:

onupgrade is called

If onUpgrade is called and the tables are deleted, then why does onCreate say that TrainingSession already exists?

"and then the app crashes" -- use Logcat to examine the Java stack trace associated with your crash: https://stackoverflow.com/q/23353173/115145 — CommonsWare, Jan 13 '19 at 14:07
There is nothing wrong with your `CREATE TABLE` statement AFAIK. You may try running that directly against SQLite to convince yourself that it is correct. There must be some other problem in your code. — Tim Biegeleisen, Jan 13 '19 at 14:08
I actually have tested the create statement in my firefox SQLite extension. It works fine. But how come the second output is not generated? I do not know what "use logcat to examine the java stack trace" means, gonna have to google that. — Robert, Jan 13 '19 at 14:11
okay that was quick: it says trainingsession already exists. I will edit my question to enlarge the picture of the code that is running. — Robert, Jan 13 '19 at 14:14

forpas · Accepted Answer · 2019-01-13T15:09:15.767

1

From https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#execSQL(java.lang.String)

execSQL
added in API level 1
public void execSQL (String sql)
Execute a single SQL statement that is NOT a SELECT or any other SQL statement that returns data.
..............................
Parameters sql String:
the SQL statement to be executed.
Multiple statements separated by semicolons are not supported.

So execSql() does not support multiple statements separated with ;
So execute multiple execSql() each for every statement:

public static final String SQL_DELETE_TABLE1 = "DROP TABLE IF EXISTS TrainingSet"
db.execSQL(SQL_DELETE_TABLE1); 
public static final String SQL_DELETE_TABLE2 = "DROP TABLE IF EXISTS TrainingSession"
db.execSQL(SQL_DELETE_TABLE2);

edited Jan 13 '19 at 15:09

answered Jan 13 '19 at 14:34

forpas

160,666
10
38
76

I know that, I am calling getWritableDatabase in the constructor for testing purposes. Your bolded statement is not true though. oncreate is called either if the database does not exist or if your database version has increased (since onupgrade calls oncreate). you can see from the logcat output that oncreate is being called. so please take back your downvote. – Robert Jan 13 '19 at 14:43
I did not downvote. *if you database version has increased* this will trigger `onUpgrade()` – forpas Jan 13 '19 at 14:47
oh I thought it was you, sorry. yes, it triggers onUpgrade. which deletes the tables (or doesn't since I get this error - but I do not know why it doesn't, that is the question here) and then calls onCreate which recreates the database. however in onCreate the app crashes since "trainingsession already exists!". but that table should have been deleted in onupgrade (and onupgrade does get called as you can see from logcat). – Robert Jan 13 '19 at 14:50
oh my god, thank you so much :) I was starting to feel like I'll never solve this and nobody knows what's wrong. that was the cause of the problem, now everything works exactly as expected! I would have never assumed that you cannot pass multiple SQL statements in that string which db.execSQL wants to have. and if so I would have expected an error somewhere, maybe something like "cannot pass multiple statements. statements were dropped". anyway, thank you!! – Robert Jan 13 '19 at 15:31

score 0 · Answer 2 · answered Jan 13 '19 at 14:14

0

If query is okay these things happen quite offen if database structure has changed.

Try to:

Option 1 : Add other sqlite database version.

Option 2 : Delete database and recreate it.

Option 3 : clear app data from phone App info screen.

answered Jan 13 '19 at 14:14

Antonis Radz

3,036
1
16
34

I have added information and code above. The table already exists, but as you can see from my code he should drop the tables before recreating them. How can they still be there? – Robert Jan 13 '19 at 14:24

Android: what is wrong with my create table statement?

2 Answers2