Error: sqlite returned: error code = 1, msg = no such table

Question

I am working on a small app which stores the place latitude and longitude in SQLite database. I have three classes as follows:

AddPlaceActyivity.java

import android.app.Activity;
import android.app.Fragment;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;

import com.example.rememberthislocation.R;




public class AddPlaceActivity extends Activity{

    Button btn_remember,btn_maps;
    Fragment fr;
    Button addlocation;
    EditText locationname;
    String mylocationname,mylatitude,mylongitude;

    @Override
     protected void onCreate(Bundle savedInstanceState){
        super.onCreate(savedInstanceState);
        Log.d("my","In oncreateview");

        setContentView(R.layout.remember_fragment);


                addlocation = (Button)findViewById(R.id.btn_addlocation);
                 locationname = (EditText)findViewById(R.id.locationname);


                  mylocationname = locationname.getText().toString();
                     Log.d("my","value 1 taken");

                    mylatitude = "0.0";
                    mylongitude = "0.0";

                      Log.d("my", "data values taken"); 


                  addlocation.setOnClickListener(new View.OnClickListener() {

                    @Override
                    public void onClick(View v) {
                        // TODO Auto-generated method stub
                        MySqliteHelper db = new MySqliteHelper(getApplicationContext());

                        db.addPlaces(new Place(mylocationname,mylatitude,mylongitude));   

                    }
                });







        }






}

MySqliteHelper.java

    import android.content.ContentValues;
    import android.content.Context;
    import android.database.sqlite.SQLiteDatabase;
    import android.database.sqlite.SQLiteException;

    import android.database.sqlite.SQLiteOpenHelper;
    import android.util.Log;

    public class MySqliteHelper extends SQLiteOpenHelper{

        private static final int DATABASE_VERSION = 1;

        private static final String DATABASE_NAME = "contactsManager";

        private static final String KEY_ID = "id";
        private static final String KEY_PLACENAME = "placename";
        private static final String KEY_LATITUDE = "latitude";
        private static final String KEY_LONGITUDE = "longitude";

        private static final String TABLE_NAME = "places";


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


        }

        @Override
        public void onCreate(SQLiteDatabase db) {



            //actual query = create table places (id primary key autoincrement, placename taxt, latitude real, longitude real);
               String query =  "CREATE TABLE" +TABLE_NAME + "( " + KEY_ID+
                        "INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_PLACENAME+ 
                        "TEXT, "+KEY_LATITUDE+
                        "REAL, "+KEY_LONGITUDE+ "REAL)";

               try {
                    db.execSQL(query);
                    Log.d("my", "Successfully created table: " + query);
                } catch (SQLiteException e) {
                    e.printStackTrace();
                }   

        }



        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("DROP TABLE IF EXISTS places");

            this.onCreate(db);
        }


        public void addPlaces(Place place)
        {

            SQLiteDatabase db = this.getWritableDatabase();

            ContentValues convalues = new ContentValues();



            convalues.put(KEY_PLACENAME,place.getname());
            convalues.put(KEY_LATITUDE,place.getlatitude());
            convalues.put(KEY_LONGITUDE,place.getlongitude());

              db.insert(TABLE_NAME, null, convalues);
              Log.d("my","db.insert(TABLE_NAME, null, convalues)");
              Log.d("my", "Values inserted");
              db.close();

        }




}

Place.java

public class Place {

    String mname,mlatitude,mlongitude,mstring;
    public Place(String name, String latitude, String longitude)
        {

          mname = name;
          mlatitude = latitude;
          mlongitude = longitude;


    }

    public Place() {
        // TODO Auto-generated constructor stub
    }

    public void setname(String placename)
    {
        mname = placename;


    }

    public String getname()
    {
        return mname;


    }

    public void setlatitude(String latitude)
    {
        mlatitude=latitude;


    }

    public String getlatitude()
    {
        return mlatitude;


    }


    public void setlongitude(String longitude)
    {
        mlongitude = longitude;


    }

    public String getlongitude()
    {
        return mlongitude;


    }
}

And my logcat log:

