android.database.sqlite.SQLiteException: near "CREATE_RECORD_TABLE"

Question

I'm quite new to android programming and getting an error I couldn't figure out. I basicly wrote a code that let the user send his current coordinates to a local SQLite database. this crush happens when I'm starting to insert the data into the dataabse. I keep getting-

android.database.sqlite.SQLiteException: near "CREATE_RECORD_TABLE": syntax error (code 1): , while compiling: CREATE_RECORD_TABLE ( id INTEGER PRIMARY KEY AUTOINCREMENT, latitude TEXT, longtitude TEXT, accuracy TEXT, time TEXT )

And i'm pretty sure it happens due alot of confusion I have with the different types. I'll be happy if you'll be able to take a look and pinpoint me to the right direction.

the record (table) activity :

package com.program.android.taskir;


public class Record {

    //private variables
    private int id;
    private double pLong;
    private double pLat;
    private float pAcc;
    private long pTime;

    public Record(){}
    // Empty constructor

    // constructor
    public Record( double pLong, double pLat, float pAcc, long pTime){
        super();
        this.pLong = pLong;
        this.pLat= pLat;
        this.pAcc= pAcc;
        this.pTime= pTime;
    }

    @Override
    public String toString() {
        return "Record [id=" + id + ", Longtitude=" + pLong + ", Latitude=" + pLat + ", Accuracy" + pAcc + ", Time" +pTime
                + "]";
    }


    // getting ID
    public int getID(){
        return this.id;
    }

    // setting id
    public void setID(int id){
        this.id = id;
    }

    // getting pLong
    public double getpLong(){
        return this.pLong;
    }

    // setting pLong
    public void setpLong(double pLong){
        this.pLong = pLong;
    }

    // getting pLat
    public double getpLat(){
        return this.pLat;
    }

    // setting pLat
    public void setpLat(double pLat){
        this.pLat = pLat;
    }

    // getting pAcc
    public float getpAcc(){
        return this.pAcc;
    }

    // setting pAcc
    public void setpAcc(float pAcc){
        this.pAcc = pAcc;
    }

    // getting pTime
    public long getpTime(){
        return this.pTime;
    }

    // setting pTime
    public void setpTime(long pTime){
        this.pTime = pTime;
    }
}

the SQlite activity :

package com.program.android.taskir;

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;

import java.util.LinkedList;
import java.util.List;

public class MySQLiteHelper extends SQLiteOpenHelper {

    // Database Version
    private static final int DATABASE_VERSION = 1;

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

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        // SQL statement to create record table
        String CREATE_RECORD_TABLE = "CREATE_RECORD_TABLE ( " +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "latitude TEXT, "+
                "longtitude TEXT, "+
                "accuracy TEXT, "+
                "time TEXT )";

