how to run a query in sqlite android

Question

I am trying to insert into the table one data by default when the user starts the program. I need to insert data into the table after it is created.

class AddressBookDatabaseHelper extends SQLiteOpenHelper {
   private static final String DATABASE_NAME = "AddressBook.db";
   private static final int DATABASE_VERSION = 1;

   // constructor
   public AddressBookDatabaseHelper(Context context) {
      super(context, DATABASE_NAME, null, DATABASE_VERSION);
   }

   // creates the contacts table when the database is created
   @Override
   public void onCreate(SQLiteDatabase db) {
      // SQL for creating the contacts table
      final String CREATE_CONTACTS_TABLE =
         "CREATE TABLE " + Contact.TABLE_NAME + "(" +
         Contact._ID + " integer primary key, " +
         Contact.COLUMN_NAME + " TEXT, " +
         Contact.COLUMN_PHONE + " TEXT, " +
         Contact.COLUMN_EMAIL + " TEXT, " +
         Contact.COLUMN_STREET + " TEXT, " +
         Contact.COLUMN_CITY + " TEXT, " +
         Contact.COLUMN_STATE + " TEXT, " +
         Contact.COLUMN_ZIP + " TEXT);";

      final String Beregero =
              "INSERT INTO " + Contact.TABLE_NAME + "(" +
                      Contact._ID + " 3, " +
                      Contact.COLUMN_NAME + " Patrice Beregeron, " +
                      Contact.COLUMN_PHONE + " 978-555-1212, " +
                      Contact.COLUMN_EMAIL + " pBeregero@BostonBruins.com, " +
                      Contact.COLUMN_STREET + " 1 causeway street, " +
                      Contact.COLUMN_CITY + " Boston, " +
                      Contact.COLUMN_STATE + " Mass, " +
                      Contact.COLUMN_ZIP + " 01236);";

      db.execSQL(CREATE_CONTACTS_TABLE); // create the contacts table
      db.execSQL(Beregero);
   }

   // normally defines how to upgrade the database when the schema changes
   @Override
   public void onUpgrade(SQLiteDatabase db, int oldVersion,
      int newVersion) { }
}

I found the following error when I try to execute the second query. any help on how I can create the single row. many thanks.

FATAL EXCEPTION: ModernAsyncTask #1
                                                                        Process: com.deitel.addressbook, PID: 13285
                                                                        java.lang.RuntimeException: An error occurred while executing doInBackground()
                                                                            at android.support.v4.content.ModernAsyncTask$3.done(ModernAsyncTask.java:142)
                                                                            at java.util.concurrent.FutureTask.finishCompletion(FutureTask.java:354)
                                                                            at java.util.concurrent.FutureTask.setException(FutureTask.java:223)
                                                                            at java.util.concurrent.FutureTask.run(FutureTask.java:242)
                                                                            at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1113)
                                                                            at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:588)
                                                                            at java.lang.Thread.run(Thread.java:818)
                                                                         Caused by: android.database.sqlite.SQLiteException: near "3": syntax error (code 1): , while compiling: INSERT INTO contacts(_id 3, name Patrice Beregeron, phone 978-555-1212, email pBeregero@BostonBruins.com, street 1 causeway street, city Boston, state Mass, zip 01236);
                                                                        #################################################################
                                                                        Error Code : 1 (SQLITE_ERROR)
                                                                        Caused By : SQL(query) error or missing database.
                                                                            (near "3": syntax error (code 1): , while compiling: INSERT INTO contacts(_id 3, name Patrice Beregeron, phone 978-555-1212, email pBeregero@BostonBruins.com, street 1 causeway street, city Boston, state Mass, zip 01236);)
                                                                        #################################################################
                                                                            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
                                                                            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1058)
                                                                            at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:623)
                                                                            at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
                                                                            at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
                                                                            at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
                                                                            at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1829)
                                                                            at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1760)
                                                                            at com.deitel.addressbook.data.AddressBookDatabaseHelper.onCreate(AddressBookDatabaseHelper.java:47)
                                                                            at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:251)
                                                                            at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:187)
                                                                            at com.deitel.addressbook.data.AddressBookContentProvider.query(AddressBookContentProvider.java:75)
                                                                            at android.content.ContentProvider.query(ContentProvider.java:1058)
                                                                            at android.content.ContentProvider$Transport.query(ContentProvider.java:245)
                                                                            at android.content.ContentResolver.query(ContentResolver.java:502)
                                                                            at android.support.v4.content.ContentResolverCompatJellybean.query(ContentResolverCompatJellybean.java:29)
                                                                            at android.support.v4.content.ContentResolverCompat$ContentResolverCompatImplJB.query(ContentResolverCompat.java:57)
                                                                            at android.support.v4.content.ContentResolverCompat.query(ContentResolverCompat.java:125)
                                                                            at android.support.v4.content.CursorLoader.loadInBackground(CursorLoader.java:59)
                                                                            at android.support.v4.content.CursorLoader.loadInBackground(CursorLoader.java:37)
                                                                            at android.support.v4.content.AsyncTaskLoader.onLoadInBackground(AsyncTaskLoader.java:296)
                                                                            at android.support.v4.content.AsyncTaskLoader$LoadTask.doInBackground(AsyncTaskLoader.java:54)
                                                                            at android.support.v4.content.AsyncTaskLoader$LoadTask.doInBackground(AsyncTaskLoader.java:42)
                                                                            at android.support.v4.content.ModernAsyncTask$2.call(ModernAsyncTask.java:128)
                                                                            at java.util.concurrent.FutureTask.run(FutureTask.java:237)
                                                                                ... 3 more

