I want to use a SQL database to store records for a game. Actually I don't have a clue about SQL. I have a class "Records" which should manage the in and Output of records. Additionally I have a class SQLDatabaseHelper which provides the SQL-Database.
My Problem is the following line:
crsRecord = sqliteDatabase.rawQuery(QUERY_GET_RECORD + category, null);
I got always the error "No such column: SYS103" "SYS103" is a name of a category. I don't know why it can be read. Do you have any idea?
SQL table creation:
CREATE TABLE records (
id INTEGER PRIMARY KEY AUTOINCREMENT,
category VARCHAR(30) NOT NULL,
displaytime VARCHAR(12) NOT NULL,
recordtime VARCHAR(10) NOT NULL);
I guess writing works just reading doesn't work.
public class Records {
private SQLiteOpenHelper sqliteOpenHelper;
private SQLiteDatabase sqliteDatabase;
private static final String INSERT_NEW_RECORD = "insert into records(category, displayrecord, timerecord) values(";
private static final String QUERY_GET_RECORD = "SELECT * FROM records WHERE category = ";
public Records(Context context){
sqliteOpenHelper = new SQLDatabaseHelper(context);
sqliteDatabase = sqliteOpenHelper.getWritableDatabase();
}
public void addRecord(String category, String displaytime, String timerecord){
ContentValues data = new ContentValues();
data.put("category", category);
data.put("displaytime", displaytime);
data.put("recordtime", timerecord);
sqliteDatabase.insert("records", null, data);
// sqliteDatabase.execSQL(INSERT_NEW_RECORD + category + ", " + strTime + ", " + dblTime + ");");
}
public String[] getRecord(String category){
String[] record = new String[3];
Cursor crsRecord;
try{
crsRecord = sqliteDatabase.rawQuery(QUERY_GET_RECORD + category, null);
}catch(SQLiteException e){
Log.d("database", e.getMessage());
String[] nullRecord = {category, "00:00.0", "0"};
return nullRecord;
}
int i=0;
while(crsRecord.moveToNext()){
record[i] = crsRecord.getString(0);
i++;
}
return record;
}
}
public class SQLDatabaseHelper extends SQLiteOpenHelper {
private Context context;
public SQLDatabaseHelper(Context context){
super(
context,
context.getResources().getString(R.string.dbname),
null,
Integer.parseInt(context.getResources().getString(R.string.version)));
this.context=context;
}
@Override
public void onCreate(SQLiteDatabase db) {
for(String sql : context.getResources().getStringArray(R.array.create)){
db.execSQL(sql);
}
Log.d("Database", "creat succesfully");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
My method to get data out of the database, but for some reason the columnIndex is alway -1:
public String[] getRecord(String category){
String[] record = new String[3];
Cursor crsRecord;
crsRecord = sqliteDatabase.rawQuery(QUERY_GET_RECORD, new String[]{ category } );
int i=0;
crsRecord.moveToFirst();
while(!crsRecord.isAfterLast()){
// Instead of using an int literal to get the colum index,
// use the getColumnIndex method
int index = crsRecord.getColumnIndex(category);
if (index == -1) {
String[] nullRecord = {category, "00:00.0", "0"};
return nullRecord;
}
else {
record[i] = crsRecord.getString(index);
i++;
}
crsRecord.moveToNext();
}
while(crsRecord.moveToNext()){
record[i] = crsRecord.getString(0);
i++;
}
return record;
}
private static final String INSERT_NEW_RECORD = "insert into records(category, displayrecord, timerecord) values(";? The column names specified there in comparison to the column names for the table creation?