CODEWITHSUNDEEP
androiddatabasesqlitelogcatandroid-sqlite
Jaiesh_bhai
Jaiesh_bhai

Reputation: 1814

Android LogCat sqlite syntax error meaning

I get the log cat error below:

 11-18 21:37:49.700: E/AndroidRuntime(19122): Caused by: android.database.sqlite.SQLiteException: no such column: Home1: , while compiling: SELECT _id FROM projects WHERE _name like Home1
11-18 21:37:49.700: E/AndroidRuntime(19122):    at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
11-18 21:37:49.700: E/AndroidRuntime(19122):    at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:68)
11-18 21:37:49.700: E/AndroidRuntime(19122):    at android.database.sqlite.SQLiteProgram.compileSql(SQLiteProgram.java:143)
11-18 21:37:49.700: E/AndroidRuntime(19122):    at android.database.sqlite.SQLiteProgram.compileAndbindAllArgs(SQLiteProgram.java:361)
11-18 21:37:49.700: E/AndroidRuntime(19122):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:127)
11-18 21:37:49.700: E/AndroidRuntime(19122):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:94)
11-18 21:37:49.700: E/AndroidRuntime(19122):    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:53)
11-18 21:37:49.700: E/AndroidRuntime(19122):    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:47)
11-18 21:37:49.700: E/AndroidRuntime(19122):    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1697)
11-18 21:37:49.700: E/AndroidRuntime(19122):    at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1582)
11-18 21:37:49.700: E/AndroidRuntime(19122):    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1538)
11-18 21:37:49.700: E/AndroidRuntime(19122):    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1618)
11-18 21:37:49.700: E/AndroidRuntime(19122):    at com.example.home_painter.DatabaseHandler.checkName(DatabaseHandler.java:334)
11-18 21:37:49.700: E/AndroidRuntime(19122):    at com.example.home_painter.MainActivity.addProject(MainActivity.java:37)
11-18 21:37:49.700: E/AndroidRuntime(19122):    ... 14 more

Is this due to "SELECT _id FROM projects WHERE _name like Home1" is not the correct syntax? I cannot seem to figure out the reason for this error.

Here is some code from my sqldatabaseHelper file: Variables:

  // table name
private static final String TABLE_PROJECTS = "projects";
private static final String TABLE_IMAGES = "images";
private static final String TABLE_COLOR_MAP = "colorMap";
private static final String TABLE_COLORS_COLLECTION = "collection";

// Project Table columns names
private static final String KEY_ID = "_id"; // Primary, integer
private static final String KEY_NAME = "_name"; // Unique, text

OnCreate method: 

  public void onCreate(SQLiteDatabase db) {

    // FOREIGN KEYS
    String FOREIGN_KEYS = "PRAGMA foreign_keys = ON;";

    // PROJECTS TABLE
    String CREATE_PROJECTS_TABLE = "CREATE TABLE " + TABLE_PROJECTS 
            + "(" + KEY_ID + " INTEGER NOT NULL, " + KEY_NAME + " TEXT NOT NULL, " +" PRIMARY KEY("
            + KEY_ID + "), UNIQUE( "+ KEY_NAME + "))";

    // IMAGES TABLE
    String CREATE_IMAGES_TABLE = "CREATE TABLE " + TABLE_IMAGES + "(" + KEY_IM_ID + " INTEGER NOT NULL, "
            + IM_URI + " TEXT, PRIMARY KEY(" + KEY_IM_ID+ "))";

    // COLOR MAP TABLE
    String CREATE_COLOR_MAP_TABLE = "CREATE TABLE " + TABLE_COLOR_MAP + "(" + PROJ_COLOR_ID + " INTEGER NOT NULL, "
            + COLLECTION_COLOR_ID + " INTEGER NOT NULL, FOREIGN KEY(" + PROJ_COLOR_ID + ") REFERENCES " 
            + TABLE_PROJECTS + "(" + KEY_ID + "), FOREIGN KEY(" + COLLECTION_COLOR_ID + ") REFERENCES " + TABLE_COLORS_COLLECTION
            + "(" + KEY_ID_COLLECT + "))";

    // COLLECTION COLOR TABLE
    String CREATE_COLORS_COLLECTION = "CREATE TABLE " + TABLE_COLORS_COLLECTION + "("
            + KEY_ID_COLLECT + " INTEGER NOT NULL, " + COLLECTION_HEX + " TEXT NOT NULL, "
            + COLLECTION_NAME + " TEXT NOT NULL, PRIMARY KEY(" + KEY_ID_COLLECT + "), UNIQUE("
            + COLLECTION_NAME + "))";
    /*System.out.println(CREATE_PROJECTS_TABLE);
    System.out.println(CREATE_IMAGES_TABLE);
    System.out.println(CREATE_COLORS_TABLE);*/

    db.execSQL(FOREIGN_KEYS);
    db.execSQL(CREATE_PROJECTS_TABLE);
    db.execSQL(CREATE_IMAGES_TABLE);
    db.execSQL(CREATE_COLORS_COLLECTION);
    db.execSQL(CREATE_COLOR_MAP_TABLE);


}

onUpgrade method:

   public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    //Drop older tables if existing
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_PROJECTS);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_IMAGES);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_COLORS_COLLECTION);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_COLOR_MAP);
    // Create tables again
    onCreate(db);
}

Code i thought was relevant from a method where I get information from project.java to put into database: 

values_PT.put(KEY_NAME, project.getName()); // project name
long project_id = db.insert(TABLE_PROJECTS, null, values_PT); // insert into database and save id for use

Code for checkName method: 

  public boolean checkName(String name) {

    boolean resultName = false;
    SQLiteDatabase db = this.getReadableDatabase();

    Cursor c = db.query(TABLE_PROJECTS, new String[]{KEY_ID}, KEY_NAME + " like " + name, null, null, null, null);

    if (c != null) {

        resultName = true;
    }

    return resultName;
}

Upvotes: 1

Views: 660

Answers (3)

mu is too short
mu is too short

Reputation: 434775

The SQL you're sending into SQLite looks like this:

SELECT _id FROM projects WHERE _name like Home1

SQLite is interpreting your Home1 as a column name because, well, an unquoted string-ish value is an identifier in SQL. That's where your "unknown column" error comes from. You want something like this getting down to SQLite:

SELECT _id FROM projects WHERE _name like 'Home1'

or (probably) better:

SELECT _id FROM projects WHERE _name = 'Home1'

If you're not using _ or % wildcards then LIKE is usually just an overcomplicated and expensive version of =.

How do we fix this? You could try manually quoting name but that's just silly in 2012, we have placeholders to push the quoting and escaping issues down into the database where they belong. I think you want to use something like this db.query call:

db.query(TABLE_PROJECTS, new String[]{KEY_ID}, KEY_NAME + "=?", new String[] {name}, null, null, null);

The ? placeholder will be replaced by the the value of name (properly quoted and escaped). Using a placeholder also helps protect you from SQL injection problems so it is a good habit to acquire.

Upvotes: 3

Sardor Dushamov
Sardor Dushamov

Reputation: 1667

your query must be:

Cursor c = db.query(TABLE_PROJECTS, new String[]{KEY_ID}, KEY_NAME + " like ?",new String[]{"'"+name+"%'"}, null, null, null);

Upvotes: 0

Marcin S.
Marcin S.

Reputation: 11191

You use query:

"SELECT _id FROM projects WHERE _name like Home1"

However in your table PROJECTS there are no _id and _name columns. You have key_id and key_name. Therefore change the query to:

"SELECT key_id FROM projects WHERE key_name like Home1"

Upvotes: 0

Related Questions