SQLiteException: Error inserting into database

Question

I have spend too much time to solve this problem. Hopefully you guys (or girls...) can help me!

LogCat says the error is 'near "set"' but I cant find the problem.

LogCat

10-20 17:21:05.969    2135-2135/com.sahota.breakfit E/SQLiteLog﹕ (1) near "set": syntax error
10-20 17:21:05.969    2135-2135/com.sahota.breakfit E/SQLiteDatabase﹕ Error inserting set_length=15 break_timer=60 exercise_name=BenchPress set=5
    android.database.sqlite.SQLiteException: near "set": syntax error (code 1): , while compiling: INSERT INTO db_table(set_length,break_timer,exercise_name,set) VALUES (?,?,?,?)
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
            at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
            at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
            at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
            at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
            at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1467)
            at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
            at com.sahota.breakfit.DBOpenHelper.insertRecord(DBOpenHelper.java:86)
            at com.sahota.breakfit.MainActivity.onCreate(MainActivity.java:51)
            at android.app.Activity.performCreate(Activity.java:5104)
            at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1080)
            at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2144)
            at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2230)
            at android.app.ActivityThread.access$600(ActivityThread.java:141)
            at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1234)
            at android.os.Handler.dispatchMessage(Handler.java:99)
            at android.os.Looper.loop(Looper.java:137)
            at android.app.ActivityThread.main(ActivityThread.java:5041)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:511)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560)
            at dalvik.system.NativeStart.main(Native Method)

MainActivity

