error while using SQLITE on android

Question

hi i am trying to create a DB and then setting text view value based on the value returned from the database. i am getting an error "SYNTAX ERROR(code1) " here is my code.

`package com.example.dbex;

import java.sql.SQLOutput;
import java.sql.SQLPermission;

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

public class DBHandler extends SQLiteOpenHelper {

    public static final int DATABASE_VERSION=1;
    public static final String DATABASE_NAME="contactsmanager";
    public static final String TABLE_CONTACTS="contacts";
    public static final String KEY_ID="id";
    public static final String KEY_NAME="name";
    public static final String KEY_NUMBER="number";
    public DBHandler(Context context) {
        super(context,DATABASE_NAME, null, DATABASE_VERSION);
        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        String CREATE_TABLE_CONTACTS="CREATE TABLE"+TABLE_CONTACTS+"("+KEY_ID+"INTEGER PRIMARY KEY,"+KEY_NAME+"TEXT,"+KEY_NUMBER+"TEXT"+")";
        db.execSQL(CREATE_TABLE_CONTACTS);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS"+TABLE_CONTACTS);
        onCreate(db);
    }

    public void addContact(Contacts contacts)
    {
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues values=new ContentValues();
        values.put(KEY_NAME, contacts.getname());
        values.put(KEY_NUMBER, contacts.getnumber());
        db.insert(TABLE_CONTACTS,null,values);
        db.close();
    }

    public Contacts getContact(int id)
    {
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor c=db.query(TABLE_CONTACTS, new String[]{KEY_ID,KEY_NAME,KEY_NUMBER},KEY_ID+"=?",new String[]{String.valueOf(id)}, null, null, null, null);
        if(c!=null)
        {
            c.moveToFirst();
        }
        Contacts contacts=new Contacts(Integer.parseInt(c.getString(0)),c.getString(1),c.getString(2));
        return contacts;
    }

    public void deleteContact(Contacts contacts)
    {
        SQLiteDatabase db=this.getWritableDatabase();
        db.delete(TABLE_CONTACTS, KEY_ID+"=?", new String[]{String.valueOf(contacts.getID())});
        ``db.close();
    }


}
`

please help me rectify my mistake in this. Thanks

Answer 1

You need a space in the SQL in your onUpgrade method:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub
    db.execSQL("DROP TABLE IF EXISTS "+TABLE_CONTACTS);
    onCreate(db);
}

Currently the executed SQL is: DROP TABLE IF EXISTScontacts

It needs to be: DROP TABLE IF EXISTS contacts

This also applies to a few places in your onCreate method:

@Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub
    String CREATE_TABLE_CONTACTS="CREATE TABLE "+TABLE_CONTACTS+"("+KEY_ID+" INTEGER PRIMARY KEY,"+KEY_NAME+" TEXT,"+KEY_NUMBER+" TEXT"+")";
    db.execSQL(CREATE_TABLE_CONTACTS);
}

Something which helps me track down errors like this is to add a try/catch block and throw your own exception which includes the generated SQL, e.g.:

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        String CREATE_TABLE_CONTACTS="CREATE TABLE "+TABLE_CONTACTS+"("+KEY_ID+" INTEGER PRIMARY KEY,"+KEY_NAME+" TEXT,"+KEY_NUMBER+" TEXT"+")";
try {
        db.execSQL(CREATE_TABLE_CONTACTS);
} catch (SQLException sqlException) {
throw new SQLException("Error executing SQL: '" + CREATE_TABLE_CONTACTS + "', message: " + sqlException.getMessage(), sqlException);
}
    }

This allows you to see the error message as well as the SQL which caused it. If you do this it's recommended to create your own execSQL method which includes this code. You could even create your own wrapper around SQLiteDatabase and add this logic to your own overridden execSQL.

Answer 2

You need a space after the "create table" and "drop table if exists":

"create table "
"drop table if exists "