11-20 17:40:02.770: D/my(10604): In oncreateview
11-20 17:40:02.890: D/my(10604): acivity layout initialized
11-20 17:40:02.950: I/PGA(10604): New SOCKET connection: berthislocation (pid 10604, tid 10604)
11-20 17:40:16.510: D/my(11196): in remember's onclick
11-20 17:40:16.540: D/my(11196): intent started
11-20 17:40:16.560: D/my(11196): In oncreateview
11-20 17:40:16.560: D/my(11196): value 1 taken
11-20 17:40:16.560: D/my(11196): data values taken
11-20 17:40:27.290: I/SqliteDatabaseCpp(11196): sqlite returned: error code = 1, msg = no such table: places, db=/data/data/com.example.rememberthislocation/databases/contactsManager
11-20 17:40:27.290: E/SQLiteDatabase(11196): Error inserting longitude=0.0 latitude=0.0 placename=
11-20 17:40:27.290: E/SQLiteDatabase(11196): android.database.sqlite.SQLiteException: no such table: places: , while compiling: INSERT INTO places(longitude,latitude,placename) VALUES (?,?,?)
11-20 17:40:27.290: E/SQLiteDatabase(11196):    at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
11-20 17:40:27.290: E/SQLiteDatabase(11196):    at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:68)
11-20 17:40:27.290: E/SQLiteDatabase(11196):    at android.database.sqlite.SQLiteProgram.compileSql(SQLiteProgram.java:143)
11-20 17:40:27.290: E/SQLiteDatabase(11196):    at android.database.sqlite.SQLiteProgram.compileAndbindAllArgs(SQLiteProgram.java:361)
11-20 17:40:27.290: E/SQLiteDatabase(11196):    at android.database.sqlite.SQLiteStatement.acquireAndLock(SQLiteStatement.java:260)
11-20 17:40:27.290: E/SQLiteDatabase(11196):    at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:112)
11-20 17:40:27.290: E/SQLiteDatabase(11196):    at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1718)
11-20 17:40:27.290: E/SQLiteDatabase(11196):    at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1591)
11-20 17:40:27.290: E/SQLiteDatabase(11196):    at com.example.rememberthisplace.MySqliteHelper.addPlaces(MySqliteHelper.java:74)
11-20 17:40:27.290: E/SQLiteDatabase(11196):    at com.example.rememberthisplace.AddPlaceActivity$1.onClick(AddPlaceActivity.java:52)
11-20 17:40:27.290: E/SQLiteDatabase(11196):    at android.view.View.performClick(View.java:3511)
11-20 17:40:27.290: E/SQLiteDatabase(11196):    at android.view.View$PerformClick.run(View.java:14105)
11-20 17:40:27.290: E/SQLiteDatabase(11196):    at android.os.Handler.handleCallback(Handler.java:605)
11-20 17:40:27.290: E/SQLiteDatabase(11196):    at android.os.Handler.dispatchMessage(Handler.java:92)
11-20 17:40:27.290: E/SQLiteDatabase(11196):    at android.os.Looper.loop(Looper.java:137)
11-20 17:40:27.290: E/SQLiteDatabase(11196):    at android.app.ActivityThread.main(ActivityThread.java:4424)
11-20 17:40:27.290: E/SQLiteDatabase(11196):    at java.lang.reflect.Method.invokeNative(Native Method)
11-20 17:40:27.290: E/SQLiteDatabase(11196):    at java.lang.reflect.Method.invoke(Method.java:511)
11-20 17:40:27.290: E/SQLiteDatabase(11196):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:825)
11-20 17:40:27.290: E/SQLiteDatabase(11196):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:592)
11-20 17:40:27.290: E/SQLiteDatabase(11196):    at dalvik.system.NativeStart.main(Native Method)

Clearly the error is in MySqliteHelper.java

Error: sqlite returned: error code = 1, msg = no such table: places

So, my MySqliteHelper class's oncreate() isn't called. I tried for hours..but can't find the solution. Can anyone help me?

Answer 1

You have bugs in your onCreate() that are hidden by the catch:

String query =  "CREATE TABLE" +TABLE_NAME + "( " + KEY_ID+
                    "INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_PLACENAME+ 
                    "TEXT, "+KEY_LATITUDE+
                    "REAL, "+KEY_LONGITUDE+ "REAL)";

Missing whitespace, change to:

String query =  "CREATE TABLE " +TABLE_NAME + "( " + KEY_ID+
                    " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_PLACENAME+ 
                    " TEXT, "+KEY_LATITUDE+
                    " REAL, "+KEY_LONGITUDE+ " REAL)";

Remove the try-catch. If there's a problem, the framework should be thrown an exception. If onCreate() returns normally, the framework thinks the database was set up successfully. onCreate() is only called once when the database is first set up.

Uninstall your app or clear its data to get rid of the old empty database file and to get onCreate() rerun.

Further reading: When is SQLiteOpenHelper onCreate() / onUpgrade() run?