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

Ian Newson · Accepted Answer · 2013-06-27 18:54:58Z

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.

edited Jun 27, 2013 at 18:54

answered Jun 27, 2013 at 18:46

Ian Newson

7,9572 gold badges58 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shivam Bhalla Over a year ago

thanks, i have fixed the errors caused due to the spaces. but now i am getting an error "cursor index out of bounds" on this line ` Contacts contacts=new Contacts(Integer.parseInt(c.getString(0)),c.getString(1),c.getString(2));`

faffaffaff · Accepted Answer · 2013-06-27 18:42:23Z

0

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

"create table "
"drop table if exists "

answered Jun 27, 2013 at 18:42

faffaffaff

3,57918 silver badges28 bronze badges

3 Comments

Shivam Bhalla Over a year ago

thanks. i have done that but i am now getting an error saying that there is "no such column named 'id' and 'number' " ?

faffaffaff Over a year ago

You need more spaces, it looks like you're creating a table with a column called IDINTEGER

Shivam Bhalla Over a year ago

thanks, i have fixed the errors caused due to the spaces. but now i am getting an error "cursor index out of bounds" on this line ` Contacts contacts=new Contacts(Integer.parseInt(c.getString(0)),c.getString(1),c.getString(2));`

Collectives™ on Stack Overflow

error while using SQLITE on android

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related