        // create books table
        db.execSQL(CREATE_RECORD_TABLE);
    }


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

        // create fresh record table
        this.onCreate(db);
    }

    // Books table name
    private static final String TABLE_RECORD = "record";

    // Books Table Columns names
    private static final String KEY_ID = "id";
    private static final String KEY_LONG = "longtitude";
    private static final String KEY_LAT = "latitude";
    private static final String KEY_ACC = "accuracy";
    private static final String KEY_TIME = "time";

    private static final String[] COLUMNS = {KEY_ID,KEY_LONG,KEY_LAT,KEY_ACC,KEY_TIME};

    public void addRecord(Record record){
        //for logging
        Log.d("addBook", record.toString());

        // 1. get reference to writable DB
        SQLiteDatabase db = this.getWritableDatabase();

        // 2. create ContentValues to add key "column"/value
        ContentValues values = new ContentValues();
        values.put(KEY_LONG, record.getpLong());
        values.put(KEY_LAT, record.getpLat());
        values.put(KEY_ACC, record.getpAcc());
        values.put(KEY_TIME, record.getpTime());

        // 3. insert
        db.insert(TABLE_RECORD, // table
                null, //nullColumnHack
                values); // key/value -> keys = column names/ values = column values

        // 4. close
        db.close();
    }

    public Record getRecord(int id){

        // 1. get reference to readable DB
        SQLiteDatabase db = this.getReadableDatabase();

        // 2. build query
        Cursor cursor =
                db.query(TABLE_RECORD, // a. table
                        COLUMNS, // b. column names
                        " id = ?", // c. selections
                        new String[] { String.valueOf(id) }, // d. selections args
                        null, // e. group by
                        null, // f. having
                        null, // g. order by
                        null); // h. limit

        // 3. if we got results get the first one
        if (cursor != null)
            cursor.moveToFirst();

        // 4. build book object
        Record record = new Record();
        record.setID(Integer.parseInt(cursor.getString(0)));
        record.setpLat(cursor.getDouble(1));
        record.setpLong(cursor.getDouble(2));
        record.setpAcc(cursor.getFloat(2));
        record.setpTime(cursor.getLong(2));

        //log
        Log.d("getBook("+id+")", record.toString());

        // 5. return book
        return record;
    }

    public List<Record> getAllRecords() {
        List<Record> records = new LinkedList<Record>();

        // 1. build the query
        String query = "SELECT  * FROM " + TABLE_RECORD;

        // 2. get reference to writable DB
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(query, null);

        // 3. go over each row, build book and add it to list
        Record record = null;
        if (cursor.moveToFirst()) {
            do {
                record = new Record();
                record.setID(Integer.parseInt(cursor.getString(0)));
                record.setpLat(cursor.getDouble(1));
                record.setpLong(cursor.getDouble(2));
                record.setpAcc(cursor.getFloat(2));
                record.setpTime(cursor.getLong(2));


                // Add book to books
                records.add(record);
            } while (cursor.moveToNext());
        }

        Log.d("getAllRecords()", record.toString());

        // return books
        return records;
    }

    public int UpdateRecords(Record record) {

        // 1. get reference to writable DB
        SQLiteDatabase db = this.getWritableDatabase();

        // 2. create ContentValues to add key "column"/value
        ContentValues values = new ContentValues();
        values.put("Latitude", record.getpLat()); //
        values.put("Longtitude", record.getpLong());
        values.put("Accuracy", record.getpAcc());
        values.put("Time", record.getpTime());

        // 3. updating row
        int i = db.update(TABLE_RECORD, //table
                values, // column/value
                KEY_ID+" = ?", // selections
                new String[] { String.valueOf(record.getID()) }); //selection args

        // 4. close
        db.close();

        return i;

    }

    public void deleteRecords(Record record) {

        // 1. get reference to writable DB
        SQLiteDatabase db = this.getWritableDatabase();

        // 2. delete
        db.delete(TABLE_RECORD, //table name
                KEY_ID+" = ?",  // selections
                new String[] { String.valueOf(record.getID()) }); //selections args

        // 3. close
        db.close();

        //log
        Log.d("deleteBook", record.toString());

    }

}

The main activity :

package com.program.android.taskir;

import android.content.Context;
import android.content.Intent;
import android.location.Location;
import android.location.LocationListener;
import android.location.LocationManager;
import android.os.Bundle;
import android.support.v7.app.ActionBarActivity;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.CompoundButton;
import android.widget.Switch;
import android.widget.TextView;
import android.widget.Toast;


public class GpsPage extends ActionBarActivity {

