CODEWITHSUNDEEP
javaandroidsqlite
Robert
Robert

Reputation: 1726

Table not being created in SQLite

I'm attempting to create a SQLite db and populate it with data initially. When I run the program through the debugger in Eclipse, nothing appears on the screen but I get the following from LogCat:

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

Here's some of the code (I'll try to show only what's needed but if you need to see more, plz let me know):

MainActivity.java

public class MainActivity extends Activity {

//TAG for LogCat.
public static final String TAG = "SQLiteDebug";
private ListView mListViewIssues;

/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    //Load listview control.
    mListViewIssues = (ListView)findViewById(R.id.listIssues);

    //Create Data Creator.
    IssueInfoCreator creator = new IssueInfoCreator(this);

    creator.open();
    try {
        creator.insertRandomData();
    } catch (ParseException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    //add data to listview through adapter.
    mListViewIssues.setAdapter(new IssueInfoAdapter(this, creator.queryAll()));

    //Close connection.
    creator.close();
}
}

Code for IssueInfoCreator:

public class IssueInfoCreator {

//DB Adapter.
private DBAdapter mDBAdapter;

//Constructor.
public IssueInfoCreator(Context c){
    mDBAdapter = new DBAdapter(c);
}

//Open DBAdapter.
public void open(){
    mDBAdapter.open();
}

//Insert Random Data to get started.
public void insertRandomData() throws ParseException{
    long i = 999;
    Date dtDate = new Date();
    dtDate.parse("1/12/2012");

    mDBAdapter.insertIssue(i++, dtDate, "This is my ticket.  I'm having trouble starting my sim.");
    mDBAdapter.insertIssue(i++, dtDate, "I can't my sim to turn on, please help.");
    mDBAdapter.insertIssue(i++, dtDate, "Aircraft is not responding to callsign.");
    mDBAdapter.insertIssue(i++, dtDate, "TTS is not recognising when we attempt to land an aircraft.");
}

//Get all Issues from DB.
public List<IssueInfo> queryAll(){
    return mDBAdapter.fetchAllIssues();
}

//close connection.
public void close(){
    mDBAdapter.close();
}
}

Code for DBAdapter:

public class DBAdapter {

//Database fields.
public static final String ISSUES_TABLE = "Issues";
public static final String COL_ISSUE_ID = "_id";
public static final String COL_DATE_RECEIVED = "DateReceived";
public static final String COL_ISSUE_SUMMARY = "IssueSummary";

//Declarations.
private Context mContext;
private SQLiteDatabase mDB;
private DBHelper mDBHelper;

//Constructor.
public DBAdapter(Context c){
    mContext = c;
}

//Open DB Connection.
public DBAdapter open() throws SQLException{
    mDBHelper = new DBHelper(mContext);
    mDB = mDBHelper.getWritableDatabase();
    return this;
}

//Close DB Connection.
public void close(){
    mDBHelper.close();
}
//remaining code omitted for brevity...
}

and finally code for DBHelper:

public class DBHelper extends SQLiteOpenHelper {

//Constants
private static final String DB_NAME = "dbOpenIssues";
private static final int DB_VERSION = 1;
private static final String CREATE_DB_SQL = 
        "CREATE TABLE Issues ( " +
                "_id int PRIMARY KEY, " +
                "DateReceived datetime NOT NULL, " +
                "IssueSummary text NOT NULL " +
                ");";
private static final String DROP_TABLE = 
        "DROP TABLE IF EXISTS Issues";

public DBHelper(Context context) {
    super(context, DB_NAME, null, DB_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(CREATE_DB_SQL);
}

@Override   
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL(DROP_TABLE);
    onCreate(db);
}
}

If I run it with an emulator attached, I can see that the DB gets created in the file explorer but if I pull it down locally and open it with SQLiteMan, it only shows the android_metadata table.

Any ideas? again, sorry for the long post, just didn't want to leave anything out and have to repost 10 different times.

UPDATE:

One thing I tried was to print the SQL statement in LogCat but I noticed the onCreate (in DBHelper)never gets called:

public void onCreate(SQLiteDatabase db){
    Log.e("DB ERROR -- ", CREATE_DB_SQL);
    db.execSQL(CREATE_DB_SQL);
}

Upvotes: 0

Views: 829

Answers (3)

Luis
Luis

Reputation: 3571

onCreate() is called when the database is created for the first time. So, if the table failed to be created but the database has already been created, onCreate() will not be called again.

Delete the database and rerun the program with any changes you've made to see if it works now...

Upvotes: 0

Jesse J
Jesse J

Reputation: 542

Have you tried running your SQL Create statement in the Emulator using adb?

Here is more info on adb if you are not familiar : http://developer.android.com/guide/developing/tools/adb.html

When debugging sqlite problems on android, I find its easiest to try running the SQL commands manually from inside the emulator.

Upvotes: 0

Yury
Yury

Reputation: 20936

There is no datetime type in SQLite. To store data you can use TEXT. So in your case query can be the following:

private static final String CREATE_DB_SQL = 
        "CREATE TABLE Issues (" +
                "_id integer PRIMARY KEY, " +
                "DateReceived text NOT NULL, " +
                "IssueSummary text NOT NULL)";

Here is the link to data types.

Upvotes: 2

Related Questions