SQL Exception: no such table

Put your problem here if it does not fit any of the other categories.

SQL Exception: no such table

Postby Dead_Jester » Fri Nov 12, 2010 2:02 am

Ok so 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
Code: Select all
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....
Code: Select all
   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
Code: Select all
databaseView.setText(db.printTable(tableName));
part and it throws an exception
Code: Select all
ERROR/AndroidRuntime(3440): android.database.sqlite.SQLiteException: no such table: test: , while compiling: SELECT * FROM test
pointing to the line in the printTable() method
Code: Select all
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?
Dead_Jester
Freshman
Freshman
 
Posts: 7
Joined: Fri Nov 05, 2010 10:06 pm

Top

Re: SQL Exception: no such table

Postby urbantrad » Fri Nov 12, 2010 2:10 pm

You should put much more functionality in your helper class, and there are much nicer ways to query the database. I found this a useful tutorial on how to properly use SQLite: http://www.screaming-penguin.com/node/7742
urbantrad
Senior Developer
Senior Developer
 
Posts: 104
Joined: Thu Sep 09, 2010 10:19 pm

Top

Return to Other Coding-Problems

Who is online

Users browsing this forum: Majestic-12 [Bot] and 11 guests