I've been having a problem with my SQLite DB for quite some time now. Every so often something goes wrong and I get this error:
java.lang.RuntimeException: An error occured while executing doInBackground()
at android.os.AsyncTask$3.done(AsyncTask.java:299)
at java.util.concurrent.FutureTask.finishCompletion(FutureTask.java:352)
at java.util.concurrent.FutureTask.setException(FutureTask.java:219)
at java.util.concurrent.FutureTask.run(FutureTask.java:239)
at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:230)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1080)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:573)
at java.lang.Thread.run(Thread.java:856)
Caused by: android.database.sqlite.SQLiteException: no such table: photo (code 1): , while compiling: SELECT * FROM photo
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1113)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:686)
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:1420)
at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1359)
at com.example.sition.diggintemp.db.DigginSQLiteHelper.getAllPhotos(DigginSQLiteHelper.java:1102)
at com.example.sition.diggintemp.LoadActivity$LoadAllTask.doInBackground(LoadActivity.java:249)
at com.example.sition.diggintemp.LoadActivity$LoadAllTask.doInBackground(LoadActivity.java:243)
at android.os.AsyncTask$2.call(AsyncTask.java:287)
at java.util.concurrent.FutureTask.run(FutureTask.java:234)
at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:230)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1080)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:573)
at java.lang.Thread.run(Thread.java:856)
When this happen I need to clear the entire DB, set version number one higher and remove all pictures from the server and the device (because they're kind of linked to the info in the DB).
Below is the AsyncTask in my LoadActivity (where I get this error), in this Activity I load the DB from the server and replace the old data from the SQLiteDB with the data from the online DB.
private class LoadAllTask extends AsyncTask<JSONArray,Void,Void> {
@Override
protected Void doInBackground(JSONArray... params) {
JSONArray jsonProjects = params[0];
JSONArray jsonPhotos = params[1];
JSONArray jsonVideos = params[2];
ArrayList<Photo> oldPhotos = db.getAllPhotos();
ArrayList<Video> oldVideos = db.getAllVideos();
db.clearDB();
try {
for (int i = 0; i < jsonProjects.length(); i++) {
JSONObject jsonProject = (JSONObject) jsonProjects.get(i);
Project project = new Project(jsonProject.getInt("id"), jsonProject.getString("title"), jsonProject.getInt("user_id"), jsonProject.getString("description"));
db.addProject(project);
JSONArray jsonWells = jsonProject.getJSONArray("wells");
for (int i2 = 0; i2 < jsonWells.length(); i2++) {
JSONObject jsonWell = (JSONObject) jsonWells.get(i2);
Well well = new Well(jsonWell.getInt("id"), jsonWell.getString("number"), jsonWell.getInt("project_id"), jsonWell.getString("description"));
db.addWell(well);
}
JSONArray jsonTracks = jsonProject.getJSONArray("tracks");
for (int i2 = 0; i2 < jsonTracks.length(); i2++) {
JSONObject jsonTrack = (JSONObject) jsonTracks.get(i2);
Track track = new Track(jsonTrack.getInt("id"), jsonTrack.getString("number"), jsonTrack.getString("description"));
boolean inDb = false;
for (Track t : db.getAllTracks()) {
if (t.toString().equals(track.toString())) {
inDb = true;
}
}
if (!inDb) {
db.addTrack(track);
}
Track_Well track_well = new Track_Well(db.getAllTrackWells().size() + 1, jsonTrack.getInt("id"), jsonTrack.getInt("well_id"));
db.addTrackWell(track_well);
}
JSONArray jsonSurfaces = jsonProject.getJSONArray("surfaces");
for (int i2 = 0; i2 < jsonSurfaces.length(); i2++) {
JSONObject jsonSurface = (JSONObject) jsonSurfaces.get(i2);
Surface surface = new Surface(jsonSurface.getInt("id"), jsonSurface.getString("number"), jsonSurface.getInt("well_id"), jsonSurface.getString("description"));
boolean inDb = false;
for (Surface s : db.getAllSurfaces()) {
if (s.toString().equals(surface.toString())) {
inDb = true;
}
}
if (!inDb) {
db.addSurface(surface);
}
Track_Surface track_surface = new Track_Surface(db.getAllTrackSurfaces().size() + 1, (!jsonSurface.isNull("track_id") ? jsonSurface.getInt("track_id") : 0), jsonSurface.getInt("id"));
db.addTrackSurface(track_surface);
}
JSONArray jsonProfiles = jsonProject.getJSONArray("profiles");
for (int i2 = 0; i2 < jsonProfiles.length(); i2++) {
JSONObject jsonProfile = (JSONObject) jsonProfiles.get(i2);
Profile profile = new Profile(jsonProfile.getInt("id"), jsonProfile.getString("number"), jsonProfile.getInt("well_id"), jsonProfile.getString("description"));
boolean inDb = false;
for (Profile p : db.getAllProfiles()) {
if (p.toString().equals(profile.toString())) {
inDb = true;
}
}
if (!inDb) {
db.addProfile(profile);
}
Track_Profile track_profile = new Track_Profile(db.getAllTrackProfiles().size() + 1, (!jsonProfile.isNull("track_id") ? jsonProfile.getInt("track_id") : 0), jsonProfile.getInt("id"));
db.addTrackProfile(track_profile);
}
JSONArray jsonFieldfinds = jsonProject.getJSONArray("fieldfinds");
for (int i2 = 0; i2 < jsonFieldfinds.length(); i2++) {
JSONObject jsonFieldfind = (JSONObject) jsonFieldfinds.get(i2);
int id = jsonFieldfind.getInt("id");
String number = jsonFieldfind.getString("number");
int project_id = (!jsonFieldfind.isNull("project_id") ? jsonFieldfind.getInt("project_id") : 0);
int well_id = (!jsonFieldfind.isNull("well_id") ? jsonFieldfind.getInt("well_id") : 0);
int track_id = (!jsonFieldfind.isNull("track_id") ? jsonFieldfind.getInt("track_id") : 0);
int surface_id = (!jsonFieldfind.isNull("surface_id") ? jsonFieldfind.getInt("surface_id") : 0);
int profile_id = (!jsonFieldfind.isNull("profile_id") ? jsonFieldfind.getInt("profile_id") : 0);
int user_id = jsonFieldfind.getInt("user_id");
String description = (jsonFieldfind.getString("description"));
Fieldfind fieldfind = new Fieldfind(id,number,project_id,well_id,track_id,surface_id,profile_id,user_id,description);
db.addFieldfind(fieldfind);
}
}
for (int i3 = 0; i3 < jsonPhotos.length(); i3++) {
JSONObject jsonPhoto = (JSONObject) jsonPhotos.get(i3);
File mainDir = new File(Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_PICTURES) + File.separator + "Diggin");
boolean isPic = jsonPhoto.getString("image_path").contains("picture");
String pathStart = mainDir.getPath() + "/";
Photo photo = new Photo(jsonPhoto.getInt("id"), jsonPhoto.getInt("user_id"), jsonPhoto.getString("imageable_type"), jsonPhoto.getInt("imageable_id"), pathStart + jsonPhoto.getString("image_path"), jsonPhoto.getString("description"), jsonPhoto.getString("metadata"), jsonPhoto.getString("wind"));
photo.setImage_path(photo.getThumbnailFromImage_path());
String filename = photo.getDBImage_path();
File thumbDir = new File(mainDir, "thumbnails");
File thumbnailFile = new File(thumbDir, filename);
photo.setImage_path(thumbnailFile.toString());
if (!thumbnailFile.exists()) {
db.addPhoto(photo);
String image_str = jsonPhoto.getString("image");
byte[] byte_arr = Base64.decode(image_str, 0);
Bitmap bitmap = BitmapFactory.decodeByteArray(byte_arr, 0, byte_arr.length);
try {
createPicture(bitmap, photo);
} catch (IOException e) {
e.printStackTrace();
}
} else {
photo.setImage_path(photo.getImageFromThumbnail_path());
if(isPic) {
photo.setImage_path(thumbnailFile.toString());
}
db.addPhoto(photo);
}
}
for (int i4 = 0; i4 < jsonVideos.length(); i4++) {
JSONObject jsonVideo = (JSONObject) jsonVideos.get(i4);
File mainDir = new File(Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_PICTURES) + File.separator + "Diggin");
boolean isVid = jsonVideo.getString("image_path").contains("video");
String pathStart = mainDir.getPath() + "/";
Video video = new Video(jsonVideo.getInt("id"), jsonVideo.getInt("user_id"), jsonVideo.getString("imageable_type"), jsonVideo.getInt("imageable_id"), pathStart + jsonVideo.getString("image_path"), jsonVideo.getString("description"), jsonVideo.getString("metadata"));
video.setImage_path(video.getThumbnailFromImage_path());
String filename = video.getDBImage_path();
File thumbDir = new File(mainDir, "thumbnails");
File thumbnailFile = new File(thumbDir, filename);
video.setImage_path(thumbnailFile.toString());
if (!thumbnailFile.exists()) {
db.addVideo(video);
String image_str = jsonVideo.getString("image");
byte[] byte_arr = Base64.decode(image_str, 0);
Bitmap bitmap = BitmapFactory.decodeByteArray(byte_arr, 0, byte_arr.length);
try {
createPicture(bitmap, video);
} catch (IOException e) {
e.printStackTrace();
}
} else {
video.setImage_path(video.getImageFromThumbnail_path());
if(isVid) {
video.setImage_path(thumbnailFile.toString());
}
db.addVideo(video);
}
}
for (Photo photo : oldPhotos) {
boolean stillExists = false;
for (Photo p : db.getAllPhotos()) {
if (p.getThumbnailFromImage_path().equals(photo.getThumbnailFromImage_path())) {
stillExists = true;
}
}
if (!stillExists) {
//Delete thumbnail picture
String deleteCmd = "rm -r " + photo.getThumbnailFromImage_path();
Runtime runtime = Runtime.getRuntime();
try {
runtime.exec(deleteCmd);
} catch (IOException e) {
e.printStackTrace();
}
}
}
for (Video video : oldVideos) {
boolean stillExists = false;
for (Video v : db.getAllVideos()) {
if (v.getThumbnailFromImage_path().equals(video.getThumbnailFromImage_path())) {
stillExists = true;
}
}
if (!stillExists) {
//Delete thumbnail picture
String deleteCmd = "rm -r " + video.getThumbnailFromImage_path();
Runtime runtime = Runtime.getRuntime();
try {
runtime.exec(deleteCmd);
} catch (IOException e) {
e.printStackTrace();
}
}
}
} catch (JSONException e) {
e.printStackTrace();
}
return null;
}
@Override
protected void onPostExecute(Void aVoid) {
super.onPostExecute(aVoid);
Intent intent = new Intent(LoadActivity.this,MainActivity.class);
startActivity(intent);
}
}
Does somebody know what causes this, because I have no clue.
EDIT:
Here is some code from the DigginSQLiteHelper:
public class DigginSQLiteHelper extends SQLiteOpenHelper{
//region Database Version and Name
private static final int DATABASE_VERSION = 9;
private static final String DATABASE_NAME = "DigginTempDB";
//endregion Database Version and Name
//region Table names
private static final String TABLE_PROJECT = "project";
private static final String TABLE_WELL = "well";
private static final String TABLE_TRACK = "track";
private static final String TABLE_SURFACE = "surface";
private static final String TABLE_PROFILE = "profile";
private static final String TABLE_FIELDFIND = "fieldfind";
private static final String TABLE_TRACK_WELL = "track_well";
private static final String TABLE_TRACK_SURFACE = "track_surface";
private static final String TABLE_TRACK_PROFILE = "track_profile";
private static final String TABLE_PHOTO = "photo";
private static final String TABLE_VIDEO = "video";
//endregion Table names
//region Table Column names
private static final String KEY_ID = "id";
private static final String KEY_TITLE = "title";
private static final String KEY_USER_ID = "user_id";
private static final String KEY_NUMBER = "number";
private static final String KEY_PROJECT_ID = "project_id";
private static final String KEY_WELL_ID = "well_id";
private static final String KEY_TRACK_ID = "track_id";
private static final String KEY_SURFACE_ID = "surface_id";
private static final String KEY_PROFILE_ID = "profile_id";
private static final String KEY_IMAGEABLE_TYPE = "imageable_type";
private static final String KEY_IMAGEABLE_ID = "imageable_id";
private static final String KEY_IMAGE_PATH = "image_path";
private static final String KEY_DESCRIPTION = "description";
private static final String KEY_METADATA = "metadata";
private static final String KEY_WIND = "wind";
//endregion
//region Table Columns
private static final String[] COLUMNS_PROJECT = {KEY_ID,KEY_TITLE,KEY_USER_ID,KEY_DESCRIPTION};
private static final String[] COLUMNS_WELL = {KEY_ID, KEY_NUMBER, KEY_PROJECT_ID,KEY_DESCRIPTION};
private static final String[] COLUMNS_TRACK = {KEY_ID, KEY_NUMBER,KEY_DESCRIPTION};
private static final String[] COLUMNS_SURFACE = {KEY_ID, KEY_NUMBER, KEY_WELL_ID,KEY_DESCRIPTION};
private static final String[] COLUMNS_PROFILE = {KEY_ID, KEY_NUMBER, KEY_WELL_ID,KEY_DESCRIPTION};
private static final String[] COLUMNS_FIELDFIND = {KEY_ID, KEY_NUMBER, KEY_PROJECT_ID, KEY_WELL_ID, KEY_TRACK_ID, KEY_SURFACE_ID, KEY_PROFILE_ID, KEY_USER_ID,KEY_DESCRIPTION};
private static final String[] COLUMNS_TRACK_WELL = {KEY_ID, KEY_TRACK_ID, KEY_WELL_ID};
private static final String[] COLUMNS_TRACK_SURFACE = {KEY_ID, KEY_TRACK_ID, KEY_SURFACE_ID};
private static final String[] COLUMNS_TRACK_PROFILE = {KEY_ID, KEY_TRACK_ID, KEY_PROFILE_ID};
private static final String[] COLUMNS_PHOTO = {KEY_ID,KEY_USER_ID,KEY_IMAGEABLE_TYPE,KEY_IMAGEABLE_ID,KEY_IMAGE_PATH,KEY_DESCRIPTION,KEY_METADATA,KEY_WIND};
private static final String[] COLUMNS_VIDEO = {KEY_ID,KEY_USER_ID,KEY_IMAGEABLE_TYPE,KEY_IMAGEABLE_ID,KEY_IMAGE_PATH,KEY_DESCRIPTION,KEY_METADATA};
//endregion
public DigginSQLiteHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
// SQL statements to create tables
String CREATE_PROJECT_TABLE =
"CREATE TABLE project ( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"title TEXT, "+
"user_id INTEGER, " +
"description TEXT);";
String CREATE_WELL_TABLE =
"CREATE TABLE well ( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"number TEXT, " +
"project_id INTEGER, " +
"description TEXT, " +
" FOREIGN KEY ("+KEY_PROJECT_ID+") REFERENCES "+TABLE_PROJECT+"("+KEY_ID+"));";
String CREATE_TRACK_TABLE =
"CREATE TABLE track ( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"number TEXT, " +
"description TEXT);";
String CREATE_SURFACE_TABLE =
"CREATE TABLE surface ( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"number TEXT, " +
"well_id INTEGER, " +
"description TEXT, " +
" FOREIGN KEY ("+KEY_WELL_ID+") REFERENCES "+TABLE_WELL+"("+KEY_ID+"));";
String CREATE_PROFILE_TABLE =
"CREATE TABLE profile ( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"number TEXT, " +
"well_id INTEGER, " +
"description TEXT, " +
" FOREIGN KEY ("+KEY_WELL_ID+") REFERENCES "+TABLE_WELL+"("+KEY_ID+"));";
String CREATE_FIELDFIND_TABLE =
"CREATE TABLE fieldfind ( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"number TEXT, " +
"project_id INTEGER, " +
"well_id INTEGER, " +
"track_id INTEGER, " +
"surface_id INTEGER, " +
"profile_id INTEGER, " +
"user_id INTEGER, " +
"description TEXT, " +
" FOREIGN KEY ("+KEY_PROJECT_ID+") REFERENCES "+TABLE_PROJECT+"("+KEY_ID+")," +
" FOREIGN KEY ("+KEY_WELL_ID+") REFERENCES "+TABLE_WELL+"("+KEY_ID+")," +
" FOREIGN KEY ("+KEY_TRACK_ID+") REFERENCES "+TABLE_TRACK+"("+KEY_ID+")," +
" FOREIGN KEY ("+KEY_SURFACE_ID+") REFERENCES "+TABLE_SURFACE+"("+KEY_ID+")," +
" FOREIGN KEY ("+KEY_PROFILE_ID+") REFERENCES "+TABLE_PROFILE+"("+KEY_ID+"));";
String CREATE_TRACK_WELL_TABLE =
"CREATE TABLE track_well ( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"track_id INTEGER, " +
"well_id INTEGER, " +
" FOREIGN KEY ("+KEY_TRACK_ID+") REFERENCES "+TABLE_TRACK+"("+KEY_ID+")" +
" FOREIGN KEY ("+KEY_WELL_ID+") REFERENCES "+TABLE_WELL+"("+KEY_ID+"));";
String CREATE_TRACK_SURFACE_TABLE =
"CREATE TABLE track_surface ( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"track_id INTEGER, " +
"surface_id INTEGER, " +
" FOREIGN KEY ("+KEY_TRACK_ID+") REFERENCES "+TABLE_TRACK+"("+KEY_ID+")" +
" FOREIGN KEY ("+KEY_SURFACE_ID+") REFERENCES "+TABLE_SURFACE+"("+KEY_ID+"));";
String CREATE_TRACK_PROFILE_TABLE =
"CREATE TABLE track_profile ( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"track_id INTEGER, " +
"profile_id INTEGER, " +
" FOREIGN KEY ("+KEY_TRACK_ID+") REFERENCES "+TABLE_TRACK+"("+KEY_ID+")" +
" FOREIGN KEY ("+KEY_PROFILE_ID+") REFERENCES "+TABLE_PROFILE+"("+KEY_ID+"));";
String CREATE_PHOTO_TABLE =
"CREATE TABLE photo( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"user_id INTEGER, "+
"imageable_type TEXT, "+
"imageable_id INTEGER, "+
"image_path TEXT, "+
"description TEXT, "+
"metadata TEXT, "+
"wind TEXT );";
String CREATE_VIDEO_TABLE =
"CREATE TABLE video( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"user_id INTEGER, "+
"imageable_type TEXT, "+
"imageable_id INTEGER, "+
"image_path TEXT, "+
"description TEXT, "+
"metadata TEXT );";
// Create tables
db.execSQL(CREATE_PROJECT_TABLE);
db.execSQL(CREATE_WELL_TABLE);
db.execSQL(CREATE_TRACK_TABLE);
db.execSQL(CREATE_SURFACE_TABLE);
db.execSQL(CREATE_PROFILE_TABLE);
db.execSQL(CREATE_FIELDFIND_TABLE);
db.execSQL(CREATE_TRACK_WELL_TABLE);
db.execSQL(CREATE_TRACK_SURFACE_TABLE);
db.execSQL(CREATE_TRACK_PROFILE_TABLE);
db.execSQL(CREATE_PHOTO_TABLE);
db.execSQL(CREATE_VIDEO_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (newVersion > oldVersion) {
// Drop older tables if existed
db.execSQL("DROP TABLE IF EXISTS video");
db.execSQL("DROP TABLE IF EXISTS photo");
db.execSQL("DROP TABLE IF EXISTS fieldfind");
db.execSQL("DROP TABLE IF EXISTS track_surface");
db.execSQL("DROP TABLE IF EXISTS track_profile");
db.execSQL("DROP TABLE IF EXISTS track_well");
db.execSQL("DROP TABLE IF EXISTS profile");
db.execSQL("DROP TABLE IF EXISTS surface");
db.execSQL("DROP TABLE IF EXISTS track");
db.execSQL("DROP TABLE IF EXISTS well");
db.execSQL("DROP TABLE IF EXISTS project");
// Create fresh tables
this.onCreate(db);
}
}
public static int getDatabaseVersion() {
return DATABASE_VERSION;
}
/**
* Method that clears the SQLite DB
*/
public void clearDB() {
SQLiteDatabase db = getWritableDatabase();
// Drop older tables if existed
db.execSQL("DROP TABLE IF EXISTS video");
db.execSQL("DROP TABLE IF EXISTS photo");
db.execSQL("DROP TABLE IF EXISTS fieldfind");
db.execSQL("DROP TABLE IF EXISTS track_surface");
db.execSQL("DROP TABLE IF EXISTS track_profile");
db.execSQL("DROP TABLE IF EXISTS track_well");
db.execSQL("DROP TABLE IF EXISTS profile");
db.execSQL("DROP TABLE IF EXISTS surface");
db.execSQL("DROP TABLE IF EXISTS track");
db.execSQL("DROP TABLE IF EXISTS well");
db.execSQL("DROP TABLE IF EXISTS project");
// Create fresh tables
this.onCreate(db);
}
public ArrayList<Photo> getAllPhotos() {
ArrayList<Photo> photos = new ArrayList<>();
// 1. build the query
String query = "SELECT * FROM " + TABLE_PHOTO;
// 2. get reference to writable DB
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(query, null);
// 3. go over each row, build project and add it to list
Photo photo;
if (cursor.moveToFirst()) {
do {
photo = new Photo(cursor.getInt(0),cursor.getInt(1),cursor.getString(2),cursor.getInt(3),cursor.getString(4),cursor.getString(5),cursor.getString(6),cursor.getString(7));
// Add photo to photos
photos.add(photo);
} while (cursor.moveToNext());
}
cursor.close();
// return photos
return photos;
}
}
Aucun commentaire:
Enregistrer un commentaire