android sqlite - insert syntax error (code 1)

Question

I cannot figure out what is wrong in my code:

    package com.example.lirans88;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHandler extends SQLiteOpenHelper {

    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "messageDatabase";

    // Messages table name
    private static final String TABLE_MESSAGES = "messages";

    // Messages Table Columns names
    private static final String KEY_ID = "id";
    private static final String KEY_TEXT = "msg";
    private static final String KEY_FROM = "from";
    private static final String KEY_TO = "to";

    public DatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_MESSAGES_TABLE = "CREATE TABLE " + TABLE_MESSAGES + "("
                + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TEXT + " TEXT," + KEY_FROM + " TEXT,"
                + KEY_TO + " TEXT" + ");";
        db.execSQL(CREATE_MESSAGES_TABLE);
    }

    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_MESSAGES);

        // Create tables again
        onCreate(db);
    }

    /**
     * All CRUD(Create, Read, Update, Delete) Operations
     */

    // Adding new message
    void addMessage(MessageItem message) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_ID, 1);
        values.put(KEY_TEXT, "hello");
        values.put(KEY_FROM, "123");
        values.put(KEY_TO, "456");

        // Inserting Row
        db.insert(TABLE_MESSAGES, null, values);            
        db.close(); // Closing database connection
    }


    // Getting All Messages
    public List<MessageItem> getAllMessages() {
        List<MessageItem> messageList = new ArrayList<MessageItem>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_MESSAGES;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                MessageItem message = new MessageItem();
                message.setId(cursor.getString(0));
                message.setText(cursor.getString(1));
                message.setFrom(cursor.getString(2));
                message.setTo(cursor.getString(3));
                // Adding message to list
                messageList.add(message);
            } while (cursor.moveToNext());
        }

        // return message list
        return messageList;
    }

    // Getting messages Count
    public int getMessagesCount() {
        String countQuery = "SELECT  * FROM " + TABLE_MESSAGES;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        cursor.close();

        // return count
        return cursor.getCount();
    }

}

When inserting data into the table using the addMessage function from my app I get these errors:

07-19 11:35:59.129: E/SQLiteLog(1656): (1) near "to": syntax error 07-19 11:35:59.179: E/SQLiteDatabase(1656): Error inserting to=456 id=1 from=123 msg=hello 07-19 11:35:59.179: E/SQLiteDatabase(1656): android.database.sqlite.SQLiteException: near "to": syntax error (code 1): , while compiling: INSERT INTO messages(to,id,from,msg) VALUES (?,?,?,?)

NOTE: I temporarily put values in the ContentValues object to rule out a problem with the rest of my app.

Thanks

Answer 1

to and from are reserved keywords in SQL. To use them as identifiers, they should be in "" double quotes. Or better yet, just rename the columns.

You should already have gotten a syntax error in your onCreate() so apparently it has not been run. Uninstall your app so the old database file is removed and your onCreate() with appropriate column names is invoked.