OnCreate() ==> execSQL()

Problems with WiFi, SQLite ,Bluetooth, WiMax, Proxies, etc...

OnCreate() ==> execSQL()

Postby Exeter » Fri Jan 08, 2010 3:12 pm

Hi All,


My database isn't populating when created, causing a NullPointerException :oops:. The execSQL() statements that should be doing this are located in the onCreate() method of my DBAdapter;

DBAdapter
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.         public void onCreate(SQLiteDatabase db)
  2.         {
  3.             db.execSQL(Constants.DATABASE_CREATE_TABLE_X);
  4.             db.execSQL(Constants.DATABASE_POPULATE_TABLE_X);
  5.         }
Parsed in 0.030 seconds, using GeSHi 1.0.8.4


Constants
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.     public static final String DATABASE_CREATE_TABLE_SQUARES =
  2.         "CREATE TABLE IF NOT EXISTS  Squares (_ID INTEGER PRIMARY KEY AUTOINCREMENT, "
  3.         + "GameID INT NULL, PlayerName TEXT NULL, SquareNum INT(3) NULL, "
  4.         + "SquareRow INT(3) NULL, SquareColumn INT(3) NULL, _IMG TEXT NULL);";
  5.  
  6.  
  7. public static final String DATABASE_POPULATE_TABLE_X =
  8.     "INSERT INTO Table_X"
  9. + " SELECT NULL , 1 , 'Joe Blow' , 1 , 2 ,3 , 'TEXT'"
  10. + "UNION ALL "
  11. + "SELECT NULL , 1 , 'Joe Blow' , 1 , 2 , 3 , 'TEXT'"
  12.  
Parsed in 0.032 seconds, using GeSHi 1.0.8.4


Is onCreate(SQLiteDatabase db) the right place?


Regards,


Exeter
Exeter
Junior Developer
Junior Developer
 
Posts: 18
Joined: Tue Dec 22, 2009 4:17 pm

Top

Postby grzegorz.zajac » Fri Jan 08, 2010 3:51 pm

Hi,

You are creating a table Squares and trying to insert data into table Table_X.

Regards,
Grzesiek
grzegorz.zajac
Developer
Developer
 
Posts: 39
Joined: Wed Feb 18, 2009 8:37 pm
Location: Krakow/Poland

Postby Exeter » Fri Jan 08, 2010 5:18 pm

grzegorz.zajac wrote:Hi,

You are creating a table Squares and trying to insert data into table Table_X.

Regards,
Grzesiek



Hey Grzesiek

1000 apologies, that's an abstraction fail on my part. :oops: The Constants on my side are all [Squares] and the execSQL isn't working.

I've pulled the database off of the emulator, opened it in SQLiteManager and the table does not exist.

