Vote count:
0
this is my database, can you please help me with it? i think there is nothing wrong i tried reinstalling the app to update the database initially but still doesnt work. it says that there is no such column _url.
This is my class that creates the database:
MySQLiteHelper.java
public class MySQLiteHelper extends SQLiteOpenHelper {
/**
* table for locations
* | _id | _latitude | _longitude | _time | _provider
*/
public static final String TABLE_LOCATION = "location";
public static final String COLUMN_LOCID = "_id";
public static final String COLUMN_LATITUDE = "_latitude";
public static final String COLUMN_LONGITUDE = "_longitude";
public static final String COLUMN_TIME = "_time";
public static final String COLUMN_ACCURACY = "_accuracy";
public static final String COLUMN_PROVIDER = "_provider";
/**
* table for pictures
* | _id | _url | _latitude | _longitude | _time
*/
public static final String TABLE_PICTURE = "picture";
public static final String COLUMN_PIC_ID = "_id";
public static final String COLUMN_PIC_URL = "_url";
public static final String COLUMN_PIC_LATITUDE = "_latitude";
public static final String COLUMN_PIC_LONGITUDE = "_longitude";
public static final String COLUMN_PIC_TIME = "_time";
public static final String COLUMN_PIC_ACCURACY = "_accuracy";
/**
* table for Accelerometer
* | _id | _x | _y | _z | _time
*/
public static final String TABLE_ACCELEROMETER = "accelerometer";
public static final String COLUMN_ACCELEROMETER_ID = "_id";
public static final String COLUMN_ACCELEROMETER_X = "_x";
public static final String COLUMN_ACCELEROMETER_Y = "_y";
public static final String COLUMN_ACCELEROMETER_Z = "_z";
public static final String COLUMN_ACCELEROMETER_TIME = "_time";
/**
* table for Sound
* | _id | _amplitude | _time
*/
public static final String TABLE_AMPLITUDE = "sound";
public static final String COLUMN_AMPLITUDE_ID = "_id";
public static final String COLUMN_AMPLITUDE_AMPLITUDE = "_amplitude";
public static final String COLUMN_AMPLITUDE_TIME = "_time";
private static final String DATABASE_NAME = "memory.db";
private static final int DATABASE_VERSION = 1;
// Database creation sql statement for location
private static final String DATABASE_CREATE_LOC = "create table "
+ TABLE_LOCATION + "(" + COLUMN_LOCID
+ " integer primary key autoincrement, " + COLUMN_LATITUDE
+ " double not null, "+ COLUMN_LONGITUDE + " double not null, "
+ COLUMN_TIME + " text not null, " + COLUMN_ACCURACY + " text not null, " +
COLUMN_PROVIDER + " text not null " +");";
// Database creation sql statement for picture
private static final String DATABASE_CREATE_PIC = "create table "
+ TABLE_PICTURE + "(" + COLUMN_PIC_ID
+ " integer primary key autoincrement, " + COLUMN_PIC_URL
+ " text not null, " + COLUMN_LATITUDE
+ " double, "+ COLUMN_LONGITUDE + " double, "
+ COLUMN_TIME + " text, " + COLUMN_PIC_ACCURACY + " text " + ");";
// Database creation sql statement for accelerometer
private static final String DATABASE_CREATE_ACCELEROMETER = "create table "
+ TABLE_ACCELEROMETER + "(" + COLUMN_ACCELEROMETER_ID
+ " integer primary key autoincrement, " + COLUMN_ACCELEROMETER_X
+ " float not null, " + COLUMN_ACCELEROMETER_Y
+ " float not null, "+ COLUMN_ACCELEROMETER_Z + " float not null, "
+ COLUMN_ACCELEROMETER_TIME + " text " +");";
// Database creation sql statement for sound
private static final String DATABASE_CREATE_AMPLITUDE = "create table "
+ TABLE_AMPLITUDE + "(" + COLUMN_AMPLITUDE_ID
+ " integer primary key autoincrement, " + COLUMN_AMPLITUDE_AMPLITUDE
+ " double not null, "
+ COLUMN_AMPLITUDE_TIME + " text " +");";
public MySQLiteHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase database) {
database.execSQL(DATABASE_CREATE_LOC);
database.execSQL(DATABASE_CREATE_PIC);
database.execSQL(DATABASE_CREATE_ACCELEROMETER);
database.execSQL(DATABASE_CREATE_AMPLITUDE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.w(MySQLiteHelper.class.getName(),
"Upgrading database from version " + oldVersion + " to "
+ newVersion + ", which will destroy all old data");
db.execSQL("DROP TABLE IF EXISTS " + TABLE_LOCATION);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_PICTURE);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_ACCELEROMETER);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_AMPLITUDE);
onCreate(db);
}
}
I dont find anything wrong in my tables. i checked for spaces.
The error i get is:
03-24 17:51:39.851: E/SQLiteLog(18574): (1) no such column: _url
03-24 17:51:39.855: W/dalvikvm(18574): threadid=11: thread exiting with uncaught exception (group=0x40eb0300)
03-24 17:51:39.863: E/AndroidRuntime(18574): FATAL EXCEPTION: Thread-799
03-24 17:51:39.863: E/AndroidRuntime(18574): android.database.sqlite.SQLiteException: no such column: _url (code 1): , while compiling: SELECT _id, _url, _latitude, _longitude, _time, _accuracy FROM picture
03-24 17:51:39.863: E/AndroidRuntime(18574): at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
03-24 17:51:39.863: E/AndroidRuntime(18574): at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
03-24 17:51:39.863: E/AndroidRuntime(18574): at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
03-24 17:51:39.863: E/AndroidRuntime(18574): at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
03-24 17:51:39.863: E/AndroidRuntime(18574): at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
03-24 17:51:39.863: E/AndroidRuntime(18574): at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
03-24 17:51:39.863: E/AndroidRuntime(18574): at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
03-24 17:51:39.863: E/AndroidRuntime(18574): at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
03-24 17:51:39.863: E/AndroidRuntime(18574): at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1161)
03-24 17:51:39.863: E/AndroidRuntime(18574): at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1032)
03-24 17:51:39.863: E/AndroidRuntime(18574): at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1200)
03-24 17:51:39.863: E/AndroidRuntime(18574): at data.DatabaseInteraction.getAllPictures(DatabaseInteraction.java:191)
03-24 17:51:39.863: E/AndroidRuntime(18574): at main.inSituApp.InSituApp$3.run(InSituApp.java:327)
I have a class that uses the database class to insert and query data to and from the database.
DatabaseInteraction.java
public class DatabaseInteraction {
// Database fields
private SQLiteDatabase database;
private MySQLiteHelper dbHelper;
private String[] locColumns = { MySQLiteHelper.COLUMN_LOCID,
MySQLiteHelper.COLUMN_LATITUDE, MySQLiteHelper.COLUMN_LONGITUDE,
MySQLiteHelper.COLUMN_TIME, MySQLiteHelper.COLUMN_ACCURACY,
MySQLiteHelper.COLUMN_PROVIDER};
private String[] picColumns = { MySQLiteHelper.COLUMN_PIC_ID,
MySQLiteHelper.COLUMN_PIC_URL, MySQLiteHelper.COLUMN_PIC_LATITUDE,
MySQLiteHelper.COLUMN_PIC_LONGITUDE,
MySQLiteHelper.COLUMN_PIC_TIME, MySQLiteHelper.COLUMN_PIC_ACCURACY };
private String[] accColumns = { MySQLiteHelper.COLUMN_ACCELEROMETER_ID,
MySQLiteHelper.COLUMN_ACCELEROMETER_X, MySQLiteHelper.COLUMN_ACCELEROMETER_Y,
MySQLiteHelper.COLUMN_ACCELEROMETER_Z,
MySQLiteHelper.COLUMN_ACCELEROMETER_TIME };
private String[] amplitudeColumns = { MySQLiteHelper.COLUMN_AMPLITUDE_ID,
MySQLiteHelper.COLUMN_AMPLITUDE_AMPLITUDE,
MySQLiteHelper.COLUMN_AMPLITUDE_TIME };
public DatabaseInteraction(Context context) {
dbHelper = new MySQLiteHelper(context);
}
public void open() throws SQLException {
database = dbHelper.getWritableDatabase();
}
public void close() {
dbHelper.close();
}
public /*Loc*/ long createLocation(double latitude, double longitude, long time,
double accuracy, String provider) {
ContentValues values = new ContentValues();
values.put(MySQLiteHelper.COLUMN_LATITUDE, latitude);
values.put(MySQLiteHelper.COLUMN_LONGITUDE, longitude);
values.put(MySQLiteHelper.COLUMN_TIME, time);
values.put(MySQLiteHelper.COLUMN_ACCURACY, accuracy);
values.put(MySQLiteHelper.COLUMN_PROVIDER, provider);
open();
long insertId = database.insert(MySQLiteHelper.TABLE_LOCATION, null,
values);
close();
return insertId;
/*Cursor cursor = database.query(MySQLiteHelper.TABLE_LOCATION,
locColumns,
MySQLiteHelper.COLUMN_LOCID + " = " + insertId, null, null,
null, null);
cursor.moveToFirst();
Loc newLoc = cursorToLocation(cursor);
cursor.close();
return newLoc;*/
}
public long createPic(String url, double latitude, double longitude,
long time) {
ContentValues values = new ContentValues();
values.put(MySQLiteHelper.COLUMN_PIC_URL, url);
values.put(MySQLiteHelper.COLUMN_LATITUDE, latitude);
values.put(MySQLiteHelper.COLUMN_LONGITUDE, longitude);
values.put(MySQLiteHelper.COLUMN_TIME, time);
open();
long insertId = database.insert(MySQLiteHelper.TABLE_PICTURE, null,
values);
close();
return insertId;
/*Cursor cursor = database.query(MySQLiteHelper.TABLE_PICTURE,
picColumns, MySQLiteHelper.COLUMN_PIC_ID + " = " + insertId,
null, null, null, null);
cursor.moveToFirst();
Pic newPic = cursorToPicture(cursor);
cursor.close();
return newPic;*/
}
public long createAccelerometer(float x, float y, float z,
long time) {
ContentValues values = new ContentValues();
values.put(MySQLiteHelper.COLUMN_ACCELEROMETER_X, x);
values.put(MySQLiteHelper.COLUMN_ACCELEROMETER_Y, y);
values.put(MySQLiteHelper.COLUMN_ACCELEROMETER_Z, z);
values.put(MySQLiteHelper.COLUMN_ACCELEROMETER_TIME, time);
open();
long insertId = database.insert(MySQLiteHelper.TABLE_ACCELEROMETER, null,
values);
close();
return insertId;
}
public long createSound(double amplitude, long time) {
ContentValues values = new ContentValues();
values.put(MySQLiteHelper.COLUMN_AMPLITUDE_AMPLITUDE, amplitude);
values.put(MySQLiteHelper.COLUMN_AMPLITUDE_TIME, time);
open();
long insertId = database.insert(MySQLiteHelper.TABLE_AMPLITUDE, null,
values);
close();
return insertId;
}
public int deleteLocation(long id) {
System.out.println("Location deleted with id: " + id);
open();
int value = database.delete(MySQLiteHelper.TABLE_LOCATION,
MySQLiteHelper.COLUMN_LOCID + " = " + id, null);
close();
return value;
}
public int deletePic(long id){
System.out.println("Picture deleted with id: " + id);
open();
int value = database.delete(MySQLiteHelper.TABLE_PICTURE,
MySQLiteHelper.COLUMN_PIC_ID + " = " + id, null);
close();
return value;
}
public int deleteAccelerometer(long id){
System.out.println("Accelerometer deleted with id: " + id);
open();
int value = database.delete(MySQLiteHelper.TABLE_ACCELEROMETER,
MySQLiteHelper.COLUMN_ACCELEROMETER_ID + " = " + id, null);
return value;
}
public int deleteSound(long id){
System.out.println("Sound deleted with id: " + id);
open();
int value = database.delete(MySQLiteHelper.TABLE_AMPLITUDE,
MySQLiteHelper.COLUMN_AMPLITUDE_ID + " = " + id, null);
close();
return value;
}
public List<Loc> getAllLocations() {
List<Loc> allLocations = new ArrayList<Loc>();
open();
Cursor cursor = database.query(MySQLiteHelper.TABLE_LOCATION,
locColumns, null, null, null, null, null);
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
Loc oneLoc = cursorToLocation(cursor);
allLocations.add(oneLoc);
cursor.moveToNext();
}
// make sure to close the cursor
cursor.close();
close();
return allLocations;
}
public List<Pic> getAllPictures() {
List<Pic> allPictures = new ArrayList<Pic>();
open();
Cursor cursor = database.query(MySQLiteHelper.TABLE_PICTURE,
picColumns, null, null, null, null, null);
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
Pic onePic = cursorToPicture(cursor);
allPictures.add(onePic);
cursor.moveToNext();
}
// make sure to close the cursor
cursor.close();
close();
return allPictures;
}
public List<Accelerometer> getAllAccelerometerValues() {
List<Accelerometer> allAccelerometerValues = new ArrayList<Accelerometer>();
Cursor cursor = database.query(MySQLiteHelper.TABLE_ACCELEROMETER,
accColumns, null, null, null, null, null);
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
Accelerometer oneAcc = cursorToAccelerometer(cursor);
allAccelerometerValues.add(oneAcc);
cursor.moveToNext();
}
// make sure to close the cursor
cursor.close();
return allAccelerometerValues;
}
public List<Amplitude> getAllSoundAmpitudes() {
List<Amplitude> allSoundAmplitudes = new ArrayList<Amplitude>();
Cursor cursor = database.query(MySQLiteHelper.TABLE_AMPLITUDE,
amplitudeColumns, null, null, null, null, null);
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
Amplitude oneSound = cursorToAmplitude(cursor);
allSoundAmplitudes.add(oneSound);
cursor.moveToNext();
}
// make sure to close the cursor
cursor.close();
return allSoundAmplitudes;
}
private Loc cursorToLocation(Cursor cursor) {
Loc location = new Loc();
location.setLatitude(cursor.getDouble(1));
location.setLongitude(cursor.getDouble(2));
location.setTime(cursor.getLong(3));
location.setAccuracy(cursor.getDouble(4));
return location;
}
private Pic cursorToPicture(Cursor cursor) {
Pic picture = new Pic();
picture.setLatitude(cursor.getDouble(1));
picture.setLongitude(cursor.getDouble(2));
picture.setTime(cursor.getLong(3));
return picture;
}
private Accelerometer cursorToAccelerometer(Cursor cursor) {
Accelerometer acc = new Accelerometer();
acc.setX(cursor.getFloat(1));
acc.setY(cursor.getFloat(2));
acc.setZ(cursor.getFloat(3));
acc.setTime(cursor.getLong(4));
return acc;
}
private Amplitude cursorToAmplitude(Cursor cursor) {
Amplitude sound = new Amplitude();
sound.setAmplitude(cursor.getDouble(1));
sound.setTime(cursor.getLong(2));
return sound;
}
}
Please any hint is appreciated. ty very much in advance.
asked 1 min ago
Aucun commentaire:
Enregistrer un commentaire