    TextView tz;
    TextView textLat;
    TextView textLong;


    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_gps_page);

        final MySQLiteHelper db = new MySQLiteHelper(this);

        tz = (TextView) findViewById(R.id.textviewtz);
        Intent intent = getIntent();
        String TeudatZeut = intent.getStringExtra("tz");
        tz.setText("תעודת זהות " + TeudatZeut);

        Switch onOffSwitch = (Switch) findViewById(R.id.switch1);
        onOffSwitch.setChecked(false);
        onOffSwitch.setOnCheckedChangeListener(new CompoundButton.OnCheckedChangeListener() {
            @Override
            public void onCheckedChanged(CompoundButton buttonView, boolean isChecked) {

                    Toast.makeText(getApplicationContext(), "שולח נתונים לשרת", Toast.LENGTH_SHORT).show();
                    textLat = (TextView) findViewById(R.id.textLat);
                    textLong = (TextView) findViewById(R.id.textLong);

                    LocationManager lm = (LocationManager) getSystemService(Context.LOCATION_SERVICE);
                    LocationListener ll = new myLocationListener();
                    lm.requestLocationUpdates(LocationManager.GPS_PROVIDER, 0, 0, ll);
                }


            class myLocationListener implements LocationListener {

                @Override
                public void onLocationChanged(Location location) {
                    if (location != null) {
                        double pLong = location.getLongitude();
                        double pLat = location.getLatitude();
                        float pAcc = location.getAccuracy();
                        long pTime = location.getTime();
                        textLat.setText(Double.toString(pLat));
                        textLong.setText(Double.toString(pLong));
                        db.addRecord(new Record(pLong, pLat, pAcc, pTime));
                    }


                }

                @Override
                public void onStatusChanged(String provider, int status, Bundle extras) {

                }

                @Override
                public void onProviderEnabled(String provider) {

                }

                @Override
                public void onProviderDisabled(String provider) {

                }
            }
        }


        );


        /**
         * CRUD Operations
         * */

        //add record


    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.menu_gps_page, menu);
        return true;
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        // Handle action bar item clicks here. The action bar will
        // automatically handle clicks on the Home/Up button, so long
        // as you specify a parent activity in AndroidManifest.xml.
        int id = item.getItemId();

        //noinspection SimplifiableIfStatement
        if (id == R.id.action_settings) {
            return true;
        }

        return super.onOptionsItemSelected(item);
    }


}

logcat report -

12-29 12:10:29.400: D/addBook(894): Record [id=0, Longtitude=1.0, Latitude=3.0, Accuracy20.0, Time1419847204000]
12-29 12:10:29.470: D/addBook(894): Record [id=0, Longtitude=1.0, Latitude=3.0, Accuracy20.0, Time1419847204000]
12-29 12:10:29.510: D/addBook(894): Record [id=0, Longtitude=1.0, Latitude=3.0, Accuracy20.0, Time1419847204000]
12-29 12:10:29.540: D/dalvikvm(894): GC_FOR_ALLOC freed 112K, 9% free 4333K/4728K, paused 28ms, total 34ms
12-29 12:10:29.580: D/addBook(894): Record [id=0, Longtitude=1.0, Latitude=3.0, Accuracy20.0, Time1419847204000]
12-29 12:10:29.620: D/addBook(894): Record [id=0, Longtitude=1.0, Latitude=3.0, Accuracy20.0, Time1419847204000]
12-29 12:10:33.860: D/dalvikvm(385): GC_FOR_ALLOC freed 761K, 21% free 6394K/8004K, paused 62ms, total 70ms

gsanskar · Accepted Answer · 2014-12-29T16:21:05.730

0

The mistake you made is in the SQlite Activity's onCreate(SQLiteDatabase db) method. Your database query should look like below :

    String CREATE_RECORD_TABLE = "CREATE TABLE RECORD ( " +
            "id INTEGER PRIMARY KEY AUTOINCREMENT, " + 
            "latitude TEXT NOT NULL, "+ 
            "longtitude TEXT NOT NULL, "+ 
            "accuracy TEXT NOT NULL, "+ 
            "time TEXT NOT NULL )";

For a full fledged example on Databases , refer Android official training page : http://developer.android.com/training/notepad/index.html

OR Codeproject : http://www.codeproject.com/Articles/524204/Simple-notepad-for-Android

edited Dec 29 '14 at 16:21

answered Dec 29 '14 at 16:15

gsanskar

673
5
10

Thank you, it worked. the only problem is that the ID is not autocincremented, any idea ? – GeoRay Dec 29 '14 at 17:00
Could you please post your logcat here?, please accept the answer if it solved your root cause :-) – gsanskar Dec 29 '14 at 17:05

android.database.sqlite.SQLiteException: near "CREATE_RECORD_TABLE"

1 Answers1

Linked