DBAdapter

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. package com.squarewolf;
  2.  
  3. import android.app.Application;
  4. import android.content.ContentValues;
  5. import android.content.Context;
  6. import android.database.Cursor;
  7. import android.database.SQLException;
  8. import android.database.sqlite.SQLiteDatabase;
  9. import android.database.sqlite.SQLiteOpenHelper;
  10. import android.util.Log;
  11.  
  12. public class DBAdapter extends Application{
  13.  
  14. private static final String TAG = "DBAdapter";
  15.  
  16. private final Context context;
  17. private DatabaseHelper DBHelper;
  18. private SQLiteDatabase db;
  19.  
  20.     public DBAdapter(Context mContext)
  21.     {
  22.         this.context = mContext;
  23.         DBHelper = new DatabaseHelper(context);
  24.     }
  25.  
  26.     private static class DatabaseHelper extends SQLiteOpenHelper
  27.     {
  28.         DatabaseHelper(Context context)
  29.         {
  30.             super(context, Constants.DATABASE_NAME, null, Constants.DATABASE_VERSION);
  31.         }
  32.  
  33.         @Override
  34.         public void onCreate(SQLiteDatabase db)
  35.         {
  36.             db.execSQL(Constants.DATABASE_CREATE_TABLE_SQUARES);
  37.             db.execSQL(Constants.DATABASE_POPULATE_TABLE_SQUARES);
  38.             //db.execSQL(Constants.DATABASE_CREATE_TABLE_PLAYERS);
  39.             //db.execSQL(Constants.DATABASE_CREATE_TABLE_TEAMS);
  40.             //db.execSQL(Constants.DATABASE_CREATE_TABLE_GAMES);
  41.         }
  42.  
  43.         @Override
  44.         public void onUpgrade(SQLiteDatabase db, int oldVersion,
  45.                               int newVersion)
  46.         {
  47.             Log.w(TAG, "Upgrading database from version " + oldVersion
  48.                   + " to "
  49.                   + newVersion + ", which will destroy all old data");
  50.             db.execSQL("DROP TABLE IF EXISTS Squares");
  51.             onCreate(db);
  52.         }
  53.     }
  54.  
  55.  
  56. //---opens the database---
  57.     public DBAdapter open() throws SQLException
  58.     {
  59.         db = DBHelper.getWritableDatabase();
  60.         return null;
  61.     }
  62.  
  63.     //---closes the database---
  64.     public void close()
  65.     {
  66.         DBHelper.close();
  67.     }
  68.  
  69.    
  70. }      
  71.  
  72.  
  73.  
  74.  
Parsed in 0.042 seconds, using GeSHi 1.0.8.4
Last edited by Exeter on Fri Jan 08, 2010 10:18 pm, edited 1 time in total.
Exeter
Junior Developer
Junior Developer
 
Posts: 18
Joined: Tue Dec 22, 2009 4:17 pm

Postby grzegorz.zajac » Fri Jan 08, 2010 5:32 pm

Hi again,

Calling execSQL() method in onCreate() is OK.
Try to run your SQL statement directly outside the emulator in your SQLiteManager and see if it is correct.

I am not sure if INT is the proper data type for SQLite. Try INTEGER instead.

Here is how I implemented onCreate() in one of my apps:

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.      @Override
  2.  
  3.     public void onCreate(SQLiteDatabase db) {
  4.  
  5.         Cursor c = db.rawQuery("SELECT name FROM sqlite_master WHERE type='table' " +
  6.  
  7.                         "AND name='" + TABLE_STOCKS + "'", null);
  8.  
  9.        
  10.  
  11.         try {
  12.  
  13.             if (c.getCount() == 0) {
  14.  
  15.                 db.execSQL("CREATE TABLE " + TABLE_STOCKS +
  16.  
  17.                             " (_id INTEGER PRIMARY KEY AUTOINCREMENT," +
  18.  
  19.                                     "nazwa TEXT, " +
  20.  
  21.                                     "czas TEXT, " +
  22.  
  23.                                     "rynek TEXT, " +
  24.  
  25.                                     "odniesienia REAL, " +
  26.  
  27.                                     "otwarcia REAL, " +
  28.  
  29.                                     "max REAL, " +
  30.  
  31.                                     "min REAL, " +
  32.  
  33.                                     "aktualny REAL, " +
  34.  
  35.                                     "zmiana REAL, " +
  36.  
  37.                                     "wolumen INTEGER, " +
  38.  
  39.                                     "obrot REAL, " +
  40.  
  41.                                     "wybrany INTEGER, " +
  42.  
  43.                                     "typ INTEGER, " +
  44.  
  45.                                     "alarm_max REAL, " +
  46.  
  47.                                     "alarm_min REAL, " +
  48.  
  49.                                     "alarm_typ INTEGER)");
  50.  
  51.             }
  52.  
  53.         }
  54.  
  55.         finally {
  56.  
  57.             c.close();
  58.  
  59.         }              
  60.  
  61.     }
Parsed in 0.040 seconds, using GeSHi 1.0.8.4


Regards,
Grzesiek
grzegorz.zajac
Developer
Developer
 
Posts: 39
Joined: Wed Feb 18, 2009 8:37 pm
Location: Krakow/Poland

