I am having an issue with my database that I can't figure out. I'm getting an android.database.sqlite.SQLiteException: no such column. I'm trying to link two tables together, but I'm not using a foreign key. Here is my database, datasource and the activity that is generating the error:
Database:
package com.mikero.termtracker;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.util.ArrayList;
import java.util.List;
public class DBOpenHelper extends SQLiteOpenHelper {
//Constants for DB
private static final String DATABASE_NAME = "termtracker";
private static final int DATABASE_VERSION = 1;
//Constants for terms table
public static final String KEY_ID = "termId";
public static final String TERMS_TABLE = "terms";
public static final String NAME_OF_TERM = "termsName";
public static final String TERM_START_DATE = "termStartDate";
public static final String TERM_END_DATE = "termEndDate";
//Constants for courses table
public static final String COURSE_TABLE = "course";
public static final String COURSE_ID = "courseId";
public static final String COURSE_NAME = "courseName";
public static final String COURSE_START = "courseStart";
public static final String COURSE_END = "courseEnd";
public static final String COURSE_STATUS = "courseStatus";
public static final String COURSE_MENTOR = "courseMentor";
//constants for assessment table
//constants for notes table
//Create terms table
private static final String CREATE_TABLE_TERMS = "CREATE TABLE "
+ TERMS_TABLE + "(" + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+ NAME_OF_TERM + " TEXT," + TERM_START_DATE + " TEXT,"
+ TERM_END_DATE + " TEXT" + ")";
//Create course table
private static final String CREATE_TABLE_COURSE = "CREATE TABLE "
+ COURSE_TABLE + " (" + COURSE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+ COURSE_NAME + " TEXT," + COURSE_START + " TEXT,"
+ COURSE_END + " TEXT," + COURSE_STATUS + " TEXT," + COURSE_MENTOR + " TEXT"
+ KEY_ID +"INTEGER FOREIGN KEY" + ")";
public DBOpenHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
//create all 4 tables
db.execSQL(CREATE_TABLE_TERMS);
db.execSQL(CREATE_TABLE_COURSE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//upgrade all 4 tables
db.execSQL("DROP TABLE IF EXISTS " + TERMS_TABLE);
db.execSQL("DROP TABLE IF EXISTS " + COURSE_TABLE);
onCreate(db);
}
}
DataSource:
package com.mikero.termtracker;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import java.util.ArrayList;
import java.util.List;
public class DataSource {
//Fields
private SQLiteDatabase db;
private DBOpenHelper helper;
private String[] allTermColums = {DBOpenHelper.KEY_ID, DBOpenHelper.NAME_OF_TERM,
DBOpenHelper.TERM_START_DATE, DBOpenHelper.TERM_END_DATE};
private String[] allCourseColums = {DBOpenHelper.COURSE_ID, DBOpenHelper.COURSE_NAME,
DBOpenHelper.COURSE_START, DBOpenHelper.COURSE_END, DBOpenHelper.COURSE_STATUS,
DBOpenHelper.COURSE_MENTOR, DBOpenHelper.KEY_ID};
public DataSource(Context context){
helper = new DBOpenHelper(context);
}
public void open() throws SQLiteException{
db = helper.getWritableDatabase();
}
public void close(){
helper.close();
}
//Create Term
public Term createTerm(Term term){
ContentValues cv = new ContentValues();
cv.put(DBOpenHelper.NAME_OF_TERM, term.getTermsName());
cv.put(DBOpenHelper.TERM_START_DATE, term.getTermStartDate());
cv.put(DBOpenHelper.TERM_END_DATE, term.getTermEndDate());
long insertId = db.insert(DBOpenHelper.TERMS_TABLE, null, cv);
term.setId(insertId);
return term;
}
//Create Course
public Course createCourse(Course course){
ContentValues cv = new ContentValues();
cv.put(DBOpenHelper.COURSE_NAME, course.getCourseName());
cv.put(DBOpenHelper.COURSE_START, course.getCourseStart());
cv.put(DBOpenHelper.COURSE_END, course.getCourseEnd());
cv.put(DBOpenHelper.COURSE_STATUS, course.getCourseStatus());
cv.put(DBOpenHelper.COURSE_MENTOR, course.getCourseMentor());
cv.put(DBOpenHelper.KEY_ID, course.getTermId());
long insertId = db.insert(DBOpenHelper.COURSE_TABLE, null, cv);
course.setId(insertId);
return course;
}
//Update term
public void updateTerm(Term term){
long id = term.getId();
ContentValues cv = new ContentValues();
cv.put(DBOpenHelper.NAME_OF_TERM, term.getTermsName());
cv.put(DBOpenHelper.TERM_START_DATE, term.getTermStartDate());
cv.put(DBOpenHelper.TERM_END_DATE, term.getTermEndDate());
db.update(DBOpenHelper.TERMS_TABLE, cv, DBOpenHelper.KEY_ID + " = " + id, null);
}
//Update Course
public void updateCourse(Course course){
long id = course.getId();
ContentValues cv = new ContentValues();
cv.put(DBOpenHelper.COURSE_NAME, course.getCourseName());
cv.put(DBOpenHelper.COURSE_START, course.getCourseStart());
cv.put(DBOpenHelper.COURSE_END, course.getCourseEnd());
cv.put(DBOpenHelper.COURSE_STATUS, course.getCourseStatus());
cv.put(DBOpenHelper.COURSE_MENTOR, course.getCourseMentor());
cv.put(DBOpenHelper.KEY_ID, course.getTermId());
db.update(DBOpenHelper.COURSE_TABLE, cv, DBOpenHelper.COURSE_ID + " = " + id, null);
}
//CursorToTerm method
private Term cursorToTerm(Cursor cursor){
Term term = new Term();
term.setId(cursor.getLong(0));
term.setTermsName(cursor.getString(1));
term.setTermStartDate(cursor.getString(2));
term.setTermEndDate(cursor.getString(3));
return term;
}
//CursorToCourse
private Course cursorToCourse(Cursor cursor){
Course course = new Course();
course.setId(cursor.getLong(0));
course.setCourseName(cursor.getString(1));
course.setCourseStart(cursor.getString(2));
course.setCourseEnd(cursor.getString(3));
course.setCourseStatus(cursor.getString(4));
course.setCourseMentor(cursor.getString(5));
return course;
}
//Getting all terms
public List<Term> getAllTerms(){
List<Term> termList = new ArrayList<>();
Cursor cursor = db.query(DBOpenHelper.TERMS_TABLE, allTermColums,
null, null, null, null, null);
cursor.moveToFirst();
while(!cursor.isAfterLast()) {
Term term = cursorToTerm(cursor);
termList.add(term);
cursor.moveToNext();
}
cursor.close();
return termList;
}
//Getting all courses
public List<Course> getAllCourses(){
List<Course> courseList = new ArrayList<>();
Cursor cursor = db.query(DBOpenHelper.COURSE_TABLE, allCourseColums,
null, null, null, null, null);
cursor.moveToFirst();
while(!cursor.isAfterLast()){
Course course = cursorToCourse(cursor);
courseList.add(course);
cursor.moveToNext();
}
cursor.close();
return courseList;
}
//Getting and creating a single term
public Term getSingleTermById(long id){
Cursor cursor = db.query(DBOpenHelper.TERMS_TABLE, allTermColums,
DBOpenHelper.KEY_ID + " = ?",
new String[] {String.valueOf(id)}, null, null, null);
if (cursor != null){
cursor.moveToFirst();
}
Term term = cursorToTerm(cursor);
return term;
}
//Getting and creating a single course
public Course getSingleCourseById(long id){
Cursor cursor = db.query(DBOpenHelper.COURSE_TABLE, allCourseColums,
DBOpenHelper.COURSE_ID + " = ?",
new String[] {String.valueOf(id)}, null, null, null);
if (cursor != null){
cursor.moveToFirst();
}
Course course = cursorToCourse(cursor);
return course;
}
//Delete single term
public void deleteTerm(Term term){
long id = term.getId();
db.delete(DBOpenHelper.TERMS_TABLE, DBOpenHelper.KEY_ID + " = " + id, null);
}
//Delete single course
public void deleteCourse(Course course){
long id = course.getId();
db.delete(DBOpenHelper.COURSE_TABLE, DBOpenHelper.COURSE_ID + " = " + id, null);
}
//Delete single term by id
public void deleteTermById(long id){
db.delete(DBOpenHelper.TERMS_TABLE, DBOpenHelper.KEY_ID + " = " + id, null);
}
//Delete single course by id
public void deleteCourseById(long id){
db.delete(DBOpenHelper.COURSE_TABLE, DBOpenHelper.COURSE_ID + " = " + id, null);
}
}
The activity causing problems:
package com.mikero.termtracker;
import android.app.ListActivity;
import android.content.Intent;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import java.util.List;
public class CourseActivity extends ListActivity {
private DataSource ds;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_course);
ds = new DataSource(this);
ds.open();
setAdapter();
}
@Override
public void onRestart(){
super.onRestart();
finish();
startActivity(getIntent());
}
public void onClick(View view){
ArrayAdapter<Course> adapter = (ArrayAdapter<Course>)getListAdapter();
Course course = null;
switch(view.getId()){
case R.id.addButtonCourse:
final long termNo = (long) getIntent().getExtras().get(TermViewActivity.EXTRA_TERMNO);
Intent intent = new Intent(getApplicationContext(), CourseEditActivity.class);
intent.putExtra(TermViewActivity.EXTRA_TERMNO, termNo);
startActivity(intent);
break;
}
}
@Override
public void onListItemClick(ListView listView,
View itemView,
int position,
long id){
Intent intent = new Intent(getApplicationContext(), CourseViewActivity.class);
Course course = (Course)getListView().getItemAtPosition(position);
long itemId = course.getId();
final long termNo = (long) getIntent().getExtras().get(TermViewActivity.EXTRA_TERMNO);
intent.putExtra(TermViewActivity.EXTRA_TERMNO, termNo);
startActivity(intent);
}
public ArrayAdapter<Course> setAdapter(){
List<Course> courseList = ds.getAllCourses();
ArrayAdapter<Course> adapter = new ArrayAdapter<Course>(this,
android.R.layout.simple_list_item_1, courseList);
setListAdapter(adapter);
return adapter;
}
}
The error report:
E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.mikero.termtracker, PID: 4304
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.mikero.termtracker/com.mikero.termtracker.CourseActivity}: android.database.sqlite.SQLiteException: no such column: termId (code 1): , while compiling: SELECT courseId, courseName, courseStart, courseEnd, courseStatus, courseMentor, termId FROM course
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2646)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2707)
at android.app.ActivityThread.-wrap12(ActivityThread.java)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1460)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:154)
at android.app.ActivityThread.main(ActivityThread.java:6077)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:865)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:755)
Caused by: android.database.sqlite.SQLiteException: no such column: termId (code 1): , while compiling: SELECT courseId, courseName, courseStart, courseEnd, courseStatus, courseMentor, termId FROM course
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1318)
at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1165)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1036)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1204)
at com.mikero.termtracker.DataSource.getAllCourses(DataSource.java:128)
at com.mikero.termtracker.CourseActivity.setAdapter(CourseActivity.java:67)
at com.mikero.termtracker.CourseActivity.onCreate(CourseActivity.java:28)
at android.app.Activity.performCreate(Activity.java:6662)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1118)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2599)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2707)
at android.app.ActivityThread.-wrap12(ActivityThread.java)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1460)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:154)
at android.app.ActivityThread.main(ActivityThread.java:6077)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:865)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:755)
Application terminated.
Thanks so much for taking the time to help :D