Answer 1

Your issue is that you have INSERT INTO contacts(_id 3,..... and also similar inclusions of the data to be inserted within the list of columns into which the data is to be inserted. The data itself should follow VALUES(, each value should separated from the next by a comma and strings should be enclosed in quotes. The last value should be followed by the closing parenthesis.

So instead of :-

INSERT INTO contacts(_id 3, name Patrice Beregeron, phone 978-555-1212, email pBeregero@BostonBruins.com, street 1 causeway street, city Boston, state Mass, zip 01236);

You should have :-

INSERT INTO contacts(_id,name,phone,email,street,city,state,zip)  VALUES(3,'Patrice Beregeron','978-555-1212','pBeregero@BostonBruins.com',`1 causeway street','Boston','Mass','01236');

i.e. the INSERT statement has the following syntax :-

SQL As Understood By SQLite- INSERT

P.S. the 'onCreate' method will only be run if the database doesn't exist. So until you fix all the statements within the onCreate method. You will need to delete the database in order to have the code rerun. You can either delete the App's data or uninstall the App to achieve this.

Additonal

You could alternatively utilise the insert convenience method, which builds the SQL on your behalf. The entire onCreate method could be :-

@Override
public void onCreate(SQLiteDatabase db) {
    // SQL for creating the contacts table
    final String CREATE_CONTACTS_TABLE =
        "CREATE TABLE " + Contact.TABLE_NAME + "(" +
        Contact._ID + " integer primary key, " +
        Contact.COLUMN_NAME + " TEXT, " +
        Contact.COLUMN_PHONE + " TEXT, " +
        Contact.COLUMN_EMAIL + " TEXT, " +
     Contact.COLUMN_STREET + " TEXT, " +
     Contact.COLUMN_CITY + " TEXT, " +
     Contact.COLUMN_STATE + " TEXT, " +
     Contact.COLUMN_ZIP + " TEXT);";
     db.execSQL(CREATE_CONTACTS_TABLE); // create the contacts table

     ContentValues cv = new ContentValues();
     cv.put(Contact._ID,3);
     cv.put(Contact.COLUMN_NAME,"Patrice Beregeron");
     cv.put(Contact.COLUMN_PHONE,"978-555-1212");
     cv.put(Contact.COLUMN_EMAIL,"pBeregero@BostonBruins.com");
     cv.put(Contact.COLUMN_STREET,"1 causeway street");
     cv.put(Contact.COLUMN_CITY,"Boston");
     cv.put(Contact.COLUMN_STATE,"Mass");
     cv.put(Contact.COLUMN_ZIP,"01236");
     db.insert(Contact.TABLE_NAME,null,cv);
}