onCreate() execSQL()

Postby Exeter » Fri Jan 08, 2010 6:53 pm

These work fine in SQLiteManager but are throwing errors in the emulator

IF TABLE EXISTS
PRIMARY KEY
AUTOINCREMENT

Thanks! :)

've backed out the DBAdapter as it contains the same DBHelper + all it did was initialize on startup)
However, now I'm getting an InstantationExeption with my ContentProvider.

In the IDE I'm being asked to abstract the Class and the following code gets inserted. The Class fails as an abstraction as well.


Abstracted Code Inserted

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.     @Override
  2.     public boolean onCreate() {
  3.         throw new UnsupportedOperationException("Not supported yet.");
  4.     }
Parsed in 0.036 seconds, using GeSHi 1.0.8.4



DBHelper


Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.     private static class DatabaseHelper extends SQLiteOpenHelper
  2.     {
  3.         DatabaseHelper(Context context)
  4.         {
  5.             super(context, Constants.DATABASE_NAME, null, Constants.DATABASE_VERSION);
  6.         }
  7.  
  8.         @Override
  9.         public void onCreate(SQLiteDatabase db)
  10.         {
  11.             db.execSQL(Constants.DATABASE_CREATE_TABLE_SQUARES);
  12.             db.execSQL(Constants.DATABASE_POPULATE_TABLE_SQUARES);
  13.             //db.execSQL(Constants.DATABASE_CREATE_TABLE_PLAYERS);
  14.             //db.execSQL(Constants.DATABASE_CREATE_TABLE_TEAMS);
  15.             //db.execSQL(Constants.DATABASE_CREATE_TABLE_GAMES);
  16.         }
  17.  
  18.         @Override
  19.         public void onUpgrade(SQLiteDatabase db, int oldVersion,
  20.                               int newVersion)
  21.         {
  22.             Log.w(TAG, "Upgrading database from version " + oldVersion
  23.                   + " to "
  24.                   + newVersion + ", which will destroy all old data");
  25.             db.execSQL("DROP TABLE IF EXISTS Squares");
  26.             onCreate(db);
  27.         }
  28.     }
  29.  
Parsed in 0.039 seconds, using GeSHi 1.0.8.4



Query Example utilizing DBHelper

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.      @Override
  2.     public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) throws SQLiteException
  3.      {
  4.         SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
  5.         qb.setTables(Constants.DATABASE_TABLE_SQUARES );
  6.         if(mUriMatcher.match(uri) == SINGLE_SQUARE) qb.appendWhere(Constants.KEY_SQUAREID + "=" + uri.getPathSegments().get(1));
  7.         String orderBy = sortOrder;
  8.         if (TextUtils.isEmpty(sortOrder)) orderBy = Constants.KEY_SQUARENUM;
  9.         SQLiteDatabase db = DBHelper.getReadableDatabase();
  10.         Cursor cursor = qb.query(db, projection, selection, selectionArgs, null, null, sortOrder);
  11.         cursor.setNotificationUri(getContext().getContentResolver(), uri);
  12.         return cursor;
  13.     }
Parsed in 0.038 seconds, using GeSHi 1.0.8.4



Any thoughts?


Thanks Again,


Exeter
Exeter
Junior Developer
Junior Developer
 
Posts: 18
Joined: Tue Dec 22, 2009 4:17 pm

Same problem

Postby Exeter » Fri Jan 08, 2010 11:57 pm

This person had the same problem. I'm going to follow the advice below:

Thanks for the offer of more help! Just the other day, I sat down with an Android pro, and he was able to fix my problem: do the getWritableDatabase() as part of the onCreate() rather than in the insert() – drewda Nov 25 at 3:10


http://stackoverflow.com/questions/1741 ... on-android
Exeter
Junior Developer
Junior Developer
 
Posts: 18
Joined: Tue Dec 22, 2009 4:17 pm

Top

Return to Networking & Database Problems

Who is online

Users browsing this forum: No registered users and 4 guests