Omar Omar - 2 days ago 4
Android Question

Caused By : SQL(query) error or missing database

Every time I click the button to add items to the table the app crashes and I get this error, logcat:

11-30 21:46:00.822 13943-13943/com.example.user.timetable_test E/SQLiteLog: (1) near "TABLESunday": syntax error
11-30 21:46:00.827 13943-13943/com.example.user.timetable_test D/AndroidRuntime: Shutting down VM
11-30 21:46:00.827 13943-13943/com.example.user.timetable_test E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.example.user.timetable_test, PID: 13943
android.database.sqlite.SQLiteException: near "TABLESunday": syntax error (code 1): , while compiling: CREATE TABLESunday(_session INTEGER PRIMARY KEY AUTOINCREMENT,_name TEXT,_length INTEGER);
#################################################################
Error Code : 1 (SQLITE_ERROR)
Caused By : SQL(query) error or missing database.
(near "TABLESunday": syntax error (code 1): , while compiling: CREATE TABLESunday(_session INTEGER PRIMARY KEY AUTOINCREMENT,_name TEXT,_length INTEGER);)
#################################################################
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1058)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:623)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1812)
at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1743)
at com.example.user.timetable_test.setup.TableDBHandler.onCreate(TableDBHandler.java:43)
at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:251)
at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:163)
at com.example.user.timetable_test.setup.TableDBHandler.addEntry(TableDBHandler.java:63)
at com.example.user.timetable_test.setup.SetTable$PlaceholderFragment$1.onClick(SetTable.java:206)
at android.view.View.performClick(View.java:5697)
at android.widget.TextView.performClick(TextView.java:10826)
at android.view.View$PerformClick.run(View.java:22526)
at android.os.Handler.handleCallback(Handler.java:739)
at android.os.Handler.dispatchMessage(Handler.java:95)
at android.os.Looper.loop(Looper.java:158)
at android.app.ActivityThread.main(ActivityThread.java:7225)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1230)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1120)


SQLitDatabase class:

package com.example.user.timetable_test.setup;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import com.example.user.timetable_test.MiscData;


public class TableDBHandler extends SQLiteOpenHelper{

private MiscData data = MiscData.getInstance();

private static int DATABASE_VERSION = 1;
private static String DATABASE_NAME = "Timetable.db";
private static String[] TABLE_DAY = new String[MiscData.getInstance().getDays()];
private static String COLUMN_SESSION_NUM = "_session";
private static String COLUMN_NAME = "_name";
private static String COLUMN_LENGTH = "_length";


public TableDBHandler(Context context, SQLiteDatabase.CursorFactory factory){

super(context, DATABASE_NAME, factory, DATABASE_VERSION);
}


@Override
public void onCreate(SQLiteDatabase sqLiteDatabase){

for(int i = 0; i < data.getDays(); i++){

TABLE_DAY[i] = data.getDay(i + data.getFirstDay());

}

for(int i = 0; i < data.getDays(); i++){
String query = "CREATE TABLE" + TABLE_DAY[i] + "(" + COLUMN_SESSION_NUM +
" INTEGER PRIMARY KEY AUTOINCREMENT " + COLUMN_NAME + " TEXT " +
COLUMN_LENGTH + " INTEGER " + ");";

sqLiteDatabase.execSQL(query);
}
}


@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1){

sqLiteDatabase.execSQL("DROP TABLE EXISTS" + TABLE_DAY);
onCreate(sqLiteDatabase);

}


public void addEntry(int day, String name, int length){

ContentValues values = new ContentValues();
values.put(COLUMN_NAME, name);
values.put(COLUMN_LENGTH, length);

SQLiteDatabase db = getWritableDatabase();
db.insert(TABLE_DAY[day], null, values);
db.close();

}


//get Session length
public int getSessionLength(int day, int session){

int length;
SQLiteDatabase db = getReadableDatabase();
String query =
"SELECT" + COLUMN_LENGTH + "FROM" + TABLE_DAY[day] + "WHERE" + COLUMN_SESSION_NUM +
"=\"" + session + "\";";

Cursor c = db.rawQuery(query, null);

length = c.getInt(c.getColumnIndex(COLUMN_LENGTH));

return length;

}


//get Session name
public String getSessionName(int day, int session){

String name;
SQLiteDatabase db = getReadableDatabase();
String query = "SELECT" + COLUMN_NAME + "FROM" + TABLE_DAY[day] +
"WHERE" + COLUMN_SESSION_NUM + "=\"" + session + "=\";";

Cursor c = db.rawQuery(query, null);

name = c.getString(c.getColumnIndex(COLUMN_NAME));

return name;
}

}


In this code I try to make an array of tables initialized at run-time, this might be where the error comes from or I might be just derping.

Where I add an entry:

tableDBHandler.addEntry(page , sessionSpinners[i].toString(), Integer.parseInt(lengthText[i].getText().toString()));


I have tried changing the data type of
COLUMN_LENGTH
from
TEXT
to
INTEGER
, but this didn't work.

Any ideas how to fix this?

Answer

The error is into the create of table "TABLESunday": syntax error. There is missing a space on the syntax on your sql code. Try this:

String query = "CREATE TABLE " + TABLE_DAY[i] + "(" + COLUMN_SESSION_NUM +
                       " INTEGER PRIMARY KEY AUTOINCREMENT " + COLUMN_NAME + " TEXT " +
                       COLUMN_LENGTH + " INTEGER " + ");";
Comments