public class MainActivity extends Activity {
    DBOpenHelper db = new DBOpenHelper(this);

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        try {
            String destPath = "/data/data/" + getPackageName() + "/databases/DBBreakFit";
            File f = new File(destPath);
            if (!f.exists()) {
                CopyDB(getBaseContext().getAssets().open("mydb"),
                        new FileOutputStream(destPath));
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        // ---add an assignment---

        try {
            db.open();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        long
                id = db.insertRecord("BenchPress", "60", "5", "15");
                id = db.insertRecord("Cablecrunch", "120", "6", "20");         
                id = db.insertRecord("Squat", "30", "2", "120");
        db.close();

        //---get all Records---
        try {
            db.open();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Cursor c = db.getAllRecords();
        if (c.moveToFirst()) {
            do {
                DisplayRecord(c);
            } while (c.moveToNext());
        }
        db.close();

        //---get a Record---
        try {
            db.open();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Cursor cursor = null;
        try {
            cursor = db.getRecord(2);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (cursor.moveToFirst())
            DisplayRecord(cursor);
        else
            Toast.makeText(this, "No Assignments found", Toast.LENGTH_LONG).show();
        db.close();


        //---update Record---
        try {
            db.open();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (db.updateRecord(1, "Hello Android", "2/19/2012", "DPR 224", "First Android Project"))
            Toast.makeText(this, "Update successful.", Toast.LENGTH_LONG).show();
        else
            Toast.makeText(this, "Update failed.", Toast.LENGTH_LONG).show();
        db.close();

        //---delete a Record---
        try {
            db.open();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (db.deleteContact(1))
            Toast.makeText(this, "Delete successful.", Toast.LENGTH_LONG).show();
        else
            Toast.makeText(this, "Delete failed.", Toast.LENGTH_LONG).show();
        db.close();


    }

    public void TestProfilesMain(View view) {
        Intent intent = new Intent(this, ProfilesMain.class);
        startActivity(intent);

    }

DBOpenHelper

package com.sahota.breakfit;

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

import java.sql.SQLException;

/**
 * Created by Harjit S. Sahota on 14-10-13.
 */
public class DBOpenHelper {
    public static final String LOG_CAT = "logcat";

    public static final String KEY_ROWID = "id";
    public static final String EXERCISE_NAME = "exercise_name";
    public static final String BREAK_TIMER = "break_timer";
    public static final String SET = "set";
    public static final String SET_LENGTH = "set_length";
    private static final String TAG = "DBOpenHelper";

    private static final String DATABASE_NAME = "DBName";
    private static final String DATABASE_TABLE = "db_table";
    private static final int DATABASE_VERSION = 2;

    private static String DATABASE_CREATE = "create table if not exists profile(id integer primary key autoincrement, " +
            "exercise_name VARCHAR not null, break_timer VARCHAR not null, set VARCHAR not null, set_length VARCHAR not null);";

    private final Context context;

    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;


    public DBOpenHelper(Context ctx) {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }

    private static class DatabaseHelper extends SQLiteOpenHelper {

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

        @Override
        public void onCreate(SQLiteDatabase db) {

            try {
                db.execSQL(DATABASE_CREATE);
            } catch (SQLiteException e) {
                e.printStackTrace();
            }

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version" + oldVersion + " to " + newVersion + " which will destroy all old data!");
            db.execSQL("DROP TABLE IF EXISTS profile");
            onCreate(db);

        }
    }

    public DBOpenHelper open() throws SQLException {
        db = DBHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        DBHelper.close();
    }

    public long insertRecord(String exerciseName, String breakTimer, String set, String setLength) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(EXERCISE_NAME, exerciseName);
        initialValues.put(BREAK_TIMER, breakTimer);
        initialValues.put(SET, set);
        initialValues.put(SET_LENGTH, setLength);

        return db.insert(DATABASE_TABLE, null, initialValues);
    }

    public boolean deleteContact(long rowID) {
        return db.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowID, null) > 0;
    }

    public Cursor getAllRecords() {
        return db.query(DATABASE_TABLE, new String[] {KEY_ROWID, EXERCISE_NAME, BREAK_TIMER, SET, SET_LENGTH}, null, null, null, null, null);
    }

    public Cursor getRecord(long rowId) throws SQLException {
        Cursor mCursor =
                db.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
                        EXERCISE_NAME, BREAK_TIMER, SET, SET_LENGTH},
                        KEY_ROWID + "=" + rowId, null, null, null, null, null);
        if (mCursor != null ) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

    public boolean updateRecord(long rowId, String exerciseName, String breakTimer, String set, String setLength) {
        ContentValues args = new ContentValues();
        args.put(EXERCISE_NAME, exerciseName);
        args.put(BREAK_TIMER, breakTimer);
        args.put(SET, set);
        args.put(SET_LENGTH, setLength);

        return db.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) >0;
    }
}

ProfilesCreate

public class ProfilesCreate extends Activity {
    DBOpenHelper db = new DBOpenHelper(this);

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.profilecreate);

    }

    public void addExercise(View view) {
        //Getting data from Profiles create-form
        EditText exerciseName = (EditText) findViewById(R.id.exercisename_edittext);
        EditText breakTimer = (EditText) findViewById(R.id.breaktimer_edittext);
        EditText set = (EditText) findViewById(R.id.set_edittext);
        EditText setLength = (EditText) findViewById(R.id.set_edittext);

        try {
            db.open();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        long id = db.insertRecord(exerciseName.getText().toString(),
                breakTimer.getText().toString(),
                set.getText().toString(),
                setLength.getText().toString());
        db.close();

        //After submitting, replace the text with an empty character.
        exerciseName.setText("");
        breakTimer.setText("");
        set.setText("");
        setLength.setText("");

        Toast.makeText(ProfilesCreate.this, "Exercise Added!", Toast.LENGTH_LONG).show();
    }

    public void addExerciseButton(View view) {
        Intent intent = new Intent(this, ProfilesShow.class);
        startActivity(intent);
    }
}

Andy Res · Accepted Answer · 2013-10-20 15:47:49Z

1

set is a reserved keyword in sql, but you are trying to use it as a name for a table field.

answered Oct 20, 2013 at 15:47

Andy Res

16.1k6 gold badges63 silver badges98 bronze badges

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

2 Comments

ymerdrengene Over a year ago

Thanks Andy! That fixed that problem. However a new appeared. LogCat says: E/SQLiteLog﹕ (1) no such table: db_table E/SQLiteDatabase﹕ Error inserting set_length=15 sets=5 break_timer=60 exercise_name=BenchPress android.database.sqlite.SQLiteException: no such table: db_table (code 1): , while compiling: INSERT INTO db_table(set_length,sets,break_timer,exercise_name) VALUES (?,?,?,?)

Andy Res Over a year ago

It says that there's no such table "db_table", and this should be sufficient to give you a hint why it crashes. And indeed, if you take a closer look in the DBOpenHelper class you may notice that DATABASE_TABLE field is declared, but not used when creating the table. Instead, you create the profile table: create table if not exists profile(...

Sarim Sidd · Accepted Answer · 2013-10-20 15:50:38Z

1

set is a reserved sql keyword, if you like to you it as a Column name then enclose it in grave accents Like

`set`

Hope this helps

answered Oct 20, 2013 at 15:50

Sarim Sidd

2,1762 gold badges22 silver badges31 bronze badges

Comments

Abhishek V · Accepted Answer · 2013-10-20 16:10:14Z

1

Table name in DATABASE_CREATE string is profile. But you have declared DATABASE_TABLE = "db_table" and using it in insert function.So declare it properly as DATABASE_TABLE = "profile"

answered Oct 20, 2013 at 16:10

Abhishek V

12.5k7 gold badges55 silver badges65 bronze badges

3 Comments

ymerdrengene Over a year ago

Thank you Abhishek! This help solving some of the problem, however a new error appeared: 1507-1507/com.sahota.breakfit E/AndroidRuntime﹕ FATAL EXCEPTION: main java.lang.RuntimeException: Unable to start activity ComponentInfo{com.sahota.breakfit/com.sahota.breakfit.MainActivity}: android.database.sqlite.SQLiteException: no such table: profile (code 1): , while compiling: SELECT id, exercise_name, break_timer, sets, set_length FROM profile

Abhishek V Over a year ago

Change your DATABASE_VERSION to 3 and run it again!

Abhishek V Over a year ago

Oh...can you edit your question with the corrections you made..like I assume u replaced set with sets

Collectives™ on Stack Overflow

SQLiteException: Error inserting into database

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related