Dead_Jester Dead_Jester - 28 days ago 8
Android Question

Android: Sqlite Exception no such table?

I have decided that I hate SQL and for that purpose I am trying to make a helper class that I can reuse over and over and never have to mess with it again but it isnt working!

Here is what I have right now:

DBAssistant.java

public class DBAssistant {
SQLiteDatabase db = null;


public DBAssistant (){
}

/** Opens database **/
public void openDB(Context context, String name){
db = new DBOpenHelper(context, name, DB_VERSION);
}

/** Creates the table "tableName" with the columns contained in "cols" **/
public void createTable(String tableName, String[] cols){
String table = "CREATE TABLE IF NOT EXISTS " + tableName + " (";
int numOfColums = cols.length;
columnNames = cols;
for (int i = 0; i < (numOfColums - 1); i++){
table += cols[i] + " VARCHAR, ";
}
table += cols[numOfColums - 1] + " VARCHAR);";
try{
db.execSQL("" + table);
System.out.println("ExecSQL:" + table);
} catch(Exception e){
System.out.println(e);
}
System.out.println("Column names: ");
for (String c : getColNames(tableName)){
System.out.print(c + " ");
}
}

/** Inserts "data" into new row **/
public void insertRow(String tableName, String[] data){
int cols = getCols(tableName);

System.out.println("if ((data.length) = " + (data.length) + ") ==" +
" (getCols(tableName) = " + getCols(tableName) + ")?");
if (data.length == cols){
System.out.println("Inside if loop");
String cmd = "INSERT INTO " + tableName + " (";
for (int i = 0; i < cols - 1; i++){
cmd += getColNames(tableName)[i] + ", ";
}
cmd += getColNames(tableName)[cols - 1] + ") VALUES ('";
for (int k = 0; k < data.length - 1; k++){
cmd += data[k] + "', '";
}
cmd += "');";
System.out.println(cmd);
db.execSQL(cmd);
}
else{
System.out.println("Inside else loop");
String dat = "";
String sCols = "";
for (String d : data)
dat += "'" + d + "'";
for (String c : getColNames(tableName))
sCols += "'" + c + "'";
System.out.println("-------------------");
System.out.println("[insertRow] ERROR: Number of elements in data[" + dat + "]");
System.out.println("doesnt match the number of columns [" + cols + "] in " + tableName);
System.out.println("-------------------");
}
}

/** Return String[] containing the column names in tableName **/
public String[] getColNames(String tableName){
Cursor c = db.rawQuery("SELECT * FROM " + tableName , null);
return c.getColumnNames();
}

/** Returns the number of rows in tableName **/
public int getCols(String tableName){
return getColNames(tableName).length;
}

/*** Other methods that have no relevance here .... ***/

private static class DBOpenHelper extends SQLiteOpenHelper {

DBOpenHelper(Context context, String dbName, int dbVersion) {
super(context, dbName, null, dbVersion);
}

@Override
public void onCreate(SQLiteDatabase arg0) {
// Do Nothing
}

@Override
public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
// Do Nothing
}
}
}


I have included several System.out.println() statements to help me debug the code and find problems so please ignore them. PrintDB is another test that I wrote that I am using to make sure everything is working. Now this is what I have written to make sure everything is working....

DBAssistant db = new DBAssistant();
String dbName = "testing";
String tableName = "test";
String[] cols = {"_num", "num_eng", "num_span"};
String[] row1 = {"1", "one", "uno"};
String[] row2 = {"2", "two", "dos"};
String[] row3 = {"3", "three", "tres"};
String[] row4 = {"4", "four", "quatro"};
String[] row5 = {"5", "five", "cinco"};
TextView databaseView = (TextView)findViewById(R.id.databaseView);

db.openDB(this, dbName);
db.createTable(tableName, cols);
db.insertRow(tableName, row1);
db.insertRow(tableName, row2);
db.insertRow(tableName, row3);
db.insertRow(tableName, row4);
db.insertRow(tableName, row5);
databaseView.setText(db.printTable(tableName));


Running this code everything goes great and all the System.out.println() statements have the right information in them until it gets to the
databaseView.setText(db.printTable(tableName));

part and it throws an exception


ERROR/AndroidRuntime(3440): android.database.sqlite.SQLiteException: no such table: test: , while compiling: SELECT * FROM test


Pointing to the line in the
printTable()
method:

Cursor c = dbr.rawQuery("SELECT * FROM " + tableName , null);


This has me so confused because that same line of code is used in the
getColNames()
method that was called several times before this and ran with no problem. Also if the table didn't exist wouldn't it have thrown an exception when I called
insertRow()
? I called that method 5 times in a row with not a single exception thrown! What is happening here?




Edit:



Implement onCreate() in your DBOpenHelper. Put there content of the createTable() method.


Why would it be necessary to create the table in the
onCreate()
method? If I were to do this would I be able to use my existing method to do so and just call it from this method?


And the second why you don't use content providers and uri >concept which is already SQLless?


Wha...? I have no idea. Please explain what that is or leave me a link to some sort of a tutorial.

Answer

Implement onCreate() in your DBOpenHelper. Put there content of the createTable() method.

See Dev Guide for reference.