Android SQLite null pointer exception on opening database

Question

I am having trouble executing my SQLite app on adroid. For testing purpose I made it as simple as possible. A query that adds data into the database, and when the user clicks on "View Schedule" button, all the data in the DB should be displayed as a TOAST message. "The MySQLitehelper.java" file has all the Database related code and the "SelectOptions.java" has the code that has the button to "View Schedule". All the right imports have been added hence I have omitted them here.

On Tracing LogCat, I see that there is a null pointer exception when you call the helper.open() method of MySQLitehelper class. I tried printing statements to track it and figured out that the program shuts down the following lines:

  public MySQLitehelper open() throws SQLException
 {
    System.out.println("Inside open function");
     db = dbhelper.getReadableDatabase();  // even tried helper.getWritableDatabase()
    return this;
 }

and stops when the line

 db = dbhelper.getReadableDatabase(); 

is encountered. Here is my code:

/* --------------MySQLitehelper.java code -----------------*/

public class MySQLitehelper {

//public static final String TABLE_COMMENTS = "comments";
  public static final String COLUMN_ID = "GWid";
  public static final String COLUMN_DATE = "date";
  public static final String COLUMN_LOCATION = "location";
  public static final String COLUMN_TIME = "time";

  public static final String TABLE_NAME = "UPDTable";

  private static final String DATABASE_NAME = "UPDdb";
  private static final int DATABASE_VERSION = 1;

  private final Context context;


  // Database creation sql statement
  private static final String DATABASE_CREATE = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME +
                                " (" +COLUMN_ID+ " VARCHAR," + COLUMN_DATE + "VARCHAR," +
                                COLUMN_LOCATION+" VARCHAR," +COLUMN_TIME +" VARCHAR);";

  private static final String DATABASE_INSERT = "INSERT INTO " +TABLE_NAME +
                                                " Values ('47688507','DEC-07-2012','MARVIN 203','20:00');";


  DatabaseHelper dbhelper;
  SQLiteDatabase db;

 public MySQLitehelper(Context ctx)
  {
      this.context = ctx;
  }

 private static class DatabaseHelper extends SQLiteOpenHelper {

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

 @Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub
    db.execSQL(DATABASE_CREATE);            //execute create table
    db.execSQL(DATABASE_INSERT);            //execute insert query

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub
    Log.w(MySQLitehelper.class.getName(),
            "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }
}


// open the DB
 public MySQLitehelper open() throws SQLException
 {
    System.out.println("Inside open function");
     db = dbhelper.getReadableDatabase();
    return this;
 }

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


public Cursor getAllrows()      //function to get all rows in the DB. Testing initially.
{
    //SQLiteDatabase db=this.getReadableDatabase();
    Cursor cur=db.query(TABLE_NAME,new String []{COLUMN_ID, COLUMN_DATE,
            COLUMN_LOCATION,COLUMN_TIME},null,null,null,null, null);

     return cur;
}

  }

/------------- SelectOptions.java class ------------------/

    public class SelectOptions extends Activity {

Button btnView, btnDrop, btnLocation;

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


    Intent intent = getIntent();
    String message = intent.getStringExtra(MainActivity.EXTRA_MESSAGE);

    btnView = (Button)findViewById(R.id.btnViewShift);
    btnDrop = (Button)findViewById(R.id.btnDropShift);
    btnLocation = (Button)findViewById(R.id.btnViewLocation);


    final MySQLitehelper helper = new MySQLitehelper(this);


    btnView.setOnClickListener(new View.OnClickListener() {

        @Override
        public void onClick(View arg0) {

            helper.open();
            Cursor c = helper.getAllrows();
              if (c.moveToFirst()) {
             do {

                 System.out.println("In Do while");
                 DisplayRecord(c); 

             } while (c.moveToNext());
          }
            helper.close(); 
            System.out.println("Out of Do");



        }  
    });


}

public void DisplayRecord(Cursor c)
{
    System.out.println("In side toast display function");
    Toast.makeText(this, "id: "+c.getString(0)+"\n"+
            "Date: "+c.getString(1)+"\n"+
            "Location: "+c.getString(2)+"\n"+
            "Time: "+c.getString(3), Toast.LENGTH_LONG).show();
}


    }

LogCat

11-29 06:18:01.393: I/System.out(8162): Inside open function
11-29 06:18:01.453: E/SQLiteLog(8162): (1) no such column: date
11-29 06:18:01.453: D/AndroidRuntime(8162): Shutting down VM
11-29 06:18:01.463: W/dalvikvm(8162): threadid=1: thread exiting with uncaught exception (group=0x40a70930)
11-29 06:18:01.503: E/AndroidRuntime(8162): FATAL EXCEPTION: main
11-29 06:18:01.503: E/AndroidRuntime(8162): android.database.sqlite.SQLiteException: no such column: date (code 1): , while compiling: SELECT GWid, date, location, time FROM UPDTable
11-29 06:18:01.503: E/AndroidRuntime(8162):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
11-29 06:18:01.503: E/AndroidRuntime(8162):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
11-29 06:18:01.503: E/AndroidRuntime(8162):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
11-29 06:18:01.503: E/AndroidRuntime(8162):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
11-29 06:18:01.503: E/AndroidRuntime(8162):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
11-29 06:18:01.503: E/AndroidRuntime(8162):     at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
11-29 06:18:01.503: E/AndroidRuntime(8162):     at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)

Answer 1

There is a syntax error in the line below . The format of the 20:00 parameter is invalid. Should be a string "20:00" or valid integer or float - 20.0

 enter code here`private static final String DATABASE_INSERT = "INSERT INTO " +
      TABLE_NAME +    " Values ('47688507','DEC-07-2012','MARVIN 203',20:00);";

Answer 2

you didn't initialized your dbhelper instance

write this statement into your MySQLitehelper() constructor

dbhelper = new DatabaseHelper(ctx);

complete code

public MySQLitehelper(Context ctx)
{
      this.context = ctx;
    dbhelper = new DatabaseHelper(ctx);
}

Edited

your insert query is wrong at last value 20:00 should be as '20:00'

private static final String DATABASE_INSERT = "INSERT INTO " +TABLE_NAME +
                                                " Values ('47688507','DEC-07-2012','MARVIN 203','20:00');";

Answer 3

you are getting the NULLPointer Exception because you haven't instantiate the dbHelper object in MySQLitehelper constructor.