0

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;

}
2
  • Do you notice anything wrong with: 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? Commented May 19, 2014 at 2:45
  • INSERT_NEW_RECORD was used before. Now it is a comment. But I tried to use it again and I changed the INSERT_NEW_RECORD line but the only difference is, that I got an other error: android.database.sqlite.SQLiteException: near ":12": syntax error (code 1): , while compiling: insert into records(category, displayrecord, recordtime) values(SYS103, 00:12.8, 5819); Commented May 19, 2014 at 2:56

1 Answer 1

1

You'll need to escape your parameters.

As is, your code executes the query:

SELECT * FROM  records WHERE category = SYS103

That's not valid SQL. It should look like this:

SELECT * FROM  records WHERE category = 'SYS103'

and you need to escape apostrophes. You'd be better off relying on rawQuery to escape your parameters:

private static final String QUERY_GET_RECORD 
                            = "SELECT * FROM  records WHERE category = ?";

and

crsRecord = 
    sqliteDatabase.rawQuery(QUERY_GET_RECORD, new String[]{ category } );
Sign up to request clarification or add additional context in comments.

6 Comments

Thank you for that...now I have another error in the "insert" line. Do you have any idea?
Do the same thing there, if you want to use rawQuery: rawQuery( "INSERT INTO records( category, displayTime, recordTime )VALUES(?,?,?)", new String[]{ cat, dt, rt } ); You might be better off using insert.
Now it works without error, but I am not able to get data out of my database. I have updated my entry above.
You're not actually accessing the the cursor in getRecord. Add something like if( crsRecord.moveToFirst() ) { return new String{} { crsRecord.getString(0), crsRecord.getString(1), crsRecord.getString(2) }; } else { ... return null record ... }
Oh yea...according to the Debugger I access the cursor. See the new code I attached. But the Problem is I got always "getColumnIndex" = -1...what means column doesn't exist, but it should because I added it before.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.